MySQL单表真的能存21亿吗即使能存下21亿数据会不会有严重的性能问题是真的可行还是只是理论可行事情是这样的早两天发了一篇《面试官问MySQL 自增 ID 用完了怎么办该如何回答呢》的文章于是有网友留言说单表21亿早就查不动了根本没有论证ID用完的必要。真是这样的吗正好之前做 IOT 项目的朋友刚好有个例子他们有个表由 1 个 bigint 主键加 10 个 int 类型的采集字段和一个时间戳字段现在数据量破 12 亿了他说实际上普通查询都不会超 1 秒。不过还没到21亿那我们今天就从理论论证下是否真的可行。21 亿根本不是 MySQL 单表的上限其实我要说21亿不是MySQL的理论上限。这个 21 亿上限其实是有符号 int 类型主键的最大值2^31-12147483647要是用无符号 int 还能到 42 亿换用 bigint 的话上限是 2^64-1那都是天文数字了根本不可能用到。从 InnoDB 底层原理来看单表理论上限是由 64 位页编号机制决定的最多可寻址 1.8×10¹⁹个 16KB 的页就算每页平均存 200 行记录总行数也是千亿级别的远超过 21 亿。1. 行大小估算InnoDB存储引擎上面说了1个bigint主键10个int类型字段1个时间戳字段。我们假设表结构如下CREATETABLEt(idBIGINTAUTO_INCREMENTPRIMARYKEY,f1INTNOTNULL,f2INTNOTNULL,...f11TIMESTAMP(3)NOTNULL)ENGINEInnoDB;用户数据id占8字节每个int占4字节共10个时间戳TIMESTAMP占7个字节合计55字节。InnoDB隐藏字段每行记录包含6字节的事务IDDB_TRX_ID和7字节的回滚指针DB_ROLL_PTR共13字节。记录头Compact行格式中记录头占5字节包含next指针等信息。合计55 13 5 73字节。实际存储中所以数据行按73字节是一个合理的估算值。2. 数据页容量与B树高度B树数据页容量InnoDB默认数据页大小为16KB16384字节。每页除存储行数据外还需预留页头、页尾、目录槽等开销一般在120到200字节甚至更多我们保守点按1k作为页的元开销可用空间按约15KB左右。每页可存储行数15 * 1024 / 70 ≈ 210行。在保守点取200行/页。存储21亿行数据需要的叶子页数21e8 / 200 1.05e7页即约1050万个叶子页。即需要1050万个叶子结点每个叶子节点存200行数据。B树非叶子节点容量非叶子节点存储索引键和子页指针。主键索引键为bigint8字节指针即子页编号通常占6个字节。每条索引记录8 6 14字节。每页可用空间同样按15KB计算可存储的索引记录数15 * 1024 / 14 ≈ 1097条。即每个非叶子节点最多可指向1097个子页。B树层数计算2层B树根节点指向1097个叶子页 。可存储行数1097 * 200 219,400≈21.94万行。3层B树根 → 第二层1097个节点 → 第三层叶子页 。可存储行数1097 * 1097 * 200 240,681,800≈2.4亿行。4层B树根 → 第二层1097个节点 → 第三层1097个节点 → 第四层叶子页 。可存储行数1097 * 1097 * 1097 * 200 ≈ 2.6e11≈2640亿行。所以21亿行数据3层是不够需要4层B树但已远低于2640亿的上限。这意味着通过主键查询一行数据最多只需进行4次磁盘I/O如果相关非叶子节点和叶子页不在内存中理论耗时在数十毫秒级别。3.存储容量计算除了树的高度我们还要考虑数据所占空间容量因为这决定了有多少数据会在内存中。根据上面计算得到1050万个叶子页需要的存储空间和上层非叶子页(节点)所需的存储空间是多少。21亿数据B树总共需要四层所以第四层全部为叶子节点第三层及以上都是非叶子节点。叶子节点所占空间10500000 * 16k 168,000,000 k ≈160G存储空间。非叶子节点所占空间(1097 * 1097 1097 1 ) * 16 k 19,272,112 k ≈18.5 G存储空间根据上面的计算21 亿的话 B 树就会到 4 层按理说每次查询要多一次 IO 对吧但是实际上非叶子节点的体积非常小21 亿行的表的主键索引非叶子节点总大小才 18.5GB。正常来说将缓冲池设为物理内存的 70%~80%32G的物理内存的话可以设置23G的innodb_buffer_pool_size,意味着几乎所有的非叶子节点都可以放在内存里。只要把缓冲池设得够大这些非叶子节点完全可以全部缓存在内存里实际查询只需要 1 次磁盘 IO 读叶子节点就行SSD 下单次 IO 也就 0.1ms 左右主键查询速度是远低于1 秒的。是不是就说明单表存21亿数据就毫无压力没有性能瓶颈了呢其实并没有这么简单还是要看怎么用。21 亿行的单表会有性能瓶颈吗会有。但是得分情况并不是所有场景都会有性能瓶颈。下面我们直接分场景说结论主键查询场景完全没问题实测同结构 21 亿行的表SSD 下主键查单条数据平均耗时也就 15-30ms远低于 1 秒。高选择性二级索引查询IOT 场景下大多是按设配 ID 或者时间维度查询只要建好对应的联合覆盖索引不需要回表的话性能也能控制在 100ms 以内。大范围扫描、无索引多条件查询那肯定会超 1 秒这个和行数没关系1000 万行的表这么查也快不了。一个表格总结如下问题答案能否存储21亿行能。B树结构支持4层索引理论最大容量可达4200亿行。单条主键查询会超过1秒吗几乎不会。最多4次I/O实际通常几十毫秒。查询前10条数据会超过1秒吗不会。直接定位最左叶子页毫秒级返回。是否存在严重性能问题存在。深分页、无索引查询、全表扫描等操作会导致长时间阻塞。需通过索引优化、查询重写、架构升级等手段规避。所以真正会导致性能下降的瓶颈其实是这几个缓冲池命中率如果热点数据分散缓冲池又太小每次查询都要读磁盘性能肯定会陡降查询类型查询没问题但是大范围扫描、无索引全表查询不管多少行都会慢维户成本大表加索引、做 DDL、备份恢复的时间都会变长这个才是最头疼的深分页即使使用主键排序MySQL仍需扫描并跳过前20亿行所以会极慢但是ID自增有办法解决。全表COUNT( * ): 统计行数需要遍历聚簇索引的所有叶子页, 同样分钟级且会严重消耗I/O影响并发。不过也有办法解决。不过说实话21亿数据必须要把优化做好否则很容易把人逼疯的。下面几个实用的优化建议是可以有效的避免大部分性能问题的缓冲池至少设到服务器内存的 70%尽量把所有非叶子索引都缓存住。用 SSD 或者 NVMe 盘把随机 IO 性能拉满。按时间做表分区冷热数据分离历史数据归档到冷存储。尽量用覆盖索引避免不必要的回表操作。所以MySQL单表存 21 亿到底行不行不能简单的用行不行来回答这个问题还是要根据业务场景具体分析。如果像这种字段非常精简的 IOT 表一样基本上是OK的。由此可见只要遵循正确的查询模式和持续的运维优化MySQL单表存储21亿行是可行的并且关键查询能够保持高性能。其实 MySQL 单表从来没有什么硬性的 2000 万、21 亿的上限所谓的上限都是业务场景、优化程度、硬件配置共同决定的。不知道你有没有遇到过什么超大表的情况或者可以分享下你们见过的最大的表有多大你们遇到这种超大数据量会怎么处理呢可以评论区留言讨论