2000万的行数还是 MySQL 表的限制吗？

最新推荐文章于 2024-10-29 23:32:56 发布

数据与人

最新推荐文章于 2024-10-29 23:32:56 发布

阅读量643

点赞数 5

分类专栏：数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_38048955/article/details/142082272

版权

数据库专栏收录该内容

104 篇文章 3 订阅

订阅专栏

传闻

网络上一直流传着一种观点，认为在单个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 <new-table> like <table> 
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 <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

4.查看innodb缓冲池状态

SHOW ENGINE INNODB STATUS
SHOW STATUS LIKE 'innodb_buffer_pool_page%'

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

验证结果

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

这种查询会造成全表扫描，这是MySQL不擅长的。
No-cache round：（第一轮）当缓冲池中没有缓存数据时，第一次执行查询。
Cache round：（Other round）当缓冲池中已经有数据缓存时执行查询，通常在第一次执行之后。

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

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

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

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

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

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

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

3.如果缓冲池不能容纳全表，查询延迟会爆发。

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

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

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

从这些实验结果中，可以明显看出，如果表中的数据不能被有效地缓存到innodb_buffer_pool中，那么执行类似于“select count(*) from <table>”的查询时，就需要进行成本较高的磁盘I/O操作来加载数据。这种磁盘I/O操作会直接导致查询运行时间的显著增加。

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

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

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

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

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

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

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

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

查询 4：select * from <table> where <index_column> = 12345

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

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

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

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

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

为何会有这种传言

首先，我们需要深入了解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表的一个非常有效的软限制。