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 以確保為優化器更新統計資訊等。任何見解將不勝感激。謝謝！

更新：

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