mySQL 查詢優化器不使用索引
在我們的生產環境(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 以確保為優化器更新統計資訊等。任何見解將不勝感激。謝謝!
更新:
- 我們的客戶端應用程序使用 JPA,可以將其配置為使用索引提示。僅僅是查詢優化器在 5.7 上更好並且我們應該升級,還是我們應該提供提示……而不依賴查詢優化器?
- 在 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
。盡量避免索引提示——它們今天可能會有所幫助,但明天會受到傷害(使用不同的常數)。