Mysql

mySQL 查詢優化器不使用索引

  • October 30, 2018

在我們的生產環境(Amazon RDS 上的 mySQL 5.6.40)中,對於某些查詢,查詢優化器會忽略表索引並需要幾分鐘才能完成,而在我們的非 RDS 測試環境(mySQL 5.7)中,相同的查詢只需要一小部分時間一秒鐘 - 由於使用索引。

而不是立即在 RDS 上從 mySQL 5.6.40 升級到 5.7(假設優化器工作得更好!),我想知道我們是否缺少一些東西,因為查詢看起來很直接……我有點驚訝優化器不選擇索引。

範例查詢:

select id, username, agent, app_instance_id, end_time, event_time, 
last_modified 
from event where username='joebloggs' and app_instance_id<>'ad69469a- 
d73f-4b33-a1c5-bde03c765d89' and last_modified>'2018-08-20 20:14:51.394' 
order by last_modified asc limit 20

執行時間處理時間:

  • mysql 5.6.40:6m+
  • mySQL 5.7:0.02 秒

解釋:

在 mySQL 5.6.40 EXPLAIN 上顯示優化器不使用任何索引(第 5 列“可能的鍵”,第 9 列“額外”)

‘1’,‘SIMPLE’,’event’,‘range’,’last_modified,username,appinstanceid,user_app_last’,’last_modified’,‘6’,NULL,‘1128956’,‘使用where’

而在 mySQL 5.7 EXPLAIN 顯示優化器選擇一個索引。

‘1’,‘SIMPLE’,’event’,NULL,‘range’,‘username,last_modified,appinstanceid,user_app_last’,’last_modified’,‘6’,NULL,‘1030540’,‘0.54’,‘使用索引條件; 使用哪裡’

長話短說,我們的 RDS 實例曾經執行 mySQL 5.5 並且一切正常……但是由於 5.5 的生命週期結束而強制升級到 5.6.40,這個問題已經浮出水面。

我還執行了 ANALYZE TABLE 以確保為優化器更新統計資訊等。任何見解將不勝感激。謝謝!

更新:

  1. 我們的客戶端應用程序使用 JPA,可以將其配置為使用索引提示。僅僅是查詢優化器在 5.7 上更好並且我們應該升級,還是我們應該提供提示……而不依賴查詢優化器?
  2. 在 RDS 上對新的 TEST 實例(從 5.6.40 升級的 mySQL 5.7.23)執行進一步測試後,在優化器是否選擇使用索引的同一查詢中,它似乎受到了打擊和遺漏。

在任何系統中,這可能是有益的,可能是最佳的:

INDEX(username, last_modified) -- in this order

請提供SHOW CREATE TABLE,以便我們查看您擁有的索引、數據類型、引擎等。

我推薦的指數

  • 是一個“複合”索引。
  • 等於兩個單列索引。
  • 故意從通過測試的列開始=
  • 忽略<>無用。
  • 包括一個“範圍”(開last_modified)。
  • 它應該但可能不會消耗ORDER BY,從而允許它有效地消耗LIMIT. 如果沒有一直到達LIMIT,它注定要掃描整個表,從而花費“很長時間”。

附註:( UUIDs例如app_instance_id看起來)在大表中索引時效率低下是出了名的。對於此查詢,我希望很少有行具有該特定值。

要進一步調查“為什麼”,請回答以下問題:

  • 表中包含的百分比username='joebloggs'。和
  • 表格包含的百分比last_modified>'2018-08-20 20:14:51.394'

如果其中任何一個超過20%,那麼優化器明智地選擇忽略相應的索引。可能是其中一個的統計數據比另一個要好。讓我們看看SHOW INDEXES FROM event

設計良好索引的食譜

盡量避免索引提示——它們今天可能會有所幫助,但明天會受到傷害(使用不同的常數)。

引用自:https://dba.stackexchange.com/questions/221092