【MySQL】005 -- 深入浅出索引(下)
此文章为《MySQL 实战 45 讲》的学习笔记，其课程链接可参见：MySQL实战45讲_MySQL_数据库-极客时间
目录
一、索引（下）
1、覆盖索引
2、最左前缀原则
3、索引下推
4、思考题
在满足语句需求的情况下， 尽量少地访问资源是数据库设计的重要原则之一
一、索引（下）
1、覆盖索引
在某些某些场景下，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。
但是我们可以通过覆盖索引来减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
例如：
如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。
| 问题：那么基于上面的覆盖索引，在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？ |
| 我们知道，身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。但是如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。 |
| 同时，索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要综合权衡考虑。 |
2、最左前缀原则
B+树这种索引结构，可以利用索引的“最左前缀”，来定位记录。
所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引，那么这条 SQL 语句就可以利用这个联合索引去进行匹配；顾名思义，就是最左优先，在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。值得注意的是，当遇到范围查询(>、<、between、like)就会停止匹配。
联合索引的最左前缀匹配指的是：
Ⅰ、where条件一定要有联合索引的第一个字段；
Ⅱ、是否走联合索引与where条件的顺序无关，只与字段有关。
只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。
| 问题：在建立联合索引的时候，如何安排索引内的字段顺序？ |
| 评估的标准是：索引的复用能力 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。 但是，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。 此时就需要考虑的原则就是空间了， |
| 【补充说明】：
联合索引相当于一个按照姓氏——名字的一个电话簿，只能先确定姓氏才可以命中索引。联合索引可以建立多列(列数大于2)的索引,建议列数最多不要越过3列，超过3列，应重新设计表。 更多内容可参考：[1] mysql的联合索引（复合索引） [2] 一文搞定联合索引 |
3、索引下推
最左前缀可以用于在索引中定位记录。那么，那些不符合最左前缀的部分，会怎么样呢？
以检索表中“名字第一个字是张，而且年龄是10岁的所有男孩”的SQL语句为例：
| mysql> select * from tuser where name like '张%' and age=10 and ismale=1; |
该语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录ID3，然后再判断其它条件是否满足：
- 而MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。而有了索引下推优化，InnoDB在(name,age)索引再内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过，所以就只需要回表2次。
- 在MySQL 5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，再对比字段值。如下图所示，无索引下推优化的情况下，需要回表4次。
4、思考题
| 问题： 实际上主键索引也是可以使用多个字段的。DBA小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：
公司的同事告诉他说，由于历史原因，这个表需要a、b做联合主键，这个小吕理解了。 但是，学过本章内容的小吕又纳闷了，既然主键包含了a、b这两个字段，那意味着单独在字段c上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？ 同事告诉他，是因为他们的业务里面有这样的两种语句：
我给你的问题是，这位同事的解释对吗，为了这两个查询模式，这两个索引是否都是必须的？为什么呢？ | ||
| 答案： 表记录 –a--|–b--|–c--|–d-- 1 2 3 d 1 3 2 d 1 4 3 d 2 1 3 d 2 2 2 d 2 3 4 d 主键 a，b的聚簇索引组织顺序相当于 order by a,b ，也就是先按a排序，再按b排序，c无序。 索引 ca 的组织是先按c排序，再按a排序，同时记录主键 –c--|–a--|–主键部分b-- （注意，这里不是ab，而是只有b） 2 1 3 2 2 2 3 1 2 3 1 4 3 2 1 4 2 3 这个跟索引c的数据是一模一样的。 索引 cb 的组织是先按c排序，在按b排序，同时记录主键 –c--|–b--|–主键部分a-- （同上） 2 2 2 2 3 1 3 1 2 3 2 1 3 4 1 4 3 2 所以，结论是ca可以去掉，cb需要保留。 |
上一篇：redis命令大全