MySQL InnoDB存储及索引原理

我会带着你远行 2024-04-17 23:30 65阅读 0赞

**MySQL查询执行流程**

MySQL执行一条SELECT查询SQL起自于客户端发起的一条请求。MySQL服务收到请求后首先根据SQL查询缓存，若果该查询SQL已经事先执行过，则直接返回缓存中的数据给客户端。否则进入SQL解析优化阶段。MySQL解析器对SQL进行语法解析，生成解析树，然后经过预处理阶段生成一棵新的语法解析树。将解析树送入查询优化器中生成执行计划。查询执行引擎根据执行计划调用存储引擎的API查询数据。

由于InnoDB是MySQL默认的存储引擎，也是我们最常用到的存储引擎，我们也没有那么多时间去把各个存储引擎的内部实现都看一遍，所以本集要唠叨的是使用InnoDB作为存储引擎的数据存储结构，了解了一个存储引擎的数据存储结构之后，其他的存储引擎都是依葫芦画瓢。

**InnoDB页的概念**

为了提高读写效率，MySQL将磁盘数据划分为页。页是MySQL和磁盘读写数据交互的基本单位，InnoDB的页的大小一般为16KB，也就是一次从磁盘读取数据的数据量最小为16KB，写入时，一次最少写入16KB的数据。

**InnoDB行的格式**

设计InnoDB存储引擎的大叔们到现在为止设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。

我们可以在创建或修改表的过程中指定行格式，如：

CREATE TABLE 表名 (列的信息) ROW\_FORMAT=行格式名称

ALTER TABLE 表名 ROW\_FORMAT=行格式名称

下面我们展开介绍这四种行格式。

**Compact行格式**

Compact行格式示意图如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70][]

从图中可以看出，Compact行格式分为两部分：额外信息和真实数据。

**额外信息**

**变长字段长度列表：**

如一些varchar(n)、Text、BLOB类型属于变长字段类型。变长字段长度列表就是记录这些变长字段长度的。需要注意的是，这里是按字段顺序的逆序存放，举例。

Compact行：

<table> 
 <tbody> 
  <tr> 
   <td style="border-color:#dbdbdb;"> <p>3|2|1</p> </td> 
   <td style="border-color:#dbdbdb;">NULL值列表</td> 
   <td style="border-color:#dbdbdb;">头信息</td> 
   <td style="border-color:#dbdbdb;"> <p>1</p> </td> 
   <td style="border-color:#dbdbdb;"> <p>2</p> </td> 
   <td style="border-color:#dbdbdb;"> <p>3</p> </td> 
  </tr> 
 </tbody> 
</table>

NULL值列表：

为了节省空间，NULL值是不需要存储的。所以Compact把这一行中所有的NULL值统一管理起来，放到”NULL

值列表“中。每一个NULL值对应一个二进制位，NULL值列表中的数据是将二进制位逆序进行存放的。

二进制位1时，代表对应位置的列为NULL。二进制位为0时代表对应位置不为NULL。

例如：

NULL值列表二进制表示为011，十六进制为0x03

Compact行：

**头信息：**

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 1][]

这些二进制位代表的详细信息如下表：

<table> 
 <tbody> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>名称</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>大小（单位：bit）</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>描述</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>预留位1</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>1</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>没有使用</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>预留位2</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>1</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>没有使用</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>delete_mask</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>1</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>标记该记录是否被删除</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>min_rec_mask</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>1</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>B+树的每层非叶子节点中的最小记录都会添加该标记</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>n_owned</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>4</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>表示当前记录拥有的记录数</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>heap_no</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>13</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>表示当前记录在记录堆的位置信息</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>record_type</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>3</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;text-align:center;"> <p>next_record</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>16</p> </td> 
   <td style="border-color:#cccccc;text-align:center;"> <p>表示下一条记录的相对位置</p> </td> 
  </tr> 
 </tbody> 
</table>

大家不要被这么多的属性和陌生的概念给吓着，我这里只是为了内容的完整性把这些位代表的意思都写了出来，现在没必要把它们的意思都记住，记住也没啥用，现在只需要看一遍混个脸熟，等之后用到这些属性的时候我们再回过头来看。

**记录真实数据**

这里再看一下Compact的行格式。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 2][]

数据部分为用户插入的真实数据。这里需要注意的是MySQL主键生成策略。如果用户定义有主键，则使用用户定的列为主键。否则查找是否定义有Unique唯一列作为主键，如果Unique列也没有，MySQL则增加一列隐藏列Row\_ID作为主键。

**InnoDB数据页的存储结构**

数据页是InnoDB的基本存储单位，一页为16KB。数据页的结构如下所示：

<table style="width:176px;"> 
 <tbody> 
  <tr> 
   <td style="border-color:#ccb879;"> <p>文件头部</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#46999d;"> <p>页面头部</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#c9a6ab;"> <p>最小记录和最大记录</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#a4bead;"> <p>用户记录</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#4387b8;"> <p>空闲空间</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#bbbbbb;"> <p>页面目录</p> </td> 
  </tr> 
  <tr> 
   <td style="border-color:#cbb29a;"> <p>文件尾部</p> </td> 
  </tr> 
 </tbody> 
</table>

<table style="width:441px;"> 
 <tbody> 
  <tr> 
   <td style="border-color:#bbbbbb;">名称</td> 
   <td style="border-color:#bbbbbb;">描述</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>文件头部</p> </td> 
   <td style="border-color:#cccccc;">页的一些通用信息</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>页面头部</p> </td> 
   <td style="border-color:#cccccc;">数据页专有的一些信息</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>最小记录和最大记录</p> </td> 
   <td style="border-color:#cccccc;">两个虚拟行</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>用户记录</p> </td> 
   <td style="border-color:#cccccc;">实际存储内容</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>空闲空间</p> </td> 
   <td style="border-color:#cccccc;">页內尚未使用空间</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>页面目录</p> </td> 
   <td style="border-color:#cccccc;">页內记录相对位置</td> 
  </tr> 
  <tr> 
   <td style="border-color:#cccccc;"> <p>文件尾部</p> </td> 
   <td style="border-color:#cccccc;">校验页的完整性</td> 
  </tr> 
 </tbody> 
</table>

以上为页的结构和各部分的简介。那么数据页中的真实数据是怎么存放的呢？这个要从前面的行格式说起。

Compact行格式的头信息暗藏玄机，如图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 3][]

图中头信息展开后，包含如下几部分：

**简化后如图所示。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 4][]

MySQL会自动生成两条虚拟记录，存放在数据页的最小记录和最大记录中。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 5][]

应该注意到，行数据头信息中的next\_record，它标识从当前记录到吓一条记录的偏移量。下一条记录并不是按插入顺序的下一条，而是按主键大小排序的，由小到大的下一条记录。而且规定，本页中最小记录的下一条就是本页的第一条数据，最后一条数据的下一条就是最大纪录，如图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 6][]

删除行后，数据仍然会存在，只是行的头信息的delete\_mark会标识为1。如果再有插入的数据，可能会复用已经删除的空间。

**页目录**

以上我们了解到，页中的数据是按照主键大小串成一串。头尾为页的虚拟记录。如果我们想定位到某一行数据，该怎么处理呢？如执行SQL（student\_Id为Student表的主键）：

Select \* from Student Where student\_Id = ‘201011201'

此时，最笨的办法就是按照过滤条件student\_Id = ‘201011201’，从每一页的第一条数据查到最后一条。但是如果数据量大的情况下，这种办法的效率就堪忧了。

那么，既然页中行是按照主键大小来存储的，是否可以对页面中的行数据进行分组？

答案是肯定的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 7][]

MySQL将数据页的用户数据分组进行管理，页內第一组和最后一组分别为虚拟的最小记录和最大记录，其中不包含标记为已删除的数据。每个组最后一条记录的头信息中n\_owned属性表示该组有多少条记录。（n\_owned表示当前行在组内的偏移量）

将每个组最后一条记录的偏移量取出来，放到页面中的页面目录部分，这些目录成为槽。所以，这个页面的目录由槽组成。

通常，组内数据为5行。

分组是按照下边的步骤进行的：

*  初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
 *  之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n\_owned值加1，表示本组内又添加了一条记录，直到该组中的记录数等于8个。
 *  在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

MySQL将不同的数据页用双向链表连接起来，页内部用单向链表连接。

**InnoDB 聚簇索引**

加入没有索引，查找数据需要先遍历数据页，定位到目标数据所在页后，按照链表顺序，从最小记录开始遍历，直到找到目标数据或者最大纪录为止。

前面提到了数据页内有槽的存在，槽其实就是一个数据组的目录。那么，数据页是否也可以有一个类似于槽的目录呢？

答案是肯定的。MySQL使用每一个数据页的最小主键值最为Key，数据页的页号作为page\_no，生成一个数据页的目录。如下图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 8][]

以图中页9为例，数据页9中最小主键值为12，因此，目录项为12/9。如果想查找主键值为100的数据，因为100>12 并且 100<209,因此数据定位于目录项3中，也就是页9中。然后，使用二分法找到对应的槽，进而找到槽对应的行数据。一次使用索引查找数据的操作完成。这里的目录就是对应到MySQL的索引。

MySQL索引的结构如图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 9][]

其实就是讲上述的页目录归置为一个特殊的页。页中行数据有两个字段，分别为主键Key和page\_no。但是怎么区分是数据页还是这个特殊的页呢？大家还记得头信息中有个字段为record\_type吗？它的取值如下：

*  0：普通的用户记录
 *  1：目录项记录
 *  2：最小记录
 *  3：最大记录

也就是说，数据页的普通数据record\_type为0，最大最小分别为3、2，上述特殊的页为1，称为目录项记录。

目录页的存储容量是有限的，如果数据量达到临界值，一个目录项就满足不了需求了。需要增加目录项页来保存目录索引。如果数据量较大，那么目录项页页会变得非常多，如此一来，查询又变得困难。其实解决方案很简单，那就是再给目录项页建立一个目录。就像是一个多级目录一样，大目录套着小目录。一层又一层，大概就是这个意思：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 10][]

以此类推，目录层数可以一直往上抽象，直到只剩一个目录页为止。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 11][]

再重新审视这个图，那不就是一棵树吗？其实他的名字叫B+树。

从图中可以看出，树的数据全都在叶子上，目录索引节点中不含数据。存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。这样一来，索引和数据在一起，索引即数据，数据即索引，这里的索引也叫做**聚簇索引**。

**二级索引**

聚簇索引是以主键为基准建立的查找树。但是如果索引建立在了非主键字段上怎么处理呢？那就是二级索引。

我们可以多建几棵树。不同的树按照不同的排序规则生长。如我们使用非主键C2建立查找树。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 12][]

这棵树是按照C2字段值将数据进行从小到大排序生成。叶子节点每行数据仅有两个字段：C2和数据主键。如果按照C2查询数据，使用这课树快速定位到行数据，然后拿到对应的主键，再使用聚簇索引查找目标数据即可。

联合索引

联合索引是将多列作为索引字段建立B+树。但是实际上MySQL只会为一个联合索引建立一个B+树。如使用C2和C3作为联合索引，规则如下：

*  先把各个记录和页按照c2列进行排序。
 *  在记录的c2列相同的情况下，采用c3列进行排序

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 13][]

每条目录项记录都由c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的c2列相同，则按照c3列的值进行排序。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/172a313ba6234c79bac8e1aaf02b69e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/95557737b6bd439383d7f4795bf4a2b5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 2]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/2de42907343b40398d31f01006fda50f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 3]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/6ce736ccef7b48dba90d06d670de653a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 4]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/b18a84e0d63d4575996ceebbd94fba54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 5]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/aea5e93c977b40e1890bb03fd609f19d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 6]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/3b253929cf1d455dabc1e1b0733283af.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 7]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/21eccec9a403441591567ffa0096dee1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 8]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/a0b66250574e4afda64db0ee68e8902e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 9]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/dcdee680e9c145f2a9f4eee079010dc9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 10]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/8d69c97d9c894bd3846d2b2d6a137b15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 11]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/ae74fcaa2b314e23b1b2db4b30019e55.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 12]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/78fba2a614ff4ccf8d36c25d6ca9efe7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29RaW5nVGlhbjEyMzQ1Ng_size_16_color_FFFFFF_t_70 13]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/b216bd09526f4b4da97a200097d07280.png