優化器根據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 可能有更多智能;我忘記了細節。)
在索引中擁有
field1
first 意味著它可能會GROUP BY
在過濾過程中消耗 。請注意,到目前為止我所說的一切都取決於基數或其他“統計數據”。但是,也可能
INDEX(field2)
導致優化器誤入歧途,做出“錯誤”的決定。請記住,“忽略所有索引”可能是執行查詢的最快方式。例如,如果
field1 IN...
不過濾掉太多,那麼INDEX(field1)
可能是浪費時間。但是,(field1, field2)
可能仍然是一個可行的索引。哦。您的特定查詢在較新版本中將無法“工作”。請參閱
only_full_group_by
- 如果不執行 field2,則不應按 field1 分組。因此,我花了一些時間進行分析,這可能對您的潛在問題毫無用處。請解決問題;然後我會繼續。