我们这里暂时只讨论B+树
B+ 树索引
索引分类
- 主键索引(聚簇索引)
- 主键索引的叶子节点记录每行数据各列值;非叶子节点记录主键的N叉树
- 二级索引(非聚簇索引)
- 所有其他索引,都叫二级索引
- 二级索引的叶子节点记录主键值;非叶子节点记录当前索引的N叉树
- 一般情况下,所有非主键查询,都要走两层索引,现在二级索引查到主键值,再回主键索引取字段(回表)。除非:
- 覆盖索引or二级索引的索引列就是用户需要的列信息,此时不用回表
- 无法命中索引,走全表扫描,会直接扫主键索引
从业务需求和性能优化考虑,二级索引又有以下分类:
-
联合索引
- 联合索引: 使用多个字段共同组成索引,方便多个字段的联合查询需求
- 联合索引满足最左匹配原则,即
colA_colB
的联合索引,其实可以实现colA_colB
和colA
两个索引功能
-
覆盖索引
- 覆盖索引一般用于性能优化,把需要查询的字段放到二级索引上,可以避免回表操作
索引使用优化分析
-
索引都是按序存储的,因此,每次插入/删除数据,都要先查询再插入/删除
- 插入过程可能存在
页分裂
,删除也可能会引发页合并
- 索引一般主键索引会用自增id,防止页分裂的开销
- 删除操作可以设置软删除,防止页合并的开销(有一定的存储消耗)
- 插入过程可能存在
-
联合索引如何优化
- 联合索引的顺序应该怎么设计?
- 可复用性
- 如果我们需要
colA
colB
两个字段的联合索引,和colB
的独立索引,此时大概率可以设计成colB_colA
,实现索引复用
- 如果我们需要
- 区分度原则
- 我们会优先把区分度高的字段放到索引前面,这样可以加快索引的查找过程(这个原则和可复用原则有些矛盾,还不太明白如何取舍???)
- 可复用性
- 联合索引的顺序应该怎么设计?
-
索引存储
- 索引文件大小
- 同等数据量,如果索引key更大,则单个页存储的记录数越少,同时需要更多的页,那么索引文件就更大
- 字段大小 -> 页内节点个数 -> 树的层数
- 8B的索引,3层索引可存储10亿条数据
- 32B的索引,3层索引可存储6400w数据
- 索引文件大小
索引失效
假设已有A_B_C
的联合索引,以下情况会导致索引失效,或索引部分失效
OR查询
: 此时where A = xxx OR B = xxx OR C = xxx
的查询,只能命中A
索引B
需要单独建立索引,触发MySQLindex 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的,此时无意义的过滤会导致索引失效
- 如过滤timestamp时,使用
思考
MySQL选错索引的影响因素有哪些?应该如何尽量避免?
影响因素主要有几个方面:
- 扫描行数
- MySQL使用采样的方式预估扫描行数,可能存在误差,影响索引选择
- 索引区分度不够,二级索引 + 回表 可能不如 直接扫主键索引来得快,此时也可能不用索引
- 是否使用临时表
- 是否排序
- 有索引1和索引2两个索引,1比2的扫描行数更少,但是如果2索引可以直接满足查询中的排序
order by
需求,那么MySQL也可能放弃1使用中应当避免
- 在建索引时,考虑索引的合理程度(一张表,不能建太多索引了)
- 写sql时,尤其是复杂查询中,要考虑相近索引的相互影响;有余力时,可以脑补MySQL的查找过程