那么,MySQL 一张表最多能存多少数据?
今天我们就从技术层面剖析一下,MySQL 单表数据不能过大的根本原因是什么?
猜想一:是索引深度吗?
很多人认为:数据量超过 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 单表不能过大的根本性因素是什么?
猜想二:是 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 过程中写操作的并发执行,保持高并发下性能的稳定。
B-Link Tree 主要改进点有 2 个:
中间节点增加 link 指针,指向右兄弟节点;
每个节点内增加字段 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 和堆组织表)的环境,进行了如下性能验证:
实验场景:在基础表的场景上,测试增量随机插入性能。
基础表总大小 10G,包含主键随机分布的 1000w 行数据,每行数据 1k;
插入主键随机分布的 1000w 行数据,每行数据大小 1k,测试并发插入性能。
结论:随着并发数的上升,GaussDB 能稳步提升系统的 TPS,而 MySQL 并发数的提高并不能带来 TPS 的显著提升。
综上所述,MySQL 无法支持大数据量下并发修改的根本原因,是由于其索引并发控制协议的缺陷造成的,而 MySQL 选择索引组织表,又放大了这一缺陷。所以,开源 MySQL 数据库更适用于主键查询为主的简单业务场景,如互联网类应用,对于复杂的商业场景限制比较明显。