MySQL 優化器在兩個索引之間進行選擇時使用什麼策略？

有問題：

使用兩個範圍條件優化查詢

OP想知道是否有辦法優化他的查詢。在給出的答案中，查詢被重寫為LEFT JOIN，這與我的想法相同。然而，這並沒有幫助，執行計劃保持不變。

table   type    possible_keys                 key
book    ref     author_id,publish_date,i0,i1  author_id

所以我刪除了索引 author_id 並創建了一個新索引，如：

 KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

看到新計劃我有點驚訝：

table   type    possible_keys                 key
book    ref     publish_date,i0,i1,i2         i0

為什麼之前確實選擇了 author_id 卻沒有選擇 i2 而不是 i0？當我在表中為 i0 和 i2 交換位置時，我更加困惑：

PRIMARY KEY (`id`),
KEY `publish_date` (`publish_date`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`),
KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`) 

現在突然選擇了索引 i2：

table   type    possible_keys                 key
book    ref     publish_date,i2,i0,i1         i2

我是否遺漏了某些東西，或者優化器是否選擇了它受益的第一個聲明的索引，即使稍後聲明了更好的索引？

使用以下索引和謂詞涉及 author_id、country、org、publish_date、price 的查詢：

KEY `author_id` (`author_id`),
KEY `i2` (`author_id`, `country`, `org`, `publish_date`, `price`)

我希望優化器選擇 i2 而不是 author_id，但如果我做對了，這取決於它們的定義順序。

這些觀察結果普遍正確還是只發生在小桌子上？

索引選擇基於使用不同索引時將訪問的估計行數。僅當兩個索引的估計成本相同時，聲明索引的順序才會有所不同。在您的情況下，org、country 和 author_id 三列可用於索引查找。可以使用以這三列開頭的任何索引，並且成本相同。

您觀察到的最可能的原因是 InnoDB 索引統計資訊在批量插入後沒有更新。如果我對這兩個表執行 ANALYZE TABLE，優化器會選擇 i0 索引而不是 author_id。

如果您想更多地了解選擇特定查詢計劃的原因，我建議您查看查詢的優化器跟踪。

沒有SELECT看，我所能做的就是揮手。

• INDEX(a,b)比INDEX(a);更笨重 使用後者的一個可能原因是它可能需要更少的索引塊。一般來說，你不應該同時擁有兩者；只有更大的索引。
• **新評論：**當您同時擁有INDEX(a)和INDEX(a,b)時，優化器有時會選擇較短的 (1-col)，即使較大的 (2-col) 會更好。因此，當較小的索引是較大索引的精確前綴時，請刪除它。
• 複合索引中的 5 列大約是“實用性”的限制。
• 優化器最近發生了多次變化——“成本模型”不斷改進。但它還沒有考慮 SSD 與 HDD，也沒有考慮已經記憶體的內容。
• 在某些情況下，優化器會有效地忽略LEFT; 不要指望它強制執行不同的查詢計劃。LEFT同時，除非您真的希望“右手”表中缺少行，否則請不要使用；它使讀者感到困惑。
• 我不認為單個列的“基數”用於選擇索引。
• =, IS NULL, !=,IN和LIKE各種“範圍”謂詞的處理方式不同。 =是最容易優化的；在某些情況下IN會LIKE變成=.
• AND易於優化；OR不是。
• 不要在函式中隱藏索引列；它不能使用索引。（例如，DATE(col) = ...）
• 如果超過大約 20% 的行將被觸及，則將迴避索引 - 表掃描可能會更快。
• 優化器對複合鍵沒有很好的統計；這可能會導致您所看到的一些情況。
• 我懷疑索引的定義順序是否重要。你有一個簡單的測試案例來證明這一點嗎？

在給定 SELECT的情況下，請參閱我的食譜以創建最佳索引。

引用自：https://dba.stackexchange.com/questions/178313