EXPLAIN：展示查詢(xún)執(zhí)行計(jì)劃，判斷是否走索引、掃描行數(shù)等關(guān)鍵信息

索引優(yōu)化：覆蓋索引、聯(lián)合索引、索引下推（ICP）是三個(gè)核心手段

Buffer Pool 調(diào)優(yōu)：InnoDB 緩沖池命中率直接影響 I/O 壓力

1.3 適用場(chǎng)景

業(yè)務(wù)響應(yīng)時(shí)間突然變慢，需要快速定位根因

新功能上線(xiàn)前的 SQL 審查

定期的數(shù)據(jù)庫(kù)健康檢查

數(shù)據(jù)量增長(zhǎng)后的索引重新評(píng)估

1.4 環(huán)境要求

二、詳細(xì)步驟

2.1 慢查詢(xún)?nèi)罩九渲?/p>

2.1.1 開(kāi)啟慢查詢(xún)?nèi)罩?/p>

# /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     # 超過(guò) 1 秒記錄，生產(chǎn)初期可設(shè) 0.5
log_queries_not_using_indexes = OFF  # 不建議開(kāi)，會(huì)產(chǎn)生大量噪音
log_slow_extra = ON     # 8.0.14+ 支持，記錄更多上下文信息
min_examined_row_limit = 100 # 掃描行數(shù)少于 100 的不記錄，過(guò)濾簡(jiǎn)單查詢(xún)

動(dòng)態(tài)修改（無(wú)需重啟）：

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

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

# 只分析最近 1 小時(shí)的慢查詢(xún)
pt-query-digest /var/log/mysql/slow.log 
 --since"1h"
 --limit20

# 過(guò)濾特定數(shù)據(jù)庫(kù)
pt-query-digest /var/log/mysql/slow.log 
 --filter'$event->{db} eq "production_db"'

# 輸出到 MySQL 表，便于歷史對(duì)比
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

關(guān)注指標(biāo)：Rows examine / Rows sent比值，超過(guò) 100 說(shuō)明索引效率低。

2.2 EXPLAIN 執(zhí)行計(jì)劃解讀

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       ← 關(guān)鍵字段
possible_keys: idx_status_created,idx_created_at
key: idx_status_created ← 實(shí)際使用的索引
key_len: 10
ref: NULL
rows: 1250        ← 預(yù)估掃描行數(shù)
filtered: 100.00
Extra: Using index condition; Using filesort ← 注意 filesort

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

Extra 字段關(guān)鍵信息：

Using index：覆蓋索引，無(wú)需回表，性能最優(yōu)

Using index condition：索引下推（ICP），在索引層過(guò)濾，減少回表次數(shù)

Using filesort：需要額外排序，如果數(shù)據(jù)量大會(huì)很慢

Using temporary：使用臨時(shí)表，GROUP BY 或 ORDER BY 時(shí)出現(xiàn)，需要重點(diǎn)關(guān)注

Using where：在 Server 層過(guò)濾，索引沒(méi)有完全覆蓋 WHERE 條件

2.2.2 EXPLAIN ANALYZE（8.0.18+）

-- EXPLAIN ANALYZE 實(shí)際執(zhí)行查詢(xún)并返回真實(shí)耗時(shí)
EXPLAINANALYZE
SELECT*FROMordersWHEREuser_id =12345ANDstatus='paid'G

-- 輸出包含實(shí)際執(zhí)行時(shí)間和行數(shù)
-- -> 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（預(yù)估）差距大時(shí)，說(shuō)明統(tǒng)計(jì)信息過(guò)期，需要ANALYZE TABLE。

2.3 索引設(shè)計(jì)原則

2.3.1 聯(lián)合索引最左前綴

-- 假設(shè)有聯(lián)合索引 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';

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

-- 范圍查詢(xún)后的列無(wú)法用索引過(guò)濾
-- 以下查詢(xún)中 status 列無(wú)法通過(guò)索引過(guò)濾
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 覆蓋索引

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

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

-- EXPLAIN 中 Extra 顯示 "Using index"，無(wú)需回表
-- 對(duì)于高頻查詢(xún)，覆蓋索引能將響應(yīng)時(shí)間從毫秒級(jí)降到微秒級(jí)

覆蓋索引的代價(jià)是索引體積增大，寫(xiě)入時(shí)維護(hù)成本上升。對(duì)于寫(xiě)多讀少的表，不要濫用。

2.3.3 索引下推（ICP）

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

-- 沒(méi)有 ICP 時(shí)：
-- 1. 存儲(chǔ)引擎用 age > 20 找到所有記錄
-- 2. 回表取完整行
-- 3. Server 層用 name LIKE 'Zhang%' 過(guò)濾

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

-- 驗(yàn)證 ICP 是否生效
SEToptimizer_switch ='index_condition_pushdown=on'; -- 默認(rèn)開(kāi)啟

2.4 InnoDB Buffer Pool 調(diào)優(yōu)

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# Buffer Pool 大?。何锢韮?nèi)存的 50-75%
# 16GB 內(nèi)存的服務(wù)器，專(zhuān)用 MySQL 實(shí)例設(shè) 10-12GB
innodb_buffer_pool_size = 10G

# Buffer Pool 實(shí)例數(shù)：每個(gè)實(shí)例獨(dú)立鎖，減少競(jìng)爭(zhēng)
# 建議每個(gè)實(shí)例 1-2GB，10GB 設(shè) 8 個(gè)實(shí)例
innodb_buffer_pool_instances = 8

# 預(yù)熱：重啟后自動(dòng)加載上次的熱數(shù)據(jù)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25    # 只 dump 最熱的 25%

# 監(jiān)控 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% 時(shí)需要考慮增大 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       # 根據(jù)業(yè)務(wù)并發(fā)量設(shè)置，不要無(wú)限調(diào)大
thread_cache_size = 50       # 緩存線(xiàn)程數(shù)，減少線(xiàn)程創(chuàng)建開(kāi)銷(xiāo)
wait_timeout = 600         # 空閑連接超時(shí)（秒）
interactive_timeout = 600
max_connect_errors = 100      # 連接錯(cuò)誤次數(shù)上限，超過(guò)則封鎖 IP

# 連接隊(duì)列
back_log = 128           # TCP 連接隊(duì)列長(zhǎng)度，高并發(fā)時(shí)適當(dāng)增大

-- 監(jiān)控連接狀態(tài)
SHOWSTATUSLIKE'Threads_%';
-- Threads_connected: 當(dāng)前連接數(shù)
-- Threads_running: 活躍線(xiàn)程數(shù)（真正在執(zhí)行 SQL）
-- Threads_cached: 緩存中的線(xiàn)程數(shù)

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

三、示例代碼和配置

3.1 生產(chǎn)案例：從慢查詢(xún)到索引優(yōu)化完整流程

案例背景

電商訂單表，數(shù)據(jù)量 5000 萬(wàn)行，某天下午業(yè)務(wù)反饋訂單列表頁(yè)響應(yīng)時(shí)間從 200ms 漲到 8s。

3.1.1 定位慢查詢(xún)

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

輸出發(fā)現(xiàn)最慢的 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;
-- 平均執(zhí)行時(shí)間 6.8s，掃描行數(shù) 320 萬(wàn)

3.1.2 分析執(zhí)行計(jì)劃

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

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

3.1.3 索引設(shè)計(jì)決策

-- 分析列的選擇性
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萬(wàn)個(gè)商戶(hù)/5000萬(wàn)行）
-- status_selectivity: 0.00000012（極低，只有幾個(gè)狀態(tài)值）
-- date_selectivity: 0.0006（低）

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

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

3.1.4 驗(yàn)證優(yōu)化效果

-- 強(qiáng)制使用新索引驗(yàn)證
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
-- 掃描行數(shù)從 5000 萬(wàn)降到 1250，響應(yīng)時(shí)間降到 15ms

3.2 直方圖統(tǒng)計(jì)（MySQL 8.0+）

-- 對(duì)低選擇性列創(chuàng)建直方圖，幫助優(yōu)化器做更準(zhǔn)確的行數(shù)估算
ANALYZETABLEordersUPDATEHISTOGRAMONstatus, merchant_idWITH256BUCKETS;

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

-- 直方圖適合：不適合建索引但需要準(zhǔn)確統(tǒng)計(jì)的列
-- 不適合：高選擇性列（直接建索引更好）、頻繁更新的列（直方圖不自動(dòng)更新）

四、最佳實(shí)踐和注意事項(xiàng)

4.1 最佳實(shí)踐

4.1.1 索引設(shè)計(jì)原則

區(qū)分度優(yōu)先：聯(lián)合索引中，區(qū)分度高的列放前面（等值條件優(yōu)先于范圍條件）

控制索引數(shù)量：?jiǎn)伪硭饕怀^(guò) 5 個(gè)，寫(xiě)多讀少的表更要克制

定期清理無(wú)用索引：

-- 查找從未使用的索引（需要運(yùn)行足夠長(zhǎng)時(shí)間后查詢(xún)）
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 編寫(xiě)規(guī)范

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

4.1.3 定期維護(hù)

-- 更新統(tǒng)計(jì)信息（數(shù)據(jù)變化超過(guò) 10% 后執(zhí)行）
ANALYZETABLEorders;

-- 重建索引（索引碎片率高時(shí)）
-- 查看碎片率
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;

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

4.2 注意事項(xiàng)

4.2.1 常見(jiàn)誤區(qū)

警告：以下操作在生產(chǎn)環(huán)境中可能造成嚴(yán)重性能問(wèn)題。

SELECT *會(huì)導(dǎo)致覆蓋索引失效，始終明確列出需要的字段

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

FORCE INDEX只用于臨時(shí)調(diào)試，不要提交到生產(chǎn)代碼

4.2.2 常見(jiàn)錯(cuò)誤

五、故障排查和監(jiān)控

5.1 實(shí)時(shí)性能診斷

-- 查看當(dāng)前正在執(zhí)行的慢 SQL（超過(guò) 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 性能監(jiān)控指標(biāo)

5.2.1 關(guān)鍵指標(biāo)

# 實(shí)時(shí)監(jiān)控 MySQL 狀態(tài)
mysqladmin -u root -p extended-status -i 1 | grep -E"Questions|Slow|Threads_running|InnoDB_buffer"

5.2.2 監(jiān)控指標(biāo)說(shuō)明

5.3 備份與恢復(fù)

5.3.1 慢查詢(xún)?nèi)罩据嗈D(zhuǎn)

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

六、總結(jié)

6.1 技術(shù)要點(diǎn)回顧

慢查詢(xún)定位：pt-query-digest 按總耗時(shí)排序，Rows examine / Rows sent > 100是優(yōu)化信號(hào)

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

索引設(shè)計(jì)：等值條件在前、范圍條件在后、覆蓋索引消除回表

Buffer Pool：命中率低于 95% 必須擴(kuò)容，重啟后預(yù)熱避免冷啟動(dòng)性能抖動(dòng)

6.2 進(jìn)階學(xué)習(xí)方向

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

查詢(xún)重寫(xiě)插件：ProxySQL 的 query_rewrite 功能，在不改代碼的情況下修改 SQL

分區(qū)表：超過(guò) 1 億行的表考慮按時(shí)間分區(qū)，配合分區(qū)裁剪減少掃描范圍

6.3 參考資料

MySQL 8.4 優(yōu)化器文檔

Percona Toolkit 文檔

Use The Index, Luke- 索引原理最佳學(xué)習(xí)資源

附錄

A. 命令速查表

# 開(kāi)啟慢查詢(xún)?nèi)罩?mysql -e"SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=1;"

# 分析慢查詢(xún)
pt-query-digest /var/log/mysql/slow.log --limit10

# 查看表索引
SHOW INDEX FROM ordersG

# 更新統(tǒng)計(jì)信息
ANALYZE TABLE orders;

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

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

B. 配置參數(shù)詳解

C. 術(shù)語(yǔ)表