欢迎关注MySQL专栏 MySQL历险记
强烈建议收藏本导航文【MySQL历险记】MySQL的核心特性汇总
前言
最近公司校招，问了一个刚刚毕业的同学什么是覆盖索引，愣是没有讲明白，只知道索引是可以用来加快查询速度的。那么本文，就和大家一起分享下覆盖索引究竟是怎么一回事。
理解覆盖索引
在理解覆盖索引之前，我们先讲讲查询语句是如何利用索引进行查询的。
在MySQL的innoDB存储结构中，会有两种类型的索引：
-
聚簇索引
- 存放所有的数据行，它也是表数据最终存放的地方。
- 默认在主键上建立聚簇索引，如果表没有主键，则会默认第一个
NOT NULL，且唯一（UNIQUE）的列作为聚簇索引，以上都没有，则会默认创建一个隐藏的row_id作为聚簇索引。 - 速度快，可以直接定位到具体行。
-
普通索引
- 结构存放聚簇索引对应的
id，不会存放整个数据行。
- 结构存放聚簇索引对应的
我们用一个例子展示，比如一个学生表student, 数据如下：
| id | name | age |
|---|---|---|
| 1 | a | 18 |
| 2 | b | 8 |
| 3 | a | 16 |
| 4 | b | 8 |
| 5 | c | 5 |
| 6 | e | 6 |
id是主键，用来建立聚簇索引- 在
name和age上建立了联合索引idx_name_age
最终得到的索引存储结构如下图所示：
关于索引底层数据结构不了的同学可以先阅读：一步步带你设计MySQL索引数据结构。
现在我们要查询select id, name, age from student where name='a', 它的执行流程是什么样的呢？
-
MySQL会把
普通索引B+树中根目录节点所在页加载到内存中，因为是排好序了，根据'a'通过二分法快速找到它的下一层目录节点所在的页。 -
MySQL再次把下一层目录节点所在的页加载到内存中，通过二分法找到对应的页。
-
最终找到叶子节点，得到的它的主键
id，现在要找name,age等其他信息，就必须去另一颗聚簇索引的B+树查找。 -
然后根据主键
id回到聚簇索引的B+树中，按照上面类似的流程再次定位到具体的行数据。这个过程也叫做 “回表” 。
很显然，回表的过程会在两棵树上搜索，增加了磁盘的IO, 查询性能相对会下降，那有什么办法可以只在一颗树上搜索呢？
我们看到图上的普通索引树上其实有了name和id字段的信息，如果我们查询的列只有id 和 name是不是就没有必要回到聚簇索引树上了。MySQL实际上也是这么处理的，我们可以看下查询计划就知道了。
- 没有覆盖索引的情况
- 用到了覆盖索引的情况
那么回到一开始的查询语句是select id, name, age from student where name='a'，我们的索引树上没有age信息啊，那我们可以基于name, age建立一个联合索引就可以实现覆盖索引。
小结：
覆盖索引就是指索引树上的信息覆盖了我们要查询过滤的字段，不需要再次进行“回表”操作。所以我们在写SQL的时候尽量避免select *的情况，根据实际业务场景查询相应的字段。
优点和缺点
前面一起理解了覆盖索引，那我们辩证的看看它的优缺点。
优点
1.避免InnoDB表进行索引的二次查询（回表）
在覆盖索引中，二级索引的值中可以获取所要的数据，避免了对主键的二次查询，减少了IO操作，提升了查询效率。
2.可以把随IO变成顺序IO加快查询效率
由于覆盖索引是按值的顺序存储的，对IO密集型的范围查找来说，对比随机从磁盘读取每一行的数据IO要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的IO转变成索引查找的顺序IO。
缺点
索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。
覆盖索引使用例子
分享一个利用覆盖索引实现优化分页查询的例子。
问题：
一个常见又非常头疼的问题就是limit 3000000, 10此时需要MySQL排序前3000000记录，仅仅返回3000000~3000010的记录，查询排序的代价非常大。
解决方案：
SELECT * FROM student t,
(SELECT id FROM student ORDER BY id LIMIT 3000000,10) a
WHERE t.id = a.id;
- 利用覆盖索引，只查找出分页范围内的
id - 然后找出对应的id在去做连接查询。
总结
覆盖索引，其实非常容易理解，由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引也是一个常用的性能优化手段。在平时的开发过程中，我们也要尽量的使用上这个优化手段。如果本文对你有帮助的话，留下一个赞。
本文正在参加「金石计划 . 瓜分6万现金大奖」
