MySQL 資料庫效能優化進階篇：索引策略、SQL 優化、引擎選型與型別設計

承接基礎篇，本文深入解析 MySQL 效能優化四大支柱：索引設計、SQL 改寫、儲存引擎選型、資料型別優化，並附贈大量資料導入、碎片整理、慢查詢定位實戰技巧。

一、建立適當索引 — 最高 CP 值優化手段

索引原理

• B+ Tree 結構，查找複雜度 O(log₂N)。
• 無索引 = 全表掃描 O(N)，資料量大時災難性。

索引代價

1. 磁碟空間：每個索引佔用額外儲存。
2. 寫入效能：INSERT/UPDATE/DELETE 需同步維護所有索引，I/O 放大。

哪些欄位適合加索引

四大索引類型

• PRIMARY：主鍵自動建立，叢集索引，InnoDB 核心。
• UNIQUE：唯一約束，業務唯一鍵（如 email、身分證）。
• INDEX：普通輔助索引，查詢加速主力。
• FULLTEXT：全文索引，僅 MyISAM 原生支援（InnoDB 5.6+ 支援），中文需分詞器（Coreseek/Sphinx）。

複合索引與最左前綴原則

CREATE INDEX idx_dept_name ON emp(deptno, ename);

• 查詢條件包含 deptno（最左列）→ 使用索引。
• 僅條件 ename → 不使用 複合索引。
• LIKE 'prefix%' 可用索引，LIKE '%suffix' 不可。

索引失效常見場景

1. OR 條件跨欄位且無全覆蓋索引。
2. 複合索引非最左列。
3. LIKE '%...' 開頭萬用字元。
4. 字串欄位查詢未加引號（隱含型別轉換）。
5. MySQL 優化器判斷全表掃描更快（如回表代價過高）。

監控索引使用

SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key 越高越好（索引命中）
-- Handler_read_rnd_next 越高越差（全表掃描）

二、常用 SQL 優化技巧

大批量插入

GROUP BY 優化

-- 預設會排序，產生 Using filesort
EXPLAIN SELECT * FROM emp GROUP BY deptno;  -- Extra: Using filesort

-- 禁止排序
EXPLAIN SELECT * FROM emp GROUP BY deptno ORDER BY NULL;  -- 無 filesort

子查詢改 JOIN

-- 子查詢需建立臨時表
EXPLAIN SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='NY');

-- JOIN 無臨時表，效能更優
EXPLAIN SELECT e.* FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.loc='NY';

OR 條件優化

若要用索引，OR 兩側欄位都必須有索引，否則全表掃描。考慮 UNION ALL 改寫：

SELECT * FROM t WHERE a=1 OR b=2
-- 改為
SELECT * FROM t WHERE a=1
UNION ALL
SELECT * FROM t WHERE b=2 AND a!=1;

三、選擇合適儲存引擎

選型決策樹

1. 資料需持久化？→ 否 → MEMORY
2. 需事務/外鍵/高併發寫入？→ 是 → InnoDB
3. 純讀/多讀少寫/不需事務？→ MyISAM（或 InnoDB 唯讀模式）

四、選擇合適資料型別

• 精確計算用 DECIMAL：FLOAT/DOUBLE 有二進制誤差，金融嚴禁。
• 日期型別最小化：DATE(3B) < DATETIME(8B) < TIMESTAMP(4B)，按需選擇。
• 整數型別足夠則不用 BIGINT：TINYINT(1B) < SMALLINT(2B) < MEDIUMINT(3B) < INT(4B) < BIGINT(8B)。
• IP 儲存用 INT UNSIGNED：INET_ATON()/INET_NTOA() 轉換，比 CHAR(15) 省空間、利索引。

五、MyISAM 碎片整理

-- 頻繁 DELETE/UPDATE 產生碎片
OPTIMIZE TABLE tbl_name;
-- 效果：釋放空間、重排資料、重建索引

InnoDB 共享表空間 (ibdata1) 不支援 OPTIMIZE TABLE，需 ALTER TABLE tbl ENGINE=InnoDB 重建（會鎖表，大表用線上工具）。

六、慢查詢定位標準流程

1. SET long_query_time=1;（預設 10s 太寬鬆）
2. 開啟慢查詢日誌：slow_query_log=1, slow_query_log_file=/var/log/mysql/slow.log
3. 分析工具：pt-query-digest, mysqldumpslow
4. EXPLAIN 分析執行計畫，針對 type=ALL, Using filesort, Using temporary 優化。

效能優化無銀彈，唯有「測量 → 定位 → 優化 → 驗證 → 迭代」的科學循環。建立「索引變更審核機制」、「慢查詢週報」、「核心 SQL 基準測試」，讓優化成為持續工程實踐而非一次性救火。