我有 2 張桌子，item和config.
item有 ~1500 萬行，config有 ~1000 行。
我想用一個WHERE子句連接兩個表并對結果進行排序。
這可能看起來像這樣：
SELECT
`t0`.`id`,
`t0`.`item_name`,
`t1`.`id`,
`t1`.`config_name`,
FROM
`item` t0
LEFT OUTER JOIN `config` `t1` ON `t0`.`config_id` = `t1`.`id`
WHERE (`t0`.`config_id` = 678)
ORDER BY
`t0`.`item_name` ASC;
這在 ~800ms 內成功運行并回傳 ~50k 行。
我也想對這個結果進行分頁，所以我運行相同的查詢并添加一個LIMIT：
SELECT
`t0`.`id`,
`t0`.`item_name`,
`t1`.`id`,
`t1`.`config_name`,
FROM
`item` t0
LEFT OUTER JOIN `config` `t1` ON `t0`.`config_id` = `t1`.`id`
WHERE (`t0`.`config_id` = 678)
ORDER BY
`t0`.`item_name` ASC LIMIT 200;
這個查詢現在需要超過 5 分鐘。
我試圖了解導致這種差異的原因。
我可以簡化查詢，JOIN完全洗掉查詢，僅查詢大表以嘗試隔離速度變慢的原因：
SELECT
`t0`.`id`,
`t0`.`item_name`,
FROM
`item` t0
WHERE (`t0`.`config_id` = 678)
ORDER BY
`t0`.`item_name` ASC;
此查詢運行良好，但同樣，添加LIMIT大大增加了查詢時間。
我怎樣才能解決這個問題或更好地診斷是什么導致了它？
The execution plan for the simplified query without LIMIT is as follows:
---- ------------- ------- ------------ ------ --------------- ----------- --------- ------- ------- ---------- ---------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------- ------------ ------ --------------- ----------- --------- ------- ------- ---------- ---------------------------------------
| 1 | SIMPLE | t0 | NULL | ref | ITEM_FK_1 | ITEM_FK_1 | 8 | const | 98524 | 100.00 | Using index condition; Using filesort |
---- ------------- ------- ------------ ------ --------------- ----------- --------- ------- ------- ---------- ---------------------------------------
Adding LIMIT 200 to the query produces this execution plan:
---- ------------- ------- ------------ ------- --------------- -------------------- --------- ------ ------- ---------- --------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |
---- ------------- ------- ------------ ------- --------------- -------------------- --------- ------ ------- ---------- --------------------------
| 1 | SIMPLE | t0 | NULL | index | ITEM_FK_1 | ITEM_RULE_ITEM_UNQ | 775 | NULL | 31933 | 0.63 | Using where; Using index |
---- ------------- ------- ------------ ------- --------------- -------------------- --------- ------ ------- ---------- --------------------------
uj5u.com熱心網友回復：
要查找行config_id=678并對其排序item_name并僅取前 200 行，您有（除其他外）以下選項：
使用由 排序的索引
item_name，并繼續閱讀，直到找到 200 行也滿足config_id=678（無需排序）config_id=678使用config_id（您的外鍵）上的索引獲取所有行，然后按名稱對這些行進行排序，并取前 200
其中哪個更快取決于您的資料。
首先，這將取決于行的config_id=678位置。例如，如果前 200 行（按名稱排序，例如以 an 開頭A）都具有此 id，這將非常快：您可以讀取 200 行，然后停止，甚至不必訂購任何東西。如果您不走運，并且所有這些 id 都在此串列的末尾（例如，只有以 a 開頭的名稱Z才有此 id），那么您必須在找到 200 個適合的行之前閱讀所有行。
第二個選項取決于您擁有的行數config_id=678。它將讀取所有 50k 個（使用索引），對它們進行排序，然后給你前 200 個。這將介于上面的快速和慢速選項之間。
MySQL現在基本上必須猜測哪個版本更快。對于帶有 的查詢limit 200，它猜錯了，顯然，它必須讀取比預期更多的行。
為了讓您了解 MySQL 的想法：
MySQL 假設您有 98.524 行（不是 50k）和
config_id=678（rows第一個執行計劃中的數字）。您有 1500 萬行，因此特定行具有該 id 的概率為 98.524 / 15 Mill = 1/150。您需要其中的 200 個，因此您需要閱讀大約 200*150=30.000（或 31.933，您的第二個執行計劃中的數字）行，直到您可能找到足夠的行。
現在 MySQL 將讀取 100k 行加上對它們進行排序與可能讀取 30k 行進行比較，并選擇了后者。在這種情況下是錯誤的（盡管 5 分鐘似乎有點多，但還有其他因素，例如增加的索引大小或可能會減慢覆寫率）。但可能適合不同的 ID。
如果您增加限制（您必須在以后的頁面中這樣做），MySQL 將在某個時刻切換執行計劃（例如，找到具有該概率的前 1.000 需要大約 1.000*150=150k > 100k 行）。
所以，你可以做什么：
- 您可以強制MySQL 使用您想要的索引，例如使用
... from item t0 force index (ITEM_FK_1) left outer join .... 這樣做的缺點是，根據 id，不同的執行計劃可能會更快。 - 您可以添加最佳索引：復合索引
(config_id, item_name)允許您僅讀取具有正確 id 的行，并且由于它們按名稱排序，因此您可以在前 200 行之后停止。無論您的資料分布如何，您總是讀取 200 行（或更少）。假設id是主鍵，沒有比這更快的解決方案。
我會選擇選項 2。
uj5u.com熱心網友回復：
添加這個
INDEX(config_id, item_name, id) -- in this order!
以及作為DROP該索引的“前綴”的任何索引。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/332692.html
標籤：mysql sql performance large-data
上一篇：Mysql查詢執行速度非常慢