您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 多表關聯查詢實戰：四種寫法效能對比與優化策略

編輯：MySQL 綜合教學

前言

多表關聯查詢是 SQL 開發的核心技能，但不同寫法的效能差異可能達數十倍。本文透過四個真實場景遞進展示，從隱式內連結到子查詢、變數參數化、多表 JOIN，深度解析各寫法的執行計畫差異與優化要點。

場景一：隱式內連結（舊式寫法）

SELECT * 
FROM order_info AS a, ivrlog4ivrdlvinst AS b
WHERE (a.saleorder = b.ext1_skill 
  AND b.start_date = @date1 
  AND se_id = '55' 
  AND b.ext1_skill <> '')
  AND CONVERT(VARCHAR(10), a.instime, 112) = @date2 
  AND MAX(a.instime);  -- 注意：此處 MAX 寫法有誤，需配合 GROUP BY 或子查詢

缺點：舊式逗號連結易產生笛卡兒積；CONVERT 函數導致索引失效；MAX 無 GROUP BY 會報錯。

場景二：子查詢替換 JOIN

SELECT * 
FROM order_info AS a
WHERE a.saleorder = (
    SELECT b.ext1_skill 
    FROM ivrlog4ivrdlvinst AS b
    WHERE b.start_date = @date1 
      AND se_id = '55' 
      AND b.ext1_skill <> ''
)
AND CONVERT(VARCHAR(10), MAX(a.instime), 112) = @date2;  -- 同樣 MAX 問題

風險：子查詢若回傳多行會報錯；MAX 仍需修正；執行計畫可能轉為 Nested Loops，大數據量下效能差。

場景三：變數參數化 + 子查詢

DECLARE @date1 VARCHAR(20) = '20100812',
        @date2 VARCHAR(20) = '2010-08-12';

SELECT * 
FROM order_info AS a
WHERE a.saleorder = (
    SELECT b.ext1_skill 
    FROM ivrlog4ivrdlvinst AS b 
    WHERE b.start_date = @date1 
      AND se_id = '55' 
      AND b.ext1_skill <> ''
)
AND CONVERT(VARCHAR(10), a.instime, 112) = @date2
AND MAX(a.instime);  -- 仍有語法問題

改進：使用變數避免重複硬編碼，利於計畫重用；但核心邏輯缺陷未解。

場景四：標準 ANSI JOIN 多表關聯（推薦）

SELECT b.caller, b.start_date, b.start_time, b.ext1_skill,
       c.deliveryno, c.destroyresult, c.deliverydate, c.deliverytime, 
       c.arrangetime, c.driverphone, c.drivermobile,
       a.servicedate, a.servicetime, a.workertel
FROM order_info AS a
INNER JOIN ivrlog4ivrdlvinst AS b ON a.saleorder = b.ext1_skill
INNER JOIN delivery_info AS c ON a.saleorder = c.order_id  -- 假設關聯欄位
WHERE b.start_date = @date1 
  AND b.se_id = '55' 
  AND b.ext1_skill <> ''
  AND CONVERT(VARCHAR(10), a.instime, 112) = @date2
ORDER BY b.start_date DESC, b.start_time DESC;

優勢：語義清晰、易維護、優化器易產生 Hash/ Merge Join；但 CONVERT 仍阻礙索引。

關鍵優化重構建議

移除欄位函數運算：將 CONVERT(VARCHAR(10), a.instime, 112) = @date2 改為範圍查詢：
```
a.instime >= @date2 AND a.instime < DATEADD(day, 1, @date2)
```
修正聚合邏輯：若需每筆訂單最新 instime，使用 ROW_NUMBER() OVER (PARTITION BY saleorder ORDER BY instime DESC) 或 APPLY 子查詢。
索引佈局：
- ivrlog4ivrdlvinst(start_date, se_id, ext1_skill)
- order_info(saleorder, instime)
- delivery_info(order_id)
參數化查詢：使用 sp_executesql 或應用層參數化，避免 SQL 注入並促進計畫重用。

效能對比總結表

寫法	可讀性	效能潛力	適用場景
隱式內連結	低	差	避免使用
子查詢	中	中（小數據）	單值查找
變數+子查詢	中高	中	參數化報表
標準 JOIN	高	優（配合索引）	生產環境首選