一直有傳 言 說，MySQL 表的數(shù)據(jù)只要超過 20 00 萬行，其性能就會(huì)下降。而本文作者用實(shí)驗(yàn)分析證明：至少在 2023 年 ，這已不再是 MySQL 表的有效軟限制。
原文 鏈接：https://yishenggong.com/2023/05/22/is-20m-of-rows-still-a-valid-soft-limit-of-mysql-table-in-2023/
未經(jīng)允許，禁止轉(zhuǎn)載！
(資料圖片)
作 者 | Yisheng Gong 譯者 | 彎月 責(zé)編 | 鄭麗媛 出品 | CSDN（ID：CSDNnews）
傳言
互聯(lián)網(wǎng)上有一則傳言說，我們應(yīng)該避免單個(gè) MySQL 表中的數(shù)據(jù)超過 2000 萬行，否則表的性能就會(huì)下降——當(dāng)數(shù)據(jù)量超過這個(gè)軟限制時(shí)，你就會(huì)發(fā)現(xiàn) SQL 的查詢速度會(huì)比平時(shí)慢很多。 這 是 多年前 針 對 H DD 做出的 判斷 。 我 想知道，時(shí)至 2 023 年， SSD 上的 MySQL 是 否 仍然 有 此 限制 。 如果 真 的有，那么原因是什么呢？
環(huán)境
數(shù)據(jù)庫
? MySQL 版本: 8.0.25
? 實(shí)例類型：AWS db.r5.large（2vCPUs, 16GiB RAM）
? EBS 存儲(chǔ)類型：General Purpose SSD（gp2）
測試客戶端
? Linux 內(nèi)核版本：6.1
? 實(shí)例類型：AWS t2.micro（1 vCPU, 1GiB RAM）
實(shí)驗(yàn)設(shè)計(jì)
創(chuàng)建具有相同結(jié)構(gòu)、但大小不同的表。我一共創(chuàng)建了 9 個(gè)表，數(shù)據(jù)行數(shù)分別為：10 萬、20 萬、50 萬、100 萬、200 萬、500 萬、1000 萬、2000 萬、3000 萬、5000 萬和 6000 萬。
1. 創(chuàng)建幾個(gè)具有相同結(jié)構(gòu)的表：
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. 插入不同的數(shù)據(jù)。我使用了測試客戶端和表復(fù)制的方式創(chuàng)建了這些表。腳本可參考：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 的值是隨機(jī)的。
3. 使用測試客戶端執(zhí)行以下 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 緩沖池狀態(tài)。
SHOW ENGINE INNODB STATUSSHOW STATUS LIKE "innodb_buffer_pool_page%
5. 每次完成表的測試，請務(wù)必重新啟動(dòng)數(shù)據(jù)庫！刷新 innodb 緩沖池，避免讀取舊緩存，得到錯(cuò)誤的結(jié)果！
結(jié)果
查詢1：select count(*) from
這種查詢會(huì)執(zhí)行全表掃描，MySQL 并 不擅長這種工作。
? 第一輪：沒有緩存。第一次執(zhí)行查 詢時(shí)，緩沖池中沒有緩存數(shù)據(jù)。
? 第二輪：有緩存。當(dāng)緩沖池中已經(jīng)有數(shù)據(jù)緩存時(shí)執(zhí)行查詢，通常在第一次查詢執(zhí)行完之后。
觀察結(jié)果：
1. 第一輪查詢的執(zhí)行時(shí) 間超出了后面幾次。
原因是 MySQL 使用了 innodb_buffer_pool 來緩存數(shù)據(jù)頁。在第一次執(zhí)行查詢之前，緩沖池是空的，所以 MySQL 必 須進(jìn)行大量的磁盤 I/O 才能從 .idb 文件加載表。但在第一次執(zhí)行結(jié)束后，緩沖池中存儲(chǔ)了數(shù)據(jù)，后續(xù)查詢可以直接讀取內(nèi)存，避免磁盤 I/O，因此速度更快。該過程稱為 MySQL 緩沖池預(yù)熱。
2. select count(*) from
會(huì)設(shè)法將整個(gè)表加載到緩沖池。 我比較了實(shí)驗(yàn)前后 innodb_buffer_po ol 的統(tǒng)計(jì)數(shù)據(jù)。運(yùn)行查詢后，如果緩沖池足夠大，則其使用量變化等于表的大小。否則，只有部分表會(huì)緩存在緩沖池中。原因是查詢 select count(*) from table 會(huì)做全表掃描，并做逐行統(tǒng)計(jì)。如果沒有緩存，就需要將完整的表加載到內(nèi)存中。為什么？因?yàn)?Innodb 支持事務(wù)，它不能保證事務(wù)在不同時(shí)間看到同一張表。全表掃描是獲得準(zhǔn)確行數(shù)的唯一安全方法。
3. 如果緩沖池不能容納全表，則 會(huì)爆發(fā)查詢延遲。
我注意到 innodb_buffer_pool 的大小 會(huì)極大地影響查詢性能，因此我嘗試在不同的配置下運(yùn)行查詢。當(dāng)使用 11G 緩沖區(qū)，而表的大小達(dá)到 5000 萬行時(shí)，就會(huì)爆發(fā)查詢延遲。接著，我將緩沖區(qū)縮減到 7G，當(dāng)表的大小達(dá)到 3000 萬行時(shí)，爆發(fā)了查詢延遲。最后，我將緩沖區(qū)縮減到 3G，當(dāng)表的大小僅為 2000 萬行時(shí)，就爆發(fā)了查詢延遲。很明顯，如果表中的數(shù)據(jù)無法緩存在緩沖池中，則 select count(*) from
必須執(zhí)行昂貴的磁盤 I/O，這會(huì)導(dǎo)致查詢運(yùn)行時(shí)間直線上升。 4. 對于沒有緩存的查詢，查詢花 費(fèi)的時(shí)間與表的大小呈線性關(guān)系，與緩沖池大小無關(guān)。
當(dāng)沒有緩存時(shí)，查詢花費(fèi)的時(shí)間 由磁盤 I/O 決定，與緩沖池大小無關(guān)。在 IOPS 相同的情況下，是否使用 select count(*) 預(yù)熱緩沖池并沒有區(qū)別。
5. 如果無法完整地緩存整個(gè)表，則有無緩存的查詢運(yùn)行時(shí)間差異是恒定的。
另請注意，如果無法完整地緩存整個(gè)表，雖然查詢運(yùn)行時(shí)會(huì)突然上升，但運(yùn)行時(shí)是可預(yù)測的。無論表的大小如何，有無緩存的時(shí)間差異是恒定的。原因是表的部分?jǐn)?shù)據(jù)緩存在緩沖區(qū)中，這里的時(shí)間差異來自從緩沖區(qū)讀取數(shù)據(jù)節(jié)省的時(shí)間。
查詢2，3：select count(*) from
where = 12345 這個(gè)查詢使用了索引。由于不是范圍查詢，MySQL 只需要利用 B+ 樹的路徑從上到下查找頁面，并將這些頁面緩存 到 innodb 緩沖池中即可。
我創(chuàng)建的表的 B+ 樹的深度都是 3，因此前面的 3~4 次 I/O 都被拿來預(yù)熱緩沖區(qū)，平均耗時(shí) 4~6 毫秒。之后，再次運(yùn)行相同的查詢，MySQL 就會(huì)直接從內(nèi)存中查找結(jié)果，耗時(shí)為 0.5 毫秒，約等于網(wǎng)絡(luò) RTT。如果緩存頁面長時(shí)間未命中，并從緩沖池中逐出，則必須再次從磁盤加載該頁面，這樣就需要磁盤 I/O（最多 4 次）。
查詢4：select * from
where = 12345 這個(gè)查詢涉及兩次索引查找。由于 select * 需要查詢獲取的 person_name、person_id 字段并不在索引中， 因此在查 詢執(zhí)行期間，數(shù)據(jù)庫引擎必須查找 2 個(gè) B+ 樹。它首先查找 insert_time B+ 樹，獲取目標(biāo)行的主鍵，然后查找主鍵 B+ 樹，獲取該行的完整數(shù)據(jù)，如下圖所示：
這就是我們應(yīng)該在生產(chǎn)中避免 select * 的 原因。 此次實(shí)驗(yàn)證實(shí)，此查詢加載的頁面塊比查詢 2 或 3 多出了 2 倍， 且最高可達(dá) 8 倍。查詢的平均運(yùn)行時(shí)間為 6~10 毫秒，也是查詢 2 或 3 的 1.5~2 倍。
傳言是怎么來的
首先，我們需要知道 innodb 索引頁的物理結(jié) 構(gòu)。默認(rèn)頁面大小為 16k，由頁眉、系統(tǒng)記錄、用戶記錄、頁面導(dǎo)向器和尾部組成。只有剩下的 14~15k 用來存儲(chǔ)數(shù)據(jù)。
假設(shè)你使用 INT 作為主鍵（4 字節(jié)），每行 1KB 的有效負(fù)載。每個(gè)葉頁可以存儲(chǔ) 15 行，一個(gè)指向該頁的指針需要 4+8=12 字節(jié)。因此，每個(gè)非葉頁最多可以容納 15k / 12 字節(jié) = 1280 個(gè)指針。如果你有一個(gè) 4 層的 B+ 樹，它最多可以容納 1280*1280*15 = 24.6M 行數(shù)據(jù)。
回到 HDD 占據(jù)市場主導(dǎo)地位，且 SSD 對于數(shù)據(jù)庫而言過于昂貴的時(shí)代，4 次隨機(jī) I/O 可能是我們可以容忍的最壞情況，而使用 2 次索引樹查找的查詢甚至?xí)骨闆r變得更糟。當(dāng)時(shí)的工程師想要控制索引樹的深度，不希望它們太深。而如今 SSD 越來越流行，隨機(jī) I/O 比以前便宜了，因此我們應(yīng)該反思一下 10 年前的規(guī)則。
順便說一句，5 層 B+ 樹可以容納 1280*1280*1280*15 = 31.4B 行數(shù)據(jù)，超過了 INT 所能容納的最大數(shù)據(jù)量。對每行大小的不同假設(shè)將導(dǎo)致不同的軟限制，或小于或大于 2000 萬行。例如，在我的實(shí)驗(yàn)中，每一行大約是 816 字節(jié)（我使用 utf8mb4 字符集，所以每個(gè)字符占用 4 個(gè)字節(jié)），4 層 B+ 樹可以容納的軟限制是 29.5M。
結(jié)論
? Innodb 緩存池的大小、表的大小決定了是否會(huì)出現(xiàn)性能降級。
? 判斷是否需要拆分 MySQL 表的一個(gè)更有意義的指標(biāo)是查詢運(yùn)行時(shí)/緩沖池命中率。如果查詢總是命中緩沖區(qū)，則不會(huì)有任何性能問題。2000 萬行只是一個(gè)經(jīng)驗(yàn)值。
? 除了拆分 MySQL 表之外，增加 Innodb 緩存池的大小和數(shù)據(jù)庫的內(nèi)存也是一個(gè)選擇。
? 如果可能，請避免在生產(chǎn)中使用 select *，這類語句在最壞的情況下會(huì)導(dǎo)致 2 次索引樹查找。
? （我個(gè)人的意見）考慮到 SSD 現(xiàn)在越來越流行，2000 萬行不再是 MySQL 表的有效軟限制。
標(biāo)簽：
推薦閱讀
-
2023-04-12 14:07:04
-
2023-04-12 13:48:04
-
2023-04-12 11:33:31
-
2023-04-12 11:30:02
-
2023-04-12 08:58:45
-
2023-04-11 17:06:56
精彩要聞
-
2023-06-01 06:16:12
-
2023-06-01 06:05:58
-
2023-06-01 06:16:26
-
2023-06-01 04:59:28
-
2023-06-01 04:48:02
-
2023-06-01 04:06:12
-
2023-06-01 04:07:27
-
2023-06-01 02:40:42
-
2023-06-01 02:35:41
-
2023-06-01 02:33:15
-
2023-06-01 02:15:00
-
2023-06-01 02:22:32
-
2023-06-01 01:08:02
-
2023-06-01 01:15:49
-
2023-06-01 00:53:41
-
2023-06-01 01:00:36
-
2023-06-01 00:03:22
-
2023-06-01 00:01:09
-
2023-06-01 00:07:42
-
2023-05-31 23:43:23
-
2023-05-31 23:14:05
-
2023-05-31 23:08:00
-
2023-05-31 23:14:02
-
2023-05-31 22:53:49
-
2023-05-31 22:56:00
-
2023-05-31 22:33:34
-
2023-05-31 22:18:32
-
2023-05-31 22:06:51
-
2023-05-31 22:23:36
-
2023-05-31 21:44:56
-
2023-05-31 21:43:22
-
2023-05-31 21:55:09
-
2023-05-31 21:46:21
-
2023-05-31 21:30:10
-
2023-05-31 21:21:54
-
2023-05-31 21:00:02
-
2023-05-31 20:51:25
-
2023-05-31 20:58:53
-
2023-05-31 20:49:13
-
2023-05-31 20:24:33
-
2023-05-31 20:03:22
-
2023-05-31 20:03:29
-
2023-05-31 19:53:05
-
2023-05-31 19:44:24
-
2023-05-31 19:57:32
-
2023-05-31 19:46:28
-
2023-05-31 19:01:30
-
2023-05-31 19:11:26
-
2023-05-31 19:15:53
-
2023-05-31 18:54:56
最新文章
-
2023-05-31 18:55:27
-
2023-05-31 18:44:39
-
2023-05-31 18:28:28
圖文推薦
-
2023-04-12 15:49:12
2023-04-12 15:14:22
2023-04-12 09:53:28
主站蜘蛛池模板：
清除唯美第一区二区三区
|
日本三级香港三级人妇gg在线
|
国产精品毛片在线更新
|
欧美成人性色xxxxx视频大
|
亚洲天堂免费在线视频
|
久草五月天|
伊人日日操
|
日本高清视频免费观看
|
免费一级做a爰片久久毛片潮
|
亚洲国产模特在线播放
|
天堂成人在线观看
|
中文字幕色在线
|
免费美剧在线观看
|
日韩波多野结衣
|
日韩视频在线观看一区
|
久久一区二区三区99
|
国产成人精品福利色多多
|
国产精品亚洲第一区广西莫菁
|
久久99免费
|
国产福利在线观看第二区
|
久久伊人亚洲
|
久久国产精品国语对白
|
国产第一页久久亚洲欧美国产
|
欧美xxxx日本
|
人人射人人爱
|
国产趴着打光屁股sp视频网站
|
人人澡人人澡人人看欧美
|
性香港xxxxx免费视频播放
|
国内一区亚洲综合图区欧美
|
亚洲综合网在线观看
|
青青国产成人精品视频
|
亚洲第一区二区快射影院
|
偷拍久久网
|
天天摸天天躁天天添天天爽
|
国产成人综合在线
|
日韩在线免费播放
|
久久国产这里只有精品
|
久草www|
午夜爽爽性刺激一区二区视频
|
久久高清免费
|
2019天天操夜夜操
|
