Mysql中B+树索引详解与使用

╰+攻爆jí腚メ 2022-10-08 13:23 168阅读 0赞

# 一、背景知识（InnoDB 数据页结构） #

1、InnoDB为了不同的目的而设计了不同类型的页， 我们把用于存放记录的页叫做数据页

2、一个数据页可以被大致划分为7个部分，分别是

*  File Header，表示页的一些通用信息，占固定的38字节
 *  Page Header，表示数据页专有的一些信息，占固定的56个字节
 *  Infimum + Supremum，两个虚拟的伪记录，分别表示页中的最小和最大记录，占固定的26个字节
 *  User Records：真实存储我们插入的记录的部分，大小不固定
 *  Free Space：页中尚未使用的部分，大小不确定
 *  Page Directory：页中的某些记录相对位置，也就是各个槽在页面中的地址偏移量，大小不固定，插入的记录越多，这个部分占用的空间越多。
 *  File Trailer：用于检验页是否完整的部分，占用固定的8个字节

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70][] ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 1][]

3、每个记录的头信息中都有一个next\_record属性，从而使页中的所有记录串联成一个单链表。

4、InnoDB会为把页中的记录划分为若干个组，每个组的最后一个记录的地址偏移量作为一个槽，存放在Page Directory

中， 所以在一个页中根据主键查找记录是非常快的，分为两步：

*  通过二分法确定该记录所在的槽
 *  通过记录的next\_record属性遍历该槽所在的组中的各个记录

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 2][]

5、每个数据的的File Header部分都有上一个和下一个页的编号， 所以所有的数据页会组成一个双链表。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 3][]

6、为保证从内存中同步到磁盘的页的完整性， 在页的首部和尾部都会存储页中数据的校验和和页面

最后修改时对应的LSN值，如果首部和尾部的校验和和LSN值校验不成功的话，就说明同步过程出现了问题。

7、汇总

InnoDB数据页有7个组成部分，各个数据页可以组成一个双向链表

而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表

每个数据页都会为存储在它里面的记录生成一个页目录

在通过主键查找某条记录的时候可以在页目录中使二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 4][]

其中页a、页b、页c...页n这些页可以不在物理结构上相连，只要通过双向链表相关联即可

# 二、B+树索引 #

mysql> CREATE TABLE index_demo(
     c1 INT PRIMARY KEY,
     c2 INT,
     c3 CHAR(1)
    ) ROW_FORMAT = Compact;

1、对于InnoDB存储引擎来说，在单个页中查找某条记录分为两种情况：

1.1、以主键为搜索条件， 可以使用Page Directory通过二分法快速定位相应的用户记录。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 5][]

根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为20的记录为例：
    
    1. 确定目录项记录页
        我们现在的存储【目录项记录】的页有两个，即页30和页32，又因为页30表示的目录项的主键值的范围是[1, 320)，
    	页32表示的目录项的主键值不小于320， 所以主键值为20的记录对应的目录项记录在页30中。
    
    2. 通过【目录项记录页】确定用户记录真实所在的页
        在一个【目录项记录页】页30中，通过主键值20在页内的单链表内不断偏移，比较到主键值为12和209，12<20<209，所以主键值20位于叶子的【用户记录页】页9
    	
    3. 在真实存储用户记录的页中定位到具体的记录
        在【用户记录页】页9中，通过主键值20不断偏移，最终得到结果值（20，2，e）

1.2、以其他列为搜索条件，需要按照记录组成的单链表依次遍历各条记录。

2、没有索引的情况下，不论是以主键还是其他列作为搜索条件，只能沿着【用户记录页】的双链表从左到右依次遍历各个页

3、InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子节点，其他层次的节点都属于内节点，内节点里存储的是目录项记录。InnoDB的索引分为两大种：

3.1、聚簇索引：以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。

3.2、二级索引（非聚簇索引）：以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键。

*  字符串排序的本质就是比较哪个字符串大一点，哪个字符串小一点，比较字符串大小就用到了该列的字符集和比较规则

3.2.1 单索引

*  对c2建立索引：create index idx\_c2 on index\_demo (c2) ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 6][]

上述是c2列的索引，在【目录项记录页】中存储的是c2的值-页号
    在叶子【用户记录页】中存储的是c2的值-主键c1
    
    
    以查找c2列的值为4的记录为例，查找过程如下：
    
    1. 确定目录项记录页
        根据根页面，也就是页44，可以快速定位到目录项记录所在的页为页42（因为2 < 4 < 9）
    2. 通过目录项记录页确定用户记录真实所在的页
        在页42中可以快速定位到实际存储用户记录的页，但是由于c2列并没有唯一性约束，所以c2列值为4的记录可能分布在多个
    数据页中，又因为2 < 4 ≤ 4，所以确定实际存储用户记录的页在页34和页35中。
    3. 在真实存储用户记录的页中定位到具体的记录
        到页34和页35中定位到具体的记录。
    4. 但是这个B+树的叶子节点中的记录只存储了c2和c1（也就是主键）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录（也就是【回表操作】）

3.2.2 联合索引

*  建立c2、c3的联合索引：create index idx\_c2\_c3 on index\_demo (c2, c3)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 7][]

每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。
    
    B+树叶子节点处的用户记录由c2、c3和主键c1列组成

*  联合索引是一棵B+树，多个单索引是多棵B+树

3.3 聚簇索引与非聚簇索引

3.3.1 定义

*  聚集索引又被称为聚簇索引，是指将数据和索引放到一起的索引，当找到索引也就找到了数据。在InnoDB引擎中，B+树的非叶子结点存放的都是索引，而叶子结点存放的是索引和数据。
 *  非聚集索引又被称为非聚簇索引，是将数据和索引分开存储
 *  InnoDB的主键索引是聚集索引，MyISAM的主键索引和二级索引都是非聚集索引。
 *  InnoDB的非主键索引的叶子结点上存放着行的主键值，当找到索引数据时可能需要根据主键值回表，也就是说当查到主键后会根据主键值回到表中查询

3.3.2 聚集索引的优缺点

*  聚集索引查询往往非常快，因为当定位到索引时，也就直接定位到了数据。
 *  但是聚集索引非常依赖有序数据，当插入或查找类似于UUID这种复杂的字符串时，往往速度很慢。
 *  还有就是聚集索引的更新代价很大，一般来说如果更新索引列数据，那么索引结构也要修改，所以主键是不建议被修改的。

3.3.3 非聚集索引的优缺点

*  非聚集索引的更新代价较小，因为叶子节点不存放数据。但非聚集索引也非常依赖有序的数据，另外非聚集索引可能需要回表。

4、MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索引全部都是二级索引，在叶子节点处存储的是列 + 页号。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 8][]

*  MyISAM会把索引信息另外存储到一个称为索引文件的另一个文件中。 MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录， 而是主键值 + 行号的组合。
 *  也就是先通过索引找到对应的行号， 再通过行号去找对应的记录

5、总结

*  每个索引都对应一棵B+树，B+树分为好多层，最下边一层是叶子节点，其余的是内节点。所有用户记录都存储在B+树的叶子节点，所有目录项记录都存储在内节点。
 *  InnoDB存储引擎会自动为主键（如果没有它会自动帮我们添加）建立聚簇索引，聚簇索引的叶子节点包含完整的用户记录。
 *  我们可以为自己感兴趣的列建立二级索引，二级索引的叶子节点包含的用户记录由索引列 + 主键组成，所以如果想通过二级索引来查找完整的用户记录的话，需要通过回表操作，也就是在通过二级索引找到主键值之后再到聚簇索引中查找完整的用户记录。
 *  B+树中每层节点都是按照索引列值从小到大的顺序排序而组成了双向链表，而且每个页内的记录（不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。
 *  通过索引查找记录是从B+树的根节点开始，一层一层向下搜索。由于每个页面都按照索引列的值建立了Page Directory（页目录），所以在这些页面中的查找非常快

# 三、B+树索引的使用 #

1、B+树索引在空间和时间上都有代价，所以没事别瞎建索引

2、B+树索引适用于下边这些情况：

*  全值匹配
 *  匹配左边的列
 *  匹配范围值（如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引）
 *  精确匹配(user\_id='xxx')某一列并范围匹配另外一列
 *  用于排序
 *  用于分组

①当联合索引左边列的值为常量，也可以使用后边的列进行排序
    ②SELECT * FROM person_info WHERE name = 'A' ORDER BY birthday, phone_number LIMIT 10;
    ③排序asc、desc混用，索引不生效

3、在使用索引时需要注意下边这些事项：

*  只为用于搜索(where)、排序(order by)或分组(group by)的列创建索引
 *  为区分度大(字段有比较多不同的值)的列创建索引（例如is\_deleted只有2个值，区分度小，就没必要了）
 *  索引列的类型尽量小（能用int就不用bigint）
 *  可以只对字符串值的前缀建立索引（create index idx\_name on te\_user (name(10))）
 *  只有索引列在比较表达式中单独出现才可以适用索引（where name>upper('AAA') 优于 upper(name)>'AAA'）
 *  为了尽可能少的让聚簇索引发页面分裂和记录移位的情况，建议让主键拥有AUTO\_INCREMENT属性
 *  定位并删除表中的重复和冗余索引
 *  尽量适用覆盖索引进行查询，避免回表带来的性能损耗

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2021062118513149.jpeg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 1]: /images/20221005/aad066682fdf41dca3f2fb6f5527b6f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 2]: /images/20221005/930497893ccc4db99fdae253f5d63d3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 3]: /images/20221005/de00e6f8db95446291314aaa995f93af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 4]: /images/20221005/6f38ddd93fe14478a78a63dbf25612bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 5]: /images/20221005/6cc8fae78a2144e0b820a9e09ef24ec2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 6]: /images/20221005/8214f988bafb474cb48e7fa11e5cd60b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 7]: /images/20221005/319f9c21de7b4f9088519b7e61d6c3d4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FzZDA1MTM3NzMwNQ_size_16_color_FFFFFF_t_70 8]: /images/20221005/d17ad4b50a6e491788cbc4112e9eb82e.png