MySQL 性能瓶颈,为什么 MySQL 表的数据量不能太大?

MySQL的性能瓶颈(为什么MySQL有几万的qps,怎么来的?

一个全表扫描的查询: 1000 万数据的全表扫描 2.1s

-- 千万数据

-- 全表扫描(name 无索引)
--耗时: 2.1s
SELECT * FROM `user` WHERE name='sSehAepjIz';

性能分析

1. tcp 性能 20 万, 在查询中的消耗几乎忽略不计(可以自己压一下)
2. 内存性能千万级(自己压: map 性能两千万写,读 5 千万),性能几乎忽略不计
3. 那么消耗几乎都在磁盘上面,磁盘 io 是多少?
   a. 一次 io 加载一页数据 16k
   b. 我的一行数据大概 60 字节,20 行假设 1k,
   c. 一次io 加载 16*20=320 行数据,1000 万行数据需要 io 3 万多次,总耗时 2s
   d. 结论: 1s 钟磁盘 io 1.5 万次左右,就是MySQL 的性能瓶颈(当然实际的应该更多,因为我的数据实际上是大小是算小了的,还有一些隐藏列什么的,实际值也应该在 1.5-2.5 万,反正差不多 2 万级别的)

为什么 MySQL 表不能太大

网上大部分人的说法:

• MySQL 数据不要超过 2000w,b+树 3 层刚好可以大概容纳 2000w 数据(反正自己算吧),
• 如果更多 b+树层数增多,io 次数就会更多,查询就会变慢.

b+树层数变多查询是会变慢,但是这是问题的关键?

问题的关键: B+树层数对查询性能的影响到底有多大?

1. MySQL 磁盘 io 2 万的 qps, 一次 io 1 毫秒都不要
2. b+树多一层容纳的数据量指数级增长(比如 3 层可以容纳 2000 万,4 层就可以容纳 200 亿行数据)
3. b+树查询是多一层多一次 io, 查询语句就算用了 10 个索引,多 10 次 io 也就 几毫秒的事
4. 结论 : 4 层b+树(200 亿数据)查询并不会太大的影响数据查询的性能,就影响几毫秒(几乎忽略)

是什么导致的 MySQL 查询缓慢?

上面已经分析了 MySQL 的性能瓶颈
● 内存 IO : 千万级 qps
● 网络IO: 20 万 qps(tcp)
● 磁盘IO: (2 万 qps)–瓶颈在这里

所以问题是磁盘 io 变多了,什么会导致磁盘 io 大量增长?

不是 B+树导致的磁盘 io 变多(b+树影响小,平均查询时间只会多几毫秒,MySQL 又是多线程的,并阻塞其他线程的查询任务);
而是需要提防的全表扫描
● 没有索引(索引不合理)
● 使用方法包裹索引字段
● 深度分页问题
这些全部都是全表扫描,巨慢,要扫描全表的数据,磁盘需要io 几万次(千万行), 几千万次(百亿行);

**1000w 数据与 2 亿数据(3 层树与 4 层树)**查询性能分析

走普通索引与全表扫描的区别

--1000 万数据
-- 全表扫描
--耗时: 2.1s
EXPLAIN SELECT * FROM `user` WHERE name='sSehAepjIz';
SELECT * FROM `user` WHERE name='sSehAepjIz';
-- 索引扫描
--耗时: 10ms(快到飞起)
SELECT * FROM `user` WHERE create_time='2024-12-27 04:14:53' ;


-- 增加到 2 亿数据

-- 全表扫描
--耗时: 40.2s(慢到爆炸)
SELECT * FROM `user` WHERE name='sSehAepjIz';

-- 索引扫描
--耗时: 14ms(快到飞起)
SELECT * FROM `user` WHERE create_time='2024-12-28 14:14:53' ;

• 走索引只慢 4 毫秒,几乎忽略不计的消耗
• 走全表扫描 慢 38 秒多,增加几十倍的时间

还有什么问题

• 随着数据量的增加,写操作会变慢(涉及到 B+树的平衡机制),
• 我只加了一个索引,前 1000 万条数据 4 分钟就插入完成了,平均 2-3 秒 10 万条,
• 到后面插入越来越慢,1.5 亿数据左右插入 10 条数据几乎要 20秒.性能慢了 10 倍

如何解决:

• 首先要避免全表扫描,这很重要,大数据表的全表扫描就是巨慢,走索引就是快(磁盘 io 几万次与 io 几次的差距)
• 避免一个表中的数量太多(分库分表)
• 有的时候难免会使用全表扫描,不可能所有字段都加索引,(索引会影响写的性能 : 涉及到 b+树 分分合合的故事; 一般表的索引不会超过 5 个)这里时候需要保证请求最频繁的业务一定要加索引,哪些几百年不用几次的就可以不加.

参考:
https://juejin.cn/post/7165689453124517896,
https://cloud.tencent.com/developer/article/2303654,
https://developer.aliyun.com/article/631927