MySQL 覆蓋索引優化實戰：百萬級圖片表查詢效能提升 25%

場景描述

• 表：pics，近 100 萬筆資料
• 欄位：user_id、picname、smallimg
• 現有索引：idx_uid (user_id)
• 高頻查詢：SELECT picname, smallimg FROM pics WHERE user_id = ?

優化前分析

EXPLAIN SELECT SQL_NO_CACHE picname, smallimg FROM pics WHERE user_id = 17853;

結果： 使用 idx_uid，type=ref，rows=估算值，Extra=NULL。平均耗時 40ms。

雖用到索引，但需回表查詢 picname、smallimg，產生額外 I/O。

優化動作：建立覆蓋索引

ALTER TABLE pics ADD INDEX idx_uid_cover (user_id, picname, smallimg);

索引順序遵循：WHERE 條件欄位在前 + SELECT 欄位在後。

優化後效果

EXPLAIN SELECT SQL_NO_CACHE picname, smallimg FROM pics WHERE user_id = 17853;

結果： 使用 idx_uid_cover，Extra=Using index。平均耗時降至 30ms，**提升 25%**。

核心原理：什麼是覆蓋索引？

索引葉子節點已包含查詢所需**所有欄位**，MySQL 僅掃描索引樹即可滿足查詢，**無需回表**存取聚簇索引，大幅減少隨機 I/O。

適用場景與限制

實戰建議

1. 使用 SQL_NO_CACHE 測試真實執行時間，排除 Query Cache 干擾
2. 觀察 Handler_read_next、Innodb_buffer_pool_reads 狀態變量驗證 I/O 降低
3. 定期 ANALYZE TABLE 更新統計資訊，確保優化器選擇正確索引