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 幾萬次(千萬行), 幾千萬次(百億行);

-- 千萬數據-- 全表掃描(name 無索引)
--耗時: 2.1s
SELECT * FROM `user` WHERE name='sSehAepjIz';-- 索引掃描(create_time 有普通索引)
--耗時: 10ms(快到飛起)
SELECT * FROM `user` WHERE create_time='2024-12-27 04:14:53' ;

如何解決:

• 首先要避免全表掃描,這很重要,大數據表的全表掃描就是巨慢,走索引就是快(磁盤 io 幾萬次與 io 幾次的差距)
• 避免一個表中的數量太多(分庫分表)
• 有的時候難免會使用全表掃描,不可能所有字段都加索引,(索引會影響寫的性能 : 涉及到 b+樹 分分合合的故事; 一般表的索引不會超過 5 個)這里時候需要保證請求最頻繁的業務一定要加索引,哪些幾百年不用幾次的就可以不加.

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