MySQL覆盖索引（Covering Index）

MySQL覆盖索引（Covering Index）

mysql高效索引之覆盖索引

概念

如果索引包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作

判断标准

使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询

注意

1、覆盖索引也并不适用于任意的索引类型，索引必须存储列的值

2、Hash 和full-text索引不存储值，因此MySQL只能使用B-TREE

3、并且不同的存储引擎实现覆盖索引都是不同的

4、并不是所有的存储引擎都支持它们

5、如果要使用覆盖索引，一定要注意SELECT 列表值取出需要的列，不可以是SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降，不能为了利用覆盖索引而这么做

InnoDB

1、覆盖索引查询时除了除了索引本身的包含的列，还可以使用其默认的聚集索引列

2、这跟INNOB的索引结构有关系，主索引是B+树索引存储，也即我们所说的数据行即索引，索引即数据

3、对于INNODB的辅助索引，它的叶子节点存储的是索引值和指向主键索引的位置，然后需要通过主键在查询表的字段值，所以辅助索引存储了主键的值

4、覆盖索引也可以用上INNODB 默认的聚集索引

5、innodb引擎的所有储存了主键ID，事务ID，回滚指针,非主键ID，他的查询就会是非主键ID也可覆盖来取得主键ID

覆盖索引是一种非常强大的工具，能大大提高查询性能，只需要读取索引而不用读取数据有以下一些优点
1、索引项通常比记录要小，所以MySQL访问更少的数据
2、索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O
3、大多数据引擎能更好的缓存索引，比如MyISAM只缓存索引
4、覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

在sakila的inventory表中，有一个组合索引(store_id,film_id)，对于只需要访问这两列的查询，MySQL就可以使用索引，如下

表结构

CREATE TABLE `inventory` (

`inventory_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,

`film_id` smallint(5) unsigned NOT NULL,

`store_id` tinyint(3) unsigned NOT NULL,

`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`inventory_id`),

KEY `idx_fk_film_id` (`film_id`),

KEY `idx_store_id_film_id` (`store_id`,`film_id`),

CONSTRAINT `fk_inventory_film` FOREIGN KEY (`film_id`) REFERENCES `film` (`film_id`) ON UPDATE CASCADE,

CONSTRAINT `fk_inventory_store` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON UPDATE CASCADE

) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8 |

查询语句

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: inventory

type: index

possible_keys: NULL

key: idx_store_id_film_id

key_len: 3

ref: NULL

rows: 4581

Extra: Using index

1 row in set (0.03 sec)

在大多数引擎中，只有当查询语句所访问的列是索引的一部分时，索引才会覆盖。但是，InnoDB不限于此，InnoDB的二级索引在叶子节点中存储了 primary key的值。因此，sakila.actor表使用InnoDB，而且对于是last_name上有索引，所以，索引能覆盖那些访问actor_id的查询，如下

mysql> EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER'\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: ref

possible_keys: idx_actor_last_name

key: idx_actor_last_name

key_len: 137

ref: const

rows: 2

Extra: Using where; Using index

1 row in set (0.00 sec)

使用索引进行排序

MySQL中，有两种方式生成有序结果集：一是使用filesort，二是按索引顺序扫描

利用索引进行排序操作是非常快的，而且可以利用同一索引同时进行查找和排序操作。当索引的顺序与ORDER BY中的列顺序相同且所有的列是同一方向(全部升序或者全部降序)时，可以使用索引来排序，如果查询是连接多个表，仅当ORDER BY中的所有列都是第一个表的列时才会使用索引，其它情况都会使用filesort

CREATE TABLE `actor` (

`actor_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(16) NOT NULL DEFAULT '',

`password` varchar(16) NOT NULL DEFAULT '',

PRIMARY KEY (`actor_id`),

KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

insert into actor(name,password) values ('cat01','1234567'),('cat02','1234567'),('ddddd','1234567'),('aaaaa','1234567');

1、 explain select actor_id from actor order by actor_id \G

mysql> explain select actor_id from actor order by actor_id \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 4

Extra: Using index

1 row in set (0.00 sec)

2、explain select actor_id from actor order by password \G

mysql> explain select actor_id from actor order by password \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 4

Extra: Using filesort

1 row in set (0.00 sec)

3、explain select actor_id from actor order by name \G

mysql> explain select actor_id from actor order by name \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: actor

type: index

possible_keys: NULL

key: name

key_len: 50

ref: NULL

rows: 4

Extra: Using index

1 row in set (0.00 sec)

当MySQL不能使用索引进行排序时，就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序，如果内存装载不下，它会将磁盘上的数据进行分块，再对各个数据块进行排序，然后将各个块合并成有序的结果集（实际上就是外排序）

对于filesort，MySQL有两种排序算法

1、两遍扫描算法(Two passes)

实现方式是先将须要排序的字段和可以直接定位到相关行数据的指针信息取出，然后在设定的内存（通过参数sort_buffer_size设定）中进行排序，完成排序之后再次通过行指针信息取出所需的Columns
注：该算法是4.1之前采用的算法，它需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。另一方面，内存开销较小

2、一次扫描算法(single pass)

该算法一次性将所需的Columns全部取出，在内存中排序后直接将结果输出
注：从 MySQL 4.1 版本开始使用该算法。它减少了I/O的次数，效率较高，但是内存开销也较大。如果我们将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存。在 MySQL 4.1 之后的版本中，可以通过设置 max_length_for_sort_data 参数来控制 MySQL 选择第一种排序算法还是第二种。当取出的所有大字段总大小大于 max_length_for_sort_data 的设置时，MySQL 就会选择使用第一种排序算法，反之，则会选择第二种。为了尽可能地提高排序性能，我们自然更希望使用第二种排序算法，所以在 Query 中仅仅取出需要的 Columns 是非常有必要的。

当对连接操作进行排序时，如果ORDER BY仅仅引用第一个表的列，MySQL对该表进行filesort操作，然后进行连接处理，此时，EXPLAIN输出“Using filesort”；否则，MySQL必须将查询的结果集生成一个临时表，在连接完成之后进行filesort操作，此时，EXPLAIN输出 “Using temporary;Using filesort”

作者：踏雪无痕
出处：http://www.cnblogs.com/chenpingzhao/

理解MySQL数据库覆盖索引

话说有这么一个表：

CREATE TABLE `user_group` (
`id` int(11) NOT NULL auto_increment,
`uid` int(11) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
KEY `group_id` (`group_id`),
) ENGINE=InnoDB AUTO_INCREMENT=750366 DEFAULT CHARSET=utf8

看AUTO_INCREMENT就知道数据并不多，75万条。然后是一条简单的查询：

SELECT SQL_NO_CACHE uid FROM user_group WHERE group_id = 245;

很简单对不对?怪异的地方在于：

　　如果换成MyISAM做存储引擎的时候，查询耗时只需要0.01s，用InnoDB却会是0.15s左右

　　如果只是就这么点差距其实不是什么大不了的事，但是真实的业务需求比这个复杂，造成的差距也很大：MyISAM只需要0.12s，InnoDB则需要2.2s.，最终定位到问题症结是在这条SQL。

　　Explain的结果是：

+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ 
| id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+ 
|  1 | SIMPLE      | user_group | ref  | group_id      | group_id | 4       | const | 5544 |       | 
+----+-------------+------------+------+---------------+----------+---------+-------+------+-------+

看起来已经用上索引了，而这条SQL语句已经简单到让我无法再优化了。最后请前同事Gaston诊断了一下，他认为：数据分布上，group_id相同的比较多，uid散列的比较均匀，加索引的效果一般，但是还是建议我试着加了一个多列索引：

ALTER TABLE user_group ADD INDEX group_id_uid (group_id, uid);

然后，不可思议的事情发生了……这句SQL查询的性能发生了巨大的提升，居然已经可以跑到0.00s左右了。经过优化的SQL再结合真实的业务需求，也从之前2.2s下降到0.05s。

再Explain一次：

+----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+ 
| id | select_type | table      | type | possible_keys         | key          | key_len | ref   | rows | Extra       | 
+----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+ 
|  1 | SIMPLE      | user_group | ref  | group_id,group_id_uid | group_id_uid | 4       | const | 5378 | Using index | 
+----+-------------+------------+------+-----------------------+--------------+---------+-------+------+-------------+

原来是这种叫覆盖索引(covering index)，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后再去查询数据，所以那是相当的快!!但是同时也要求所查询的字段必须被索引所覆盖到，在Explain的时候，输出的Extra信息中如果有“Using Index”，就表示这条查询使用了覆盖索引。

不用覆盖索引的情况下，mysql的存储引擎Myisam要比innodb查询速度快的原因是，这两者的数据存储方式不一样，myisan是顺序存储，而innodb聚簇索引，myisam的索引指针指向的直接就是数据的的屋里地址，而innodb的索引指向的是主键，所以需要进行二次查找(sql用到索引时将会是行锁，锁的粒度小)，降低了查询的效率，因此innodb引入了索引覆盖技术，提高查找的效率

本文主要概述MySQL的覆盖索引，以及几种常见的优化场景

内容概要

聚集索引和辅助索引

什么是覆盖索引

几种优化场景

总体建议

聚集索引和辅助索引

聚集索引（主键索引）

—innodb存储引擎是索引组织表，即表中的数据按照主键顺序存放。而聚集索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的即为整张表的记录数据

—聚集索引的叶子节点称为数据页，数据页，数据页！重要的事说三遍。聚集索引的这个特性决定了索引组织表中的数据也是索引的一部分。

辅助索引（二级索引）

—非主键索引

—叶子节点=键值+书签。Innodb存储引擎的书签就是相应行数据的主键索引值

辅助索引检索数据图示如下

由于检索数据时，总是先获取到书签值(主键值)，再返回查询，因此辅助索引也被称之为二级索引

什么是覆盖索引

覆盖索引（covering index）指一个查询语句的执行只需要从辅助索引中就可以得到查询记录，而不需要查询聚集索引中的记录。也可以称之为实现了索引覆盖。

那么，优点显而易见。辅助索引不包含一整行的记录，因此可以大大减少IO操作。覆盖索引是mysql dba常用的一种SQL优化手段

先看一个简单示例

从执行计划看到，这个SQL语句只通过索引，就取到了所需要的数据，这个过程，就称为索引覆盖

几种优化场景

1、无where条件的查询优化

如下这个查询

执行计划中，type为ALL，代表进行了全表扫描，扫描的行数也与表的行数一致

如何改进？优化措施很简单，就是对这个查询列建立索引。如下，

mysql> alter table t1 add key(staff_id);

我们再看一下优化之后的效果

执行计划

mysql> explain select sql_no_cache count(staff_id) from t1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: staff_id
      key_len: 1
          ref: NULL
         rows: 1023849

1 row in set (0.00 sec)

Using index表示使用到了索引

查询消耗

从时间消耗上来看，才100W的数据，已经有了比较明显的差别了

执行计划解读如下：

Possible_keys为null，说明没有where条件时优化器无法通过索引检索数据；

但是这里使用了索引的另外一个优点，即从索引中获取数据，减少了读取的数据块的数量

无where条件的查询，可以通过索引来实现索引覆盖查询，但前提条件是，查询返回的字段数足够少，更不用说select *之类的了。毕竟，建立key length过长的索引，始终不是一件好事情。

2、二次检索优化

如下这个查询，

 mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.13 sec)

执行计划：

mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id
          key: inventory_id
      key_len: 3
          ref: NULL
         rows: 153734
        Extra: Using index condition
1 row in set (0.00 sec)

从执行计划，我们看到，这个SQL其实是使用到了索引的，虽然查询的数据量很大，但是相对比全表扫描的性能消耗，优化器还是选择了索引。

更优的改进措施？

从上面执行计划中，我们发现Extra信息为Using index condition而不是Using index，这说明，使用的检索方式为二级检索，即79999个书签值被用来进行回表查询。可想而知，还是会有一定的性能消耗的

尝试针对这个SQL建立联合索引，如下

mysql> alter table t1 add key(inventory_id,rental_date);

这个联合索引前置列为where子句的检索字段，第二个字段为查询返回的字段。下面来看下效果如何。

为避免优化器对索引的选择出现偏差，我们首先收集一下统计信息

mysql> analyze table t1\G
*************************** 1. row ***************************
   Table: sakila.t1
      Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.03 sec)

执行计划

mysql> explain select sql_no_cache rental_date from t1 where inventory_id<80000\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: inventory_id,inventory_id_2
          key: inventory_id_2
      key_len: 3
          ref: NULL
         rows: 162884

1 row in set (0.00 sec)

同样是使用索引，但这里的提示信息为Using index而不是Using index condition。这代表没有了回表查询的过程，也就是实现了索引覆盖

查询消耗

mysql> select sql_no_cache rental_date from t1 where inventory_id<80000;
…
…
| 2005-08-23 15:08:00 |
| 2005-08-23 15:09:17 |
| 2005-08-23 15:10:42 |
| 2005-08-23 15:15:02 |
| 2005-08-23 15:15:19 |
| 2005-08-23 15:16:32 |
+---------------------+
79999 rows in set (0.09 sec)

从执行时间上来看，快了大约40ms，虽然只有40ms，但在实际的生产环境下，却可能会因系统的总体负载被无限放大。

和前面场景限制类似，当where条件与查询字段总数较少的情况下，使用这种优化建议，是个不错的选择。

3、分页查询优化

分页查询的优化，相信大部分的DBA同学都碰到过，通常比较常规的优化手段就是查询改写，这里主要介绍一下新的思路，就是通过索引覆盖来优化

如下这个查询场景

mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.75 sec)

在未优化之前，我们看到它的执行计划是如此的糟糕

mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1023675

1 row in set (0.00 sec)

全表扫描，加上额外的排序，相信产生的性能消耗是不低的

如何通过覆盖索引优化呢？

我们创建一个索引，包含排序列以及返回列，由于tid是主键字段，因此，下面的复合索引就包含了tid的字段值

mysql> alter table t1 add index liu(inventory_id,return_date);
Query OK, 0 rows affected (3.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> analyze table t1\G
*************************** 1. row ***************************
   Table: sakila.t1
      Op: analyze
Msg_type: status
Msg_text: OK
1 row in set (0.04 sec)

那么，效果如何呢？

mysql> select tid,return_date from t1 order by inventory_id limit 50000,10;
+-------+---------------------+
| tid   | return_date         |
+-------+---------------------+
| 50001 | 2005-06-17 23:04:36 |
| 50002 | 2005-06-23 03:16:12 |
| 50003 | 2005-06-20 22:41:03 |
| 50004 | 2005-06-23 04:39:28 |
| 50005 | 2005-06-24 04:41:20 |
| 50006 | 2005-06-22 22:54:10 |
| 50007 | 2005-06-18 07:21:51 |
| 50008 | 2005-06-25 21:51:16 |
| 50009 | 2005-06-21 03:44:32 |
| 50010 | 2005-06-19 00:00:34 |
+-------+---------------------+
10 rows in set (0.03 sec)

可以发现，添加复合索引后，速度提升0.7s！

我们看一下改进后的执行计划

mysql> explain select tid,return_date from t1 order by inventory_id limit 50000,10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: liu
      key_len: 9
          ref: NULL
         rows: 50010

1 row in set (0.00 sec)

执行计划也可以看到，使用到了复合索引，并且不需要回表

接下来，我们对比一下，索引覆盖与常规优化手段的效果差异

为了让结果更明显，我将查询修改为 limit 800000,10

现在看一下，通过覆盖索引查询的性能消耗

mysql> select tid,return_date from t1 order by inventory_id limit 800000,10;
+--------+---------------------+
| tid    | return_date         |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+

与之对比的是如下改写SQL方式

改写后的sql如下，思想是通过索引消除排序

select a.tid,a.return_date from  t1 a
inner join
(select tid from t1 order by  inventory_id limit 800000,10) b on a.tid=b.tid;

并在此基础上，我们为inventory_id列创建索引，并删除之前的覆盖索引

mysql> alter table t1 add index idx_inid(inventory_id),drop index liu;

然后收集统计信息。

查询消耗如下

mysql> select a.tid,a.return_date from  t1 a inner join  (select tid from t1 order by  inventory_id limit 800000,10) b on a.tid=b.tid;
+--------+---------------------+
| tid    | return_date         |
+--------+---------------------+
| 800001 | 2005-08-24 13:09:34 |
| 800002 | 2005-08-27 11:41:03 |
| 800003 | 2005-08-22 18:10:22 |
| 800004 | 2005-08-22 16:47:23 |
| 800005 | 2005-08-26 20:32:02 |
| 800006 | 2005-08-21 14:55:42 |
| 800007 | 2005-08-28 14:45:55 |
| 800008 | 2005-08-29 12:37:32 |
| 800009 | 2005-08-24 10:38:06 |
| 800010 | 2005-08-23 12:10:57 |
+--------+---------------------+

可以看到，这种优化手段较前者时间消耗多了大约140ms。

这种优化手段虽然使用索引消除了排序，但是还是要通过主键值回表查询。因此，在select返回列较少或列宽较小的时候，我们可以通过建立复合索引的方式优化分页查询，效果更佳，因为它不需要回表！

总体建议

索引具有以下两大用处：

1、通过索引检索仅需要数据

2、从索引中直接获取查询结果

覆盖索引的优势，就是利用到索引的第二大用处，在某些场景下，具有意想不到的优化效果。个人总结如下：

Select查询的返回列包含在索引列中
有where条件时，where条件中要包含索引列或复合索引的前导列
查询结果的总字段长度可以接受

from:

mysql延迟查询, 覆盖索引使用例子

引用自 'mysql高性能' 5.3.6章节

不能使用覆盖索引的情况 :

解决办法 :

About Me

.............................................................................................................................................

● 本文整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群1 小麦苗的DBA宝典QQ群2 小麦苗的微店

.............................................................................................................................................