浅聊 MySQL索引覆盖

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并 且需要返回的列，在该索引中已经全部能够找到 。

现在有一张用户表tb_user

索引情况：

接下来，我们来看一组SQL的执行计划，看看执行计划的差别，然后再来具体做一个解析。

Using whereUsing Index：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需 要回表查询数据

Using index condition：查找使用了索引，但是需要回表查询数据

因为，在tb_user表中有一个联合索引 idx_user_pro_age_sta，该索引关联了三个字段 profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主 键id。 所以当我们查询返回的数据在 id、profession、age、status 之中，则直接走二级索引 直接返回数据了。 如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。 而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表 查询（除非是根据主键查询，此时只会扫描聚集索引）。

为了大家更清楚的理解，什么是覆盖索引，什么是回表查询，我们一起再来看下面的这组SQL的执行过 程。

id是主键，是一个聚集索引。 name字段建立了普通索引，是一个二级索引（辅助索引）。

B. 执行SQL : select * from tb_user where id = 2

根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

C. 执行SQL：selet id,name from tb_user where name = 'Arm'

虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索 引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高。

D. 执行SQL：selet id,name,gender from tb_user where name = 'Arm'

由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相 对较差一点。

通常开发人员会根据查询的where条件来创建合适的索引，但是优秀的索引设计应该考虑到整个查询。其实mysql可以使用索引来直接获取列的数据。如果索引的叶子节点包含了要查询的数据，那么就不用回表查询了，也就是说这种索引包含（亦称覆盖）所有需要查询的字段的值，我们称这种索引为覆盖索引。

注：引入数据表t_user,插入约1千万条记录，用作下文例子使用。

explain命令是 查看查询优化器如何决定执行查询的主要方法 。要使用此命令，只需要在select关键字之前添加这个命令即可。当执行查询时，它会返回信息，显示出执行计划中的每一部分和执行的次序，而 并非真正执行这个查询 。如图1.1所示，是执行explain的显示结果，其中sql语句中的\G表示将输出按列显示

当发起一个被索引覆盖的查询时，在explain的Extra列可以看到 Using index 的标识。

场景：查询表中name列有值的记录数

如上图2.1所示，其中查询语句用SQL_NO_CACHE关键字来禁止缓存查询结果。此查询耗时6.43秒。从图3的执行计划得知，type:ALL，表示MySQL扫描整张表，从头到尾去找到需要的行。下面对此查询列建立索引。

如图2.3所示，为name列建立索引之后，重新执行查询。此时查询耗时3.80秒，比未加索引提高了2.63秒。从图2.5的查询计划可知，type：index，这个跟全表扫描一样，只是MySQL扫描表时按索引次序进行而不是行。但是看到Extra：Using index，说明MySQL正在使用覆盖索引，它只扫描索引的数据，而不是按索引次序的每一行。它比按索引次序全表扫描的开销少很多。

3分页查询email

从图3.1可知，分页查询耗时53.99，如图3.2所示，type：All，说明MySQL进行了全表扫描。下面在password和email列上创建联合索引。

如图3.4所示，分页查询基本不耗时间。从图3.5可知，Extra：Using index，MySQL使用了覆盖索引进行查询。查询效率得到极大的提升。

---