MySQL 覆蓋索引優化經典案例：百萬級圖片表查詢提速 25%

MySQL 覆蓋索引優化經典案例

本文復盤一個真實生產優化案例：單表近百萬行、高頻單表查詢，通過建立覆蓋索引將平均響應時間從 40ms 降至 30ms，性能提升 25%，並深度解析 Using index 執行計劃含義。

業務場景

• 表 pics：存儲用戶圖片信息，約 100 萬行
• 核心字段：user_id、picname、smallimg
• 查詢模式：SELECT picname, smallimg FROM pics WHERE user_id = ?
• 現有索引：idx_uid (user_id)

優化前執行計劃

  
使用了單列索引 idx_uid
  
Extra 為空 → 需回表獲取 picname、smallimg



優化策略：建立覆蓋索引
EXPLAIN SELECT SQL_NO_CACHE picname, smallimg FROM pics WHERE user_id=17853;
-- type=ref, key=idx_uid_pic, Extra=Using index
-- 10 次平均 30ms


  
優化器選擇新建複合索引 idx_uid_pic
  
Extra=Using index → 覆蓋索引生效，無回表


核心結論

  
Using index = 覆蓋索引：索引已包含查詢所需所有列，僅掃描索引樹
  
消除回表 → 減少隨機 IO → 顯著降低延遲
  
適用場景：查詢列較少、寫入頻率允許額外索引維護成本


避坑提示

  
不要過度建立覆蓋索引：每個索引增加 INSERT/UPDATE 開銷
  
索引列順序錯誤（如 (picname, user_id)）將導致索引失效
  
若 SELECT *，覆蓋索引無效（除非索引包含所有列，不現實）