效能調優#

MySQL 效能調優涉及多個層面，從索引選擇到查詢最佳化，再到參數調整。本章介紹實用的調優技術和方法論。

索引選擇策略#

普通索引 vs 唯一索引#

如果業務能保證唯一性，建議使用普通索引，可以利用 change buffer 最佳化更新效能。

change buffer 機制#

flowchart TD
    A[更新請求] --> B{資料頁在記憶體？}
    B -->|是| C[直接更新記憶體]
    B -->|否| D[記錄到 change buffer]
    D --> E[下次讀取時 merge]

    style C fill:#c8e6c9
    style D fill:#fff3e0
    style E fill:#e3f2fd

-- 調整 change buffer 大小（佔 buffer pool 的比例）
SET GLOBAL innodb_change_buffer_max_size = 50;

redo log 節省隨機寫 IO，change buffer 節省隨機讀 IO。兩者互補。

最佳化器行為#

索引統計資訊#

MySQL 使用採樣統計來估算索引的基數（cardinality）：

-- 查看索引統計
SHOW INDEX FROM table_name;

-- 重新統計索引資訊
ANALYZE TABLE table_name;

統計方式：

• 採樣 N 個資料頁，計算不同值的平均數
• 乘以索引頁數得到基數估計

最佳化器選錯索引#

最佳化器可能選錯索引的原因：

1. 索引統計不準確
2. 沒有考慮回表成本
3. 排序因素的誤判

解決方法：

-- 方法一：強制使用索引
SELECT * FROM t FORCE INDEX(a) WHERE a BETWEEN 1 AND 1000;

-- 方法二：重新統計索引
ANALYZE TABLE t;

-- 方法三：改寫 SQL 引導最佳化器
-- 原語句
SELECT * FROM t WHERE a BETWEEN 1 AND 1000 ORDER BY b LIMIT 1;
-- 改寫後
SELECT * FROM t WHERE a BETWEEN 1 AND 1000 ORDER BY b,a LIMIT 1;

FORCE INDEX 不夠優雅，索引改名需要同步修改 SQL。應該作為最後手段。

flowchart TD
    A[查詢效能差?] --> B[EXPLAIN 檢查]
    B --> C{rows 估算偏差?}
    B --> D{選錯索引?}
    B --> E{排序影響?}
    C -->|是| C1[ANALYZE TABLE 重新統計]
    D -->|是| D1[改寫 SQL / FORCE INDEX]
    E -->|是| E1[改寫 ORDER BY]

    style A fill:#ffcdd2
    style C1 fill:#c8e6c9
    style D1 fill:#c8e6c9
    style E1 fill:#c8e6c9

最佳化器統計資訊與執行計畫#

前一節談了最佳化器「選錯索引」的補救；本節往下挖一層，講清楚最佳化器憑什麼做選擇——它依賴的統計資訊從哪來、為什麼會過時、過時又如何釀成災難，以及這一切如何透過 EXPLAIN 顯現。

成本式最佳化器是怎麼決策的#

InnoDB 用的是成本式最佳化器（Cost-Based Optimizer, CBO）：對每個候選執行計畫估算一個「成本」，挑成本最低的執行。成本粗略由兩部分構成：

$$ \text{Cost} \approx (\text{掃描列數} \times \text{每列 CPU 成本}) + (\text{需讀取的頁數} \times \text{每頁 IO 成本}) $$

關鍵在於：最佳化器在執行前並不知道真實列數，只能「估算」。它要在「走索引 a（少掃列但需回表）」「走索引 b」「全表掃描（無回表但掃很多列）」之間比較，而比較的全部基礎，就是對各路徑「掃描列數」的估算。

最佳化器的決策品質完全取決於列數估算的準確度。估算一旦失真，再好的索引設計也會被繞過。 這也是為什麼「明明建了索引卻走了全表掃描」是最常見的疑難——根因往往不在索引，而在統計資訊。

列數估算與採樣機制#

精確統計每個值對應多少列，代價太高，所以 InnoDB 用採樣（sampling）：隨機抽取若干索引頁，數出不同值的平均分布，再乘以總頁數推算出全表的基數（cardinality，即某索引欄位的不重複值個數）。基數越接近總列數，代表該索引「區分度」越高、越值得走。

採樣的頁數與持久化策略由下列參數控制（接續前節對比表）：

持久化統計（預設 ON）的最大價值是計畫穩定性：不會因為背景隨機採樣或重啟而讓同一條 SQL 今天走索引、明天走全表。代價是統計不會即時反映資料變化，需要靠自動或手動重算來追上。

過時統計如何釀成災難#

統計資訊是「快照」，資料卻在持續變化。當一張表短時間內大量增刪——例如批次匯入、月底歸檔、活動洗數據——統計可能嚴重滯後於真實分布，最佳化器便基於錯誤的列數估算選錯計畫：

flowchart TD
    A[大量 INSERT/DELETE] --> B[實際資料分布劇變]
    B --> C[統計資訊仍是舊快照]
    C --> D[列數估算嚴重失真]
    D --> E{最佳化器誤判}
    E --> E1[把高區分度索引估成低區分度<br/>→ 放棄索引走全表]
    E --> E2[把全表掃描成本估太低<br/>→ 掃描千萬列]
    E1 --> F[查詢從毫秒級惡化到秒級]
    E2 --> F

    style A fill:#fff3e0
    style D fill:#ffcdd2
    style F fill:#ffcdd2

典型故障場景：一條原本走索引、穩定 5ms 的查詢，在一次批次匯入後突然變成全表掃描、耗時 3s，而 SQL 與索引都一個字沒改。這類「無變更卻突然變慢」的案例，第一順位嫌疑就是過時統計。

ANALYZE TABLE：重新校準統計#

ANALYZE TABLE 強制重新採樣、刷新統計資訊（它不重建索引、不鎖寫太久，成本遠低於 OPTIMIZE TABLE）：

-- 重新採樣統計，校準最佳化器
ANALYZE TABLE orders;

-- 查看當前各索引的基數估算
SHOW INDEX FROM orders;   -- 關注 Cardinality 欄

-- 直接檢視持久化統計表
SELECT table_name, index_name, stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE table_name = 'orders';

-- 對採樣不足的大表，可調高採樣頁數後重算
ALTER TABLE orders STATS_SAMPLE_PAGES = 100;
ANALYZE TABLE orders;

把 ANALYZE TABLE 排入大批量資料變動之後的維護流程（如匯入腳本末尾、歸檔作業之後），比事後救火可靠得多。對讀多寫少且分布穩定的表則無須頻繁執行。

與 EXPLAIN 的關聯：如何驗證估算準不準#

統計資訊的好壞，最終都顯現在 EXPLAIN 的 rows 欄（最佳化器估算要掃的列數）。要判斷估算是否失真，把它和真實值比對：

-- 看最佳化器的估算（rows、key、type）
EXPLAIN SELECT * FROM orders WHERE status = 'PENDING';

-- MySQL 8.0+：EXPLAIN ANALYZE 同時給出「估算 vs 實際」
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PENDING';
-- 輸出形如 (rows=12) (actual rows=98000 ...)，落差巨大即統計失真

-- 看最佳化器各候選計畫的成本明細
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE status = 'PENDING';

判讀準則：

• EXPLAIN 的 rows 與 EXPLAIN ANALYZE 的 actual rows 落差達數量級 ➡️ 統計過時，先 ANALYZE TABLE。
• ANALYZE 後 EXPLAIN 的 type 從 ALL（全表）回到 ref/range（走索引）➡️ 問題確認並解決。
• 若校準後最佳化器仍固執選錯，才退而使用前節的 FORCE INDEX 或改寫 SQL 作為最後手段。

rows 永遠是估算值，不要把它當成精確的掃描列數來判斷查詢快慢；它的價值在於和實際值比對來偵測統計失真。在 8.0 之前無 EXPLAIN ANALYZE，可用慢查詢日誌中 Rows_examined 對照 EXPLAIN 的 rows 達到類似效果。

字串索引最佳化#

前綴索引#

-- 完整索引
ALTER TABLE t ADD INDEX idx_email(email);

-- 前綴索引（取前 6 個字元）
ALTER TABLE t ADD INDEX idx_email_6(email(6));

確定前綴長度：

-- 計算完整欄位的區分度
SELECT COUNT(DISTINCT email) AS L FROM t;

-- 計算不同前綴長度的區分度
SELECT
  COUNT(DISTINCT LEFT(email, 4)) AS L4,
  COUNT(DISTINCT LEFT(email, 5)) AS L5,
  COUNT(DISTINCT LEFT(email, 6)) AS L6,
  COUNT(DISTINCT LEFT(email, 7)) AS L7
FROM t;

-- 選擇區分度 >= L * 95% 的最短前綴

前綴索引的代價：

• 增加掃描次數
• 無法使用覆蓋索引（覆蓋索引機制詳見「02 索引原理」章節）

特殊字串處理#

對於區分度集中在後面的字串（如身分證號），有兩種方案：

方案一：倒序儲存

-- 儲存時倒序
INSERT INTO t(id_card) VALUES(REVERSE('110108199001011234'));

-- 查詢時倒序
SELECT * FROM t WHERE id_card = REVERSE('110108199001011234');

方案二：hash 欄位

-- 加入 hash 欄位
ALTER TABLE t ADD id_card_crc INT UNSIGNED, ADD INDEX(id_card_crc);

-- 插入時計算 hash
INSERT INTO t(id_card, id_card_crc)
VALUES('110108199001011234', CRC32('110108199001011234'));

-- 查詢時使用 hash + 原值
SELECT * FROM t
WHERE id_card_crc = CRC32('110108199001011234')
  AND id_card = '110108199001011234';

這兩種方案都不支援範圍查詢，只能用於等值查詢。

flowchart TD
    A[字串欄位需要索引] --> B{需要範圍查詢?}
    B -->|是| C{欄位很長?}
    C -->|是| C1[前綴索引]
    C -->|否| C2[完整索引]
    B -->|否| D{區分度在尾部?}
    D -->|是| D1[倒序儲存或 Hash 欄位]
    D -->|否| D2[前綴索引]

    style A fill:#e3f2fd
    style C1 fill:#c8e6c9
    style C2 fill:#c8e6c9
    style D1 fill:#fff3e0
    style D2 fill:#c8e6c9

查詢最佳化#

EXPLAIN 關鍵欄位#

EXPLAIN SELECT * FROM t WHERE id = 1;

常見最佳化技巧#

1. 避免全表掃描

-- 差：無法使用索引
SELECT * FROM t WHERE YEAR(create_time) = 2023;

-- 好：可以使用索引
SELECT * FROM t WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

2. 避免回表（覆蓋索引）

優先使用覆蓋索引以避免回表（完整說明見「02 索引原理」章節）：

-- 需要回表
SELECT * FROM t WHERE name = 'zhang';

-- 覆蓋索引，不需回表（前提：有 (name) 或 (name, ...) 索引）
SELECT id, name FROM t WHERE name = 'zhang';

3. 利用最左前綴

-- 聯合索引 (a, b, c)

-- 可以使用索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 無法使用索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

4. 避免 SELECT *

-- 差：傳輸大量不需要的資料
SELECT * FROM t WHERE id = 1;

-- 好：只取需要的欄位
SELECT name, age FROM t WHERE id = 1;

連接池與連接管理#

長連接記憶體問題#

長連接會導致 MySQL 記憶體持續增長，因為臨時記憶體綁定在連接物件上。

解決方案：

-- MySQL 5.7+ 重置連接資源
mysql_reset_connection();

-- 或定期斷開重連

連接參數#

-- 空閒連接超時（預設 8 小時）
SET GLOBAL wait_timeout = 28800;

-- 最大連接數
SET GLOBAL max_connections = 500;

參數調優#

記憶體相關#

-- InnoDB 緩衝池大小（建議實體記憶體的 50-80%）
innodb_buffer_pool_size = 8G

-- 緩衝池執行個體數（大於 1G 時建議多執行個體）
innodb_buffer_pool_instances = 8

-- 日誌緩衝區
innodb_log_buffer_size = 64M

IO 相關#

-- redo log 檔案大小
innodb_log_file_size = 1G

-- redo log 檔案數量
innodb_log_files_in_group = 4

-- 刷盤策略
innodb_flush_log_at_trx_commit = 1  -- 每次提交刷盤（最安全）
sync_binlog = 1                      -- binlog 每次提交刷盤

並行相關#

-- 並行執行緒數
innodb_thread_concurrency = 0  -- 0 表示不限制

-- IO 讀寫執行緒數
innodb_read_io_threads = 4
innodb_write_io_threads = 4

慢查詢分析#

啟用慢查詢日誌#

-- 開啟慢查詢日誌
SET GLOBAL slow_query_log = ON;

-- 設定閾值（秒）
SET GLOBAL long_query_time = 1;

-- 記錄沒有使用索引的查詢
SET GLOBAL log_queries_not_using_indexes = ON;

分析慢查詢#

# 使用 mysqldumpslow 分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# -s t: 按查詢時間排序
# -t 10: 顯示前 10 條

常見慢查詢原因#

flowchart TD
    A[啟用 slow_query_log] --> B[mysqldumpslow 識別慢查詢]
    B --> C[EXPLAIN 分析]
    C --> D{type=ALL?}
    C --> E{有索引但沒用上?}
    C --> F{rows 很大?}
    C --> G{Lock_time 高?}
    D -->|是| D1[加入合適索引]
    E -->|是| E1[檢查 SQL 寫法]
    F -->|是| F1[分頁 / 分區 / 歸檔]
    G -->|是| G1[最佳化事務 / 減少鎖範圍]

    style A fill:#e3f2fd
    style B fill:#e3f2fd
    style C fill:#fff3e0
    style D1 fill:#c8e6c9
    style E1 fill:#c8e6c9
    style F1 fill:#c8e6c9
    style G1 fill:#c8e6c9

本章小結#

效能調優的核心思路：減少磁碟 IO（索引、快取）；減少網路傳輸（只查需要的欄位）；減少鎖競爭（最佳化事務）；利用好快取（buffer pool、change buffer）。