MySQL性能优化实战：慢查询分析与索引调优全流程

一、概述

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 的不记录，过滤简单查询

动态修改（无需重启）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_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 \
  --limit 10 \
  --order-by Query_time:sum \
  > /tmp/slow_report.txt

# 只分析最近 1 小时的慢查询
pt-query-digest /var/log/mysql/slow.log \
  --since "1h" \
  --limit 20

# 过滤特定数据库
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 \
  --history h=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 10:00:00 to 2024-01-15 11:00:00
# 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 核心字段含义

EXPLAIN SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20\G

输出示例：

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 实际执行查询并返回真实耗时
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 12345 AND status = '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 * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1ANDstatus = 'paid';
SELECT * FROM orders WHERE user_id = 1ANDstatus = 'paid'AND created_at > '2024-01-01';

-- 不能用索引（跳过了 user_id）
SELECT * FROM orders WHEREstatus = 'paid';
SELECT * FROM orders WHEREstatus = 'paid'AND created_at > '2024-01-01';

-- 范围查询后的列无法用索引过滤
-- 以下查询中 status 列无法通过索引过滤
SELECT * FROM orders WHERE user_id = 1AND created_at > '2024-01-01'ANDstatus = 'paid';
-- 建议改为：WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'
-- 把等值条件放前面，范围条件放最后

2.3.2 覆盖索引

-- 原始查询，需要回表
SELECT id, amount, created_at FROM orders WHERE user_id = 1 AND status = 'paid';

-- 创建覆盖索引，包含查询所需的所有列
ALTER TABLE orders ADD INDEX idx_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 是否生效
SET optimizer_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 / (
        SELECT variable_value
        FROM performance_schema.global_status
        WHERE variable_name = 'Innodb_buffer_pool_read_requests'
      )
    )) * 100, 2
  ) AS hit_rate_pct
FROM performance_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_reads';

-- 命中率低于 95% 时需要考虑增大 Buffer Pool
-- 查看 Buffer Pool 使用详情
SELECT pool_id, pool_size, free_buffers, database_pages, hit_rate
FROM information_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
FROM information_schema.PROCESSLIST
WHERE command != '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 \
  --limit 5

输出发现最慢的 SQL：

SELECT o.id, o.order_no, o.amount, o.status, o.created_at,
       u.name, u.phone
FROM orders o
JOINusers u ON o.user_id = u.id
WHERE o.merchant_id = 1001
AND o.status IN ('pending', 'paid')
AND o.created_at BETWEEN'2024-01-01'AND'2024-01-31'
ORDERBY o.created_at DESC
LIMIT20OFFSET0;
-- 平均执行时间 6.8s，扫描行数 320 万

3.1.2 分析执行计划

EXPLAIN SELECT o.id, o.order_no, o.amount, o.status, o.created_at,
               u.name, u.phone
FROM orders o
JOINusers u ON o.user_id = u.id
WHERE o.merchant_id = 1001
AND o.status IN ('pending', 'paid')
AND o.created_at BETWEEN'2024-01-01'AND'2024-01-31'
ORDERBY o.created_at DESC
LIMIT20\G

结果：type: ALL，rows: 50000000，Extra: Using where; Using filesort。全表扫描 + 文件排序，问题明确。

3.1.3 索引设计决策

-- 分析列的选择性
SELECT
COUNT(DISTINCT merchant_id) / COUNT(*) AS merchant_selectivity,
COUNT(DISTINCTstatus) / COUNT(*) AS status_selectivity,
COUNT(DISTINCTDATE(created_at)) / COUNT(*) AS date_selectivity
FROM orders;
-- merchant_selectivity: 0.0002（低，1万个商户/5000万行）
-- status_selectivity: 0.00000012（极低，只有几个状态值）
-- date_selectivity: 0.0006（低）

-- 设计联合索引：等值条件在前，范围条件在后
-- merchant_id（等值）+ status（IN，等值）+ created_at（范围+排序）
ALTERTABLE orders
ADDINDEX idx_merchant_status_created (merchant_id, status, created_at);

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

3.1.4 验证优化效果

-- 强制使用新索引验证
EXPLAINSELECT o.id, o.order_no, o.amount, o.status, o.created_at,
               u.name, u.phone
FROM orders o FORCEINDEX (idx_merchant_status_created)
JOINusers u ON o.user_id = u.id
WHERE o.merchant_id = 1001
AND o.status IN ('pending', 'paid')
AND o.created_at BETWEEN'2024-01-01'AND'2024-01-31'
ORDERBY o.created_at DESC
LIMIT20\G
-- type: range, rows: 1250, Extra: Using index condition
-- 扫描行数从 5000 万降到 1250，响应时间降到 15ms

3.2 直方图统计（MySQL 8.0+）

-- 对低选择性列创建直方图，帮助优化器做更准确的行数估算
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, merchant_id WITH 256 BUCKETS;

-- 查看直方图信息
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE table_name = 'orders'\G

-- 直方图适合：不适合建索引但需要准确统计的列
-- 不适合：高选择性列（直接建索引更好）、频繁更新的列（直方图不自动更新）

四、最佳实践和注意事项

4.1 最佳实践

4.1.1 索引设计原则

区分度优先：联合索引中，区分度高的列放前面（等值条件优先于范围条件）
控制索引数量：单表索引不超过 5 个，写多读少的表更要克制
定期清理无用索引：

-- 查找从未使用的索引（需要运行足够长时间后查询）
SELECT object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY object_schema, object_name;

4.1.2 SQL 编写规范

-- 避免在索引列上使用函数
-- 错误：无法使用 created_at 上的索引
SELECT * FROM orders WHEREYEAR(created_at) = 2024;
-- 正确：
SELECT * FROM orders WHERE created_at >= '2024-01-01'AND created_at < '2025-01-01';

-- 避免隐式类型转换
-- 错误：phone 是 VARCHAR，传入整数会导致全表扫描
SELECT * FROMusersWHERE phone = 13800138000;
-- 正确：
SELECT * FROMusersWHERE phone = '13800138000';

-- 大分页问题：OFFSET 越大越慢
-- 错误：OFFSET 100000 需要扫描 100020 行
SELECT * FROM orders ORDERBYidLIMIT20OFFSET100000;
-- 正确：游标分页
SELECT * FROM orders WHEREid > 100000ORDERBYidLIMIT20;

4.1.3 定期维护

-- 更新统计信息（数据变化超过 10% 后执行）
ANALYZETABLE orders;

-- 重建索引（索引碎片率高时）
-- 查看碎片率
SELECT table_name,
       ROUND(data_free / (data_length + index_length) * 100, 2) AS frag_pct
FROM information_schema.TABLES
WHERE table_schema = 'production_db'
AND data_free > 0
ORDERBY frag_pct DESC;

-- 在线重建（8.0+ 支持，不锁表）
ALTERTABLE orders ENGINE=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) AS sql_snippet
FROM information_schema.PROCESSLIST
WHERE command = 'Query'
ANDtime > 5
ORDERBYtimeDESC;

-- 查看锁等待情况
SELECT
  r.trx_id AS waiting_trx_id,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query AS waiting_query,
  b.trx_id AS blocking_trx_id,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query AS blocking_query
FROM information_schema.INNODB_TRX r
JOIN information_schema.INNODB_TRX b
ON r.trx_wait_started ISNOTNULL
AND b.trx_id = (
    SELECT blocking_trx_id
    FROM performance_schema.data_lock_waits
    WHERE requesting_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 --limit 10

# 查看表索引
SHOW INDEX FROM orders\G

# 更新统计信息
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	无法利用索引排序，需要额外排序操作