MySQL覆盖索引

正确地创建和使用索引是实现高性能查询的基础。本文简要介绍MySQL覆盖索引。

覆盖索引

根据查询的WHERE条件创建合适的索引，不过只是索引优化的一个方面。设计优秀的索引还应该考虑到整个查询，MySQL也可以使用索引来直接获取列的数据，这样就不需要读取数据行了。
如果一个索引包含或覆盖所有需要查询的字段的值，我们就成之为“覆盖索引”。

覆盖索引的优点

被索引覆盖的查询即为索引覆盖查询。索引覆盖查询只需要扫描索引而无需回表，不仅能够极大地提高性能，而且也有很大其他好处：

• 索引条目通常远小于数据行大小，所以如果只需要读取索引，MySQL就会极大地减少数据访问量。因为索引比数据更小，就更容易把全部索引放入内存中。
• 因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取数据的I/O要少得多。
• 一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
• 由于InnoDB的聚簇索引，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

覆盖索引的限制

• 不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引等都不存储索引列的值，索引MySQL只能使用B-Tree索引做覆盖索引。

举例

新建author表如下：

CREATE TABLE `author` (
   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
   `name` varchar(32) NOT NULL COMMENT '姓名',
   `gender` tinyint(1) NOT NULL COMMENT '性别，0-男，1-女',
   `age` tinyint(3) NOT NULL DEFAULT '0' COMMENT '年龄',
   `email` varchar(32) NOT NULL DEFAULT '' COMMENT '邮箱',
   `homepage` varchar(128) NOT NULL DEFAULT '' COMMENT '主页',
   `add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间',
   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '修改时间',
   PRIMARY KEY (`id`),
   KEY `idx_author_union_1` (`gender`,`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

// 插入5条数据
insert into `author` (`name`, `gender`, `age`, `email`) values('xx','0','20','xx@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('yy','1','18','yy@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('zz','0','25','zz@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','30','xyz123@126.com');
insert into `author` (`name`, `gender`, `age`, `email`) values('xyz123','0','120','xxx@163.com');

执行相关查询及其执行计划如下：

从执行计划看，Extra列都是“Using index”，由此可知这两个查询都是索引覆盖查询。另外，二级索引idx_author_union_1只有gender和email两个索引列，但查询id字段仍然是索引覆盖查询，这也印证了二级索引在叶子节点中保存了行的主键值，且可以对主键值覆盖查询。

第一个查询的执行计划type=index，说明MySQL使用了索引扫描来做排序，下面的查询结果也印证的这一点。

需要注意的是，只有当索引的列顺序和ORDER BY 子句的顺序一致，并且所有列的排序方向都一样时，MySQL才能使用索引来对结果进行排序。如果查询需要关联多张表，则只有当ORDER BY 子句引用的字段全部为第一个表时，才能使用索引做排序。