深入理解MySQL官方为何推荐使用自增主键
来源参考:
一、前言
在《高性能MySQL(第3版)》中,提及了这么一段话:
如果正在使用InnoDB表并且没有什么数据需要聚集,那么可以定义一个代理键(surrogate key)作为主键,这种主键的数据应该和应用无关,最简单的方法是使用
AUTO_INCREMENT
自增列。这样可以保证数据行是按顺序写入,对于根据主键做关联操作的性能也会更好。
为何书本的作者会在此推荐MySQL的在选用默认存储引擎InnoDB时使用自增主键?这可能得从InnoDB存储引擎底层的数据存储结构 B+树 开始说起。
二、B+树
B+树也是树结构大家族中的一员,但它的存在与其它的兄弟姐妹相对比却显得那么格格不入。如老大哥红黑树、二叉搜索树、二叉平衡树等都是 “高瘦“ 型的**,而B+树与它们相比显得有些 “矮胖” ,并且它的性格也十分古怪,只在叶子节点存储具体的数据信息,且叶子节点之间以链表的形式进行连接,非叶子节点只存储关键字的索引,不像传统的树结构每个节点都存储对应的具体数据信息。
虽然B+树在大家族中是一个“另类”的存在,但也正因为存在独特的性质,使得它与其它的兄弟姐妹相比,更适合作为数据库索引结构。
- 只有叶子节点存储具体的数据信息,减少了耗时的IO(磁盘)操作。
- 每个叶子节点都处于同一深度,使得查询每一条数据时的效率相当,查询效率稳定。
- 叶子节点之间使用链表进行连接,方便进行全表扫描。
需要注意的是: 为了方便范围查询的操作,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中按主键顺序插入可能会造成明显的争用。主键的上界会成为“热点”。因为所有的插入都发生在这里,所以并发插入可能导致间隙锁竞争。
因此,对于自增主键的使用仅是推荐,我们还需要结合自身项目的需求权衡利弊来做出最终的选择,毕竟抛开实际业务而不谈的行为都是“耍流氓”。