导读
相信读者看过很多 MySQL 索引优化的文章, 其中有很多优化的方法, 比如最佳左前缀, 覆盖索引等方法, 但是你真正理解为什么要使用最佳左前缀, 为什么使用覆盖索引会提升查询的效率吗?
本篇文章将从 MySQL 内部结构上讲一下为什么覆盖索引能够提升效率.
文章转载自 MySQL 性能优化: 为什么要用覆盖索引? https://mp.weixin.qq.com/s/-IRhK4KyDCkTd_lojeEolw
InnoDB 索引模型
在 InnoDB 中, 表都是根据主键顺序以索引的形式存放的, 这种存储方式的表称为索引组织表. 又因为前面我们提到的, InnoDB 使用了 B + 树索引模型, 所以数据都是存储在 B + 树中的.
每一个索引在 InnoDB 里面对应一棵 B + 树.
主键索引和非主键索引的区别
主键索引又叫聚簇索引 , 非主键索引又叫普通索引, 那么这两种索引有什么区别呢?
主键索引的叶子节点存放的是整行数据, 非主键索引的叶子节点存放的是主键的值.
假设有一张 User 表 (id,age,name,address), 其中有 id 和 age 两个字段, 其中 id 是主键, age 是普通索引, 有几行数据 u1-u5 的(id,age) 的值是 (100,1),(200,2),(300,3),(500,5) 和(600,6) , 此时的两棵树的示例如下:
从上图可以看出来, 基于主键索引的树的叶子节点存放的是整行 User 数据, 基于普通索引 age 的叶子节点存放的是 id(主键)的值.
什么是回表?
假设有一条查询语句如下:
select * from user where age=3;
上面这条 sql 语句执行的过程如下:
1, 根据 age 这个普通索引在 age 索引树上搜索, 得到主键 id 的值为 300.
2, 因为 age 索引树并没有存储 User 的全部数据, 因此需要根据在 age 索引树上查询到的主键 id 的值 300 再到 id 索引树搜索一次, 查询到了 u3.
3, 返回结果.
上述执行的过程中, 从 age 索引树再到 id 索引树的查询的过程叫做回表(回到主键索引树搜索的过程).
也就是说通过非主键索引的查询需要多扫描一棵索引树, 因此需要尽量使用主键索引查询.
为什么使用覆盖索引?
有了上述提及到的几个概念, 便能很清楚的理解为什么覆盖索引能够提升查询效率了, 因为少了一次回表的过程.
假设我们使用覆盖索引查询, 语句如下:
select id from user where age=3;
这条语句执行过程很简单, 直接在 age 索引树中就能查询到 id 的值, 不用再去 id 索引树中查找其他的数据, 避免了回表.
总结
覆盖索引的使用能够减少树的搜索次数, 避免了回表, 显著提升了查询性能, 因此覆盖索引是一个常用的性能优化手段.
留给读者一个问题: 身份证是一个人的唯一识别凭证, 如果有根据身份证号查询市民信息的需求, 我们只要在身份证号字段上建立索引就够了. 而再建立一个 (身份证号, 姓名) 的联合索引, 是不是浪费空间?
来源: https://www.cnblogs.com/Chenjiabing/p/12591343.html