MySQL优化之覆盖索引

发表于 | 分类于

只需扫描索引而无需回表

概念

MySQL可以利用索引返回SELECT列表中的字段，而不必根据索引再次读取数据文件，包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)，也就是平时所说的不需要回表操作。

优点

只扫描索引而无需回表的优点：

  1. 索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
  2. 因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
  3. 一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
  4. innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)

覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。

场景

假设存在如下表：

1
2
3
4
5
6
7
8
9
CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `pid` int(20) NOT NULL,
  `age` varchar(255) NOT NULL,
  `school` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `pid` (`pid`),
  KEY `school_age` (`school`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如果在该表上执行：

1
2
3
4
5
explain select count(*) from student;

id|select_type|table  |type |possible_keys|key|key_len|ref|rows|Extra      |
--|-----------|-------|-----|-------------|---|-------|---|----|-----------|
 1|SIMPLE     |student|index|             |pid|5      |   | 165|Using index|

遍历聚集索引和辅助索引都可以统计出结果，但辅助索引要远小于聚集索引，所以优化器会选择辅助索引来统计。key和Extra显示使用了pid这个辅助索引。

还有，假设执行以下sql：

1
2
3
4
5
explain select *  from student where age > 10 and age < 15;

id|select_type|table   |type|possible_keys|key|key_len|ref|rows|Extra      |
--|-----------|--------|----|-------------|---|-------|---|----|-----------|
 1|SIMPLE     |students|ALL |             |   |       |   |   1|Using where|

因为联合索引school_age的字段顺序是先school再age，按照age做条件查询，不会命中索引。

但是，如果保持条件不变，查询所有字段改为查询条目数：

1
2
3
4
5
explain select count(*) from student where age > 10 and age < 15;

id|select_type|table   |type |possible_keys|key           |key_len|ref|rows|Extra                   |
--|-----------|--------|-----|-------------|--------------|-------|---|----|------------------------|
 1|SIMPLE     |students|index|             |idx_school_age|1534   |   |   1|Using where; Using index|

优化器会选择这个联合索引。