MySQL回表查询与索引覆盖

前言
  InnoDB引擎中，B+树索引可以分为聚簇索引和辅助索引两大类。在介绍 “回表” 和 “索引覆盖” 之前，我们先来了解一下这两个概念。

聚簇索引

  聚簇索引也叫聚集索引，它并不是一种单独的索引类型，在聚簇索引的叶子页中，保存了整张表的行数据信息，所以也将聚簇索引的叶子节点称为数据页。
  名词 “聚簇” 表示数据行和相邻的键值紧凑的存储在一起。因为不能同时把数据行存储在两个不同的地方，所以一个表只能有一个聚簇索引。

InnoDB选取聚簇索引的规则如下：

1. 如果表中定义了主键，则主键为聚簇索引；
2. 如果没有主键，选择第一个非空的唯一索引为聚簇索引；
3. 如果以上都没有，InnoDB会隐式定义一个6字节的rowid主键来作为聚簇索引。

辅助索引

  辅助索引也叫非聚簇索引、非聚集索引、二级索引等。辅助索引跟聚簇索引的区别在于，聚簇索引叶子节点中保存了完整的行数据，而辅助索引叶子节点中保存的是聚簇索引中的索引键值。
  辅助索引的存在不影响数据在聚簇索引中的组织，因此每张表中可以有多个辅助索引。

假如我们有这样一个表，建表语句如下：

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(32) NOT NULL COMMENT '姓名',
  `age` tinyint(3) unsigned NOT NULL COMMENT '年龄',
  `gender` tinyint(3) unsigned NOT NULL COMMENT '性别：1男，0女',
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中数据：

表中有两个索引，一个是主键索引 id，一个是普通索引 (name,age)，根据前边介绍的聚簇索引和辅助索引的定义，这里主键索引就是聚簇索引，普通索引就是我们的辅助索引。

两棵索引树的示意图如下

回表查询

那么，什么是回表查询呢？

假如我们需要查询姓名为Lucy的用户信息

select * from t_user where name = 'Lucy';

会先通过(name,age)这课索引树找到主键id（这里是3），再根据id=3，回到主键索引树中，找到对应的行数据信息（“Lucy”,17,0）,这个过程，就叫做 “回表查询”。通过执行计划，也能看到使用了辅助索引 idx_name。

回表查询需要扫描两次索引树，即先扫描辅助索引树，再扫描聚簇索引树，故它的性能比扫一遍索引树低。以上边的查询为例，辅助索引树高度为2，聚簇索引树高度也为2，因此一共需要4次逻辑IO才能得到最终的数据页。

索引覆盖
  从上边我们知道了，通过辅助索引查询数据时，需要回到聚簇索引再扫描一遍，也就是需要 “回表查询” 。那有没有不需要回表查询的情况呢？
  InnoDB存储引擎支持 “索引覆盖” （也叫做 “覆盖索引” ），即从索引中就可以得到查询结果，从而不需要查询聚簇索引中的行数据信息。

索引覆盖可以带来很多的好处：

• 辅助索引不包含行数据的所有信息，故其大小远小于聚簇索引，因此可以减少大量的IO操作。
• 索引覆盖只需要扫描一次索引树，不需要回表扫描聚簇索引树，所以性能比回表查询要高。
• 索引中列值是按顺序存储的，索引覆盖能避免范围查询回表带来的大量随机IO操作。

判断一条语句是否用到索引覆盖

索引覆盖有这么多的好处，那平常开发中，我们怎么知道语句是否用到了索引覆盖呢？

我们来看下这条语句的执行计划，

EXPLAIN select * from t_user where name = 'Lucy';

通过执行计划，显示用到了索引 idx_name ，也就是(name,age) 这两字段对应的辅助索引。

对这条语句做下修改，再来看下执行计划

EXPLAIN select id,name from t_user where name = 'Lucy';

执行计划中有了变化啊，最后一列Extra中多了 Using index 。而这里Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到，也就是用到了索引覆盖。

这也容易理解，我们修改语句后，需要查询的只有 id 和 name ，而这俩字段在我们的辅助索引(name,age)树中都有，name 就是索引键值的一部分，id保存在叶子节点中，所以也就不需要再回表查询了。

会用到索引覆盖的SQL示例：

我们来看下这些例子

EXPLAIN select id,name,age from t_user where name = 'Lucy';
EXPLAIN select id,name,age from t_user where name = 'Lucy' and age = 17;
EXPLAIN select count(*) from t_user where name = 'Lucy';

这三条语句应该不难分析，name 就是索引键值的一部分，符合最左匹配原则，并且想要查询的数据从索引树中就能拿到。所以用到了索引覆盖。

EXPLAIN select id,name,age from t_user where age = 17;
EXPLAIN select count(*) from t_user ;

上边这两条语句，也用到了索引覆盖。

WHAT ？有同学可能就发现问题了，不对吧？第一条语句查询条件 where age = 17 不符合最左匹配原则，没办法使用索引啊。第二条语句都没有查询条件，也没办法使用索引啊。

别急，我们先来看下执行计划。


通过执行计划，我们会发现它们的 possible_keys 这列都没有值。执行计划中， possible_keys 这一列表示的是可能用到的索引，而我们之前截图中，这一列中都是有值的。但 key 这一列中都有值 idx_name， 并且 Extra 中也都有 Using index ，说明用到了索引覆盖。

真实原因是这样的。MySQL优化器分析发现，查询语句无法使用到索引，只能通过全表扫描了。不过还发现一点，不管是只扫描聚簇索引对应的表，还是只扫描辅助索引对应的表，最终都能得到查询结果。而辅助索引对应的表远小于聚簇索引对应的表，这样就可以减少IO操作，所以优化器就选择了全表扫描辅助索引对应的表，也就用到了索引覆盖。

EXPLAIN select id from t_user where id = '3';
EXPLAIN select count(*) from t_user where id = '3';

再继续来看上边这两条语句。先来说下结论，这两条也用到了索引覆盖。

看下执行计划

这两条语句都是通过 id 来进行查询，所以会用到主键索引，但是为什么也会用到索引覆盖呢？它们已经不需要回表了呀？

做出回答前，我们先再来看一条SQL执行计划

EXPLAIN select name from t_user where id = '3';

这条语句跟前边的差别只在于，前边只查询了 id，而这里只查询了 name 。执行计划中就看到，这条查询 name 的语句就没有用到索引覆盖。

我们再来体会一下：“索引覆盖” 指的是，从索引中就可以得到查询结果，从而不需要查询聚簇索引中的行数据信息。

也就是说，如果只查询 ‘id’ 的话，从聚簇索引中就能得到结果，最终也不需要再查询行数据信息，也就是用到了索引覆盖。同样的，count(*) 也是这样的原理。

总结

  索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。
  通常大家会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。

  最后，希望博主的文章能给大家带来一些帮助。也希望能跟朋友们一起互相学习，共同进步，加油！！