深入浅出谈索引（下）笔记

深入浅出谈索引（下）笔记

由于查询结果所需要的数据只在主键索引上有，所以不得不回表。可以通过一些优化的方法来避免回表的过程。

覆盖索引

1. 什么是覆盖索引

在查询时，尽量只查询树上包含的字段。例如通过二级索引查询主键等行为，当某些查询频率较高，可以尝试建立联合索引通过覆盖索引的手段，提高查询效率

2. 覆盖索引的优点

通过覆盖索引，可以避免回表操作减少树的搜索次数，显著提升查询性能

3. 什么是联合索引

1. 联合索引是将多个字段，按照字典序列的b+树，每个节点是一个多字段的值，类似于python中的元组，例如一个联合索引中有两个字段（a,b）,我们只分析a的时候，会发现a是有序的；只分析b的时候是无序的，当确定a了之后，再来看b，b又是有序的。

2. 联合索引的失效：

   1. 最佳左前缀法则

      select * from test where a=1 and b=2;
      -- 上述语句是直接命中索引的，首先找a，a是有序的，在确定了a之后找b，在a确定后b也是有序的。
      
      select * from test where b=2;
      -- 上述语句未命中索引，此时在构建的树中，b是无序的，无法使用二分查找。mysql找无序的数据就是全表扫描。
      

   2. 范围查找右边失效的原理

      其实和上面相同，当你使用了范围查找，能够锁定部分的a值，但是去除掉了这些a之后，还有许多其他的a值绑定的b值依然是无序的。

   3. like索引失效原理

      like一般要配合百分号，一般是查字符类型的。在mysql里字符会按照自己的算法排好，当然也是从小到大，如字母就是用ascii码表排序。根据我们之前是结论，要命中索引必须要确定a的值，那么只有a%是符合的，因为后两种都无法确定前面的值，但是这种仅仅只有一个a%其实也是很难命中索引的，因为a开头的值可能有一个，也可能一百万个。

最左前缀原则

B+树结构可以利用索引的最左前缀，来定位记录。索引项是按照索引定义里面出现的字段顺序排序的，下图是联合索引。

这里注意最左前缀指的是联合索引的最左N个字段，也可以是字符串索引的最左M个字符

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

1. 如何通过调整顺序，可以少维护一个索引，那么这个顺序往往就是优先考虑采用的。当有了(a,b)的这个联合索引后，一般就不需要在单独在a上建立索引了。这个联合索引也拥有了a单独索引的功能。
2. 空间原则。查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。比如name 字段是比 age 字段大的 ，创建一个（name,age) 的联合索引和一个 (age) 的单字段索引。

索引下推

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

在 MySQL 5.6 之前，只能通过最左前缀找到对应的主键后，开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。