深入理解MySQL官方为何推荐使用自增主键

深入理解MySQL官方为何推荐使用自增主键

来源参考：

浅谈MySQL为何推荐使用自增主键

为什么 Mysql 推荐使用整形自增的主键而不使用 UUID

一、前言

在《高性能MySQL（第3版）》中，提及了这么一段话：

如果正在使用InnoDB表并且没有什么数据需要聚集，那么可以定义一个代理键（surrogate key）作为主键，这种主键的数据应该和应用无关，最简单的方法是使用 AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入，对于根据主键做关联操作的性能也会更好。

为何书本的作者会在此推荐MySQL的在选用默认存储引擎InnoDB时使用自增主键？这可能得从InnoDB存储引擎底层的数据存储结构 B+树 开始说起。

---

二、B+树

B+树也是树结构大家族中的一员，但它的存在与其它的兄弟姐妹相对比却显得那么格格不入。如老大哥红黑树、二叉搜索树、二叉平衡树等都是 “高瘦“ 型的**，而B+树与它们相比显得有些 “矮胖” ，并且它的性格也十分古怪，只在叶子节点存储具体的数据信息，且叶子节点之间以链表的形式进行连接，非叶子节点只存储关键字的索引，不像传统的树结构每个节点都存储对应的具体数据信息。

虽然B+树在大家族中是一个“另类”的存在，但也正因为存在独特的性质，使得它与其它的兄弟姐妹相比，更适合作为数据库索引结构。

1. 只有叶子节点存储具体的数据信息，减少了耗时的IO（磁盘）操作。
2. 每个叶子节点都处于同一深度，使得查询每一条数据时的效率相当，查询效率稳定。
3. 叶子节点之间使用链表进行连接，方便进行全表扫描。

需要注意的是： 为了方便范围查询的操作，InnoDB的B+树结构叶子节点之间是通过双链表进行连接的。详情可到MySQL官方文档查看：https://dev.mysql.com/doc/internals/en/innodb-fil-header.html

下图为一颗标准的B+树示例图：

---

三、页

在对InnoDB的存储结构B+树做了简单了解后，我们接着来了解 ”页” 的概念。

定义： 页是InnoDB存储引擎管理数据库的最小磁盘单位（默认情况下页的大小为 16kb ）。

如下图： InnoDB其中一页数据包含四条信息分别为：data1、data2、data3、data4，且每条数据的所占空间大小都为 4kb 。在不引入页的思想时，查询数据data1后，此时执行一次IO操作；如果我还想继续查询数据data2，需要再执行一次IO操作。总共执行了两次IO操作，但当我们引入页的思想后，查询数据data1时，将会查询出整一页的内容（data1、data2、data3、data4），接着查询data2时，由于内存中已经存在data2的数据了，因此无需再进行IO操作，在内存中获取即可。

笔者个人观点： 页的设计也应该归属于“空间换取时间”思想的一种，牺牲一点内存空间来节省在将来有可能会发生的IO操作的时间。

当然，以上观点只是笔者自己对“页”作用的一种理解，若存在不合理之处或您有更好的理解欢迎在下方评论区提出~！

---

---

四、页分裂

页分裂的讲解，笔者仅在这里描述其基本原理，不做深入的探究。对该知识点有强烈兴趣的读者，推荐阅读此篇博客：https://www.cnblogs.com/better-farther-world2099/articles/14704530.html

如下图：共有三个“页”（page1、page2、page3），其中page1存储了关键字在1 ~ 5的数据，page2存储了关键字在21 ~ 25的数据，page3只存储了一个关键字为32的数据。

若向库中新增一条关键字为33的数据，因为底层数据存储结构是B+树，数据关键之间是顺序的，且33 > 32， 因此只需要在page3中关键字为32的数据后面新增该数据即可，此时并不会出现“页分裂”的情况。

但当我们需要新增一条关键字为15的数据时，发现可以插入到page1的尾部，但是page1的空间已经满了，无法容下新数据15，新数据无法插进去。这时我们就需要考虑对page1进行分裂操作。

InnoDB的做法是将需要分裂的页从中间开始分裂，前半截继续保留在原来的页面，后半截移动到新开辟的页面中，最后再将需要新添加的数据插入。

第一步：从中间开始分裂

第二步：新开辟页面，将后半截数据移动至新页面中

第三步：将新数据15插入

---

补充知识（选择阅读）

既然说到了页分裂,就顺便提一下页合并吧,页合并与主键自增和非自增并没有关系,它是在删除行或者通过 UPDATE 操作缩短行时才有可能发生的情况

当 DELETE 或 UPDATE 缩短了行长度时,索引页的 “page-full” 百分比低于 MERGE_THRESHOLD 值,InnoDB 会尝试将索引页与相邻索引页合并.

MERGE_THRESHOLD 的最小值为 1,最大值为 50,默认值就是 50,当索引页面的 “page-full” 百分比低于 50％ 时,Innodb 会尝试将索引页与相邻页合并.如果两个页面都接近 50％ 已满,则在合并页面后很快就会发生页面拆分, 如果频繁发生此合并拆分行为,则可能会对性能产生负面影响.为避免频繁的合并拆分,可以降低 MERGE_THRESHOLD 值,以便 InnoDB 以较低的 “page-full” 百分比尝试页面合并,以较低页面满百分比合并页面会在索引页面中留出更多空间,并有助于减少合并拆分行为.可以为表或单个索引定义索引页的 MERGE_THRESHOLD,为单个索引定义的MERGE_THRESHOLD 值优先于为表定义的 MERGE_THRESHOLD 值.如果未定义,则 MERGE_THRESHOLD 值默认为 50.

下面就以删除来演示页合并现象

假设原始数据如下

现在我们要删除 id = 60 的数据行

当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用,删除了 id= 60 数据之后整个数据的存储并不会发生任何变化

当继续同时删除 id 为 41、44、51、54 的数据之后

页中删除的记录达到 MERGE_THRESHOLD(默认为页存储总量的50%),Innodb 会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用

---

五、总结

写到这里，其实笔者从始至终都没有直接提及推荐MySQL使用自增主键的原因。但已经对B+树、页分裂等概念做了基本了解的你，相信已经拥有了能够独立推断其中缘由的能力。

MySQL默认的InnoDB存储引擎，为了提高数据的查询效率选用叶子节点为双向链表连接的B+树作为数据存储结构，使得每一条数据都按照关键字有序排列好，在进行查找操作时，只需通过关键字即可快速定位数据位置。

与此同时，InnoDB引擎以页作为磁盘和内存交互的基本单位，为了保证每一条数据都能够有序的存储，每一次进行插入操作时，都有可能触发“页分裂”操作，因而拉低插入操作的效率。就如同我们为了提高某个字段的查询效率而为其增加辅助索引一般，虽然提高了查询效率，但伴随而来的“副作用”则是数据插入效率的降低。

“在程序猿的世界里，鱼和熊掌很难做到兼得，为了某一目的，你总得牺牲些什么。”

使用自增主键能够最大程度的避免“页分裂”的发生，从而减少这种“牺牲”的出现，同时也是解决该问题最便捷、有效的方法之一。

当然，还是那句话，我们很难做到两全其美的事情，使用自增主键是最便捷的，但绝对不是最优的解决方案，其也会为我们带来一定的“副作用”。

对于高并发工作负载，在InnoDB中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里，所以并发插入可能导致间隙锁竞争。

因此，对于自增主键的使用仅是推荐，我们还需要结合自身项目的需求权衡利弊来做出最终的选择，毕竟抛开实际业务而不谈的行为都是“耍流氓”。