前言

应"极客时间"之约，写了这个文章... ...

1.提出问题

学习技术，如武功修炼，学会招式很重要，但掌握它的内在心法更重要，这样一来，才能成为高手。

比如，像使用MySQL设计表结构时，建主键是避免不了的。但大家知道主键的长度是会影响到查询性能的么？这其中到底是受什么影响的呢？虽然这个问题在你使用MySQL进行业务系统设计时，不是那么显著的问题。但理解了这个问题的本质，尤其是Mysyql Innodb存储引擎中B+树的数据结构、构建这个数据结构的过程、及结构与查询性能间的关系，你将会获得不同的新的认知。了解了这个过程，那么之后在使用其他存储系统时，也会有相关的对比经验。

现在，就以“为什么MySQL设计主键时key不能过长”这个问题为切入点，通过分析Innodb 表空间文件和数据页的结构来看一下Mysql Innodb在表的数据索引结构和查询性能上的关系。

2.解决问题的核心关键点

众所周知，Innodb引擎是使用B+树这种数据结构来存储数据库表中的数据的。表的主键存储在B+树的叶子节点上，叶子节点上还存储着主键所关联的行记录数据，也就是我们常说的聚集索引，它存储着整张表的数据。但这只是逻辑上的描述。

（图1 - 聚集索引树逻辑表达）

只从这个知识点上，是不能理解为什么主键key的长度和查询性能间的关系的。为了解决这个问题，我们需要更深入一层，可以从几个关键点来理解：

一个是聚集索引的底层物理结构，需要理解在这个物理结构上它是如何存储数据的；

再来说说第二个关键点，也就是理解查找遍历数据时，遍历操作是如何基于B+树这个逻辑和物理结构进行查找数据的；

最后，来看看第三个关键点，你需要理解在构建这颗B+树和遍历时，都有哪些因素会影响到查询性能，并需要在各个影响因素下做一下对比来深入理解。

为了能够更加形象地说明，这里举一个例子，并创建一个数据库和一个数据库表。表test1的结构是这样的:

(图2 - 表test1的结构)

其中，主键索引p_k 和二级索引s_k都是一个Int 类型的字段，并通过python脚本导入100000行数据记录：

(图3 - 导入数据脚本)

3.解决问题

导入后，我们可以看到Innodb会创建表空间文件来存储数据，比如例子中的ibdata1文件（图4）和dbtest目录中test1.ibd文件（图5）。其中，dbtest 是数据库名，ibdata1是系统表空间文件，test1.ibd文件则实际存储着表中的数据：

(图4 - 系统表空间文件) （图5 - test1的表空间文件）

在开始分析表空间文件之前，我们需要建立这样几点认知：

一是，Innodb存储引擎中存储数据的最小单位是页，也就是page，默认情况下，这个页所占用的磁盘空间大小为16KB；

第二点认知是，Innodb引擎一个数据页中一个数据行记录的大小，不能超过8000个字节。因此，一个数据页中在行记录最大size的情况下，只能最多能存储2行记录；如果一个数据页存不下一行记录，会创建新的数据页来存储；

最后，再来说说第三点认知，在Innodb存储引擎中，会有好几种页类型来存储不同类别的数据。其中Index索引页是专门用于存储表的行数据的。

我们可以通过一些开源的Innodb 表空间文件分析工具，来查看表空间内部的数据信息。结合这张图来看，你可以看到类型为Index的就是B+Tree 索引，基于我们的例子，在Innodb中，存储100000行数据和创建的二级索引数据，共占据了301个索引页，大约占据了36%的存储空间：

（图6 - 表空间文件中的不同数据页类型）

当然，还可以通过其他的命令模式，来查看更细粒度的信息，看下这张图（图7），你可以看到索引数据页被分成了两个部分。其中一个是以索引树root page 编号为3 的索引，另一个是以索引树root page 编号为4的索引，编号为3的索引树总共有216个页，编号为4的索引树总共有85个，这其实是分别对应我们例子中表test1的主键聚集索引p_k和二级索引s_k两颗B+树的根节点数据页，因为聚集索引叶子节点中存储了整行的数据。而二级索引存储的是二级索引字段的值和主键字段的值，并没有存储例子表中图2的那个名为‘c’的字符列，所以主键索引树占据的数据页要比二级索引树占据的数据页多一些：

（图7 - 索引数据页基础信息）

另外，我们再来查看下聚集索引树中的结构，从这张图来看（图8），表test1在存储100000行数据的情况下，聚集索引树总共分裂成了两层，第一层是根页面节点，第二层为叶子节点并存储着行数据。编号为3的根页面节点，总共存储了215个记录，其中第一个记录所存储的数据为主键的值，也就是 p_k=0 及其所指向的叶子节点页面编号指针，比如图例为Leaf Node #5的叶子节点。可以看到，叶子节点的上一层所指向的非叶子节点存储的数据记录，总是其叶子节点中主键的最小值：

（图8 - 聚集索引树中每个节点存储的数据）

通过这张图（图9），可以更加形象化地看到聚集索引树的大体物理存储结构：

（图9 -形象化表示聚集索引树结构）

不难看出，非叶子节点的分叉数量和子节点数据页的数量，以及自身数据页所能存储的记录数有关。

接下来，让我们再看看一个数据页的内部结构，以便更好地理解查找遍历数据的过程。这里我们Dump编号为7的数据页的数据结构信息，可以在图10和图11中看到一些关键信息：

一个是：数据页中存在一个叫做Page Directory的页目录，它总共占据了236个字节，共有118个Entry：

（图10 - 编号7数据页中的页目录大小） （图11 - 编号7数据页中的页目录列表）

再来看看第二个关键信息，除了第一个槽位和最后一个槽位，每个page directory 槽管理了4个行记录，而页面目录Entry关联存储着所管理主键值最小的那一行记录的偏移量；

（图12 - 编号7数据页中的页目录详细信息）

最后一个关键信息就是：在一个数据页中，各个数据行通过记录下一个数据行的偏移量offset，从而构成了一个单向链表。

（图13 - 编号7数据页中的页目录详细信息）

那么，在一个SQL语句中给定一个主键值作为查询条件并返回所在行的数据，会经历一个怎么样的查找遍历数据过程呢？我们通过分析工具模拟Innodb在聚集索引树上进行二分查找主键，可以看到具体所遍历的数据页和页面目录的过程。比如，我们查找主键值为88888的记录，如下图（图14）所示：

（图14 - 查找遍历数据结果）

可以看到，遍历这颗聚集索引树，总共遍历了2个数据页：

首先，在编号3的根节点数据页中，通过Page Directory（页目录）二分查找，经历了6次折半遍历和4次顺序遍历;

其次，在编号350的子节点数据页中，通过Page Directory（页目录）二分查找，总共经历了8次折半遍历和4次顺序遍历。

（图15 - 查找遍历数据的概要信息）

由此可见，在聚集索引树上通过主键查找一个数据记录时，需要经历从根节点到非叶子节点，再到叶子节点的过程，并且在每个数据页中通过Page Directory（页目录）进行二分查找，来进一步缩减顺序查找的范围，可以参看如下流程图（图16）：

（图16 - 查找遍历数据的逻辑流图）

而从性能角度来说，查找遍历索引数据时，所遍历的数据页Page 数量越多，耗时越多，因为从磁盘装载数据页，都需要耗费一次IO，从而可以确信地推断出来，索引树中非叶子数据页的数量和叶子数据页节点的数量越多，查找耗时也会越多。

前面的例子中，还只是展示了直接通过主键索引为条件，去查找遍历聚集索引树返回数据行的过程。而一般实际情况下，很多都是先通过二级索引树来先找到主键索引，然后再通过主键索引树查找数据的。二级索引树也是一个B+树的逻辑结构，非叶子节点存储的是二级索引Key，叶子节点存储的是二级索引Key和主键索引值，参看图17

（图17 - 二级索引数据页结构）

好了，到这里，我们已经看到通过分析Innodb 的表空间文件和数据页的结构，能够理解到查找遍历数据是基于数据页的，并且一个数据页内存储的数据行数是有限制的，从而导致超过数据页限制的数据行会被分裂到新的数据页当中。

接下来我们创建另外一张表，通过加大主键key的长度，来和第一张表test1做对比分析。这一次我们将主键key从Int类型8字节长度变成可变字符类型。这里，我们设定主键Key最大可存储300个字符的varchar（300），看看这么长的主键Key与Int类型的主键存储相同数据量的数据时，在查找性能上会有什么差别。

表test2的结构是这样的（图18），同样地，我们可以通过脚本导入100000行数据。

（图18 - 表test2结构） （图19 - 导入脚本） （图20 - 导入脚本）

其中，主键的取值为一个长度为300个字符并且从尾字母开始依次从‘a’,’b’,’c’,’d’,’e’,’f’,’g’六个字符中向高位循环递增的字符，例如：

（图21 - 表test2主键值）

导过数据之后，表test2的空间文件test2.ibd 如下图所示：

（图22 - test2的表空间文件）

并且，通过查询聚集索引的数据页结构详细信息，可以看到，同样一行记录，主键索引取值变长且在二级索引字段s_k和字符列’c’列取值不变的情况下，聚集索引树的高度已经变成了3层，且每个数据页中所包含的数据行数大大减少。

（图23 - 索引数据页信息） （图24 - 索引数据页中的页目录）

那么，当我们在这颗聚集索引树上查找遍历数据时，性能又会如何呢？为了对比，我们在表test2中查找与表test1具有同样相对位置的数据行，比如，二级索引s_k = 88888 对应的记录，模拟查找遍历的过程和概要信息如图25，26：

（图25 - 查找遍历一个数据行的过程） （图26 - 查找遍历一个数据行的概要信息）

可见，查找遍历所装载的数据页变成了3个页面，这意味着3次磁盘IO，相比主键为Int类型8字节长度的表test1多了一次IO，且比较主键key的次数也变多了，这些数字的变化都意味着查询效率的变低。

4.总结

到此，我们来小结一下，主键长度变长和查询遍历数据效率之间的关系：

首先，存储表中各个数据行的聚集索引逻辑结构，在物理表现上，是将各个行按主键值顺序插入到各个索引数据页当中；

其次，受限于一个数据页能够存储的数据行大小的限制，无论是聚集索引还是二级索引数据页，如果主键长度设定的过长，都将会导致数据页分裂，尤其是索引树的非叶子节点数据页和二级索引的叶子节点数据页，使得数据页内部能够存储的记录数量变少；

从而，使得查找遍历数据时，不得不在相对更多的数据页中进行查找，增加了装载数据页的耗时和比较主键key的次数，降低了效率。

5.延伸思考

这里，关于Mysql主键长度和查找性能关系的话题，就先说到这儿。大家也可以自己开脑洞，在数据存储层面去看看还有哪些因素会影响查询性能，比如：

1）如果以乱序的方式将数据插入到Mysql Innodb表中时，对于性能是否有影响，影响的因素是什么；

2）或者，如果在高并发且设置了自增主键的情况下，那么锁竞争对于插入性能的影响？Mysql Innodb 又提供了哪些方案来应对？

另外，还有几点想谈一下：

1）分析和学习一项技术，掌握其范式非常重要。比如，数据存储，那么对其基础数据存储模型和结构是要先构建认知的；其次是需要多收集相关的信息，通过对比、发散、联想、举例实践，进而加深理解；然后是总结、输出；

2）平时注重不断温习数据结构、算法和编程模型（好的、"别人"的代码是怎么写的，为什么这样写？）等基础。只有拥有了好的基础，才能更快、更准确的理解与接收。