原文：http://blog.itpub.net/22664653/viewspace-774667/ 一个包含查询所需的字段的索引称为 covering index 覆盖索引。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提供效率。 当你对一个sql 使用explain statement 查看一个sql的执行计划时，在EXPLAIN的Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引。
【使用场景】生产过程中遇到的一个例子，且先不讨论 count(字段)还是 count(*) root@yang 06:38:34>select count(o.order_id) as cnt from `order` o WHERE o.settle_type = 2 and o.order_status = 2 and o.buyer_id=1979459339672858; +------+| cnt |+------+| 7574 |+------+1 row in set (0.22 sec)
root@yang 06:36:38>explain select count(o.order_id) as cnt from `order` o WHERE o.settle_type = 2 and o.order_status = 2 and o.buyer_id=1979459339672858; +----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+| 1 | SIMPLE | o | index_merge | buyer_id,order_status | buyer_id,order_status | 9,1 | NULL | 3852 | Using intersect(buyer_id,order_status); Using where |+----+-------------+-------+-------------+-----------------------+-----------------------+---------+------+------+-----------------------------------------------------+1 row in set (0.00 sec)
上述select语句的执行计划使用了index_merge 索引聚合，整体sql执行花费0.22s 。根据查询条件对表结构进行如下调整：root@yang 06:33:14>alter table `ordert` add key ind_od_bid_st_ty_oid(`buyer_id`,`order_status`,`settle_type`,`order_id`);Query OK, 0 rows affected (3.00 sec)Records: 0 Duplicates: 0 Warnings: 0
root@yang 06:38:50>explain select count(o.order_id) as cnt from `ordert` o WHERE o.settle_type = 2 and o.order_status = 2 and o.buyer_id=1979459339672858;+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+| 1 | SIMPLE | o | ref | ind_od_bid_st_ty_oid | ind_od_bid_st_ty_oid | 11 | const,const,const | 15242 | Using where; Using index |+----+-------------+-------+------+----------------------+----------------------+---------+-------------------+-------+--------------------------+1 row in set (0.00 sec)执行计划使用了 using index --覆盖索引而且执行时间由0.22s，下降到小于0.01s。root@yang 06:39:06>select count(o.order_id) as cnt from `ordert` o WHERE o.settle_type = 2 and o.order_status = 2 and o.buyer_id=1979459339672858; +------+| cnt |+------+| 7574 |+------+1 row in set (0.00 sec)
【覆盖索引的限制】遇到以下情况，执行计划不会选择覆盖查询：1select选择的字段中含有不在索引中的字段 ，也即索引没有覆盖全部的列。2 where 条件中不能含有对索引进行like的操作。
root@odbsyunying 08:18:15>explain select count(*) as cnt from `ordert` o WHERE o.settle_type = 2 > and o.order_status = 2 > and o.buyer_id like '1979459339672858' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: o type: indexpossible_keys: ind_od_bid_st_ty_oid key: ind_od_bid_st_ty_oid key_len: 19 ref: NULL rows: 767466 ---覆盖索引扫描 Extra: Using where; Using index1 row in set (0.00 sec)