MySQL JOIN + ORDER BY + LIMIT 效能陷阱與改寫技巧

問題現場

兩表 t_team (1,000 筆) 、t_people (100,000 筆)，需求：依 people.pname 排序取前 10 筆，並關聯 team 名稱。

直覺寫法 (慢)

SELECT *
FROM t_people p
LEFT JOIN t_team t ON p.team_id = t.id
ORDER BY p.pname
LIMIT 10;  -- 耗時 ~3 秒

對比實驗

根因分析

MySQL 執行順序：**JOIN → 排序 → LIMIT**。關聯後產生 100,000 列中間結果，再全量排序，極度浪費。

改寫方案：子查詢先排序再 JOIN

SELECT *
FROM (
    SELECT * FROM t_people ORDER BY pname LIMIT 10
) p
LEFT JOIN t_team t ON p.team_id = t.id
LIMIT 10;  -- 耗時 ~0.16 秒，提升 20 倍

子查詢先在單表完成「排序 + LIMIT」，僅 10 列進入 JOIN，中間結果集從 10 萬降為 10。

實戰案例

生產環境複雜表結構：原寫法 80+ 秒，改寫後 < 1 秒。

建表與測試資料腳本

CREATE TABLE t_team (id INT PRIMARY KEY, tname VARCHAR(100));
CREATE TABLE t_people (id INT PRIMARY KEY, pname VARCHAR(100), team_id INT,
    FOREIGN KEY (team_id) REFERENCES t_team(id));

DELIMITER //
CREATE PROCEDURE seed_data()
BEGIN
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    WHILE i < 1000 DO
        INSERT INTO t_team VALUES (i+1, CONCAT('team', i+1));
        SET i = i + 1;
    END WHILE;
    SET i = 0;
    WHILE i < 100000 DO
        INSERT INTO t_people VALUES (i+1, CONCAT('people', i+1), i%1000+1);
        SET i = i + 1;
    END WHILE;
    COMMIT;
END//
DELIMITER ;
CALL seed_data();

適用場景與限制

• ✅ 驅動表 (排序表) 有索引支援 ORDER BY
• ✅ LIMIT 較小 (分頁首頁、Top N)
• ⚠️ 若需 ORDER BY t.tname (被驅動表欄位)，此技巧不適用，需覆蓋索引或物化視圖

結語

「先縮小、再關聯」是大表分頁查詢的黃金法則。遇到 JOIN + ORDER BY + LIMIT 慢查詢，優先嘗試子查詢/衍生表先排序取 Top N，再做關聯。