2024年了,2000万的行数还是 MySQL 表的限制吗?

2024年 5月 23日 99.9k 0

将 "数据与人" 设为 "星标⭐"
第一时间收到文章更新2024年了,2000万的行数还是 MySQL 表的限制吗?-12024年了,2000万的行数还是 MySQL 表的限制吗?-2

传闻

网络上一直流传着一种观点,认为在单个MySQL表中,数据的行数一旦超过2000万,表的性能就可能受到影响。这种观点主要源于早些时候使用HDD硬盘存储时的经验。

2024年了,当我们使用基于SSD的MySQL数据库时,这种判断是否依然有效。换句话说,基于现代存储技术,MySQL表的行数是否仍然需要限制在2000万以内,以保证性能不受影响?如果这种限制仍然存在,那么背后的原因又是什么呢?

带着这个疑问,开始我们的验证。

环境

MySQL 版本:8.0.25
服务器内存:16GB
EBS 存储类型:通用 SSD

验证过程

创建模拟数据:创建了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`)
);

# 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 like

insert into (`person_id`, `person_name`, `insert_time`, `update_time`)
select `person_id`, `person_name`, `insert_time`, `update_time` from

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

3.执行以下sql查询来测试性能。

select count(*) from

-- full table scan
select count(*) from

where id = 12345 -- query by primary key
select count(*) from

where insert_time = 12345 -- query by index
select * from

where insert_time = 12345 -- query by index, but cause 2-times index tree lookup

4.查看innodb缓冲池状态

SHOW ENGINE INNODB STATUS
SHOW STATUS LIKE 'innodb_buffer_pool_page%'

5.每次在表上测试完重启数据库!刷新 innodb 缓冲池以避免读取旧缓存并得到错误结果!

验证结果

查询 1。select count(*) from

2024年了,2000万的行数还是 MySQL 表的限制吗?-3这种查询会造成全表扫描,这是MySQL不擅长的。
No-cache round:(第一轮)当缓冲池中没有缓存数据时,第一次执行查询。
Cache round:(Other round)当缓冲池中已经有数据缓存时执行查询,通常在第一次执行之后。

1.第一次执行的查询运行时间比后面的要长

2024年了,2000万的行数还是 MySQL 表的限制吗?-4

MySQL之所以能提高查询效率,是因为它使用了innodb_buffer_pool来缓存数据页。

在首次执行查询操作之前,缓冲池内并没有存储任何数据,所以它需要从.idb文件中读取大量的数据页到内存中。一旦首次查询执行完毕,相关的数据就会被存储在缓冲池里。这样一来,后续的查询操作就可以直接从内存中获取计算结果,而无需再次进行磁盘I/O操作,从而大大提高了查询速度。这个过程在MySQL中被称为缓冲池预热。

2.select count(*) from

将尝试将整个表加载到缓冲池

2024年了,2000万的行数还是 MySQL 表的限制吗?-5

我对比了实验前后innodb_buffer_pool的统计数据。如果查询执行后,缓冲池的大小足够,那么缓冲池的使用变化应当与表的大小相等。反之,如果缓冲池不够大,那么只会有一部分表数据被缓存在缓冲池中。

这是因为执行查询select count(*) from table会涉及到全表扫描的操作,即逐行统计表中的行数。若表中数据未被缓存,那么这一操作就需要将整个表的数据加载到内存中。

为何会这样呢?原因在于InnoDB支持事务处理,它不能保证在不同时间点,事务所看到的数据视图是完全一致的。因此,进行全表扫描是确保获得准确行数的唯一可靠方法。

3.如果缓冲池不能容纳全表,查询延迟会爆发。
2024年了,2000万的行数还是 MySQL 表的限制吗?-6

2024年了,2000万的行数还是 MySQL 表的限制吗?-72024年了,2000万的行数还是 MySQL 表的限制吗?-8

可以观察到innodb_buffer_pool的大小对查询性能有着显著的影响。具体而言,当缓冲池大小设定为11G时,查询延迟的显著增加发生在表的大小达到50M的时候。

接着,我将缓冲池大小调整为7G,并再次运行查询。这次,我发现查询延迟的显著增加出现在表大小为30M的时候。

最后,我将缓冲池的大小进一步减小到3G,并重新执行查询。此时,查询运行时间的显著增加发生在表大小达到20M的时候。

从这些实验结果中,可以明显看出,如果表中的数据不能被有效地缓存到innodb_buffer_pool中,那么执行类似于“select count(*) from

”的查询时,就需要进行成本较高的磁盘I/O操作来加载数据。这种磁盘I/O操作会直接导致查询运行时间的显著增加。

4. 在不缓存的情况下,查询运行时间与表大小呈线性关系,与缓冲池大小无关。

2024年了,2000万的行数还是 MySQL 表的限制吗?-9

无缓存循环运行时间由磁盘 I/O 决定,与缓冲池大小无关。select count(*)
使用相同 IOPS 的存储磁盘预热缓冲池没有区别。

5. 如果表无法完全缓存在缓冲池中,那么无缓存轮和有缓存轮之间的查询运行时间差是恒定的。

同时注意到,尽管如果表无法完全缓存在缓冲池中会导致查询运行时间的突增,但运行时间是可预测的。无缓存轮运行时间和有缓存轮运行时间之间的差值是恒定的,无论表的大小如何。原因是表的部分数据被缓存在缓冲池中,这个差值表示了从缓冲池而不是磁盘进行查询所节省的时间。

查询 2、3:select count(*) from

where = 12345

2024年了,2000万的行数还是 MySQL 表的限制吗?-10

上述查询在执行过程中会充分利用索引。由于查询不是范围查询,因此它只需要按照B+树的路径从上到下依次查找所需的页面。在查找的过程中,这些页面会被缓存到innodb缓冲池中,以便后续快速访问。

创建的测试表其B+树的深度均为3,因此,进行缓冲区预热时需要进行大约3至4次的I/O操作。这种预热过程平均耗时为4至6毫秒。在完成预热后,如果再次运行相同的查询,它将直接从内存中获取结果,此时耗时仅为0.5毫秒,这与网络往返时间(RTT)相当。

如果某个缓存页面长时间没有被访问并从缓冲池中移除,那么当需要再次访问该页面时,就必须从磁盘中重新加载,这最多可能需要4次磁盘I/O操作

查询 4:select * from

where = 12345

2024年了,2000万的行数还是 MySQL 表的限制吗?-11

这个查询过程中,需要进行两次索引查找操作。

原因在于,执行select *语句时,除了索引中包含的信息外,还需要获取person_name和person_id这两个字段的数据,而这两个字段并不包含在当前的索引中。因此,在查询执行的过程中,数据库引擎不得不分别查找两个不同的B+树结构。首先,它会通过insert_time对应的B+树来定位到目标行的主键值;随后,再利用这个主键值,去查找主键B+树,从而获取该行的完整数据记录。

整个查找过程可以参考下面的图示说明。

2024年了,2000万的行数还是 MySQL 表的限制吗?-12

这就是我们在实际生产环境中应该尽量避免使用`select *`查询的原因。

根据我们在实验中所获得的数据,这种查询方式加载的页面块数量是查询2或查询3的两倍之多,最多可达到8倍。此外,`select *`查询的平均运行时间也明显较长,介于6至10毫秒之间,这同样是查询2或查询3所需时间的1.5到2倍。

为何会有这种传言

2024年了,2000万的行数还是 MySQL 表的限制吗?-13

首先,我们需要深入了解InnoDB索引页的物理结构。在默认情况下,页面大小为16KB,其结构包括页眉、系统记录、用户记录、页面导向器和尾部等部分。实际上,用于存储实际数据的空间大约是15KB到14KB。

假设使用INT类型作为主键,它占用4字节,而每行的有效负载是1KB。那么,每个叶页能存储的行数大约是15行,因为除了数据本身,每行还需要额外的8字节用于存储指向该页的指针,所以总共是4字节(主键)+ 8字节(指针)= 12字节。

基于这样的计算,每个非叶页最多可以容纳的指针数量是15KB除以每个指针占用的12字节,即大约1280个指针。

如果有一个4层的B+树,那么它理论上最多可以容纳的行数就是每一层指针数量的乘积,即1280乘以1280再乘以15,等于大约24.6M行数据。

回顾过去,当HDD在市场上占据主导地位,而SSD对于数据库来说还是一种昂贵的技术时,4次随机I/O操作可能是我们可以接受的性能底线。而使用需要2次索引树查找的查询,其性能甚至可能更差。那时的工程师们努力控制索引树的深度,以免它们生长得过于庞大。

然而,随着技术的进步,现在SSD已经越来越普及,随机I/O操作的成本也相对较低。因此,我们或许可以重新评估那些十年前制定的规则。

顺便一提,如果是一个5层的B+树,它理论上可以容纳的行数会更多,计算下来大约是1280乘以1280再乘以1280再乘以15,即31.4B行数据。这个数量实际上已经超过了INT类型主键所能表示的最大行数。

当然,每行数据的大小不同,也会对B+树能够容纳的行数产生影响。这些不同的假设会导致不同的“软限制”,也就是实际使用中可能达到的最大行数,这个数字可能会小于或大于20M。例如,在我的实验中,每行数据大约是816字节(因为我使用了utf8mb4字符集,每个字符占用4个字节),在这种情况下,一个4层的B+树能够容纳的行数软限制大约是29.5M。

个人观点:考虑到SSD现在的普及,2000万行并不是MySQL表的一个非常有效的软限制。

2024年了,2000万的行数还是 MySQL 表的限制吗?-14
更多精彩内容,关注我们▼▼

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论