效能調優#

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

索引選擇策略#

普通索引 vs 唯一索引#

操作普通索引唯一索引
查詢找到後繼續掃描（效能差異微乎其微）找到後立即返回
更新可用 change buffer不能用 change buffer

如果業務能保證唯一性，建議使用普通索引，可以利用 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

適用場景

  • 寫多讀少的業務（如日誌系統）：寫入時無需讀取資料頁，收益最大
  • 普通索引 / 非唯一索引更新：可延遲合併，不需即時檢查唯一性

不適用場景

  • 唯一索引：必須讀取判斷唯一性
  • 寫後立即讀：會觸發 merge，沒有收益
-- 調整 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 個資料頁，計算不同值的平均數
  • 乘以索引頁數得到基數估計
參數innodb_stats_persistent=ONinnodb_stats_persistent=OFF
儲存位置持久化到磁碟只存記憶體
採樣頁數 N208
觸發更新閾值 M1/101/16

最佳化器選錯索引#

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

  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

字串索引最佳化#

前綴索引#

-- 完整索引
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';
方案優點缺點
倒序儲存不需額外欄位每次需呼叫 REVERSE
hash 欄位查詢效能穩定需要額外欄位和計算

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

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;
欄位說明關注點
type存取類型const > eq_ref > ref > range > index > ALL
key實際使用的索引是否符合預期
rows預估掃描列數越小越好
Extra額外資訊Using index（覆蓋索引）, Using filesort（需排序）

常見最佳化技巧#

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

本章小結#

主題要點
索引選擇業務保證唯一性時，用普通索引
change buffer寫多讀少場景，提升普通索引更新效能
最佳化器統計不準時用 ANALYZE TABLE
字串索引前綴索引節省空間，注意區分度
查詢最佳化避免全表掃描，利用覆蓋索引
慢查詢開啟日誌，定期分析最佳化

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

Backward 高可用架構 Redis 資料結構 Forward