一直有傳 言 說，MySQL 表的數據只要超過 20 00 萬行，其性能就會下降。而本文作者用實驗分析證明：至少在 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）
傳言
互聯網上有一則傳言說，我們應該避免單個 MySQL 表中的數據超過 2000 萬行，否則表的性能就會下降——當數據量超過這個軟限制時，你就會發現 SQL 的查詢速度會比平時慢很多。 這 是 多年前 針 對 H DD 做出的 判斷 。 我 想知道，時至 2 023 年， 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 AUTO_INCREMENT,`person_id` int NOT ,`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` fromperson_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 lookup4. 查看 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_po ol 的統計數據。運行查詢后，如果緩沖池足夠大，則其使用量變化等于表的大小。否則，只有部分表會緩存在緩沖池中。原因是查詢 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
