優化器根據mysql 5.6中的行數選擇不正確的索引進行查詢

我有 mysql 5.6。有問題的查詢具有以下格式：

SELECT field_1, field_2
 FROM trace_ejb3
WHERE field_1 IN (...) AND field_2 BETWEEN ... AND ...
GROUP BY field_1;

優化器計算每個執行計劃的成本並最終選擇不正確的索引來使用。據我了解主要原因 - 返回的行數。但實際上我需要使用另一個索引。執行的差異是 2 天與 1 小時。

我嘗試使用以下選項： - innodb_stats_persistent + sample_pages - optimizer_switch - optimizer_search_depth - optimizer_prune_level

然而，關於選項都不會影響結果：優化器仍然根據返回的最少行數來選擇執行計劃。

問題：有沒有辦法強制優化器不考慮行數？

PS 我知道索引提示，但我不認為它是一個長期的解決方案。

通常，強制優化器採用特定路徑的唯一方法是添加程式碼（如您提到的索引提示）以明確告訴優化器它應該（或不應該）使用什麼。

除此之外，通常還必須查看特定的查詢和查詢計劃、優化器擁有的資訊以及它選擇路徑的原因。在大多數情況下，優化器選擇次優路徑是因為它的資訊不好（缺少或過期的統計資訊），或者是因為記憶體的計劃在記憶體時執行良好，但在目前參數下效果不佳。當然，有時可用的最佳路徑仍然很糟糕。

在這些情況下，您可以查看查詢計劃，查看查詢出現最多問題的地方，並找出一種方法來為優化器提供更好的選擇。這可以創建一個覆蓋索引；添加或更新統計資訊（假設可以在 MySQL 下完成）；將查詢分解為多個步驟，優化器可以在這些步驟上做出準確的估計等。

沒有“通用”解決方案。到目前為止提到的調整（以及其他調整）有時都是單獨有用的。

特定查詢可能會受益於復合INDEX(field1, field2)…

如果優化器足夠聰明，可以從 field1 跳轉到 field1 以在 field2 上執行範圍（舊版本的 MySQL 沒有），那麼它可能會使用該索引來獲得很好的優勢。（5.5 可能缺少這種智能；5.7 可能有更多智能；我忘記了細節。）

在索引中擁有field1first 意味著它可能會GROUP BY在過濾過程中消耗 。

請注意，到目前為止我所說的一切都取決於基數或其他“統計數據”。但是，也可能INDEX(field2) 導致優化器誤入歧途，做出“錯誤”的決定。

請記住，“忽略所有索引”可能是執行查詢的最快方式。例如，如果field1 IN...不過濾掉太多，那麼INDEX(field1)可能是浪費時間。但是，(field1, field2)可能仍然是一個可行的索引。

哦。您的特定查詢在較新版本中將無法“工作”。請參閱only_full_group_by- 如果不執行 field2，則不應按 field1 分組。因此，我花了一些時間進行分析，這可能對您的潛在問題毫無用處。請解決問題；然後我會繼續。

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