V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
cyd
V2EX  ›  MySQL

请教一个关于和最左前缀原则矛盾的执行计划

  •  
  •   cyd · 2019-11-22 17:21:40 +08:00 · 4405 次点击
    这是一个创建于 1588 天前的主题,其中的信息可能已经有所发展或是发生改变。

    rt,mysql 版本 5.7,建立表:

    CREATE TABLE `just_for_test` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `str1` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
      `str2` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
      `str3` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
      `str4` varchar(20) COLLATE utf8mb4_bin DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `aaa` (`str1`,`str2`,`str3`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    

    简单插 2 条数

    INSERT INTO `temp_information_extract`.`just_for_test`( `str1`, `str2`, `str3`, `str4`) VALUES ( 'a', 'b', 'c', 'd');
    INSERT INTO `temp_information_extract`.`just_for_test`( `str1`, `str2`, `str3`, `str4`) VALUES ( 'a', 'c', 'b', 'd');
    

    执行下面查询语句

    explain select str1,str2,str3 from just_for_test where str2 > 'b'
    
    

    结果: 1 SIMPLE just_for_test index aaa 249 2 50.00 Using where; Using index

    md 表格好像用不了??不纠结了。。 我几个问题是,这个为何会使用了联合索引 aaa ?因为按照最左前缀的原则,没办法从第二个 key 开始查。 还是说查询优化器比较了使用全表扫描和这个 aaa 索引,发现 aaa 存在索引覆盖,比全表效率高,然后就硬用这个联合索引?这个联合索引还是得进行全部值遍历?

    11 条回复    2019-12-17 11:41:42 +08:00
    cyd
        1
    cyd  
    OP
       2019-11-22 17:28:25 +08:00
    顺带还有个疑惑,str1 = 'a' and str2 > 'b' and str3 = 'e',在执行计划里看,也是 Using where; Using index,这种情况下也是走了索引对吧?我看高性能 mysql 说是不走的?还是我记错了。。。
    Sasasu
        2
    Sasasu  
       2019-11-22 18:50:51 +08:00
    因为你的 str1 只有 a 一个值,查询转化成 where str1='a' and str2 > 'b',完全命中索引
    Leexiaobu
        3
    Leexiaobu  
       2019-11-22 19:29:24 +08:00
    @Sasasu 我刚刚试了在 str1 新增了 b 值,以及 str2 新增了 a 值,却还是走了联合索引
    xaplux
        4
    xaplux  
       2019-11-23 09:20:38 +08:00
    因为你的查询满足覆盖索引,你试试 select 把 str4 加上就无法走索引了
    taogen
        5
    taogen  
       2019-11-23 09:49:08 +08:00 via iPhone
    先随机插几百条记录,再试试看
    Aruforce
        6
    Aruforce  
       2019-11-23 11:32:51 +08:00 via Android
    关注…
    cyd
        7
    cyd  
    OP
       2019-11-23 20:11:07 +08:00
    感谢各位解答。
    @Sasasu 了解了。不过这个说法有合理的文章或者官方文档说明吗,mysql 怎么知道我 str1 只有 a ?如果我多造几个是否就一定不走?
    @taogen 了解了,我周一去公司造数据去。
    @xaplux 那我再额外问下,索引覆盖了,是否还是得扫描索引叶子节点的全部数据?是是的吧?不然你用第二个 key 如何能定位数据?以及,联合索引的非叶子节点是否如大部分百度说的只是存储第一个 key,还是某些大牛说存储这全部的 key ?
    xaplux
        8
    xaplux  
       2019-11-23 22:38:14 +08:00 via Android
    1. 个人感觉还是会扫描一遍索引树形结构
    2. 联合索引非叶子结点存储的是全部 key,对应实例就是('a','b','c')
    cyd
        9
    cyd  
    OP
       2019-11-24 12:50:43 +08:00
    @xaplux 感谢,同意第二点,不过第一点我觉得不会从根节点走,因为所有数据都需要扫描判断(而且树结构是冗余的),可能直接走叶子节点链表~?这个等大佬解答或者等我看完书看看有没有答案。
    Starxy
        10
    Starxy  
       2019-11-26 17:24:59 +08:00
    刚看到这一块,个人观点。参考高性能 Mysql 第三版 172 页,185 页。explain 的 extra 的 using index,只是说 select 的内容在索引树里面就能拿出来,也就是发起了一个被索引覆盖的查询。Extra 还有个 using where 不要忽略了。explain 的 type 的 index 只是说查询结果是按照索引的顺序来排序的。个人认为还是全表扫描,只不过是对索引树进行全表扫描,毕竟 select 只涉及索引字段。
    cyhulk
        11
    cyhulk  
       2019-12-17 11:41:42 +08:00
    这里不存在矛盾,这里使用了 index 是没有问题的,mysql 官方文档也说了,type--index 只是使用了索引,但并不表示索引就是呗正常使用,index 的速度接近于 all,但是为什么使用 index,是因为你查询的数据都已经在 index 中了,完全不需要再去主键记录查了,毕竟只要 index 就可以查,而且 index 读的数据量可能明显小于簇族,这也是 mysql 的优化。如果你吧 str4 也加上,type 就直接 all。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2759 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 00:20 · PVG 08:20 · LAX 17:20 · JFK 20:20
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.