MySQL不带条件查询时，会走那些索引？

摘要

本文介绍了开发过程中发现的一个奇怪的现象，不带条件查询时由于列个数不同导致查询时不一定会主键索引的情况。

我在开发过程中遇到了一个奇怪的现象，假如一个表只有两个字段，例如user_info表

其中，id为主键，name为一个二级索引

我在使用如下SQL不带任何条件查询的时候发现了一个奇怪的现象

explain select * from user_info

居然走了name这个index索引，而不是走主键索引。

很奇怪，我们都知道主键索引查询效率高于二级索引啊，为啥不走主键索引呢？

通过以下SQL看了下这个表使用的存储引擎

SHOW TABLE STATUS from yihuijia where Name='user_info';

结果显示使用的InnoDB,引擎没有设置错误。

去官方文档上看了下关于type的内容：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_type，发现了这样一句话。

It is possible for key to name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan.

看这句话的意思是二级索引可能会覆盖主键索引，也就是说，很有可能mysql在执行的时候发现二级索引执行效率比主键索引快，为啥会出现这种情况呢。

想了很久，感觉是不是有这种可能性：由于主键索引的数据结构中叶子节点保存的是整行数据，而二级索引的叶子节点仅仅保存的主键的值的原因？这样下来查询主键可能消耗的性能是不是会比二级的高？

如果是这种原因的话，就会产生另一个问题，如果表不止这两个列，并且存在多个二级索引，那应该走哪个索引呢？

基于这个问题，我做了个实验，在上面表的基础上加了一个字段age，也创建上索引，再次运行执行计划

神奇的一幕出现了

居然哪个索引都没走，并且看,并没有覆盖索引，简直是目瞪口呆啊，难道这样就表示走了主键索引？

简直不可思议，求大佬们在线解答这个疑问

自我解答：

后面研究了下InnoDB中聚簇索引和非聚簇索引（二级索引）的关系，发现了如下情况

1、假如查询的条件为非聚簇索引，那么正常是先根据非聚簇索引查询出叶子节点上的主键id的值，然后去聚簇索引上根据id查询对应的行数据。

2、由于聚簇索引的叶子节点保存的时整行数据，而非聚簇索引叶子节点保存的时主键的值，因此查询非聚簇索引的性能要高于聚簇索引。

3、由于本文中表只有两个字段，因此非聚簇索引上的叶子节点上包含的主键的值加上叶子节点本身的值，已经全部包含了该行的所有数据，因此当只有两个字段时，发生了覆盖索引（Extra值为Using index）

4、当表里面加了一个字段变成三个字段后，非聚簇索引叶子节点和叶子节点的主键值已经不能满足包含所有列的情况，因此此时将不会走非聚簇索引，有可能是去查聚簇索引（直接遍历叶子节点而不是从索引的根节点开始），因此没有发生覆盖索引

这里说下索引覆盖：正常来说如果查询二级索引，他会拿到叶子节点的主键的值，然后去主键索引中查询行数据的其他字段补充道select结果中，这个时候Extra中是不会有值的，但是如果通过子查询查出的结果已经满足select内容，那么它就不会再去查询主键索引了(发生了覆盖索引)，所以我认为这个才是本问题的关键。

 收藏 ( 0 )  赞 ( 0 )