資料庫優化器 (Cost-Based Optimizer) 僅能在**用戶提交的 SQL 邏輯框架內**尋找最優執行計畫。一條邏輯混亂、缺乏索引支撐、濫用子查詢的 SQL，無論優化器多聰明，也無法變出高效執行路徑。優化的起點永遠是**寫出優質的查詢計畫**。
| 場景 | 索引類型 | 範例 |
|---|---|---|
| 頻繁 JOIN 關聯欄位 (非外鍵) | 單欄/組合索引 | INDEX idx_user_id (user_id) |
| WHERE/ORDER BY/GROUP BY 高頻欄位 | 單欄/組合索引 | INDEX idx_status_created (status, created_at) |
| 高基數欄位 (唯一值多) | B-Tree 索引 | 身分證號、手機號、UUID |
| 多欄位固定順序排序/分組 | 組合索引 (最左前綴) | INDEX idx_a_b_c (a, b, c) 支援 a / a,b / a,b,c |
INDEX idx_content_prefix (content(100))。-- 檢查索引碎片/損壞 (MyISAM/InnoDB)
ANALYZE TABLE tbl_name; -- 更新統計信息
OPTIMIZE TABLE tbl_name; -- 整理碎片、重建索引 (鎖表)
-- 大表線上重建 (Percona Toolkit)
pt-online-schema-change --alter "ENGINE=InnoDB" D=db,t=tblORDER BY/GROUP BY 完全一致 (含方向 ASC/DESC)。| 模式 | 原因 | 優化方向 |
|---|---|---|
ORDER BY a ASC, b DESC | 混合排序方向 | 建立 INDEX idx_a_b (a ASC, b DESC) (MySQL 8.0+ 支援降序索引) |
WHERE x=1 ORDER BY y | 索引為 (x) 或 (y) | 建立組合索引 (x, y) |
JOIN 多表 ORDER BY t1.a | 排序欄位非驅動表 | 調整 JOIN 順序、冗餘欄位、或應用層排序 |
-- 三層嵌套，每層 1000 行 → 10 億次訪問
SELECT * FROM t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
WHERE t1.type = 'A';ALTER TABLE t2 ADD INDEX idx_t1_id (t1_id); ALTER TABLE t3 ADD INDEX idx_t2_id (t2_id);
-- 原查詢：OR 條件導致全表掃描
SELECT * FROM orders
WHERE (customer_num = 104 AND order_num > 1001) OR order_num = 1008;
-- 優化：UNION ALL 合併兩個索引友好查詢
SELECT * FROM orders WHERE customer_num = 104 AND order_num > 1001
UNION ALL
SELECT * FROM orders WHERE order_num = 1008;關鍵：UNION ALL 不去重，效能遠優於 UNION；確保業務邏輯允許重複或結果集本無重疊。
-- 外層每行觸發一次內層查詢
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.id AND i.price > 100);-- 內層先聚合/去重，再 JOIN
SELECT o.* FROM orders o
JOIN (SELECT DISTINCT order_id FROM order_items WHERE price > 100) i
ON o.id = i.order_id;
-- 或使用半連接 (MySQL 8.0+ 優化器自動轉換)
SELECT * FROM orders o
WHERE o.id IN (SELECT order_id FROM order_items WHERE price > 100);| 殺手模式 | 原因 | 替代方案 |
|---|---|---|
LIKE '%keyword%' | 前導萬用字元，無法用索引 | 全文索引、Elasticsearch、前綴索引 LIKE 'keyword%' |
LIKE '_abc%' | 非開頭固定長度萬用字元 | 同上 |
REGEXP '[0-9]{3}' | 正則不走索引 (MySQL 8.0 始支援部分) | 應用層預處理、生成計算欄位索引 |
col LIKE '98____' | 固定長度模式 | 改為範圍查詢 col BETWEEN '98000' AND '98999' 走索引 |
-- 1. 建立臨時表 (會話級自動清理)
CREATE TEMPORARY TABLE tmp_active_customers AS
SELECT c.id, c.name, c.balance
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'paid' AND c.balance > 0
GROUP BY c.id, c.name, c.balance;
-- 2. 後續多次查詢直接命中臨時表
SELECT * FROM tmp_active_customers WHERE region = '华东';
SELECT COUNT(*) FROM tmp_active_customers WHERE balance > 10000;⚠️ 注意：臨時表**不反映源表實時變更**，高頻寫入場景需謹慎評估數據新鮮度要求。
機械磁盤隨機 I/O 延遲 ~10ms，循序讀取 ~0.1ms，**差距 100 倍**。SSD 雖縮小差距，但隨機讀放大寫放大仍存在。
-- 原查詢：隨機讀取 10000 個離散 ID
SELECT * FROM products WHERE id IN (1001, 5003, 9999, ...);
-- 優化：批量查詢 + 應用層排序/映射
SELECT * FROM products WHERE id IN (1001, 5003, 9999, ...) ORDER BY id;
-- 或拆分為範圍查詢
SELECT * FROM products WHERE id BETWEEN 1000 AND 2000
UNION ALL
SELECT * FROM products WHERE id BETWEEN 5000 AND 6000; ...SELECT *？ → 顯式列出欄位。EXPLAIN 驗證 type=ref/range/eq_ref。WHERE phone = 13800000000 (phone 是 varchar) 導致索引失效。OFFSET 100000 LIMIT 20？ → 改用游標分頁 WHERE id > last_id ORDER BY id LIMIT 20。OR 導致索引失效？ → 改 UNION ALL。utf8mb4_general_ci vs utf8mb4_unicode_ci 混用導致隱式轉換。查詢優化不是玄學，而是**索引設計、SQL 重構、執行計畫分析**三位一體的工程實踐。內化這 7 大原則，配合 EXPLAIN、OPTIMIZER_TRACE、sys.schema_table_statistics 等工具，讓每一條 SQL 都經得起高併發大數據的考驗。
