MySQL 非主鍵關聯查詢優化：子查詢取代 JOIN 實測 12 秒→0.016 秒

場景

兩表 t_team (10,000) 、t_people (1,000,000) 以 **非主鍵欄位** 關聯：
t_team.tname (UK) ↔ t_people.team_name (無索引)

直覺 JOIN 寫法 (極慢)

-- 寫法 1
SELECT * FROM t_team t, t_people p
WHERE t.tname = p.team_name AND p.pname = 'people20000' LIMIT 1;  -- 12.59 秒

-- 寫法 2 (ANSI JOIN)
SELECT * FROM t_team t
INNER JOIN t_people p ON t.tname = p.team_name
WHERE p.pname = 'people20000' LIMIT 1;  -- 12.36 秒

優化：子查詢先過濾驅動表

SELECT *
FROM (SELECT * FROM t_people WHERE pname = 'people20000' LIMIT 1) p
JOIN t_team t ON t.tname = p.team_name
LIMIT 1;  -- 0.016 秒，提升 770 倍

原理

1. 子查詢在 t_people 單表以 pname 過濾，**僅 1 列** 進入 JOIN
2. 避免了 100 萬列 × 1 萬列的笛卡兒積或全表掃描關聯
3. 配合 LIMIT 1 雙重保障

測試資料腳本

CREATE TABLE t_team (tid INT PRIMARY KEY, tname VARCHAR(100));
CREATE TABLE t_people (pid INT PRIMARY KEY, pname VARCHAR(100), team_name VARCHAR(100));

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

延伸建議

• 若 team_name 高頻 JOIN，**務必加索引**
• 長期應調整設計：採用 team_id (PK/FK) 關聯，避免字串比對
• 子查詢法適用於「驅動表可高度過濾」場景；若雙表皆無過濾條件，需走索引 JOIN

結語

非主鍵字串關聯是效能殺手。**子查詢先縮小驅動表** 是快速止血方案；長期治本靠「改整數 FK + 索引」。