InnoDB行格式、以及索引底层原理分析

素颜马尾好姑娘i 2022-11-03 11:08 143阅读 0赞

**目录**

局部性原理

InnoDB数据页结构

索引

聚簇索引

二级索引（辅助索引）

联合索引

目录项记录的唯一性

总结

--------------------

# 局部性原理 #

在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要**先将数据加载到内存**，表中读取某些记录时，InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：**将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，****InnoDB中页的大小一般为 16 KB，也就是说，当需要从磁盘中读数据时 每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。**

# InnoDB数据页结构 #

//页是InnoDB管理存储空间的基本单位，一个页的大小默认是16KB。
    SHOW GLOBAL STATUS like 'Innodb_page_size';

![20210227203046598.png][]

页结构：

![48b606ef7999d232a7b079fe8845196f.png][]

说明：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70][]

# 索引 #

--走索引的sql,
    EXPLAIN SELECT * FROM t1 WHERE b = 1;
    
    --走全表扫描，不走索引的sql,原因：如果走索引会有多次回表。
    EXPLAIN SELECT * FROM t1 WHERE b > 1;
    
    --不走索引的sql,原因：如果走索引会有多次回表。
    EXPLAIN SELECT * FROM t1 WHERE b > 6;
    
    explain select b from t1;
    
    explain select	* from t1 where e = 1;	
    explain select	* from t1 where e = '1';
    explain select	* from t1 where a = 1;	
    explain select	* from t1 where a = '1';

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 1][]

a字段主键索引B+树结构图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 2][]

bcd字段联合索引B+树结构图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 3][]

## 聚簇索引 ##

聚簇索引的特点：

1. 按主键值的大小进行记录和页的排序：

数据页(叶子节点)里的记录是按照主键值从小到大排序的一个单向链表。

数据页(叶子节点)之间也是是按照主键值从小到大排序的一个双向链表。

B+树中同一个层的页目录也是按照主键值从小到大排序的一个双向链表。

2. B+ 树的叶子节点存储的是完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

具有这两种特性的B+树称为聚簇索引，**所有完整的用户记录都存放在这个聚簇索引的叶子节点处**。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建。InnoDB存储引擎会自动的为我们创建聚簇索引。在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

## 二级索引（辅助索引） ##

聚簇索引只能在搜索条件是**主键值**时才能发挥作用，因为B+树中的数据都是按照**主键**进行排序的。当我们想以别 的列作为搜索条件时我们可以多建几棵B+树，不同的B+树中的数据采用不同的排序规则。

二级索引与聚簇索引有几处不同：

1. 按指定的索引列的值来进行排序

2. 叶子节点存储的不是完整的用户记录，而只是**索引列****+主键**。

3. 目录项记录中**不是**主键\+页号，而是**索引列****+页号**。

4. 在对二级索引进行查找数据时，需要根据主键值去聚簇索引中再查找一遍完整的用户记录，这个过程叫做**回表**

## 联合索引 ##

以多个列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。

## 目录项记录的唯一性 ##

我们需要保证在B+树的同一层内节点的目录项记录除页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

1.  索引列的值
2.  主键值
3.  页号

# 总结 #

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

[20210227203046598.png]: /images/20221024/22b47e85a8e2477c93be783924a2f9bf.png
[48b606ef7999d232a7b079fe8845196f.png]: /images/20221024/edb7535a2c85407fa23e391aa106f8ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70]: /images/20221024/6ac040ca81084e34bafece27ffb03ee1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 1]: /images/20221024/1d9bab0f2027456f847f735c6e6c1397.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 2]: /images/20221024/0ea86ae7999b4b0aaaa86f92920c409f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2NDA4MjI5_size_16_color_FFFFFF_t_70 3]: /images/20221024/4babd165dca6414b8ef1522625bc5a5b.png