反直觉SQL举例说明

版本MySQL5.7.34

有一个student表，字段如下

• id

• name

• age

• position

• sex

建立联合索引(name, age, position)

问以下的SQL哪些会使用这个索引？

1、explain select * from student where name > 'AAA'

2、explain select * from student where age = 25

3、explain select name, age, sex from student where name like '%A' and age = 25

4、explain select name, age from student where name like '%A' and age = 25

5、explain select name from student where name like '%A'

解释:

第一句和第二句很基础，第一句可以，第二句不行，因为违背最左匹配原则

第三句也不行，因为是左模糊匹配，并且索引没有覆盖要查询的字段

那第四句呢？

第四句很明显是左模糊匹配，一般情况下我们会认为无法使用索引，但是实际上是可以的，原因如下

• 首先这个索引覆盖了全部查询字段，在MySQL5.7以及之后的版本，优化器得到了较大的提升，会评估“全表扫描”以及“全索引扫描”的代价差距，一般情况下，如果索引能覆盖查询字段，会使用覆盖索引进行全索引扫描。

• 为什么全索引扫描要性能更好？因为覆盖索引包含的字段是少于聚簇索引的，MySQL中一页的大小是固定的，覆盖索引加载相同数量的页可以加载更多的数据，从而减少IO次数，而我们知道MySQL的主要瓶颈其实就是磁盘IO。

• 其次age使用了等值查询，所以可以在全索引扫描之后，查询到符合的记录，再使用age列进行索引使用，提高过滤age时的性能。

第五句SQL同理，全扫描覆盖索引代价是更低的，所以会使用覆盖索引。

如果例如第三句SQL，有一个字段在索引外部，无法使用覆盖索引，就算使用了，也还需要回表查询其它字段，MySQL就会直接全表扫描。