MySQL的索引优化

执行 select * from T where k between 3 and 5, 需要执行几次树的搜索操作？

 

这条SQL语句的执行流程：

1. 在k索引树上找到k=3的记录，取得ID=300

2. 再到ID索引树查到ID=300对应的R3

3. 在k索引树取下一个值k=5，取得ID=500

4. 再回到ID索引树查到ID=500对应的R4

5. 在k索引树取下一个值k=6，不满足条件，循环结束

该查询过程读了k索引树的3条记录，回表2次 。有没可能经过索引优化，避免回表过程？

覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5

这时只需要查ID的值，ID的值已经在k索引树上了（普通索引树叶子节点是主键的值），因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经覆盖了我们的查询需求，称为覆盖索引 

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

身份证号是市民的唯一标识，如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。而再建一个（身份证号、姓名）联合索引，是不是浪费空间？

如果有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义。可以在高频 请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

索引字段的维护总是有代价的，在建立冗余索引来支持覆盖索引时就需要权衡考虑了

最左前缀原则

联合索引（name,age）

（name,age）索引示意图

索引项是按照索引定义里面出现的字段顺序排序的

当需要查到所有名字是“张三”的人时，可以快速定位到ID4，然后向右遍历得到所有需要的结果。

如果要查所有名字第一个字是“张”的人，SQL语句的条件是“where name like ‘张%’ ”，也能用上这个索引，找到第一个符合条件的记录是ID3，然后向右遍历，直到不满足条件为止。

不只是索引的全部定义，只要满足最左前缀，就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符 （查询的时候如果命中联合索引的最左N个字段/字符串的最左M个字符，都是可以用到索引加速的）

在建立联合索引的时候，如何安排索引内的字段顺序？

评估标准，索引的复用能力。因为可以支持最左前缀，当有了（a,b）这个联合索引后，一般就不需要单独在a上建立索引了

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序是需要优先考虑采用的

如果有高频请求根据身份证号查姓名，低频请求根据身份证号查地址 =》就可以建立联合索引（身份证号，姓名）

如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用（a,b）这个联合索引的，这时候不得不维护另外一个索引，需要同时维护（a,b）、（b）

此时，需要考虑空间原则，如果是上面市民表，name字段比age字段大，建议创建（name,age）联合索引和（age）单字段索引。

索引下推

市民表联合索引（name, age），检索出表中“名字第一个字是张，年龄是10岁的所有男孩”，SQL如下：

select * from tuser where name like '张%' and age=10 and ismale=1;

这个语句在搜索索引树的时候，只能用“张” 找到第一个满足条件的记录ID3，然后判断其他条件是否满足

在MySQL5.6之前，只能从ID3开始一个个回表。到主键索引树上找出数据行，再对比字段值

而MySQL5.6引入的索引下推优化，可以在索引树中遍历的过程中，对索引中包含的字段先做判断（找到name符合条件的节点，先判断下节点中字段age是否满足 [联合索引在同一个节点]），过滤掉不满足条件的记录，减少回表次数

B+树中每个结点存放的是多个索引字段的值 

在满足语句需求的情况下，尽量少地访问资源是数据库设计的重要原则之一

 学习笔记 --《MySQL实战45讲》