一种强大的MySQL索引—覆盖索引

通常大家都会根据査询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方 面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一 种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不 再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要 再回表査询呢？如果一个索引包含（或者说覆盖）所有需要査询的字段的值，我们就称＜3S 之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果査询只需要扫描索引 而无须回表，会带来多少好处：
• 索引条目通常远小于数据行大小，所以如果只需要读取索引，那MySQL就会极大 地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花 费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小， 更容易全部放入内存中（这对于MylSAM尤其正确，因为MylSAM能压缩索引以 变得更小）。

• 因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的 范围査询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎，例 如MylSAM和Percona XtraDB,甚至可以通过OPTIMIZE命令使得索引完全顺序排 列，这让简单的范围査询能使用完全顺序的索引访问。

• 一些存储引擎如MylSAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因 此要访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统 调用占了数据访问中的最大开销的场景。

• 由于InnoDB的聚簇索弓［,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在 叶子节点中保存了行的主键值，所以如果二级主键能够覆盖査询，则可以避免对主 键索引的二次査询。
在所有这些场景中，在索引中满足査询的成本一般比査询行要小得多。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索 引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做 覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都 支持覆盖索引（在写作本书时，Memory存储引擎就不支持覆盖索引）。
当发起一个被索引覆盖的査询（也叫做索引覆盖査询）时，在EXPLAIN的Ext「a列可以 看到“Using index”的信息注"。例如，表sakila.inventory有一个多列索引（store id, film_id）o MySQL如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** ] row ***************************
IJTD>
id: select type: table: type: possible_keys: key: key_len: ref: rows: Extra:
1
SIMPLE inventory index
NULL idx_store_id_film_id
3	~ ~
NULL
4673
Using index

索引覆盖査询还有很多陷阱可能会导致无法实现优化。MySQL査询优化器会在执行查 询前判断是否有一个索引能进行覆盖。假设索引覆盖了 WHERE条件中的字段，但不是整 个査询涉及的字段。如果条件为假（false）, MySQL 5.5和更早的版本也总是会回表获 取数据行，尽管并不需要这一行且最终会被过滤掉。

概念

如果索引包含所有满足查询需要的数据，则该索引称为覆盖索引(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 |

查询语句

```c
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的查 询，如下


```c
```c
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




```c
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”