萬盛學電腦網

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

SQL 多表查詢核心技術:JOIN 聯結、子查詢、EXPLAIN 分析與優化實戰

多表查詢是關係型數據庫開發的核心技能。從基本的 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 聯結類型全景圖

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)

1. 內聯結:已付款訂單詳情

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;

2. 左聯結:所有用戶及其訂單統計 (含零訂單用戶)

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,會將左表未匹配行過濾掉,退化為內聯結。

3. 多表聯結:熱銷商品 TOP 10

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;

四、子查詢與 CTE (公用表表達式)

1. 標量子查詢:SELECT 列表中嵌套

SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id AND status = 'paid') AS paid_orders
FROM users u;

注意:若子查詢返回多行會報錯。大數據量下性能差,優先用 JOIN 或關聯子查詢優化。

2. 關聯子查詢:外層列引用內層

-- 找出每個用戶的最大單筆訂單
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
);

3. CTE (WITH 子句,MySQL 8.0+):模塊化複雜查詢

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 執行計劃深度解讀

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訪問的表 表示衍生表(子查詢/CTE)
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)

實戰優化案例:消除 Using temporary + Using filesort

-- 原查詢:慢
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 消失,性能提升數量級

六、高級優化技巧

  1. 小表驅動大表:LEFT JOIN 左表應為小表;INNER JOIN 優化器自動選擇,但可用 STRAIGHT_JOIN 強制順序。
  2. 覆蓋索引:查詢列全包含在索引中,Extra 顯示 Using index,避免回表。
  3. 避免 SELECT *:只查所需列,減少 IO、內存、網絡傳輸,利於覆蓋索引。
  4. 條件下推 (ICP):MySQL 5.6+ 在存儲引擎層過濾 WHERE 條件,減少上層交互。
  5. 派生表物化:MySQL 8.0+ 自動物化 CTE/子查詢,可加索引;舊版本需手動建臨時表。
  6. JOIN 列類型、長度、字符集必須一致:否則隱式轉換導致索引失效。

結語

多表查詢的藝術在於:正確建模關係(JOIN 類型)、精準定義連接條件(ON vs WHERE)、合理拆解複雜邏輯(CTE)、科學分析執行計劃(EXPLAIN)、精準建立索引覆蓋查詢。掌握這套體系,面對億級數據的複雜報表、儀表盤、API 聚合查詢,皆能游刃有餘。記住:EXPLAIN 是數據庫開發者的「聽診器」,每次上線前必聽。

copyright © 萬盛學電腦網 all rights reserved