为什么 MySQL 单表不能超过 2000 万行？
最近看到一篇《我说 mysql 每张表最好不要超过 2000 万数据，面试官让我回去等通知》的文章，非常有趣。
文中提到，他朋友在面试的过程中说，自己的工作就是把用户操作信息存到 mysql 里，因为数据量超大（5000 万条左右），需要每天定时生成 3 张表，然后将数据取模分别存到这三张表里。
接下来是两人的对话：
面试后续暂且不论，不过，互联网江湖上的确流传着一个说法：单表数据量超过 500 万行时就要进行分表分库，已经超过 2000 万行时 mysql 的性能就会急剧下降。
那么，mysql 一张表最多能存多少数据？
今天我们就从技术层面剖析一下，mysql 单表数据不能过大的根本原因是什么？
猜想 1，是索引深度吗？
很多人认为：数据量超过 500 万行或 2000 万行时，引起 b+tree 的高度增加，延长了索引的搜索路径，进而导致了性能下降。事实果真如此吗？
我们先理一下关系，mysql 采用了索引组织表的形式组织数据，叶子节点存储数据，非叶子节点存储主键与页面号的映射关系。若用户的主键长度是 8 字节时，mysql 中页面偏移占 4 个字节，在非叶子节点的时候实际上是 8+4=12 个字节，12 个字节表示一个页面的映射关系。
mysql 默认是 16k 的页面，抛开它的配置 header，大概就是 15k，因此，非叶子节点的索引页面可放 15*1024/12=1280 条数据，按照每行 1k 计算，每个叶子节点可以存 15 条数据。同理，三层就是 15*1280*1280=24576000 条数据。只有数据量达到 24576000 条时，深度才会增加为 4，所以，索引深度没有那么容易增加，详细数据可参考下表：
搜索路径延长导致性能下降的说法，与当时的机械硬盘和内存条件不无关系。
之前机械硬盘的 iops 在 100 左右，而现在普遍使用的 ssd 的 iops 已经过万，之前的内存最大几十 g，现在服务器内存最大可达到 tb 级。
因此，即使深度增加，以目前的硬件资源，io 也不会成为限制 mysql 单表数据量的根本性因素。
那么，限制 mysql 单表不能过大的根本性因素是什么？
猜想 2，是 smo 无法并发吗？
我们可以尝试从 mysql 所采用的存储引擎 innodb 本身来探究一下。
大家知道 innodb 引擎使用的是索引组织表，它是通过索引来组织数据的，而它采用 b+tree 作为索引的数据结构。b+tree 操作非原子，所以当一个线程做结构调整（smo，struction-modification-operation）时一般会涉及多个节点的改动。
smo 动作过程中，此时若有另一个线程进来可能会访问到错误的 b+tree 结构，innodb 为了解决这个问题采用了乐观锁和悲观锁的并发控制协议。
innodb 对于叶子节点的修改操作如下：
方法一，先采用乐观锁的方式尝试进行修改。
对根节点加 s 锁（shared lock，叫共享锁，也称读锁），依次对非叶子节点加 s 锁。
如果叶子节点的修改不会引起 b+tree 结构变动，如分裂、合并等操作，那么只需要对叶子节点进行加 x 锁（exclusive lock，叫排他锁，也称为写锁）即可完成修改。如下图中所示 ：
方式二，采用悲观锁的方式
如果对叶子结点的修改会触发 smo，那么会采用悲观锁的方式。
采用悲观锁，需要重新遍历 b+tree，对根节点加全局 sx 锁（sx 锁是行锁），然后从根节点到叶子节点可能修改的节点加 x 锁）。在整个 smo 过程中，根节点始终持有 sx 锁（sx 锁表示有意向修改这个保护的范围，sx 锁与 sx 锁、x 锁冲突，与 s 锁不冲突），此时其他的 smo，则需要等待。
因此，innodb 对于简单的主键查询比较快，因为数据都存储在叶子节点中，但对于数据量大且改操作比较多的 tp 型业务，并发会有很严重的瓶颈问题。
在对叶子节点的修改操作中，innodb 可以实现较好的 1 与 1、1 与 2 的并发，但是无法解决 2 的并发。因为在方式 2 中，根节点始终持有 sx 锁，必须串行执行，等待上一个 smo 操作完成。这样在具有大量的 smo 操作时，innodb 的 b+tree 实现就会出现很严重的性能瓶颈。
解决方案
目前业界有一个更好的方案 b-link tree，与 b+tree 相比，b-link tree 优化了 b+tree 结构调整时的锁粒度，只需要逐层加锁，无需对 root 节点加全局锁，因此，可以做到在 smo 过程中写操作的并发执行，保持高并发下性能的稳定。
主要改进点有 2 个：
1.中间节点增加 link 指针，指向右兄弟节点；
2.每个节点内增加字段 high key，存储该节点中最大的 key 值。
新增的 link 指针便是为了解决 smo 过程中并发写的问题，在 smo 过程中，b-link tree 对修改节点逐层加锁，修改完一层即可放锁，然后去加上一层节点的锁继续修改。这样在 innodb 引擎中被 smo 阻塞的写操作可以有机会再 smo 操作过程中并发进行。
如下图所示，在节点 2 分裂为节点 2 和 4 的过程中，只需要在最后一步将父节点 1 指向新节点 4 时，对父节点 1 加锁，其他操作均无需对父节点加锁，更无需对 root 节点加锁，因此，大大提升了 smo 过程中写操作的并发度。
由此可见，和 b+tree 全局加锁对比起来，b-link tree 在高并发操作下的性能是显著优于 b+tree 的。华为云 gaussdb 当前采用的就是 b-link tree 索引数据结构。
innodb 的索引组织表更容易触发 smo
索引组织表的叶子节点，存储主键以及应对行的数据，innodb 默认页面为 16k，若每行数据的大小为 1000 字节，每个叶子节点仅能存储 16 行数据。
在索引组织表中，当叶子节点的扇出值过低时，smo 的触发将更加频繁，进而放大了 smo 无法并发写的缺陷。
目前业界有一个堆组织表的数据组织方案，也是华为云数据库 gaussdb 采用的方案。它的叶子节点存储索引键以及对应的行指针（所在的页面编号及页内偏移），堆组织表叶子节点可以存更多的数据，分析可得在同样的数据量与业务并发量下，堆组织表会比索引组织表发生 smo 概率低许多。
性能对比
在 8u32g 的两台服务器分别搭建了 mysql（b+tree 和索引组织表）与 gaussdb（b-link tree 和堆组织表）的环境，进行了如下性能验证：
实验场景：在基础表的场景上，测试增量随机插入性能。
1.基础表总大小 10g，包含主键随机分布的 1000w 行数据，每行数据 1k；
2.插入主键随机分布的 1000w 行数据，每行数据大小 1k，测试并发插入性能。
结论：随着并发数的上升，gaussdb 能稳步提升系统的 tps，而 mysql 并发数的提高并不能带来 tps 的显著提升。
总结
mysql 无法支持大数据量下并发修改的根本原因，是因为其索引并发控制协议的缺陷造成的，而 mysql 选择索引组织表，又放大了这一缺陷。所以，开源 mysql 数据库更适用于主键查询为主的简单业务场景，如互联网类应用，对于复杂的商业场景限制比较明显。
相比之下，采用 b-link tree 和堆组织表的 gaussdb 数据库在性能和场景应用方面更胜一筹。
电感器的充放电过程
Vivox60如何切换系统，如何切换到经典桌面
STM8S如何实现Atomthreads最低功耗
战事升级：高通要求美国禁止进口iPhone
合力泰积极布局LCP柔性线路板领域，解决智能终端产品高需求
为什么 MySQL 单表不能超过 2000 万行？
!销售/收购/维修MT8852A蓝牙测试仪MT8852B!
魅族PRO7用三星8895还是联发科X30？黄章：都不答应！
霍金其实已经死了？著名物理学家霍金去世我们带你了解一个真实的霍金
电动机有几种干燥方法
吉利汽车与沃尔沃达成最新的合并方案
虹科干货 | MQTT 5协议中的基础更改（一）
比特币转账的手续费到底该如何计算
RFID标签读写器让工地仓库管理转向无人化、数字化
碳化硅二极管的应用领域及优势你知道吗
英飞凌寻求30亿欧元以下的收购 228亿人民币
安森美半导体推出移动医疗电子设备的系统级封装方案
如何使用Python-OpenCV实现餐盘水果识别与计价的应用
常见液压元件原理动图
联芸科技发布新Agile ECC技术，提升QLC闪充性能和使用寿命
文中提到，他朋友在面试的过程中说，自己的工作就是把用户操作信息存到 mysql 里，因为数据量超大（5000 万条左右），需要每天定时生成 3 张表，然后将数据取模分别存到这三张表里。
接下来是两人的对话：
面试后续暂且不论，不过，互联网江湖上的确流传着一个说法：单表数据量超过 500 万行时就要进行分表分库，已经超过 2000 万行时 mysql 的性能就会急剧下降。
那么，mysql 一张表最多能存多少数据？
今天我们就从技术层面剖析一下，mysql 单表数据不能过大的根本原因是什么？
猜想 1，是索引深度吗？
很多人认为：数据量超过 500 万行或 2000 万行时，引起 b+tree 的高度增加，延长了索引的搜索路径，进而导致了性能下降。事实果真如此吗？
我们先理一下关系，mysql 采用了索引组织表的形式组织数据，叶子节点存储数据，非叶子节点存储主键与页面号的映射关系。若用户的主键长度是 8 字节时，mysql 中页面偏移占 4 个字节，在非叶子节点的时候实际上是 8+4=12 个字节，12 个字节表示一个页面的映射关系。
mysql 默认是 16k 的页面，抛开它的配置 header，大概就是 15k，因此，非叶子节点的索引页面可放 15*1024/12=1280 条数据，按照每行 1k 计算，每个叶子节点可以存 15 条数据。同理，三层就是 15*1280*1280=24576000 条数据。只有数据量达到 24576000 条时，深度才会增加为 4，所以，索引深度没有那么容易增加，详细数据可参考下表：
搜索路径延长导致性能下降的说法，与当时的机械硬盘和内存条件不无关系。
之前机械硬盘的 iops 在 100 左右，而现在普遍使用的 ssd 的 iops 已经过万，之前的内存最大几十 g，现在服务器内存最大可达到 tb 级。
因此，即使深度增加，以目前的硬件资源，io 也不会成为限制 mysql 单表数据量的根本性因素。
那么，限制 mysql 单表不能过大的根本性因素是什么？
猜想 2，是 smo 无法并发吗？
我们可以尝试从 mysql 所采用的存储引擎 innodb 本身来探究一下。
大家知道 innodb 引擎使用的是索引组织表，它是通过索引来组织数据的，而它采用 b+tree 作为索引的数据结构。b+tree 操作非原子，所以当一个线程做结构调整（smo，struction-modification-operation）时一般会涉及多个节点的改动。
smo 动作过程中，此时若有另一个线程进来可能会访问到错误的 b+tree 结构，innodb 为了解决这个问题采用了乐观锁和悲观锁的并发控制协议。
innodb 对于叶子节点的修改操作如下：
方法一，先采用乐观锁的方式尝试进行修改。
对根节点加 s 锁（shared lock，叫共享锁，也称读锁），依次对非叶子节点加 s 锁。
如果叶子节点的修改不会引起 b+tree 结构变动，如分裂、合并等操作，那么只需要对叶子节点进行加 x 锁（exclusive lock，叫排他锁，也称为写锁）即可完成修改。如下图中所示 ：
方式二，采用悲观锁的方式
如果对叶子结点的修改会触发 smo，那么会采用悲观锁的方式。
采用悲观锁，需要重新遍历 b+tree，对根节点加全局 sx 锁（sx 锁是行锁），然后从根节点到叶子节点可能修改的节点加 x 锁）。在整个 smo 过程中，根节点始终持有 sx 锁（sx 锁表示有意向修改这个保护的范围，sx 锁与 sx 锁、x 锁冲突，与 s 锁不冲突），此时其他的 smo，则需要等待。
因此，innodb 对于简单的主键查询比较快，因为数据都存储在叶子节点中，但对于数据量大且改操作比较多的 tp 型业务，并发会有很严重的瓶颈问题。
在对叶子节点的修改操作中，innodb 可以实现较好的 1 与 1、1 与 2 的并发，但是无法解决 2 的并发。因为在方式 2 中，根节点始终持有 sx 锁，必须串行执行，等待上一个 smo 操作完成。这样在具有大量的 smo 操作时，innodb 的 b+tree 实现就会出现很严重的性能瓶颈。
解决方案
目前业界有一个更好的方案 b-link tree，与 b+tree 相比，b-link tree 优化了 b+tree 结构调整时的锁粒度，只需要逐层加锁，无需对 root 节点加全局锁，因此，可以做到在 smo 过程中写操作的并发执行，保持高并发下性能的稳定。
主要改进点有 2 个：
1.中间节点增加 link 指针，指向右兄弟节点；
2.每个节点内增加字段 high key，存储该节点中最大的 key 值。
新增的 link 指针便是为了解决 smo 过程中并发写的问题，在 smo 过程中，b-link tree 对修改节点逐层加锁，修改完一层即可放锁，然后去加上一层节点的锁继续修改。这样在 innodb 引擎中被 smo 阻塞的写操作可以有机会再 smo 操作过程中并发进行。
如下图所示，在节点 2 分裂为节点 2 和 4 的过程中，只需要在最后一步将父节点 1 指向新节点 4 时，对父节点 1 加锁，其他操作均无需对父节点加锁，更无需对 root 节点加锁，因此，大大提升了 smo 过程中写操作的并发度。
由此可见，和 b+tree 全局加锁对比起来，b-link tree 在高并发操作下的性能是显著优于 b+tree 的。华为云 gaussdb 当前采用的就是 b-link tree 索引数据结构。
innodb 的索引组织表更容易触发 smo
索引组织表的叶子节点，存储主键以及应对行的数据，innodb 默认页面为 16k，若每行数据的大小为 1000 字节，每个叶子节点仅能存储 16 行数据。
在索引组织表中，当叶子节点的扇出值过低时，smo 的触发将更加频繁，进而放大了 smo 无法并发写的缺陷。
目前业界有一个堆组织表的数据组织方案，也是华为云数据库 gaussdb 采用的方案。它的叶子节点存储索引键以及对应的行指针（所在的页面编号及页内偏移），堆组织表叶子节点可以存更多的数据，分析可得在同样的数据量与业务并发量下，堆组织表会比索引组织表发生 smo 概率低许多。
性能对比
在 8u32g 的两台服务器分别搭建了 mysql（b+tree 和索引组织表）与 gaussdb（b-link tree 和堆组织表）的环境，进行了如下性能验证：
实验场景：在基础表的场景上，测试增量随机插入性能。
1.基础表总大小 10g，包含主键随机分布的 1000w 行数据，每行数据 1k；
2.插入主键随机分布的 1000w 行数据，每行数据大小 1k，测试并发插入性能。
结论：随着并发数的上升，gaussdb 能稳步提升系统的 tps，而 mysql 并发数的提高并不能带来 tps 的显著提升。
总结
mysql 无法支持大数据量下并发修改的根本原因，是因为其索引并发控制协议的缺陷造成的，而 mysql 选择索引组织表，又放大了这一缺陷。所以，开源 mysql 数据库更适用于主键查询为主的简单业务场景，如互联网类应用，对于复杂的商业场景限制比较明显。
相比之下，采用 b-link tree 和堆组织表的 gaussdb 数据库在性能和场景应用方面更胜一筹。
电感器的充放电过程
Vivox60如何切换系统，如何切换到经典桌面
STM8S如何实现Atomthreads最低功耗
战事升级：高通要求美国禁止进口iPhone
合力泰积极布局LCP柔性线路板领域，解决智能终端产品高需求
为什么 MySQL 单表不能超过 2000 万行？
!销售/收购/维修MT8852A蓝牙测试仪MT8852B!
魅族PRO7用三星8895还是联发科X30？黄章：都不答应！
霍金其实已经死了？著名物理学家霍金去世我们带你了解一个真实的霍金
电动机有几种干燥方法
吉利汽车与沃尔沃达成最新的合并方案
虹科干货 | MQTT 5协议中的基础更改（一）
比特币转账的手续费到底该如何计算
RFID标签读写器让工地仓库管理转向无人化、数字化
碳化硅二极管的应用领域及优势你知道吗
英飞凌寻求30亿欧元以下的收购 228亿人民币
安森美半导体推出移动医疗电子设备的系统级封装方案
如何使用Python-OpenCV实现餐盘水果识别与计价的应用
常见液压元件原理动图
联芸科技发布新Agile ECC技术，提升QLC闪充性能和使用寿命