MySQL 20 條核心優化準則：從索引設計到查詢重寫的實戰總結

MySQL 20 條核心優化準則：從索引設計到查詢重寫的實戰總結

彙整自生產優化經驗與社區最佳實踐，涵蓋索引、查詢、架構、配置四大維度。每條附帶反例與正例，可直接作為 Code Review 清單。

一、索引設計 7 條鐵律

1. 建立索引要有選擇性：唯一值佔比 < 1% 的欄位（如性別、狀態）建索引無效，優化器會選全表掃描。

   -- 反例
   ALTER TABLE users ADD INDEX idx_sex(sex);  -- sex 只有 M/F
   -- 正例：組合索引包含高選擇性欄位
   ALTER TABLE users ADD INDEX idx_sex_created(sex, created_at);

2. 索引不是越多越好：每個索引增加 INSERT/UPDATE/DELETE 開銷，單表建議 ≤ 5 個索引。
3. 組合索引遵循「最左前綴」：INDEX (a,b,c) 支援 a、a,b、a,b,c，不支援 b、c、b,c。
4. 覆蓋索引避免回表：SELECT a,b FROM t WHERE c=1 若有 INDEX (c,a,b)，Extra 顯示 Using index，無需回主鍵查詢。
5. 字串欄位建前綴索引：INDEX (name(10))，長度取區分度 ≥ 0.9 時的最小值，節省空間。
6. 外鍵欄位必須建索引：InnoDB 自動建立，但檢查是否存在，避免鎖升級導致死鎖。
7. 定期清理無用/重複索引：pt-duplicate-key-checker 或 sys.schema_unused_indexes 定期掃描。

二、查詢重寫 8 大技巧

1. 避免 SELECT *：只查需要欄位，減少網路傳輸、利用覆蓋索引、減少緩存污染。
2. WHERE 條件勿對欄位做運算/函數：

   -- 反例：索引失效
   WHERE DATE(created_at) = '2023-01-01'
   WHERE YEAR(created_at) = 2023
   -- 正例：範圍查詢走索引
   WHERE created_at >= '2023-01-01' AND created_at < '2023-01-02'

3. 慎用 !=、NOT IN、IS NULL：導致全表掃描。改用 NOT EXISTS、LEFT JOIN ... IS NULL、預設值代替 NULL。
4. IN 改 EXISTS、JOIN：子查詢 IN 易產生臨時表，EXISTS 可半連接優化。
5. UNION ALL 代替 UNION：無去重需求時，UNION ALL 避免排序去重開銷。
6. 大偏移 LIMIT 優化：LIMIT 100000, 10 改為覆蓋索引子查詢 + 回表：

   SELECT * FROM t WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 100000,1) ORDER BY id LIMIT 10;

7. OR 拆分為 UNION ALL：WHERE a=1 OR b=2 無法用組合索引，拆分後各走各索引再合併。
8. 分頁遊標法替代 OFFSET：記住上一頁最大 ID，WHERE id > last_id ORDER BY id LIMIT 20，極大偏移也極快。

三、架構與配置 5 項關鍵

1. 讀寫分離 + 連接池：主庫寫、從庫讀，應用層路由或 ProxySQL。連接池大小 = CPU核心 × 2~4。
2. 緩存熱點資料：Redis/Memcached 承擔 80%+ 讀請求，MySQL 專注寫入與複雜查詢。
3. 分庫分表：單表超 2000 萬行或 10GB 考慮分表；跨庫事務用最終一致性（TCC/Saga）。
4. 關鍵參數調優：

   innodb_buffer_pool_size = 物理記憶體 60~70%
   innodb_log_file_size = 1~4 GB
   innodb_flush_log_at_trx_commit = 1  (資料安全) 或 2 (效能優先)
   sync_binlog = 1
   max_connections = 2000~5000

5. 監控與慢查詢：slow_query_log=1、long_query_time=1，配合 pt-query-digest 定期分析 Top SQL。

避坑指南：

• 隱式類型轉換導致索引失效：WHERE varchar_col = 123 → WHERE varchar_col = '123'
• COUNT(*) 在 InnoDB 無 WHERE 時需全表掃描，改用 SHOW TABLE STATUS 或維護計數表。
• 大事務長時間持有鎖，拆分為小批次提交。
• 備份期間 FLUSH TABLES WITH READ LOCK 造成阻塞，改用 --single-transaction 熱備份。

結語：優化無銀彈，但「建對索引、寫對 SQL、調對參數、分對架構」四步走能解決 90% 效能問題。建議團隊建立 SQL 審核機制，上線前必經 EXPLAIN 驗證。