MySQL 子查詢效能優化實戰：強制主鍵索引消除 Using Temporary 與 Filesort

MySQL 子查詢效能優化實戰：強制主鍵索引消除 Using Temporary 與 Filesort

某業務系統列表查詢包含關聯子查詢統計點贊數，執行時間超預期。通過 EXPLAIN 分析發現主表出現 Using temporary; Using filesort，結合索引結構分析，最終使用 FORCE INDEX(PRIMARY) 強制走主鍵索引，查詢效能提升數十倍。本文完整復盤排查與優化全過程。

一、 慢 SQL 現場

SELECT 
    pg.product_goods_id, pg.product_id, pg.pdt_code, pg.pdt_name, 
    pg.brand_name, pg.reference_price, pg.deposit, pg.sale_status,
    pg.is_delete, pg.create_date, pg.create_operator, pg.update_date,
    pg.update_operator, si.shop_id, si.shop_name, pg.goods_img_url,
    pg.is_bargain, pg.qr_code_url,
    (SELECT COUNT(*) FROM product_attention pa 
     WHERE pa.product_goods_id = pg.product_goods_id AND `status` = 0) AS laud,
    pc.category_name, pg.is_experience, pg.buy_type, pg.content,
    pg.assure_flag, pg.market_price, pg.qty_cnt, pg.sales_cnt
FROM product_goods pg
LEFT JOIN shop_info si ON si.shop_id = pg.shop_id
LEFT JOIN product_category pc ON pc.category_id = pg.category_id
WHERE si.market_id IN (1, 2, 3, 12, 13)
ORDER BY pg.update_date DESC, pg.product_goods_id DESC
LIMIT 0, 20;

二、 執行計劃分析

關鍵異常： product_goods 表（別名 pg）的 Extra 欄顯示 Using temporary; Using filesort，表明臨時表物化與文件排序開銷極大。

索引現狀：

• product_goods (pg)：主鍵 PRIMARY (product_goods_id)、普通索引 idx_shop_id (shop_id)
• product_attention (pa)：索引 idx_product_goods_id (product_goods_id, status)
• shop_info (si)：主鍵 shop_id

數據量： pg 表 6 萬行，關聯表均 3 千行級，理論上不應這麼慢。

三、 根因假設與驗證

1. 假設 ORDER BY 引發 Filesort： 去掉 ORDER BY 後查詢變快，但業務強依賴排序不可移除
2. 觀察索引選擇： EXPLAIN 顯示 pg 表走了 idx_shop_id，而非主鍵
3. 核心洞察： 子查詢關聯條件 pa.product_goods_id = pg.product_goods_id 使用的是主鍵，但優化器選擇 shop_id 索引導致：
   • 通過 shop_id 索引回表獲取主鍵 ID
   • 主鍵 ID 無序，導致 ORDER BY pg.update_date, pg.product_goods_id 無法利用索引順序
   • 被迫建臨時表 + Filesort

四、 優化方案：強制主鍵索引

-- 核心變更：在主表別名後添加 FORCE INDEX(PRIMARY)
FROM product_goods pg FORCE INDEX(PRIMARY)
LEFT JOIN shop_info si ON si.shop_id = pg.shop_id
...

五、 優化效果驗證

優化後 EXPLAIN：

• type 變為 index（全索引掃描）
• key 顯示 PRIMARY
• Extra 消失 Using temporary; Using filesort
• rows 從全表掃描降為 20（LIMIT 推下）

實測查詢時間從秒級降至毫秒級，問題徹底解決。

六、 避坑指南與最佳實踐

七、 總結

本案例展示了 索引順序性 對排序優化的決定性作用。當子查詢關聯鍵與外層排序鍵一致（均為主鍵）時，強制走主鍵索引可同時滿足 JOIN 關聯順序、子查詢關聯順序與 ORDER BY 順序，消除臨時表與文件排序開銷。實戰中應善用 EXPLAIN 觀察 key 與 Extra，結合業務語義引導優化器做出最優選擇。