MySQL 效能優化三板斧：從架構層面徹底解決慢查詢

索引、SQL 改寫、架構調整是優化三層次。本文聚焦**結構設計與 SQL 改寫**兩大高收益低成本方向。

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

• 定長優於變長：CHAR(6) 郵遞區號優於 VARCHAR(255)
• 整數優於字串：MEDIUMINT (3 bytes) 優於 BIGINT (8 bytes) 儲存 ID
• NOT NULL 預設：省去 NULL 位圖空間，比較時跳過 NULL 判斷
• ENUM 代替字串枚舉：gender ENUM('M','F') 內部儲存為 1/2，比較極快
• IP 儲存用 INT UNSIGNED：INET_ATON()/INET_NTOA() 轉換

效果： 表越小 → 記憶體能緩存更多頁 → 磁碟 I/O 越少 → 查詢越快。

絕招二：用 JOIN 替代子查詢 (Sub-Queries)

反模式：

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

優化為 LEFT JOIN + IS NULL：

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

原理： MySQL 子查詢會建立**臨時表**並全表掃描；JOIN 可直接利用索引 (需 salesinfo.CustomerID 有索引)，避免臨時表與鎖競爭。

絕招三：用 UNION 替代手動臨時表

舊模式： 建立臨時表 → 分多次 INSERT → 查詢 → 刪除臨時表

UNION 模式：

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

優勢：

• 單次查詢完成，客戶端會話結束自動清理
• UNION ALL 無去重排序開銷，效能遠優於 UNION
• 避免鎖定臨時表、減少權限管理

進階補充：EXPLAIN 驗證優化成果

EXPLAIN SELECT ... JOIN ...;
-- 關鍵指標：
-- type: ref/eq_ref (索引查找) vs ALL (全表掃描)
-- rows: 預估掃描行數
-- Extra: Using index / Using where / Using temporary / Using filesort

實戰心法： 先優化結構 (欄位型別、索引)、再改寫 SQL (JOIN/UNION)、最後調參數。這三招解決 80% 以上慢查詢，無需動架構、無需加硬體。