​

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

谣言

互联网上有传言说咱们应该防止在单个 MySQL 表中有超越 2000 万行。不然，表的功用会下降，当它超越软约束时，你会发现 SQL 查询比平常慢得多。这些判别是在多年前运用HDD硬盘存储时做出的。我想知道在2023年关于根据SSD的MySQL数据库来说，这是否仍然成立，假如成立，原因是什么？

环境

数据库
MySQL 版别：8.0.25
实例类型：AWS db.r5.large (2vCPUs, 16GiB RAM)
EBS 存储类型：通用 SSD (gp2)
测验客户端
Linux 内核版别：6.1
实例类型：AWS t2.micro (1 vCPU，1GiB 内存）

实验设计

创立具有相同形式但巨细不同的表。我创立了9个表，别离包括10万、20万、50万、100万、200万、500万、1000万、2000万、3000万、5000万和6000万行。

1.创立几个具有相同形式的表：

CREATE TABLE row_test(
	`id` int NOT NULL AUTO_INCREMENT,
	`person_id` int NOT NULL,
	`person_name` VARCHAR(200),
	`insert_time` int,
	`update_time` int,
    PRIMARY KEY (`id`),
    KEY `query_by_update_time` (`update_time`),
    KEY `query_by_insert_time` (`insert_time`)
);

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

  1. 插入不同行的表格。我运用测验客户端和仿制来创立这些表。脚本能够在这里找到。
# test client
INSERT INTO {table} (person_id, person_name, insert_time, update_time) VALUES ({person_id}, {person_name}, {insert_time}, {update_time})
# copy
create table <new-table> like <table> 
insert into  (`person_id`, `person_name`, `insert_time`, `update_time`)
select `person_id`, `person_name`, `insert_time`, `update_time` from

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

person_id、person_name、insert_time 和 update_time 的值是随机的。

3.运用测验客户端履行以下sql查询来测验功用。脚本能够在这里找到。

select count(*) from <table>                             -- full table scan
select count(*) from <table> where id = 12345            -- query by primary key
select count(*) from <table> where insert_time = 12345   -- query by index
select * from <table> where insert_time = 12345          -- query by index, but cause 2-times index tree lookup

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

4.检查innodb缓冲池状态

SHOW ENGINE INNODB STATUS
SHOW STATUS LIKE 'innodb_buffer_pool_page%'

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

5.每次在表上测验完一定要重启数据库！刷新 innodb 缓冲池以防止读取旧缓存并得到错误成果！

成果

查询 1：select count(*) from <table>

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

这种查询会造成全表扫描，这是MySQL不擅长的。

No-cache round：（第一轮）当缓冲池中没有缓存数据时，第一次履行查询。
Cache round：（Other round）当缓冲池中已经有数据缓存时履行查询，通常在第一次履行之后。

几个调查：

1.第一次履行的查询运转时刻比后边的要长

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

原因是MySQL运用了innodb_buffer_pool来缓存数据页。在第一次履行之前，缓冲池是空的，它有必要进行大量的磁盘 I/O 才能从 .idb 文件加载表。但第一次履行后，数据存储在缓冲池中，后续履行能够通过内存计算得到成果，防止磁盘I/O，速度更快。该进程称为MySQL 缓冲池预热。

2.select count(*) from <table>将尝试将整个表加载到缓冲池

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

我比较了实验前后的 innodb_buffer_pool 统计数据。运转查询后，假如缓冲池足够大，缓冲池运用变化等于表巨细。不然只要部分表会缓存在缓冲池中。原因是查询select count(*) from table会做全表扫描，一行一行地统计行数。假如没有缓存，这需求将完整表加载到内存中。为什么？因为 Innodb 支撑事务，它不能确保事务在不同时刻看到同一张表。全表扫描是取得准确行数的唯一安全办法。

3.假如缓冲池不能包容全表，查询推迟会爆发

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

我注意到 innodb_buffer_pool 巨细会对查询功用发生很大影响，因而我尝试在不同的装备下运转查询。当运用 11G 的缓冲池时，查询推迟的突增发生在表巨细达到 50M 时。然后将缓冲池巨细减小为 7G，查询推迟的突增发生在表巨细为 30M 时。最后将缓冲池巨细减小到 3G，查询运转时刻的突增发生在表巨细为 20M 时。很明显，假如表中的数据无法被缓存到缓冲池中，履行select count(*) from

就需求进行贵重的磁盘 I/O 操作来加载数据，然后导致查询运转时刻的突增。

4.在不缓存的状况下，查询运转时刻与表巨细呈线性关系，与缓冲池巨细无关。

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

无缓存循环运转时刻由磁盘 I/O 决议，与缓冲池巨细无关。select count(*)运用相同 IOPS 的存储磁盘预热缓冲池没有区别。

5.假如表无法彻底缓存在缓冲池中，那么无缓存轮和有缓存轮之间的查询运转时刻差是稳定的。

同时注意到，虽然假如表无法彻底缓存在缓冲池中会导致查询运转时刻的突增，但运转时刻是可猜测的。无缓存轮运转时刻和有缓存轮运转时刻之间的差值是稳定的，无论表的巨细怎样。原因是表的部分数据被缓存在缓冲池中，这个差值表明了从缓冲池而不是磁盘进行查询所节约的时刻。

查询 2、3：select count(*) from <table> where <index_column> = 12345

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

此查询运用索引。因为不是范围查询，只需求在B+树的途径中从上到下查找页面，并将这些页面缓存到innodb缓冲池中即可。

我创立的表的 B+ 树的深度都是 3，导致 3-4 次 I/O 来预热缓冲区，均匀耗时 4-6ms。之后，假如我再次运转相同的查询，它会直接从内存中查找成果，即 0.5ms，等于网络 RTT。假如缓存页面长时刻未射中并从缓冲池中逐出，则有必要再次从磁盘加载该页面，这最多需求 4 次磁盘 I/O。

查询 4：select * from <table> where <index_column> = 12``345

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

此查询导致 2 次索引查找。因为select *需求查询获取不包括在索引中的person_name,person_id，因而在查询履行期间数据库引擎有必要查找 2 个 B+ 树。它首要查找insert_timeB+ 树以获取方针行的主键，然后查找主键 B+ 树以获取该行的完整数据，如下图所示：

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

这便是咱们在生产中应该防止的原因select *。并且在实验中，数据证明此查询加载的页面块比查询 2 或 3 多 2 倍，最多为 8。均匀查询运转时刻为 6-10 毫秒，也是查询 2 或 3 的 1.5 到 2 倍。

谣言是怎样来的

2000万的行数在2023年仍然是 MySQL 表的有效软限制吗？

首要咱们需求知道innodb索引页的物理结构。默认页面巨细为 16k，由页眉、体系记载、用户记载、页面导向器和尾部组成。将只剩下 15-14k 来存储免费数据。

假定您运用 INT 作为主键（4 字节），每行 1KB 的有用负载。每个叶页能够存储 15 行，它将是 4+8=12 字节，使其成为指向该页的指针。因而，每个非叶页最多能够包容 15k / 12 字节 = 1280 个指针。假如你有一个 4 层的 B+ 树，它最多能够包容 1280128015 = 24.6M 行数据。

回到 HDD 占有市场主导地位且 SSD 关于数据库而言过于贵重的年代，4 次随机 I/O 可能是咱们能够容忍的最坏状况，而运用 2 次索引树查找的查询乃至会使状况变得更糟。其时的工程师想要操控索引树的深度，不期望它们长得太深。现在SSD越来越盛行，随机I/O比以前便宜了，咱们能够回顾一下10年前的规则。

顺便说一句，5层B+树能够包容128012801280*15 = 31.4B行数据，超越了INT所能包容的最大数量。对每行巨细的不同假定将导致不同的软约束，小于或大于 20M。例如，在我的实验中，每行大约 816 字节（我运用utf8mb4字符集，所以每个字符占用 4 个字节），4 层 B+ 树能够包容的软约束是 29.5M。

结论

  1. Innodb_buffer_pool 巨细/表巨细决议是否会出现功用下降。
  2. 一个更有意义的目标来判别是否需求拆分MySQL表是 查询运转时刻与缓冲池射中率的比值 。 假如查询总是射中缓冲池，就不会有功用问题。2000万行只是根据经验的一个值。
  3. 除了拆表，添加InnoDB缓冲池巨细或数据库内存也是一个选择。
  4. 在生产环境中，假如可能的话，尽量防止运用select *，因为在最坏的状况下会导致索引树的两次查找。
  5. （个人观点）考虑到SSD现在的遍及，2000万行并不是MySQL表的一个非常有用的软约束。

来源：Yisheng’sblog

更多技能干货请关注公号“云原生数据库”

squids.cn，根据公有云基础资源，提供云上 RDS，云备份，云搬迁，SQL 窗口门户企业功用，

协助企业快速构建云上数据库融合生态。

​