时至 2023 年,2000 万行仍然是 MySQL 表的软限制吗?

3d3d2c8581a02ad8b74425a24f242747.gif

一直有传言说,MySQL 表的数据只要超过 2000 万行,其性能就会下降。而本文作者用实验分析证明:至少在 2023 年,这已不再是 MySQL 表的有效软限制。

原文链接:https://yishenggong.com/2023/05/22/is-20m-of-rows-still-a-valid-soft-limit-of-mysql-table-in-2023/

未经允许,禁止转载!

作者 | Yisheng Gong

译者 | 弯月   责编 | 郑丽媛

出品 | CSDN(ID:CSDNnews)

a10cd49d629278b66dcfad80079d84fe.png

传言

互联网上有一则传言说,我们应该避免单个 MySQL 表中的数据超过 2000 万行,否则表的性能就会下降——当数据量超过这个软限制时,你就会发现 SQL 的查询速度会比平时慢很多。这是多年前针对 HDD 做出的判断。我想知道,时至 2023 年,SSD 上的 MySQL 是否仍然有此限制。如果真的有,那么原因是什么呢?

0a3ae4de8f3b97678ee04cb39a97fa53.jpeg

384bad833459b008b33b8a5e5e4ef811.png

环境

数据库

▶ MySQL 版本: 8.0.25

▶ 实例类型:AWS db.r5.large(2vCPUs, 16GiB RAM)

▶ EBS 存储类型:General Purpose SSD(gp2)

测试客户端

▶ Linux 内核版本:6.1

▶ 实例类型:AWS t2.micro(1 vCPU, 1GiB RAM)

1d198dc93153af1b881245e19e7216c0.png

实验设计

创建具有相同结构、但大小不同的表。我一共创建了 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`)
);

2. 插入不同的数据。我使用了测试客户端和表复制的方式创建了这些表。脚本可参考:https://github.com/gongyisheng/playground/blob/main/mysql/row_test/insert_data.py。

# 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 <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 查询来测试性能。脚本可参考:https://github.com/gongyisheng/playground/blob/main/mysql/row_test/select_test.py。

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 缓冲池,避免读取旧缓存,得到错误的结果!

814dad8ac314db983f3f5bbf6788dcd6.png

结果

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

a0cb7a576a1d5c5f7fea9f84c8958638.png

这种查询会执行全表扫描,MySQL 并不擅长这种工作。

▶ 第一轮:没有缓存。第一次执行查询时,缓冲池中没有缓存数据。

▶ 第二轮:有缓存。当缓冲池中已经有数据缓存时执行查询,通常在第一次查询执行完之后。

观察结果:

1. 第一轮查询的执行时间超出了后面几次。 

38e88d0018b018860c4cb08d68ada077.png

原因是 MySQL 使用了 innodb_buffer_pool 来缓存数据页。在第一次执行查询之前,缓冲池是空的,所以 MySQL 必须进行大量的磁盘 I/O 才能从 .idb 文件加载表。但在第一次执行结束后,缓冲池中存储了数据,后续查询可以直接读取内存,避免磁盘 I/O,因此速度更快。该过程称为 MySQL 缓冲池预热。

2. select count(*) from <table> 会设法将整个表加载到缓冲池。

7dd5e6672ccc5e55798d61f434754080.png

我比较了实验前后 innodb_buffer_pool 的统计数据。运行查询后,如果缓冲池足够大,则其使用量变化等于表的大小。否则,只有部分表会缓存在缓冲池中。原因是查询 select count(*) from table 会做全表扫描,并做逐行统计。如果没有缓存,就需要将完整的表加载到内存中。为什么?因为 Innodb 支持事务,它不能保证事务在不同时间看到同一张表。全表扫描是获得准确行数的唯一安全方法。

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

bc9201ec3b01a9fc38aeca8b38cacf8f.png

我注意到 innodb_buffer_pool 的大小会极大地影响查询性能,因此我尝试在不同的配置下运行查询。当使用 11G 缓冲区,而表的大小达到 5000 万行时,就会爆发查询延迟。接着,我将缓冲区缩减到 7G,当表的大小达到 3000 万行时,爆发了查询延迟。最后,我将缓冲区缩减到 3G,当表的大小仅为 2000 万行时,就爆发了查询延迟。很明显,如果表中的数据无法缓存在缓冲池中,则 select count(*) from <table> 必须执行昂贵的磁盘 I/O,这会导致查询运行时间直线上升。

4. 对于没有缓存的查询,查询花费的时间与表的大小呈线性关系,与缓冲池大小无关。

0d2b8e5f85ed6830513cae63a4cfd855.png

当没有缓存时,查询花费的时间由磁盘 I/O 决定,与缓冲池大小无关。在 IOPS 相同的情况下,是否使用 select count(*) 预热缓冲池并没有区别。

5. 如果无法完整地缓存整个表,则有无缓存的查询运行时间差异是恒定的。

另请注意,如果无法完整地缓存整个表,虽然查询运行时会突然上升,但运行时是可预测的。无论表的大小如何,有无缓存的时间差异是恒定的。原因是表的部分数据缓存在缓冲区中,这里的时间差异来自从缓冲区读取数据节省的时间。

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

a1b8da1d78db24594b0d9d7b8837f7a1.png

这个查询使用了索引。由于不是范围查询,MySQL 只需要利用 B+ 树的路径从上到下查找页面,并将这些页面缓存到 innodb 缓冲池中即可。

我创建的表的 B+ 树的深度都是 3,因此前面的 3~4 次 I/O 都被拿来预热缓冲区,平均耗时 4~6 毫秒。之后,再次运行相同的查询,MySQL 就会直接从内存中查找结果,耗时为 0.5 毫秒,约等于网络 RTT。如果缓存页面长时间未命中,并从缓冲池中逐出,则必须再次从磁盘加载该页面,这样就需要磁盘 I/O(最多 4 次)。

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

ea81c1d32fd30b72f0e6aa1399b8f640.png

这个查询涉及两次索引查找。由于 select * 需要查询获取的 person_name、person_id 字段并不在索引中,因此在查询执行期间,数据库引擎必须查找 2 个 B+ 树。它首先查找 insert_time B+ 树,获取目标行的主键,然后查找主键 B+ 树,获取该行的完整数据,如下图所示:

e73cb563a0bfd51297a54692627f024c.png

这就是我们应该在生产中避免 select * 的原因。此次实验证实,此查询加载的页面块比查询 2 或 3 多出了 2 倍,且最高可达 8 倍。查询的平均运行时间为 6~10 毫秒,也是查询 2 或 3 的 1.5~2 倍。

4b0187006b19f76c97d4e787166ad3b0.png

传言是怎么来的

5237aec862c0f8378a8e60ac1255ec24.png

首先,我们需要知道 innodb 索引页的物理结构。默认页面大小为 16k,由页眉、系统记录、用户记录、页面导向器和尾部组成。只有剩下的 14~15k 用来存储数据。

假设你使用 INT 作为主键(4 字节),每行 1KB 的有效负载。每个叶页可以存储 15 行,一个指向该页的指针需要 4+8=12 字节。因此,每个非叶页最多可以容纳 15k / 12 字节 = 1280 个指针。如果你有一个 4 层的 B+ 树,它最多可以容纳 1280*1280*15 = 24.6M 行数据。

回到 HDD 占据市场主导地位,且 SSD 对于数据库而言过于昂贵的时代,4 次随机 I/O 可能是我们可以容忍的最坏情况,而使用 2 次索引树查找的查询甚至会使情况变得更糟。当时的工程师想要控制索引树的深度,不希望它们太深。而如今 SSD 越来越流行,随机 I/O 比以前便宜了,因此我们应该反思一下 10 年前的规则。

顺便说一句,5 层 B+ 树可以容纳 1280*1280*1280*15 = 31.4B 行数据,超过了 INT 所能容纳的最大数据量。对每行大小的不同假设将导致不同的软限制,或小于或大于 2000 万行。例如,在我的实验中,每一行大约是 816 字节(我使用 utf8mb4 字符集,所以每个字符占用 4 个字节),4 层 B+ 树可以容纳的软限制是 29.5M。

411c303639057708429e0f48de7196b9.png

结论

▶ Innodb 缓存池的大小、表的大小决定了是否会出现性能降级。

▶ 判断是否需要拆分 MySQL 表的一个更有意义的指标是查询运行时/缓冲池命中率。如果查询总是命中缓冲区,则不会有任何性能问题。2000 万行只是一个经验值。

▶ 除了拆分 MySQL 表之外,增加 Innodb 缓存池的大小和数据库的内存也是一个选择。

▶ 如果可能,请避免在生产中使用 select *,这类语句在最坏的情况下会导致 2 次索引树查找。

▶ (我个人的意见)考虑到 SSD 现在越来越流行,2000 万行不再是 MySQL 表的有效软限制。

推荐阅读:

▶快播公司已破产注销;ChatGPT 之父警告:AI 可能灭绝人类;苹果官方:618 将开启全球首次直播|极客头条

▶FBI 花 3 年暴力破解 iPhone X 密码,竟成一场空?法院:搜查令已过期,证据无效

▶Rust 社区管理再起“内讧”,外部专家遭排挤,核心成员主动请辞,立即生效!

ea765aa47db890a02dde41a8542ed88c.jpeg

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/313.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

GPT-4 Copilot X震撼来袭!AI写代码效率10倍提升,码农遭降维打击

新智元报道 【新智元导读】GPT-4加强版Copilot来了&#xff01;刚刚&#xff0c;GitHub发布了新一代代码生成工具GitHub Copilot X&#xff0c;动嘴写代码不再是梦。 微软真的杀疯了&#xff01; 上周&#xff0c;微软刚用GPT-4升级了Office办公全家桶&#xff0c;还没等人们反…

FBI 花 3 年暴力破解 iPhone X 密码,竟成一场空?法院:搜查令已过期,证据无效...

整理 | 郑丽媛 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 很难预料到&#xff0c;几年前 FBI 和苹果之间那场备受关注的隐私大战&#xff0c;时至今日仍有余波&#xff1a; ▶ 2016 年&#xff0c;正值苹果与 FBI “剑拔弩张”时&#xff0c;其安全指南曾声称…

发布 21 年后,Windows XP 被破解,仅 18KB 即可离线激活

整理 | 郑丽媛 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 都 2023 年了&#xff0c;如今再提起 Windows XP&#xff0c;可能颇有些“时代的眼泪”的味道。 &#xff08;Windows XP 经典的默认桌面壁纸&#xff09; 2001 年 10 月 25 日正式登陆零售商店&…

​iPhone 14 Pro 全系降价 700 元;Gmail 之父:有了 ChatGPT,搜索引擎活不过两年了|极客头条...

「极客头条」—— 技术人员的新闻圈&#xff01; CSDN 的读者朋友们早上好哇&#xff0c;「极客头条」来啦&#xff0c;快来看今天都有哪些值得我们技术人关注的重要新闻吧。 整理 | 梦依丹 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 一分钟速览新闻点&#…

ChatGPT陷伦理风波 “纯净版”机器人在赶来的路上

近期&#xff0c;AI安全问题闹得沸沸扬扬&#xff0c;多国“禁令”剑指ChatGPT。自然语言大模型采用人类反馈的增强学习机制&#xff0c;也被担心会因人类的偏见“教坏”AI。 4月6日&#xff0c;OpenAI 官方发声称&#xff0c;从现实世界的使用中学习是创建越来越安全的人工智…

快播公司已破产注销;ChatGPT 之父警告:AI 可能灭绝人类;苹果官方:618 将开启全球首次直播|极客头条...

「极客头条」—— 技术人员的新闻圈&#xff01; CSDN 的读者朋友们早上好哇&#xff0c;「极客头条」来啦&#xff0c;快来看今天都有哪些值得我们技术人关注的重要新闻吧。 整理 | 梦依丹 出品 | CSDN&#xff08;ID&#xff1a;CSDNnews&#xff09; 一分钟速览新闻点&#…

AI聊天机器人ChatGPT遭破解,引发数据泄露风险

近日&#xff0c;一款基于人工智能技术的聊天机器人——ChatGPT遭受黑客攻击&#xff0c;导致用户数据泄露风险加大。这一事件引起了广泛的关注&#xff0c;也引发了人们对于人工智能安全性的担忧。 ChatGPT是一种被广泛应用于企业客户服务和市场营销等领域的AI聊天机器人&…

行走的代码生成器:chatGPT要让谷歌和程序员“下岗”了

就在本周&#xff0c;OpenAI 又发布了一个全新的聊天机器人模型 ChatGPT&#xff0c;作为 GPT-3.5 系列的主力模型之一。 图片来源&#xff1a;OpenAI 更重要的是它是完全免费公开的&#xff01;所以一经发布大家立刻就玩开了——很快&#xff0c;网友们就被 ChatGPT 的能力所…

ChatGPT会让程序员失业?ChatGPT:“ 是友军,我不从事任何职业。

毫无疑问&#xff0c;ChatGPT“出圈”了。 它似乎无所不能。 许多人担忧它是否会取代自己的饭碗&#xff0c;唯恐自己的进步赶不上 AI 的发展。 然而&#xff0c;有人在试用几次之后&#xff0c;又算是松了口气&#xff1a;打工人我呀&#xff0c;工作算是保住啦~ 那么&…

除了ChatGPT,还有哪些AI工具会抢你“饭碗”?

1、Daft Art 人工智能专辑封面生成器 Daft Art 是独立设计师和开发者 Ahmed 所创建的服务&#xff0c;这项服务经过大量的数据训练&#xff0c;可以根据你的音乐的标题、内容来创建一系列的封面图&#xff0c;你可以在其中选择和你的音乐氛围接近的图。 Daft Art 能够生成的封…

Chatgpt到底有多牛?

在人工智能领域&#xff0c; ChatGPT可以说是最具影响力的 AI之一。从全球最大的中文搜索引擎百度&#xff0c;到中国最大的新闻聚合网站人民日报&#xff0c;再到中国最大的知识问答网站知乎&#xff0c; ChatGPT都有不俗的表现。而在 ChatGPT被美国《时代周刊》评为“人工智能…

ChatGPT或许很强大,但还抢不走你的饭碗

ChatGPT变得家喻户晓是在2022年的11月&#xff0c;当时OpenAI正式对外推出了GPT3.5。 但实际上&#xff0c;这场AI革命的战争早已开始打响。过去十年间&#xff0c;谷歌、 脸书、亚马逊、苹果和微软这些硅谷有名有姓的科技巨头纷纷开启AI“军备竞赛”&#xff0c;先后成立专门…

ChatGPT 会取代程序员吗?

ChatGPT 是由 OpenAI 于 2022 年 11 月 30 日推出的智能聊天机器人。由于技术表现非常优秀&#xff0c;一出道就火爆全球。它不仅让谷歌、苹果等 IT 巨头睡不着觉&#xff0c;还成功吸引了微软 100 亿美金的技术投资。 第一波吃到螃蟹的道友开始用它生成代码&#xff0c;玩得不…

昨天,我被ChatGPT抢饭碗了!

分享人&#xff1a;Mr.K 作者&#xff1a;ChatGPT 昨天&#xff0c;K哥发了一条朋友圈&#xff1a;我用ChatGPT写的演讲稿&#xff0c;跟企业家同学们分享《AIGC如何赋能企业》。 不要再说AI离你还很远&#xff0c;AI代替人类工作还早。抛弃你的从来不是时代&#xff0c;而是…

ChatGPT面世具有何意义?ChatGPT会不会取代程序员?

本篇文章给大家谈谈ChatGPT面世具有何意义一个有趣的事情&#xff0c;以及ChatGPT会不会取代程序员一个有趣的事情&#xff0c;希望对各位有所帮助&#xff0c;不要忘了收藏本站喔。 1、chatgpt是什么? chatgpt介绍如下&#xff1a; ChatGPT是由人工智能研究实验室OpenAI在202…

最近大热的 chatGPT 会取代你的工作吗?

ChatGPT 由于其高效的自然语言处理能力&#xff0c;它最容易取代的领域可能是&#xff1a; 文本分类&#xff1a;ChatGPT 可以用作文本分类系统&#xff0c;对文本进行分类 聊天机器人&#xff1a;ChatGPT 可以制作聊天机器人&#xff0c;提供人性化的交互体验 文本生成&…

ChatGPT这是要抢走我的饭碗?我10年硬件设计都有点慌了

前 言 呃……问个事儿&#xff0c;听说ChatGPT能写电路设计方案了&#xff0c;能取代初级工程师了&#xff1f;那我这工程师的岗位还保得住么&#xff1f;心慌的不行&#xff0c;于是赶紧打开ChatGPT问问它。 嘿&#xff0c;还整的挺客气&#xff0c;快来看看我的职业生涯是否…

普通人是否能从ChatGPT中分一杯羹?

ChatGPT3.0刚刚推出&#xff0c;最开始的时候&#xff0c;人们只是将ChatGPT看作一个很会聊天的机器人&#xff0c;无论问题多么天马行空&#xff0c;它的答案看上去都有理有据。后来&#xff0c;像打开潘多拉魔盒一样&#xff0c;很多人开始拿它编大纲、撰写文案、编代码、创作…

ChatGPT 这个风口,普通人怎么抓住?

最近在测试ChatGPT不同领域的变现玩法&#xff0c;有一些已经初见成效&#xff0c;接下来会慢慢分享出来。 今天先给大家分享一个&#xff0c;看完就能直接上手的暴力引流玩法。 所需工具&#xff1a; 1&#xff09;ChatGPT&#xff08;最好是plus版&#xff0c;需要保证快速…

别担心,ChatGPT还抢不动BI工程师的饭碗!

ChatGPT火了&#xff0c;在发布仅仅五天后就完成了Facebook耗时10个月才完成的目标——用户超过100万。指数级别的算力数据让AI产生了质变&#xff0c;不再像之前一样只是昙花一现。我们要知道&#xff0c;人之所以比动物聪明&#xff0c;是因为人脑千万亿级别的神经元和神经突…