時至 2023 年，2000 萬行仍然是 MySQL 表的軟限制嗎？

程式CSDN

一直有傳言說，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

譯者 | 彎月 責編 | 鄭麗媛

傳言

傳言

網際網路上有一則傳言說，我們應該避免單個 MySQL 表中的資料超過 2000 萬行，否則表的性能就會下降——當資料量超過這個軟限制時，你就會發現 SQL 的查詢速度會比平時慢很多。這是多年前針對 HDD 做出的判斷。我想知道，時至 2023 年，SSD 上的 MySQL 是否仍然有此限制。如果真的有，那麼原因是什麼呢？

環境

環境

資料庫

▶ 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）

實驗設計

實驗設計

創建具有相同結構、但大小不同的表。我一共創建了 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 clientINSERT INTO {table} (person_id, person_name, insert_time, update_time) VALUES ({person_id}, {person_name}, {insert_time}, {update_time})# copycreate 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 查詢來測試性能。腳本可參考：https://github.com/gongyisheng/playground/blob/main/mysql/row_test/select_test.py。
select count(*) from 
 -- full table scanselect count(*) from 
 where id = 12345 -- query by primary keyselect count(*) from 
 where insert_time = 12345 -- query by indexselect * from 
 where insert_time = 12345 -- query by index, but cause 2-times index tree lookup
4. 查看 innodb 緩衝池狀態。
SHOW ENGINE INNODB STATUSSHOW STATUS LIKE 'innodb_buffer_pool_page%
5. 每次完成表的測試，請務必重新啟動資料庫！刷新 innodb 緩衝池，避免讀取舊快取，得到錯誤的結果！
結果
結果
查詢1：select count(*) from
這種查詢會執行全表掃描，MySQL 並不擅長這種工作。
▶ 第一輪：沒有快取。第一次執行查詢時，緩衝池中沒有快取資料。
▶ 第二輪：有快取。當緩衝池中已經有資料快取時執行查詢，通常在第一次查詢執行完之後。
觀察結果：
1. 第一輪查詢的執行時間超出了後面幾次。
原因是 MySQL 使用了 innodb_buffer_pool 來快取資料頁。在第一次執行查詢之前，緩衝池是空的，所以 MySQL 必須進行大量的磁碟 I/O 才能從 .idb 檔案載入表。但在第一次執行結束後，緩衝池中儲存了資料，後續查詢可以直接讀取記憶體，避免磁碟 I/O，因此速度更快。該過程稱為 MySQL 緩衝池預熱。
2. select count(*) from
 會設法將整個表載入到緩衝池。
我比較了實驗前後 innodb_buffer_pool 的統計資料。運行查詢後，如果緩衝池足夠大，則其使用量變化等於表的大小。否則，只有部分表會快取在緩衝池中。原因是查詢 select count(*) from table 會做全表掃描，並做逐行統計。如果沒有快取，就需要將完整的表載入到記憶體中。為什麼？因為 Innodb 支持事務，它不能保證事務在不同時間看到同一張表。全表掃描是獲得準確行數的唯一安全方法。
3. 如果緩衝池不能容納全表，則會爆發查詢延遲。
我注意到 innodb_buffer_pool 的大小會極大地影響查詢性能，因此我嘗試在不同的配置下運行查詢。當使用 11G 緩衝區，而表的大小達到 5000 萬行時，就會爆發查詢延遲。接著，我將緩衝區縮減到 7G，當表的大小達到 3000 萬行時，爆發了查詢延遲。最後，我將緩衝區縮減到 3G，當表的大小僅為 2000 萬行時，就爆發了查詢延遲。很明顯，如果表中的資料無法快取在緩衝池中，則 select count(*) from
 必須執行昂貴的磁碟 I/O，這會導致查詢運行時間直線上升。
4. 對於沒有快取的查詢，查詢花費的時間與表的大小呈線性關係，與緩衝池大小無關。
當沒有快取時，查詢花費的時間由磁碟 I/O 決定，與緩衝池大小無關。在 IOPS 相同的情況下，是否使用 select count(*) 預熱緩衝池並沒有區別。
5. 如果無法完整地快取整個表，則有無快取的查詢運行時間差異是恆定的。
另請注意，如果無法完整地快取整個表，雖然查詢運行時會突然上升，但運行時是可預測的。無論表的大小如何，有無快取的時間差異是恆定的。原因是表的部分資料快取在緩衝區中，這裡的時間差異來自從緩衝區讀取資料節省的時間。
查詢2，3：select count(*) from
 where  = 12345
這個查詢使用了索引。由於不是範圍查詢，MySQL 只需要利用 B+ 樹的路徑從上到下查找頁面，並將這些頁面快取到 innodb 緩衝池中即可。
我創建的表的 B+ 樹的深度都是 3，因此前面的 3~4 次 I/O 都被拿來預熱緩衝區，平均耗時 4~6 毫秒。之後，再次運行相同的查詢，MySQL 就會直接從記憶體中查找結果，耗時為 0.5 毫秒，約等於網路 RTT。如果快取頁面長時間未命中，並從緩衝池中逐出，則必須再次從磁碟載入該頁面，這樣就需要磁碟 I/O（最多 4 次）。
查詢4：select * from
 where  = 12345
這個查詢涉及兩次索引查找。由於 select * 需要查詢獲取的 person_name、person_id 欄位並不在索引中，因此在查詢執行期間，資料庫引擎必須查找 2 個 B+ 樹。它首先查找 insert_time B+ 樹，獲取目標行的主鍵，然後查找主鍵 B+ 樹，獲取該行的完整資料，如下圖所示：
這就是我們應該在生產中避免 select * 的原因。此次實驗證實，此查詢載入的頁面塊比查詢 2 或 3 多出了 2 倍，且最高可達 8 倍。查詢的平均運行時間為 6~10 毫秒，也是查詢 2 或 3 的 1.5~2 倍。
傳言是怎麼來的
傳言是怎麼來的
傳言是怎麼來的
首先，我們需要知道 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。
結論
結論
▶ Innodb 快取池的大小、表的大小決定了是否會出現性能降級。
▶ 判斷是否需要拆分 MySQL 表的一個更有意義的指標是查詢運行時/緩衝池命中率。如果查詢總是命中緩衝區，則不會有任何性能問題。2000 萬行只是一個經驗值。
▶ 除了拆分 MySQL 表之外，增加 Innodb 快取池的大小和資料庫的記憶體也是一個選擇。
▶ 如果可能，請避免在生產中使用 select *，這類語句在最壞的情況下會導致 2 次索引樹查找。
▶ （我個人的意見）考慮到 SSD 現在越來越流行，2000 萬行不再是 MySQL 表的有效軟限制。
相關文章
CNNVD 通報微軟多個安全漏洞
 CNNVD 通報微軟多個安全漏洞
 2023-06-16
近日，CNNVD（國家資訊安全漏洞庫）正式通報微軟多個安全漏洞，其中微軟產品本身漏洞77個，影響到微軟產品的其他廠商漏洞8個。包括Micro...
CNNVD通報Oracle多個安全漏洞
 CNNVD通報Oracle多個安全漏洞
 2023-07-23
近日，CNNVD通報Oracle多個安全漏洞，其中Oracle產品本身漏洞60個，影響到Oracle產品的其他廠商漏洞247個。包括Orac...
維他命每日安全簡訊（2023.06.25）
 維他命每日安全簡訊（2023.06.25）
 2023-06-27
1、Pilot Credentials被黑洩露美國航空和西南航空部分資訊 據媒體6月24日報道，全球最大的兩家航空公司美國航空和西南航空披露...
10倍效率的開發者即將消亡
 10倍效率的開發者即將消亡
 2023-07-23
【編者按】這篇文章是作者對於 10 倍效率的開發者這一概念的批判和反思。作者認為，這種概念是基於錯誤的假設和測量方法，導致了一些不良的後果，...