MySQL 查詢優化五大核心策略：數據類型、字符集、COUNT、子查詢與 UNION 實戰調優指南

引言

SQL 語句寫法不同，性能可能相差數量級。本文針對 MySQL 總結五大高頻優化策略：**數據類型精準選擇、字符集隱式轉換消除、COUNT 聚合優化、子查詢轉聯結、UNION 去重開銷規避**，並給出驗證方法與避坑清單。

一、優化數據類型：小而精，拒絕默認

1.1 避免 NULL：隱性成本高

• NULL 需額外 1 bit 標記（MyISAM 按字節對齊）、索引統計特殊處理、IS NULL/IS NOT NULL 無法命中範圍掃描優化
• 規範：NOT NULL DEFAULT 默認值（0、''、'1970-01-01'、'0000-00-00' 等業務無效值）
• 極少數業務真需「未知」語義時，使用單獨 is_xxx_known BOOLEAN NOT NULL DEFAULT 1 標記

1.2 選擇最小足夠類型

**效益**：百萬行表，每行省 10B → 省 10MB 磁盤 + 緩存，IOPS、備份、複製全鏈路受益。

二、小心字符集隱式轉換：殺手級性能殺手

• 客戶端 character_set_client、連接 connection、結果 results、表/列 charset 不一致 → 隱式 CONVERT()，**導致索引失效、全表掃描、CPU 飆升**
• 規範：全鏈路統一 utf8mb4（含 Emoji），collate utf8mb4_general_ci 或 0900_ai_ci
• 驗證：SHOW VARIABLES LIKE 'character_set%';、SHOW CREATE TABLE 檢查列級字符集
• 應用層：JDBC characterEncoding=utf8mb4、PHP mysqli_set_charset、Python charset='utf8mb4'

三、優化 COUNT(*) 與 COUNT(col)

**大表聚合通用方案**：
1. 觸發器/應用邏輯維護計數表（強一致）
2. 定時任務刷新統計表（最終一致，低開銷）
3. SELECT TABLE_ROWS FROM information_schema.tables 近似值（僅參考）

四、優化子查詢：轉聯結是黃金法則

4.1 為什麼子查詢慢？

• MySQL 優化器歷史包袱：IN/EXISTS 子查詢常被執行為 **Dependent Subquery**（外層每行內層執行一次），而非 Semi-Join
• 無法利用聯結優化器的聯結重排、索引下推、BKA 等高級策略

4.2 標準轉換模式

-- IN 子查詢 → INNER JOIN (去重)
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2);
→
SELECT DISTINCT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

-- NOT IN 子查詢 → LEFT JOIN ... IS NULL
SELECT * FROM t1 WHERE id NOT IN (SELECT id FROM t2);
→
SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

-- 相關標量子查詢 → LEFT JOIN
SELECT t1.*, (SELECT name FROM t2 WHERE t2.id = t1.t2_id) AS t2_name FROM t1;
→
SELECT t1.*, t2.name AS t2_name FROM t1 LEFT JOIN t2 ON t1.t2_id = t2.id;

4.3 關鍵前提：被聯結表的聯結列**必須有索引**

否則聯結退化為 Block Nested Loop，性能更差。

4.4 例外：DELETE 聯結不支持標準語法

MySQL 不支持 DELETE t1 FROM t1 JOIN t2 ...（MariaDB/新版本已支持）。舊版本方案：
1. 應用層兩步：SELECT id ... → DELETE FROM t1 WHERE id IN (...)
2. CONCAT() 動態生成批量 DELETE 腳本管道執行

五、優化 UNION：避免隱式排序去重

• UNION = UNION ALL + 全局排序去重（臨時表 + filesort），大數據集極慢
• UNION ALL 直接拼接結果，**無排序、無去重**，性能優 10-100 倍

5.1 選擇原則

1. 業務確定無重複行 → **直接用 UNION ALL**
2. 業務可容忍重複行（上層去重/應用層去重）→ **UNION ALL**
3. 必須數據庫層去重且數據量大 → **分步驟**：CREATE TEMPORARY TABLE tmp AS SELECT ... UNION ALL ...; ALTER TABLE tmp ADD UNIQUE KEY(...); SELECT * FROM tmp; 利用索引去重避免 filesort

六、驗證與持續優化閉環

1. EXPLAIN / EXPLAIN ANALYZE (8.0.18+) 檢查執行計劃：type、key、rows、Extra
2. SHOW PROFILE / performance_schema.events_statements_history_long 定位耗時階段
3. 慢查詢日志 long_query_time=0 全量採集，pt-query-digest 分析 Top SQL
4. 大數據集真實負載壓測（sysbench / tpcc / 實際業務流量回放）
5. 基準線對比：優化前後 QPS、P99、Rows_examined、Innodb_buffer_pool_reads

七、總結

MySQL 查詢優化核心：**小類型、統一字符集、COUNT 計數表、子查詢轉聯結、UNION ALL 代替 UNION**。每條 SQL 上線前必過 EXPLAIN、慢查詢日志必分析、大表變更必壓測。優化無終點，唯有持續度量與迭代。