多表查詢是關係型數據庫開發的核心技能。從基本的 INNER JOIN、LEFT/RIGHT JOIN 到子查詢、派生表、公用表表達式 (CTE),再到 EXPLAIN 執行計劃分析與索引優化,本文系統構建多表查詢知識體系,通過實戰案例演示如何編寫高性能、可維護的複雜查詢 SQL。
-- 單表查詢
SELECT column_list FROM table_name WHERE condition ORDER BY col LIMIT offset, count;
-- 別名簡化引用
SELECT e.name, d.dept_name
FROM employees AS e
JOIN departments AS d ON e.dept_id = d.id;
| JOIN 類型 | Venn 圖語義 | SQL 標準語法 | 適用場景 |
|---|---|---|---|
| INNER JOIN | 交集 | SELECT * FROM A INNER JOIN B ON A.id = B.a_id | 只需匹配記錄,如訂單-用戶關聯 |
| LEFT JOIN | 左表全集 + 匹配右表 | SELECT * FROM A LEFT JOIN B ON A.id = B.a_id | 主表完整保留,如用戶-訂單(含無訂單用戶) |
| RIGHT JOIN | 右表全集 + 匹配左表 | SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id | 較少用,可改 LEFT JOIN 交換表順序 |
| FULL JOIN | 並集 (MySQL 不直接支援) | LEFT JOIN UNION RIGHT JOIN | 雙向完整保留,MySQL 用 UNION 模擬 |
| CROSS JOIN | 笛卡爾積 | SELECT * FROM A CROSS JOIN B | 生成組合、測試數據、矩陣運算 |
| NATURAL JOIN | 同名列自動匹配 | SELECT * FROM A NATURAL JOIN B | 不推薦:隱式依賴列名,重構極易出錯 |
users(id, name, email, created_at)orders(id, user_id, total, status, created_at)order_items(id, order_id, product_id, qty, price)products(id, name, category_id, price)SELECT
o.id AS order_id,
u.name AS customer,
o.total,
o.created_at
FROM orders AS o
INNER JOIN users AS u ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;
SELECT
u.id,
u.name,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent
FROM users AS u
LEFT JOIN orders AS o ON u.id = o.user_id AND o.status = 'paid' -- 條件放 ON 中,而非 WHERE
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
關鍵點:左聯結的篩選條件放在 ON 子句,而非 WHERE。若放 WHERE,會將左表未匹配行過濾掉,退化為內聯結。
SELECT
p.name,
p.category_id,
SUM(oi.qty) AS total_qty,
SUM(oi.qty * oi.price) AS revenue
FROM order_items AS oi
INNER JOIN orders AS o ON oi.order_id = o.id AND o.status = 'paid'
INNER JOIN products AS p ON oi.product_id = p.id
GROUP BY p.id, p.name, p.category_id
ORDER BY total_qty DESC
LIMIT 10;
SELECT
u.name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id AND status = 'paid') AS paid_orders
FROM users u;
注意:若子查詢返回多行會報錯。大數據量下性能差,優先用 JOIN 或關聯子查詢優化。
-- 找出每個用戶的最大單筆訂單
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.total = (
SELECT MAX(total)
FROM orders
WHERE user_id = u.id
);
WITH paid_orders AS (
SELECT * FROM orders WHERE status = 'paid'
),
user_stats AS (
SELECT user_id, COUNT(*) AS cnt, SUM(total) AS sum
FROM paid_orders
GROUP BY user_id
)
SELECT u.name, us.cnt, us.sum
FROM users u
JOIN user_stats us ON u.id = us.user_id
WHERE us.cnt > 5
ORDER BY us.sum DESC;
優勢:邏輯分層、可複用、避免重複子查詢、優化器更易生成優秀計劃。
EXPLAIN
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
| 列名 | 含義 | 優化關注點 |
|---|---|---|
| id | 查詢序號 | 相同 id 從上往下執行;不同 id 數字大先執行 |
| select_type | 查詢類型 | SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、DEPENDENT SUBQUERY |
| table | 訪問的表 | |
| partitions | 匹配的分區 | 分區表優化 |
| type | 訪問類型 (核心指標) | system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。目標:至少達到 ref,避免 ALL (全表掃描) |
| possible_keys | 可能用到的索引 | 無則需建立索引 |
| key | 實際使用的索引 | NULL 表示未用索引 |
| key_len | 索引鍵長度 | 判斷聯合索引用了幾列 |
| ref | 與索引比較的列/常量 | const、列名、func |
| rows | 預估掃描行數 | 越小越好,乘積反映總成本 |
| filtered | 按條件過濾後剩餘比例% | 低則說明 WHERE 過濾性差 |
| Extra | 額外信息 (關鍵) | Using index (覆蓋索引) Using where (WHERE 過濾) Using temporary (臨時表) Using filesort (外部排序) Using join buffer (Join Buffer) |
-- 原查詢:慢
EXPLAIN SELECT * FROM orders WHERE user_id = 100 ORDER BY created_at DESC LIMIT 10;
-- Extra: Using where; Using filesort
-- 優化:建立聯合索引 (user_id, created_at DESC)
CREATE INDEX idx_user_created ON orders(user_id, created_at DESC);
-- 再次 EXPLAIN
-- type: ref, key: idx_user_created, Extra: Using index condition (或 Backward index scan)
-- filesort 消失,性能提升數量級
LEFT JOIN 左表應為小表;INNER JOIN 優化器自動選擇,但可用 STRAIGHT_JOIN 強制順序。Extra 顯示 Using index,避免回表。多表查詢的藝術在於:正確建模關係(JOIN 類型)、精準定義連接條件(ON vs WHERE)、合理拆解複雜邏輯(CTE)、科學分析執行計劃(EXPLAIN)、精準建立索引覆蓋查詢。掌握這套體系,面對億級數據的複雜報表、儀表盤、API 聚合查詢,皆能游刃有餘。記住:EXPLAIN 是數據庫開發者的「聽診器」,每次上線前必聽。