MySQL学习笔记二：索引原理

我们这里暂时只讨论B+树

B+ 树索引

索引分类

1. 主键索引（聚簇索引）
   • 主键索引的叶子节点记录每行数据各列值；非叶子节点记录主键的N叉树
2. 二级索引（非聚簇索引）
   • 所有其他索引，都叫二级索引
   • 二级索引的叶子节点记录主键值；非叶子节点记录当前索引的N叉树
   • 一般情况下，所有非主键查询，都要走两层索引，现在二级索引查到主键值，再回主键索引取字段(回表)。除非：
     • 覆盖索引or二级索引的索引列就是用户需要的列信息，此时不用回表
     • 无法命中索引，走全表扫描，会直接扫主键索引

从业务需求和性能优化考虑，二级索引又有以下分类：

1. 联合索引

   • 联合索引: 使用多个字段共同组成索引，方便多个字段的联合查询需求
   • 联合索引满足最左匹配原则，即colA_colB的联合索引，其实可以实现colA_colB和colA两个索引功能

2. 覆盖索引

   • 覆盖索引一般用于性能优化，把需要查询的字段放到二级索引上，可以避免回表操作

索引使用优化分析

1. 索引都是按序存储的，因此，每次插入/删除数据，都要先查询再插入/删除

   • 插入过程可能存在页分裂，删除也可能会引发页合并
     • 索引一般主键索引会用自增id，防止页分裂的开销
     • 删除操作可以设置软删除，防止页合并的开销（有一定的存储消耗）

2. 联合索引如何优化

   • 联合索引的顺序应该怎么设计？
     • 可复用性
       • 如果我们需要colA colB两个字段的联合索引，和colB的独立索引，此时大概率可以设计成colB_colA，实现索引复用
     • 区分度原则
       • 我们会优先把区分度高的字段放到索引前面，这样可以加快索引的查找过程（这个原则和可复用原则有些矛盾，还不太明白如何取舍？？？）

3. 索引存储

   • 索引文件大小
     • 同等数据量，如果索引key更大，则单个页存储的记录数越少，同时需要更多的页，那么索引文件就更大
   • 字段大小 -> 页内节点个数 -> 树的层数
     • 8B的索引，3层索引可存储10亿条数据
     • 32B的索引，3层索引可存储6400w数据

索引失效

假设已有A_B_C的联合索引，以下情况会导致索引失效，或索引部分失效

• OR查询: 此时where A = xxx OR B = xxx OR C = xxx 的查询，只能命中A索引
  • B需要单独建立索引，触发MySQL index merge
• 隐式类型转换: 这个踩过坑
• 范围查询: where A = xxx and B > xxx and C = xx，只能命中A索引，与此类似：
  • like语句，如B like "%a"
  • 包含计算语句，如where A + 1 = 100， 此时无法命中索引
• 区分度低: 假如A字段的查询只能过滤一小部分行数，那么Innodb会觉得查询 + 回表的消耗，不如直接扫主键表，此时索引失效
• 查询范围无意义：这个是业务上需要注意的事项
  • 如过滤timestamp时，使用timestamp > 0，然而所有的timestamp都是大于0的，此时无意义的过滤会导致索引失效

思考

MySQL选错索引的影响因素有哪些？应该如何尽量避免？

影响因素主要有几个方面:

1. 扫描行数
   • MySQL使用采样的方式预估扫描行数，可能存在误差，影响索引选择
   • 索引区分度不够，二级索引 + 回表 可能不如 直接扫主键索引来得快，此时也可能不用索引
2. 是否使用临时表
3. 是否排序
   • 有索引1和索引2两个索引，1比2的扫描行数更少，但是如果2索引可以直接满足查询中的排序order by需求，那么MySQL也可能放弃1

使用中应当避免

1. 在建索引时，考虑索引的合理程度（一张表，不能建太多索引了）
2. 写sql时，尤其是复杂查询中，要考虑相近索引的相互影响；有余力时，可以脑补MySQL的查找过程