MySQL 效能優化三大絕招：欄位屬性精簡、JOIN 替代子查詢、UNION 替代臨時表

針對 MySQL 效能優化，三大高收益、低成本的重構技巧：選取最適用欄位屬性、用 JOIN 取代子查詢、用 UNION 取代手動臨時表。附原理分析、對比 SQL 與效能說明。

絕招一：選取最適用的欄位屬性（縮小表體積 = 加速一切）

• 原則：表越小，掃描越快、索引越小、快取命中率越高、IO 越少。
• 實務：
  • 郵遞區號用 CHAR(6) 而非 VARCHAR(255)。
  • 整數鍵用 MEDIUMINT (3 字節) 代替 BIGINT (8 字節)，節省 60% 空間。
  • 能 NOT NULL 就 NOT NULL，查詢時省去 NULL 判斷。
  • 低基數枚舉欄位（省份、性別、狀態）用 ENUM：內部存數值，比較速度快於字串，空間極小。

絕招二：用 JOIN 替代子查詢（消除臨時表 = 省記憶體/CPU）

子查詢缺點

DELETE FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);
-- 或
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo);

• MySQL 執行子查詢時，需建立臨時表存放子查詢結果，再由外層查詢掃描/關聯。
• 臨時表可能寫入磁碟（超過 tmp_table_size），效能急劇下降。

JOIN 改寫（Left Join + IS NULL）

SELECT c.*
FROM customerinfo c
LEFT JOIN salesinfo s ON c.CustomerID = s.CustomerID
WHERE s.CustomerID IS NULL;

• 單次掃描，無臨時表，配合 salesinfo.CustomerID 索引效能極佳。
• NOT IN 含 NULL 值時語義陷阱（返回空集），LEFT JOIN ... IS NULL 無此問題。

絕招三：用 UNION 替代手動臨時表（自動清理、單次查詢）

舊模式：建臨時表 → 多次 INSERT → 查詢 → 手動 DROP

CREATE TEMPORARY TABLE tmp AS SELECT Name, Phone FROM client;
INSERT INTO tmp SELECT Name, BirthDate FROM author;
INSERT INTO tmp SELECT Name, Supplier FROM product;
SELECT * FROM tmp;
DROP TEMPORARY TABLE tmp;

UNION 一氣呵成

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product;

• UNION 自動去重（UNION ALL 保留重複、更快）。
• 所有 SELECT 欄位數、順序、類型必須一致。
• 會話結束自動清理，無殘留。
• MySQL 4.0+ 支援，零學習成本。

綜合效能影響

結語

三大絕招無需架構變更、無需新工具，純 SQL 重構即可見效。建議納入 Code Review 清單：子查詢能否 JOIN？臨時表能否 UNION？欄位類型是否過大？持續微優化，積少成多。