為什麼查詢優化器選擇這個糟糕的執行計劃？
我們有一個包含超過 1TB 數據的 MariaDB 表（故事），定期執行一個查詢，獲取最近添加的行以在其他地方建立索引。
innodb_version: 5.6.36-82.1 version : 10.1.26-MariaDB-0+deb9u1當查詢優化器決定使用二級索引進行範圍遍歷（在 1000 的儲存桶中）時，查詢工作得很好：
explain extended SELECT stories.item_guid FROM `stories` WHERE (updated_at >= '2018-09-21 15:00:00') AND (updated_at <= '2018-09-22 05:30:00') ORDER BY `stories`.`id` ASC LIMIT 1000; +------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+ | 1 | SIMPLE | stories | range | index_stories_on_updated_at | index_stories_on_updated_at | 5 | NULL | 192912 | 100.00 | Using index condition; Using filesort | +------+-------------+---------+-------+-----------------------------+-----------------------------+---------+------+--------+----------+---------------------------------------+ 1 row in set, 1 warning (0.00 sec)但偶爾，即使數據集差異很小（注：與上面查詢的第二個時間戳差異，值得一提的是整張表保存了幾年的數據，保存了幾千萬行）決定使用主鍵索引：
explain extended SELECT stories.item_guid FROM `stories` WHERE (updated_at >= '2018-09-21 15:00:00') AND (updated_at <= '2018-09-22 06:30:00') ORDER BY `stories`.`id` ASC LIMIT 1000; +------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | stories | index | index_stories_on_updated_at | PRIMARY | 8 | NULL | 240259 | 83.81 | Using where | +------+-------------+---------+-------+-----------------------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)導致它遍歷整個主鍵索引（我猜是按順序）然後過濾
updated_at需要幾個小時才能完成的欄位。該查詢是由 ORM ActiveRecord 創建的，可能遠非理想。作為一種解決方案，我們提出了幾個手動製作的查詢，將其
ORDER BY stories.id移出，和/或使用使用/強制索引來避免 PK，因為我們實際上是通過updated_at.我在這裡有興趣了解的是查詢優化器如何/為什麼選擇該執行計劃？我知道查詢優化器使用索引/表統計資訊來做出該決定，但是在這種情況下，如果我對
innodb工作原理的理解是正確的，那麼很明顯，在不使用任何 PK id 進行過濾的情況下遍歷一個巨大的 PK 不是理想的。我本質上是想了解那個“壞”決定來自哪裡，使用了哪些統計數據或未知變數，以免最終得出好的計劃（經常選擇的計劃，並且速度快幾個數量級）。
請隨意糾正我的任何假設，因為我絕對不是 DBA 專家。
簡短的回答： the
WHERE和 theORDER BY正朝著不同的方向發展。優化器還不夠聰明，無法始終正確地決定要拉哪個方向。長答案：
任何以 .開頭的
WHERE索引的好處。請參閱“100%”等內容。這樣的索引可以快速找到所需的行，全部為 192K。但是查詢隨後需要對 192K 行 ( ) 進行排序，然後才能到達.updated_at``ORDER BY``LIMIT的
ORDER BY id好處PRIMARY KEY。該索引允許查詢按順序獲取所有行（從而避免排序），因此可以到達LIMIT（因此永遠不會超過 1000 行。如果 1000 的值很小
id，它將執行得很快。如果所需的 1000 在表中延遲，則查詢將執行緩慢。優化器無法預測（沒有更多的智能和復雜性）。
update_at主要是跟踪嗎id？如果是這樣，那麼任何一個索引都會導致基本相同的塊。但是優化器既沒有註意到這一點，也沒有利用它。可能的加速：
“覆蓋”索引是包含所有需要的列的索引。由於 PK 與數據“聚集”在一起，
PRIMARY KEY(id)因此是一種覆蓋索引。但我們看到了它可能有多糟糕。以下可能會更好：INDEX(updated_at, -- first, to deal with the WHERE id, item_guid) -- in either order該查詢對 192K 行進行範圍掃描的速度比它需要
INDEX(updated_at)在數據之間反彈 192K 次來查找的速度更快item_guid。排序不會得到改善。唉，這加快了查詢速度。所以，讓我重新戴上我的思考帽。
分區？
哦，您可能已經找到了
PARTITIONing. 六年前，我只有 4 個案例；我還沒有找到新的案例。讓我談談你的情況可能會有所不同。假設你
PARTITION BY RANGE (TO_DAYS(updated_at))在表上使用，設置了大約 50 個分區。（如果您需要清除“舊”數據，分區非常好。）設置分區時，需要重新考慮所有索引。你現在有什麼索引？我會假設這些：
PRIMARY KEY(id), INDEX(updated_at)就您而言，也許不會有太大變化：
PRIMARY KEY(id, updated_at), INDEX(updated_at)您的查詢會發生什麼？
解決第
WHERE一個問題不會有太大變化。是的，會有分區修剪，加上二級索引。速度將保持不變。通過解決第
ORDER BY一個問題，查詢計劃還可以精簡到一個（或兩個）分區。然後在那個/那些分區內按順序掃描id將是 50（或 25）倍的速度。可能會增加排序，因為來自不同分區的行不會按順序排列。其他注意事項
我們能看到
SHOW CREATE TABLE嗎？一個 1TB 的表需要很多東西來幫助提高性能。如果你所有的數字都是INTs，那麼可能有很多空間可以通過縮小到MEDIUMINT等來恢復。規範化可能值得做。如果被索引，那對插入性能GUID肯定是一個很大的負擔。等等等等。超魔方的
如果
ORDER BY updated_at, id可以完成任務，那麼就消除了使用慢解釋計劃的誘惑。並INDEX(updated_at, id, item_guid)成為最佳指標。並且分區變得無用。分頁
不是通過 OFFSET，記住你離開的地方。這討論瞭如何處理涉及多個列的“遺漏”。