4.B+树索引

快速查询的秘籍 —— B+ 树索引

1.索引的概念和作用

索引(也称目录)是一种排好序的支持快速查找的数据结构，类似于字典，用于查询数据时快速定位到具体的数据页。

在MySQL，索引<=>B+树

2.B+树索引介绍

(1)B+树索引图(简略版)：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EBCP7ulU-1686638463676)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220824152808674.png)]

• 用户记录在叶子节点，索引记录在非叶子节点
• 如图所示，从根节点出发，有非叶子节点的数据页在user_records存放的是索引记录，和用户记录一样是以链表形式按照主键从小到大存放，索引记录只有两列分别是主键值和页码。
• 非叶子节点的数据页中每一条索引记录的第一列主键值存的是第二列页码所在数据页索引记录的最小值主键。第二列页码存的是子节点
• 索引记录的记录头信息中的record_type = 1，数据页中第一个索引记录的min_rec_mask = 1，其余为0

(2)聚簇索引(主键索引)：

B+树索引的一种类型，InnoDB默认创建的索引，也叫主键索引。

(3)二级索引：

B+树索引的一种类，使用单个非主键列创建的索引，索引记录存放的是非主键列值,主键列值，页码。也是以链表形式但是按照非主键列值从小到大存放。二级索引的策略其实是回表，也就是找到对应非主键列值的用户记录后，根据主键列再去聚簇索引查一遍。查询时使用覆盖索引可以不回表（覆盖索引：查询列表⾥只包含索引列）

因为二级索引和聚合索引的用户记录往往只有1个或2、3个非主键列。而有时候select * 时需要查询出所有的，因此就必须利用主键再去聚簇索引查一遍。如果第一步在二级索引中匹配的数据量小的话对效率没什么影响，但是如果数据量大例如匹配到了90%的数据，那么这时候还不如直接去‘全表扫描’，庆幸的是InnoDB自身会有这种优化。即不使用索引也不回表，直接去全表扫描

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YNH0EUEN-1686638463677)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220824160354627.png)]

(4)聚合索引：

和二级索引一样的概念，只是非主键列变成了多个组合在一起，假设有(c1,c2)列作为索引，那么排序的时候就是先按c1排，相同的再按c2排

2.B+树索引使用

每建立一个索引都要为它建立一颗B+树，默认为每一张表创建聚簇索引。索引的使用方式有4种

• 使用聚簇索引或唯一二级索引
• 使用普通二级索引
• 使用聚合索引
• 遍历索引

(1)索引使用口诀

索引失效：1.回表转全表扫描 2.直接全表扫描

回表转全表扫描：因为对于二级索引，聚合索引来说，遇到相同数据太多的情况下，不会进行回表操作改为全表扫描了。这样索引就失效

• 全职匹配我最爱，最左前缀要遵守
• 带头大哥不能死，中间兄弟不可端
• 索引列上不计算，范围之后(可能)全失效
• Like百分写最右，覆盖索引多使用
• 不等空值还有or，索引(可能)失效要少用

(2)如何挑选索引

• 只为⽤于搜索、排序或分组的列创建索引
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ExLrJnZI-1686638463678)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220826143009589.png)]
• 为列的基数⼤的列创建索引
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FHdJ2MFE-1686638463679)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220826142955939.png)]
• 索引列的类型尽量⼩
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ePtB74Ft-1686638463680)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220826142837799.png)]
• 可以只对字符串值的前缀建⽴索引
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kCARq6OY-1686638463681)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220826142822955.png)]
• 只有索引列在⽐较表达式中单独出现才可以适⽤索引（即“索引列上不计算”）
  • [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s5fDAveZ-1686638463681)(C:\Users\10059\AppData\Roaming\Typora\typora-user-images\image-20220826142738223.png)]
• 为了尽可能少的让聚簇索引发⽣⻚⾯分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性
• 尽量使⽤覆盖索引进⾏查询，避免回表带来的性能损耗

(3)SQL操作索引

• 创建表时创建索引

  • //KEY和INDEX是同义词，任意选⽤⼀个就可以

    create table 表名（

    ​ 各个列…

    ​ [index|key] 索引名 (需要被索引的列)

    ）；

• 修改表时添加索引

  • alter table 表名 add [index|key] 索引名 (需要被索引的列) //索引可以是单列或者多列

• 修改表时删除索引

改表时添加索引

• alter table 表名 add [index|key] 索引名 (需要被索引的列) //索引可以是单列或者多列

• 修改表时删除索引

  • alter table 表名 drop [index|key] 索引名