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

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

2019-11-22 17:28:25 +08:00

顺带还有个疑惑，str1 = 'a' and str2 > 'b' and str3 = 'e'，在执行计划里看，也是 Using where; Using index，这种情况下也是走了索引对吧？我看高性能 mysql 说是不走的？还是我记错了。。。

Sasasu

2019-11-22 18:50:51 +08:00

因为你的 str1 只有 a 一个值，查询转化成 where str1='a' and str2 > 'b'，完全命中索引

DengDDDD

2019-11-22 19:29:24 +08:00

@Sasasu 我刚刚试了在 str1 新增了 b 值，以及 str2 新增了 a 值，却还是走了联合索引

xaplux

2019-11-23 09:20:38 +08:00

因为你的查询满足覆盖索引，你试试 select 把 str4 加上就无法走索引了

taogen

2019-11-23 09:49:08 +08:00 via iPhone

先随机插几百条记录，再试试看

Aruforce

2019-11-23 11:32:51 +08:00 via Android

关注…

cyd

2019-11-23 20:11:07 +08:00

感谢各位解答。
@Sasasu 了解了。不过这个说法有合理的文章或者官方文档说明吗，mysql 怎么知道我 str1 只有 a ？如果我多造几个是否就一定不走？
@taogen 了解了，我周一去公司造数据去。
@xaplux 那我再额外问下，索引覆盖了，是否还是得扫描索引叶子节点的全部数据？是是的吧？不然你用第二个 key 如何能定位数据？以及，联合索引的非叶子节点是否如大部分百度说的只是存储第一个 key，还是某些大牛说存储这全部的 key ？

xaplux

2019-11-23 22:38:14 +08:00 via Android

1. 个人感觉还是会扫描一遍索引树形结构
2. 联合索引非叶子结点存储的是全部 key，对应实例就是('a','b','c')

cyd

2019-11-24 12:50:43 +08:00

@xaplux 感谢，同意第二点，不过第一点我觉得不会从根节点走，因为所有数据都需要扫描判断（而且树结构是冗余的），可能直接走叶子节点链表~？这个等大佬解答或者等我看完书看看有没有答案。

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

2019-12-17 11:41:42 +08:00

这里不存在矛盾，这里使用了 index 是没有问题的，mysql 官方文档也说了，type--index 只是使用了索引，但并不表示索引就是呗正常使用，index 的速度接近于 all，但是为什么使用 index，是因为你查询的数据都已经在 index 中了，完全不需要再去主键记录查了，毕竟只要 index 就可以查，而且 index 读的数据量可能明显小于簇族，这也是 mysql 的优化。如果你吧 str4 也加上，type 就直接 all。