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

MySQL性能调优的系统化思路

MySQL性能调优是数据库运维中最核心也最常被误用的技能。很多工程师遇到慢查询就盲目加索引，结果索引膨胀导致写入性能下降，查询反而更慢。正确的做法是先量化问题，再精准优化。本文从慢查询分析入手，讲解索引设计原则、执行计划解读和SQL查询优化的实战方法。

慢查询日志：问题定位的第一步

慢查询日志（Slow Query Log）是MySQL性能调优的起点。开启并配置合理的阈值：

# my.cnf配置
[mysqld]
slow_query_log = ON
long_query_time = 0.5          # 超过500ms记录
log_queries_not_using_indexes = ON  # 未使用索引的查询也记录
min_examined_row_limit = 100   # 扫描行数低于100不记录，过滤低影响查询

# 运行时动态开启（无需重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;

使用pt-query-digest分析慢查询日志，按总执行时间排序找出Top SQL：

# 分析慢日志Top 20
pt-query-digest --limit 20 /var/lib/mysql/slow.log

# 输出中关键指标：
# Query ID   - 查询指纹
# Exec time  - 总执行时间
# Rows exam  - 总扫描行数
# Rows sent  - 总返回行数
# Ratio      - Rows exam / Rows sent（越高说明索引效率越低）

数据库高可用架构中，建议在从库上开启慢日志分析，避免对主库造成额外IO开销。

执行计划：读懂EXPLAIN的每个字段

拿到慢SQL后，EXPLAIN是判断优化方向的关键工具。以下是必须关注的字段：

EXPLAIN SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.status = 'paid' AND o.created_at > '2026-01-01';

type字段（访问类型，从优到差排序）：

system > const > eq_ref > ref > range > index > ALL

目标：至少达到ref级别，range是较理想状态，index表示全索引扫描（通常需优化），ALL是全表扫描（必须优化）。

Extra字段中的危险信号：

– Using filesort：排序未使用索引，需额外排序操作
– Using temporary：使用了临时表，常见于GROUP BY无索引场景
– Using where; Using index：覆盖索引，这是理想状态
– Using join buffer：连接缓冲区不足，需增大join_buffer_size或添加索引

索引设计原则：选择性、覆盖性与最左前缀

SQL查询优化的核心是索引设计，三个原则决定索引的有效性：

选择性原则：索引列的基数（distinct值数量）占总行数的比例越高，索引过滤效果越好。性别字段只有2个值，选择性极低，单独建索引几乎无效。联合索引中，高选择性列放在最前面。

覆盖索引原则：查询所需的所有列都包含在索引中，无需回表读取数据行：

-- 原始查询：需要回表
SELECT user_id, order_no, amount FROM orders WHERE status = 'paid';

-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_status_cover (status, user_id, order_no, amount);

-- EXPLAIN中Extra显示 Using where; Using index 即为覆盖索引生效

最左前缀原则：联合索引(a, b, c)可以支持a、(a,b)、(a,b,c)三种查询条件组合，但无法支持(b,c)或单独c的查询。分库分表方案中，分片键通常放在联合索引首位，保证路由效率和查询性能。

常见反模式与优化案例

反模式1：索引列上使用函数

-- 无法使用索引
SELECT * FROM orders WHERE DATE(created_at) = '2026-08-07';

-- 优化：改为范围查询
SELECT * FROM orders 
WHERE created_at >= '2026-08-07' AND created_at < '2026-08-08';

反模式2：隐式类型转换

-- user_id是VARCHAR类型，传入整数导致隐式转换，索引失效
SELECT * FROM users WHERE user_id = 12345;

-- 优化：确保类型一致
SELECT * FROM users WHERE user_id = '12345';

反模式3：ORDER BY与GROUP BY索引不匹配

-- WHERE用status索引，ORDER BY用created_at，产生filesort
SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- 优化：创建匹配WHERE+ORDER BY的联合索引
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at DESC);

数据备份恢复策略中，建议在非高峰期执行ANALYZE TABLE更新索引统计信息，确保优化器选择正确的执行计划。国产数据库在MySQL兼容模式下的执行计划可能存在差异，迁移前务必用实际业务SQL验证执行计划一致性。