MySQL八股学习记录3-索引专项from 小林coding
索引概念
一言以蔽之,索引就是数据的目录
索引的分类
种类:聚簇索引、主键索引、二级索引、普通索引、唯一索引、hash索引、B+树索引
按照不同的原理,索引的分类也不同
按照数据结构分类:B+树,Hash索引,Full-text索引
按照物理存储分类:聚簇索引,二级索引
按照字段特性分类:主键索引,唯一索引,普通索引,前缀索引
按照字段个数分类:单列索引,联合索引
InnoDB是MySQL5.5之后的默认引擎,B+树是InooDB引擎使用最多的引擎类型
B+树特点
只有叶子节点才存放数据,非叶子节点存放索引,每个节点的数据是按照主键的顺序存放的,并且同层节点之间双向链表连接起来,数据库的索引和数据都是存放在磁盘上的,读取一个节点可以当作一个磁盘IO操作,所以B+树由于矮宽的特点,查询效率很高,数据量很大的情况下,查询一个数据依然只需要3-4个磁盘IO
通过二级索引查询数据的过程
在通过非主键索引查询数据时,需要使用二级索引来查询数据,每个索引会建立一个B+树,这些B+树的叶子节点存储的是与主键的映射,得到主键之后,如果欲查询的数据在叶子节点中没有,(即不是覆盖索引),再查询聚簇索引(回表操作)查到真实的数据
B+树的优势
hash | 二叉树 | b树 |
---|---|---|
不能范围查询 | 树过高 | 1.树过高(非叶节点需要存储信息)2.叶子节点没有链表(无法有效支持范围查询) |
按照字段个数进行分类
从字段个数上看,索引可以分为单列索引和联合索引
联合索引有一个重要的特征就是**最左匹配原则,**因为假设建立了一个联合索引,并且索引ID分别为(a,b,c…),那么B+树的排序准则就是先按照a排序,a相同按照b依次类推,因此b,c只是局部有序的,全局无序
联合索引的最左匹配原则,在遇到范围查询(如 >、<)的时候,就会停止匹配,也就是范围查询的字段可以用到联合索引,但是在范围查询字段的后面的字段无法用到联合索引。注意,对于 >=、<=、BETWEEN、like 前缀匹配的范围查询,并不会停止匹配,前面我也用了四个例子说明了
TIPS:建立联合索引时,将区分度高的字段排在前面,这样区分度大的字段更有可能被更多的SQL使用到
索引使用指南
索引需要占用物理空间,并且索引的创建和维护需要耗费时间
适用索引的情况
- 字段有唯一性限制的,比如商品编码;
- 经常用于 WHERE 查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。
- 经常用于 GROUP BY 和 ORDER BY 的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。
不适用索引的情况 - WHERE 条件,GROUP BY,ORDER BY 里用不到的字段,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的,因为索引是会占用物理空间的。
- 字段中存在大量重复数据,不需要创建索引,比如性别字段,只有男女,如果数据库表中,男女的记录分布均匀,那么无论搜索哪个值都可能得到一半的数据。在这些情况下,还不如不要索引,因为 MySQL 还有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
- 表数据太少的时候,不需要创建索引;
- 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。
索引优化指南
- 前缀索引优化;
- 覆盖索引优化;
- 主键索引最好是自增的;
- 防止索引失效;
索引失效情况
- 当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效;
- 当我们在查询条件中对索引列做了计算、函数、类型转换操作,这些情况下都会造成索引失效;
- 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
- 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。