效能調優#

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。應該作為最後手段。

字符串索引最佳化#

前綴索引#

-- 完整索引
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% 的最短前綴

前綴索引的代價：

  • 增加掃描次數
  • 無法使用覆蓋索引

特殊字符串處理#

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

方案一：倒序存儲

-- 存儲時倒序
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 字段查詢效能穩定需要額外字段和計算

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

查詢最佳化#

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. 避免回表

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

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

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 條

常見慢查詢原因#

原因現象解決方案
無索引type=ALL添加合適索引
索引失效有索引但沒用上檢查 SQL 寫法
數據量大rows 很大分頁、分區、歸檔
鎖等待Lock_time 高最佳化事務、減少鎖範圍

本章小結#

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

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

Backward 高可用架構 架構設計 Forward