MySQL-单表不要超过 2000W 行？

MySQL 单表不要超过 2000W 行？

Created: October 16, 2023 4:05 PM
Class: MySQL
Reviewed: No

// 设置临时表的内存
SET GLOBAL tmp_table_size =512*1024*1024; （512M）
// 设置存储引擎缓冲池大小
SET global innodb_buffer_pool_size= 1*1024*1024*1024 (1G);

单表数量限制

主键类型大小可以限制表的上限。

主键声明为 int，支持 2^32-1；

主键声明为 bigint，支持 2^62-1；

如果自增字段使用无符号的 bigint，增加一条记录需要一秒，自增到最大需要 5800 万年。

表空间

表空间里的数据被存储在页中，数据页的大小为 16KB。

页的数据结构如下

插入记录时，会先在Free+Space中申请一个记录大小的空间划分到 User Records 中，但是在寻找数据时，这样挨个寻找一定不足够高效，索引就来了。

索引页大小也是 16KB，索引页中的基本单位是类似于键值对的结构：

ID 与页号地址，ID 表示对应页号地址中的最小记录ID 值，页号地址是一个指向对应页的指针。

每一个索引页能存多少键值对

File Header (38 byte)、Page Header (56 byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右。

假设主键 ID 是Bigint (8 byte)，页号地址（4byte），一条数据为 12byte。

每一个索引页能存 15*1024/12≈1280 个键值对。

索引页的层级有多高

叶子节点存储的是真正的记录数据，按照 1k 计算，一个叶子节点能够存储 15 个记录数据。假设 B+树是三层，那么能够记录的数据为 1280^2*15=24576000≈2000W，对应标题

总结

MySQL 为了提高性能，会将表的索引装载到内存中，在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。

当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。

2000W 的值不是固定的，要看数据到达多大会使的 InnoDB 使用四层的 B+树。