萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

SQL 多表查詢核心語法與效能優化實戰

多表查詢是 SQL 核心技能之一。本文系統整理 內連接、外連接、交叉連接、子查詢 等多表查詢語法,對比各類 JOIN 效能差異,並提供索引優化、查詢重寫、避坑指南,助你寫出高效、可維護的多表查詢。

一、多表查詢四大核心語法

1. 內連接(INNER JOIN)─ 最常用

-- 顯式語法(推薦,清晰可讀)
SELECT a.id, a.name, b.order_no, b.amount
FROM users a
INNER JOIN orders b ON a.id = b.user_id
WHERE a.status = 1;

-- 隱式語法(舊標準,不推薦)
SELECT a.id, a.name, b.order_no, b.amount
FROM users a, orders b
WHERE a.id = b.user_id AND a.status = 1;

特點: 只返回兩表匹配成功的行。A 表無對應 B 表資料則不顯示。

2. 左外連接(LEFT JOIN)─ 保留左表所有行

SELECT a.id, a.name, b.order_no, b.amount
FROM users a
LEFT JOIN orders b ON a.id = b.user_id AND b.status = 1  -- 條件放 ON 而非 WHERE
WHERE a.status = 1;

關鍵差異:

  • 條件放 ON:先關聯再過濾,左表行保留,右表欄位為 NULL。
  • 條件放 WHERE:關聯後過濾,等同 INNER JOIN。

3. 右外連接(RIGHT JOIN)─ 保留右表所有行

邏輯與 LEFT JOIN 鏡像,實務極少用,**建議統一用 LEFT JOIN**(交換表順序即可),提升可讀性。

4. 完全外連接(FULL JOIN)─ MySQL 不直接支援

-- MySQL 用 UNION 模擬
SELECT * FROM users a LEFT JOIN orders b ON a.id = b.user_id
UNION
SELECT * FROM users a RIGHT JOIN orders b ON a.id = b.user_id;

二、多表連接實戰範例(三表以上)

-- 用戶 → 訂單 → 訂單明細 → 商品
SELECT u.name, o.order_no, od.qty, p.title, p.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_details od ON o.id = od.order_id
INNER JOIN products p ON od.product_id = p.id
WHERE u.status = 1 AND o.status = 2;  -- 已付款訂單

建議: 驅動表(結果集最小)放最前,大表靠後;所有 JOIN 欄位必須有索引。

三、子查詢 vs JOIN:效能對比

子查詢適用場景

  • IN/EXISTS 子查詢:SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000)
  • 衍生表:SELECT * FROM (SELECT user_id, SUM(amount) total FROM orders GROUP BY user_id) t JOIN users u ON t.user_id = u.id

效能建議

  1. 優先用 JOIN:MySQL 優化器對 JOIN 優化更成熟(Hash Join、BNL 等)。
  2. NOT IN → NOT EXISTS/LEFT JOIN ... IS NULL:NOT IN 遇 NULL 值會返回空結果,效能差。
  3. 衍生表物化: MySQL 5.6+ 會自動物化衍生表,但大結果集仍建議拆分為臨時表或 CTE(MySQL 8.0+ 支援 WITH)。

四、多表查詢效能優化清單

優化點說明驗證方式
JOIN 欄位加索引ON 條件欄位必須有索引,複合索引遵循最左前綴EXPLAIN type=ref/eq_ref
小表驅動大表結果集小的表放前面(驅動表),減少掃描行數EXPLAIN rows 欄位
只選需要欄位避免 SELECT *,減少網路/記憶體/回表開銷EXPLAIN Extra: Using index
WHERE 條件下推MySQL 5.6+ Condition Pushdown,將過濾條件下推到存儲引擎EXPLAIN Extra: Using where
避免 FilesortORDER BY 欄位建複合索引,順序方向一致EXPLAIN Extra: 無 Using filesort
使用覆蓋索引查詢欄位全在索引中,避免回表EXPLAIN Extra: Using index

五、常見錯誤與避坑

  1. 笛卡爾積: 忘寫 ON 條件或 WHERE 條件 → 結果集爆炸。EXPLAIN 會顯示 type=ALL、rows 極大。
  2. LEFT JOIN 右表條件誤放 WHERE: 導致退化為 INNER JOIN。
  3. 隱式類型轉換: JOIN ON a.id = b.user_id 其中一個 INT 一個 VARCHAR → 索引失效。
  4. OR 條件跨表: WHERE a.name='x' OR b.title='y' → 無法用索引,拆分為 UNION。

六、MySQL 8.0+ 新功能:CTE (WITH) 簡化複雜查詢

WITH user_orders AS (
  SELECT user_id, COUNT(*) cnt, SUM(amount) total
  FROM orders WHERE status = 2 GROUP BY user_id
),
high_value AS (
  SELECT user_id FROM user_orders WHERE total > 10000
)
SELECT u.name, uo.cnt, uo.total
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
JOIN high_value hv ON uo.user_id = hv.user_id;

優勢: 邏輯清晰、可重複引用、優化器可獨立優化各 CTE。

七、總結

多表查詢核心是 JOIN 類型選擇 + 索引設計 + 執行計畫分析。掌握 INNER/LEFT JOIN 差異、最左前綴原則、覆蓋索引、Condition Pushdown、CTE 重構,並養成 EXPLAIN 驗證習慣,即能應對 90% 以上的多表查詢場景。

copyright © 萬盛學電腦網 all rights reserved