一、概述

1.1 背景介紹

MySQL 性能問題在生產環境中的表現通常是漸進式的：業務量增長、數據量膨脹，某天突然發現 P99 響應時間從 50ms 漲到 2s。慢查詢是最常見的根因，而索引設計不合理又是慢查詢的主要來源。

MySQL 8.4 LTS 在查詢優化器、直方圖統計、索引跳躍掃描等方面有明顯改進，但核心的分析方法論沒有變化：先定位慢查詢，再用 EXPLAIN 分析執行計劃，最后針對性地調整索引或 SQL。

1.2 技術特點

慢查詢日志：記錄執行時間超過閾值的 SQL，是性能分析的起點

EXPLAIN：展示查詢執行計劃，判斷是否走索引、掃描行數等關鍵信息

索引優化：覆蓋索引、聯合索引、索引下推（ICP）是三個核心手段

Buffer Pool 調優：InnoDB 緩沖池命中率直接影響 I/O 壓力

1.3 適用場景

業務響應時間突然變慢，需要快速定位根因

新功能上線前的 SQL 審查

定期的數據庫健康檢查

數據量增長后的索引重新評估

1.4 環境要求

組件	版本要求	說明
MySQL	8.4.x LTS	優化器在 8.4 有改進
pt-query-digest	3.5+	Percona Toolkit 組件
操作系統	Linux	pt-query-digest 依賴 Perl
權限	PROCESS, SELECT	分析慢查詢和執行計劃所需

二、詳細步驟

2.1 慢查詢日志配置

2.1.1 開啟慢查詢日志

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1     # 超過 1 秒記錄，生產初期可設 0.5
log_queries_not_using_indexes = OFF  # 不建議開，會產生大量噪音
log_slow_extra = ON     # 8.0.14+ 支持，記錄更多上下文信息
min_examined_row_limit = 100 # 掃描行數少于 100 的不記錄，過濾簡單查詢

動態修改（無需重啟）：

SETGLOBALslow_query_log =ON;
SETGLOBALlong_query_time =1;
SETGLOBALlog_slow_extra =ON;

2.1.2 pt-query-digest 分析

# 安裝 Percona Toolkit
apt install percona-toolkit # Ubuntu
# 或
yum install percona-toolkit # CentOS

# 基礎分析：按總執行時間排序，輸出 Top 10 慢查詢
pt-query-digest /var/log/mysql/slow.log 
 --limit10 
 --order-by Query_time:sum 
 > /tmp/slow_report.txt

# 只分析最近 1 小時的慢查詢
pt-query-digest /var/log/mysql/slow.log 
 --since"1h"
 --limit20

# 過濾特定數據庫
pt-query-digest /var/log/mysql/slow.log 
 --filter'$event->{db} eq "production_db"'

# 輸出到 MySQL 表，便于歷史對比
pt-query-digest /var/log/mysql/slow.log 
 --review h=127.0.0.1,D=percona,t=query_review 
 --historyh=127.0.0.1,D=percona,t=query_history 
 --no-report

pt-query-digest 輸出解讀：

# Query 1: 0.50 QPS, 2.50x concurrency, ID 0xABC123 at byte 12345
# This item is included in the report because it matches --limit.
# Scores: V/M = 1.23
# Time range: 2024-01-15 1000 to 2024-01-15 1100
# Attribute  pct  total   min   max   avg   95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count     15  1800
# Exec time   42  1800s  0.5s   5s   1s  2.1s  0.8s  0.9s
# Lock time   2   90s   0s  0.1s  0.05s  0.08s  0.02s  0.04s
# Rows sent   8  14400    1   20    8    15    4    8
# Rows examine 65 585000   100  1000   325   800   200   300

關注指標：Rows examine / Rows sent比值，超過 100 說明索引效率低。

2.2 EXPLAIN 執行計劃解讀

2.2.1 核心字段含義

EXPLAINSELECTo.id, o.amount, u.name
FROMorders o
JOINusersuONo.user_id = u.id
WHEREo.status ='pending'
ANDo.created_at >'2024-01-01'
ORDERBYo.created_atDESC
LIMIT20G

輸出示例：

id: 1
select_type: SIMPLE
table: o
partitions: NULL
type: range       ← 關鍵字段
possible_keys: idx_status_created,idx_created_at
key: idx_status_created ← 實際使用的索引
key_len: 10
ref: NULL
rows: 1250        ← 預估掃描行數
filtered: 100.00
Extra: Using index condition; Using filesort ← 注意 filesort

type 字段（從好到差排序）：

type	含義	性能
system	表只有一行	最優
const	主鍵或唯一索引等值查詢	極好
eq_ref	JOIN 時使用主鍵/唯一索引	很好
ref	非唯一索引等值查詢	好
range	索引范圍掃描	可接受
index	全索引掃描	較差
ALL	全表掃描	最差，必須優化

Extra 字段關鍵信息：

Using index：覆蓋索引，無需回表，性能最優

Using index condition：索引下推（ICP），在索引層過濾，減少回表次數

Using filesort：需要額外排序，如果數據量大會很慢

Using temporary：使用臨時表，GROUP BY 或 ORDER BY 時出現，需要重點關注

Using where：在 Server 層過濾，索引沒有完全覆蓋 WHERE 條件

2.2.2 EXPLAIN ANALYZE（8.0.18+）

-- EXPLAIN ANALYZE 實際執行查詢并返回真實耗時
EXPLAINANALYZE
SELECT*FROMordersWHEREuser_id =12345ANDstatus='paid'G

-- 輸出包含實際執行時間和行數
-- -> Filter: (orders.status = 'paid') (cost=5.25 rows=3) (actual time=0.045..0.089 rows=2 loops=1)
--   -> Index lookup on orders using idx_user_id (user_id=12345)
--    (cost=3.50 rows=15) (actual time=0.038..0.075 rows=15 loops=1)

actual rows與rows（預估）差距大時，說明統計信息過期，需要ANALYZE TABLE。

2.3 索引設計原則

2.3.1 聯合索引最左前綴

-- 假設有聯合索引 idx_user_status_created (user_id, status, created_at)

-- 能用索引（最左前綴匹配）
SELECT*FROMordersWHEREuser_id =1;
SELECT*FROMordersWHEREuser_id =1ANDstatus='paid';
SELECT*FROMordersWHEREuser_id =1ANDstatus='paid'ANDcreated_at >'2024-01-01';

-- 不能用索引（跳過了 user_id）
SELECT*FROMordersWHEREstatus='paid';
SELECT*FROMordersWHEREstatus='paid'ANDcreated_at >'2024-01-01';

-- 范圍查詢后的列無法用索引過濾
-- 以下查詢中 status 列無法通過索引過濾
SELECT*FROMordersWHEREuser_id =1ANDcreated_at >'2024-01-01'ANDstatus='paid';
-- 建議改為：WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'
-- 把等值條件放前面，范圍條件放最后

2.3.2 覆蓋索引

-- 原始查詢，需要回表
SELECTid, amount, created_atFROMordersWHEREuser_id =1ANDstatus='paid';

-- 創建覆蓋索引，包含查詢所需的所有列
ALTERTABLEordersADDINDEXidx_covering (user_id,status, amount, created_at);

-- EXPLAIN 中 Extra 顯示 "Using index"，無需回表
-- 對于高頻查詢，覆蓋索引能將響應時間從毫秒級降到微秒級

覆蓋索引的代價是索引體積增大，寫入時維護成本上升。對于寫多讀少的表，不要濫用。

2.3.3 索引下推（ICP）

-- 聯合索引 idx_age_name (age, name)
-- 查詢：WHERE age > 20 AND name LIKE 'Zhang%'

-- 沒有 ICP 時：
-- 1. 存儲引擎用 age > 20 找到所有記錄
-- 2. 回表取完整行
-- 3. Server 層用 name LIKE 'Zhang%' 過濾

-- 有 ICP 時（MySQL 5.6+ 默認開啟）：
-- 1. 存儲引擎用 age > 20 找到索引記錄
-- 2. 在索引層直接檢查 name LIKE 'Zhang%'
-- 3. 只有滿足條件的記錄才回表
-- EXPLAIN Extra 顯示 "Using index condition"

-- 驗證 ICP 是否生效
SEToptimizer_switch ='index_condition_pushdown=on'; -- 默認開啟

2.4 InnoDB Buffer Pool 調優

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Buffer Pool 大小：物理內存的 50-75%
# 16GB 內存的服務器，專用 MySQL 實例設 10-12GB
innodb_buffer_pool_size = 10G

# Buffer Pool 實例數：每個實例獨立鎖，減少競爭
# 建議每個實例 1-2GB，10GB 設 8 個實例
innodb_buffer_pool_instances = 8

# 預熱：重啟后自動加載上次的熱數據
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25    # 只 dump 最熱的 25%

# 監控 Buffer Pool 命中率
# 命中率 = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

-- 查看 Buffer Pool 命中率
SELECT
FORMAT(
  (1- (
   variable_value / (
   SELECTvariable_value
   FROMperformance_schema.global_status
   WHEREvariable_name ='Innodb_buffer_pool_read_requests'
   )
  )) *100,2
 )AShit_rate_pct
FROMperformance_schema.global_status
WHEREvariable_name ='Innodb_buffer_pool_reads';

-- 命中率低于 95% 時需要考慮增大 Buffer Pool
-- 查看 Buffer Pool 使用詳情
SELECTpool_id, pool_size, free_buffers, database_pages, hit_rate
FROMinformation_schema.INNODB_BUFFER_POOL_STATS;

2.5 連接池配置

[mysqld]
max_connections = 500       # 根據業務并發量設置，不要無限調大
thread_cache_size = 50       # 緩存線程數，減少線程創建開銷
wait_timeout = 600         # 空閑連接超時（秒）
interactive_timeout = 600
max_connect_errors = 100      # 連接錯誤次數上限，超過則封鎖 IP

# 連接隊列
back_log = 128           # TCP 連接隊列長度，高并發時適當增大

-- 監控連接狀態
SHOWSTATUSLIKE'Threads_%';
-- Threads_connected: 當前連接數
-- Threads_running: 活躍線程數（真正在執行 SQL）
-- Threads_cached: 緩存中的線程數

-- 如果 Threads_running 持續接近 max_connections，說明有連接積壓
-- 查看當前連接詳情
SELECTuser, host, db, command,time, state, info
FROMinformation_schema.PROCESSLIST
WHEREcommand !='Sleep'
ORDERBYtimeDESC
LIMIT20;

三、示例代碼和配置

3.1 生產案例：從慢查詢到索引優化完整流程

案例背景

電商訂單表，數據量 5000 萬行，某天下午業務反饋訂單列表頁響應時間從 200ms 漲到 8s。

3.1.1 定位慢查詢

# 分析最近 30 分鐘的慢查詢
pt-query-digest /var/log/mysql/slow.log 
 --since"30m"
 --order-by Query_time:sum 
 --limit5

輸出發現最慢的 SQL：

SELECTo.id, o.order_no, o.amount, o.status, o.created_at,
   u.name, u.phone
FROMorders o
JOINusersuONo.user_id = u.id
WHEREo.merchant_id =1001
ANDo.statusIN('pending','paid')
ANDo.created_atBETWEEN'2024-01-01'AND'2024-01-31'
ORDERBYo.created_atDESC
LIMIT20OFFSET0;
-- 平均執行時間 6.8s，掃描行數 320 萬

3.1.2 分析執行計劃

EXPLAINSELECTo.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROMorders o
JOINusersuONo.user_id = u.id
WHEREo.merchant_id =1001
ANDo.statusIN('pending','paid')
ANDo.created_atBETWEEN'2024-01-01'AND'2024-01-31'
ORDERBYo.created_atDESC
LIMIT20G

結果：type: ALL，rows: 50000000，Extra: Using where; Using filesort。全表掃描 + 文件排序，問題明確。

3.1.3 索引設計決策

-- 分析列的選擇性
SELECT
COUNT(DISTINCTmerchant_id) /COUNT(*)ASmerchant_selectivity,
COUNT(DISTINCTstatus) /COUNT(*)ASstatus_selectivity,
COUNT(DISTINCTDATE(created_at)) /COUNT(*)ASdate_selectivity
FROMorders;
-- merchant_selectivity: 0.0002（低，1萬個商戶/5000萬行）
-- status_selectivity: 0.00000012（極低，只有幾個狀態值）
-- date_selectivity: 0.0006（低）

-- 設計聯合索引：等值條件在前，范圍條件在后
-- merchant_id（等值）+ status（IN，等值）+ created_at（范圍+排序）
ALTERTABLEorders
ADDINDEXidx_merchant_status_created (merchant_id,status, created_at);

-- 如果需要覆蓋索引（避免回表），加上 SELECT 的列
-- 但 name、phone 在 users 表，JOIN 無法避免
-- 只能覆蓋 orders 表的列
ALTERTABLEorders
ADDINDEXidx_merchant_status_created_cover
  (merchant_id,status, created_at,id, order_no, amount, user_id);

3.1.4 驗證優化效果

-- 強制使用新索引驗證
EXPLAINSELECTo.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROMorders oFORCEINDEX(idx_merchant_status_created)
JOINusersuONo.user_id = u.id
WHEREo.merchant_id =1001
ANDo.statusIN('pending','paid')
ANDo.created_atBETWEEN'2024-01-01'AND'2024-01-31'
ORDERBYo.created_atDESC
LIMIT20G
-- type: range, rows: 1250, Extra: Using index condition
-- 掃描行數從 5000 萬降到 1250，響應時間降到 15ms

3.2 直方圖統計（MySQL 8.0+）

-- 對低選擇性列創建直方圖，幫助優化器做更準確的行數估算
ANALYZETABLEordersUPDATEHISTOGRAMONstatus, merchant_idWITH256BUCKETS;

-- 查看直方圖信息
SELECT*FROMinformation_schema.COLUMN_STATISTICS
WHEREtable_name ='orders'G

-- 直方圖適合：不適合建索引但需要準確統計的列
-- 不適合：高選擇性列（直接建索引更好）、頻繁更新的列（直方圖不自動更新）

四、最佳實踐和注意事項

4.1 最佳實踐

4.1.1 索引設計原則

區分度優先：聯合索引中，區分度高的列放前面（等值條件優先于范圍條件）

控制索引數量：單表索引不超過 5 個，寫多讀少的表更要克制

定期清理無用索引：

-- 查找從未使用的索引（需要運行足夠長時間后查詢）
SELECTobject_schema, object_name, index_name
FROMperformance_schema.table_io_waits_summary_by_index_usage
WHEREindex_nameISNOTNULL
ANDcount_star =0
ANDobject_schemaNOTIN('mysql','performance_schema','information_schema')
ORDERBYobject_schema, object_name;

4.1.2 SQL 編寫規范

-- 避免在索引列上使用函數
-- 錯誤：無法使用 created_at 上的索引
SELECT*FROMordersWHEREYEAR(created_at) =2024;
-- 正確：
SELECT*FROMordersWHEREcreated_at >='2024-01-01'ANDcreated_at 100000ORDERBYidLIMIT20;

4.1.3 定期維護

-- 更新統計信息（數據變化超過 10% 后執行）
ANALYZETABLEorders;

-- 重建索引（索引碎片率高時）
-- 查看碎片率
SELECTtable_name,
   ROUND(data_free / (data_length + index_length) *100,2)ASfrag_pct
FROMinformation_schema.TABLES
WHEREtable_schema ='production_db'
ANDdata_free >0
ORDERBYfrag_pctDESC;

-- 在線重建（8.0+ 支持，不鎖表）
ALTERTABLEordersENGINE=InnoDB, ALGORITHM=INPLACE,LOCK=NONE;

4.2 注意事項

4.2.1 常見誤區

警告：以下操作在生產環境中可能造成嚴重性能問題。

SELECT *會導致覆蓋索引失效，始終明確列出需要的字段

在大表上直接ALTER TABLE ADD INDEX會鎖表，使用pt-online-schema-change或gh-ost

FORCE INDEX只用于臨時調試，不要提交到生產代碼

4.2.2 常見錯誤

錯誤現象	原因分析	解決方案
索引存在但不走	統計信息過期，優化器誤判	ANALYZE TABLE 更新統計信息
加索引后反而變慢	索引選擇性太低，回表開銷大于全表掃描	刪除該索引，考慮覆蓋索引
ORDER BY 走 filesort	排序列不在索引中，或索引順序不匹配	調整聯合索引列順序
JOIN 性能差	被驅動表關聯列無索引	在被驅動表的關聯列上建索引
深分頁極慢	OFFSET 大導致掃描大量行后丟棄	改用游標分頁或延遲關聯

五、故障排查和監控

5.1 實時性能診斷

-- 查看當前正在執行的慢 SQL（超過 5 秒）
SELECTid,user, host, db,time, state,LEFT(info,200)ASsql_snippet
FROMinformation_schema.PROCESSLIST
WHEREcommand ='Query'
ANDtime>5
ORDERBYtimeDESC;

-- 查看鎖等待情況
SELECT
 r.trx_idASwaiting_trx_id,
 r.trx_mysql_thread_idASwaiting_thread,
 r.trx_queryASwaiting_query,
 b.trx_idASblocking_trx_id,
 b.trx_mysql_thread_idASblocking_thread,
 b.trx_queryASblocking_query
FROMinformation_schema.INNODB_TRX r
JOINinformation_schema.INNODB_TRX b
ONr.trx_wait_startedISNOTNULL
ANDb.trx_id = (
 SELECTblocking_trx_id
 FROMperformance_schema.data_lock_waits
 WHERErequesting_engine_transaction_id = r.trx_id
 LIMIT1
 );

5.2 性能監控指標

5.2.1 關鍵指標

# 實時監控 MySQL 狀態
mysqladmin -u root -p extended-status -i 1 | grep -E"Questions|Slow|Threads_running|InnoDB_buffer"

5.2.2 監控指標說明

指標名稱	正常范圍	告警閾值	說明
Buffer Pool 命中率	> 99%	< 95%	低于 95% 需增大 buffer pool
Slow queries/s	< 1	> 10	每秒慢查詢數
Threads_running	< 20	> 50	活躍線程數，高說明有積壓
InnoDB row lock waits	< 5/s	> 50/s	行鎖等待頻率
Questions/s	業務基線	基線 2x	QPS 突增可能是慢查詢堆積

5.3 備份與恢復

5.3.1 慢查詢日志輪轉

# /etc/logrotate.d/mysql-slow
/var/log/mysql/slow.log {
  daily
  rotate 7
  compress
  missingok
  notifempty
  postrotate
   # 通知 MySQL 重新打開日志文件
    mysql -u root -p"${MYSQL_ROOT_PASS}"-e"FLUSH SLOW LOGS;"
  endscript
}

六、總結

6.1 技術要點回顧

慢查詢定位：pt-query-digest 按總耗時排序，Rows examine / Rows sent > 100是優化信號

EXPLAIN 解讀：type 從 ALL 優化到 range 或 ref，消除 Using filesort 和 Using temporary

索引設計：等值條件在前、范圍條件在后、覆蓋索引消除回表

Buffer Pool：命中率低于 95% 必須擴容，重啟后預熱避免冷啟動性能抖動

6.2 進階學習方向

Performance Schema 深度使用：比 SHOW STATUS 更細粒度的性能數據，可以定位到具體 SQL 的 I/O 等待

查詢重寫插件：ProxySQL 的 query_rewrite 功能，在不改代碼的情況下修改 SQL

分區表：超過 1 億行的表考慮按時間分區，配合分區裁剪減少掃描范圍

6.3 參考資料

MySQL 8.4 優化器文檔

Percona Toolkit 文檔

Use The Index, Luke- 索引原理最佳學習資源

附錄

A. 命令速查表

# 開啟慢查詢日志
mysql -e"SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=1;"

# 分析慢查詢
pt-query-digest /var/log/mysql/slow.log --limit10

# 查看表索引
SHOW INDEX FROM ordersG

# 更新統計信息
ANALYZE TABLE orders;

# 查看 Buffer Pool 命中率
mysql -e"SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"

# 在線加索引（大表使用）
pt-online-schema-change --alter"ADD INDEX idx_name (col)"D=db,t=table --execute

B. 配置參數詳解

參數	推薦值	說明
innodb_buffer_pool_size	物理內存 60-70%	最重要的性能參數
innodb_buffer_pool_instances	buffer_pool_size/1GB	減少鎖競爭
long_query_time	0.5-1	慢查詢閾值（秒）
max_connections	300-500	根據并發量設置
thread_cache_size	50-100	線程緩存，減少創建開銷

C. 術語表

術語	英文	解釋
覆蓋索引	Covering Index	索引包含查詢所需全部列，無需回表
回表	Table Lookup	通過索引找到主鍵后再查完整行數據
索引下推	Index Condition Pushdown	在存儲引擎層過濾索引條件，減少回表
直方圖	Histogram	列值分布統計，幫助優化器估算行數
文件排序	Filesort	無法利用索引排序，需要額外排序操作