MySQL性能調優實戰：從索引選擇到聯合索引優化

問題場景：單一索引導致結果集過大

在優化一個圖片資料表的查詢時，發現針對userid='admin' ORDER BY clicks DESC LIMIT 10的查詢，MySQL選擇了clicks索引，但掃描行數高達5455行，遠超預期。這是因為clicks索引無法同時滿足WHERE userid='admin'和ORDER BY clicks的需求，導致大量不必要的行被讀入內存排序。

嘗試方案一：強制索引+條件過濾

嘗試添加WHERE clicks > 1000並強制使用clicks索引：

EXPLAIN SELECT * FROM imgs USE INDEX (clicks) 
WHERE userid='admin' AND clicks > 1000 
ORDER BY clicks DESC LIMIT 10;

結果集縮減至312行，排序開銷可接受。但此方案需為每個userid尋找合適的採樣點（如1000），對程式邏輯極不友好，且不同用戶的點擊分佈差異大，固定閾值會導致結果數量不穩定，影響用戶體驗。

最佳方案：建立聯合索引(userid, clicks)

創建複合索引：

CREATE INDEX userid_clicks ON imgs (userid, clicks);

再次執行EXPLAIN，發現key為userid_clicks，rows仍為2944，但關鍵在於Extra列的Using filesort消失了。這意味著MySQL直接利用索引的有序性完成排序，無需額外內存排序，效率大幅提升。

聯合索引的權衡與Limit深分頁問題

雖然聯合索引解決了排序問題，但若查詢模式多樣，過多索引會拖慢寫入效能並增加表損壞風險。此外，MySQL對LIMIT 100000, 10這類深分頁仍無完美解法——必須掃描前10萬行定位偏移量。目前只能通過業務層面（如記錄上次最大ID、使用游標分頁）或表結構設計（如冗余排序字段）來迴避。

總結

索引優化是MySQL性能提升的核心：優先讓索引覆蓋WHERE、ORDER BY、LIMIT，善用複合索引消除filesort；權衡索引數量與寫入性能；深分頁問題需從應用層解決。掌握這些原理，往往能避免單純堆疊硬體資源。