MySQL性能调优实战：从慢查询定位到索引优化的全流程诊断指南

MySQL性能调优从哪里入手

MySQL性能问题最常见的表现是查询慢、连接数飙升、CPU占用高。面对这些问题，直接改参数往往治标不治本。正确的调优路径是：先定位瓶颈（慢查询），再分析根因（执行计划），最后针对性优化（索引/SQL/配置）。本文按这个诊断流程给出完整的操作方法。

慢查询定位：找到真正的瓶颈

不是所有慢查询都一样严重。先开启慢查询日志，再通过分析工具找到高影响度的SQL。

1. 配置慢查询日志

-- my.cnf 配置
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

-- 动态修改（不重启）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;

2. 分析慢查询日志

# 使用mysqldumpslow快速分析
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# -s t: 按查询时间排序
# -t 20: 显示前20条

# 使用pt-query-digest深度分析
pt-query-digest /var/log/mysql/slow.log > slow_report.txt

# 输出中关注以下指标：
# - Query Time: 95%   慢查询的95分位耗时
# - Rows Examined: 扫描行数（远大于返回行数说明索引不佳）
# - Rows Sent: 实际返回行数
# - 执行次数：高频慢查询优先优化

3. Performance Schema实时监控

-- 查看当前正在执行的SQL及其耗时
SELECT * FROM performance_schema.events_statements_current
WHERE TIMER_WAIT/1000000000000 > 0.5
ORDER BY TIMER_START DESC;

-- 查看TOP 10耗时SQL
SELECT DIGEST_TEXT, COUNT_STAR, 
       ROUND(SUM_TIMER_WAIT/1000000000000, 2) AS total_time_sec,
       ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_time_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

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

拿到慢SQL后，EXPLAIN是分析根因的核心工具。

1. 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'
ORDER BY o.created_at DESC
LIMIT 20;

重点看以下字段：
– type: 访问类型。ALL（全表扫描）→ index（索引扫描）→ range（范围扫描）→ ref（非唯一索引查找）→ const（唯一索引查找）。目标是range以上。
– key: 实际使用的索引。NULL表示没用索引。
– rows: 预估扫描行数。与实际返回行数的差距越大，索引效率越低。
– Extra: 额外信息。Using filesort（额外排序）和Using temporary（临时表）是性能杀手。
– filtered: 过滤比例。100%表示扫描的行全部满足条件，低于10%说明索引选择性差。

2. 常见执行计划问题与修复

问题与修复对照：
– 全表扫描：type=ALL, key=NULL → 添加合适索引
– 索引失效：key=index但rows很大 → 检查索引列是否被函数/隐式转换破坏
– 额外排序：Extra=Using filesort → 调整索引顺序匹配ORDER BY
– 临时表：Extra=Using temporary → 优化GROUP BY或添加覆盖索引
– 回表过多：rows大但filtered低 → 使用覆盖索引避免回表

索引优化：从设计到维护的完整方法

索引是MySQL性能调优最有效的手段，但错误的索引比没有索引更糟。

1. 索引设计原则

-- 原则1: 高选择性列在前
-- 选择性 = DISTINCT(col) / COUNT(*) 
SELECT 
  COUNT(DISTINCT status) / COUNT(*) AS status_selectivity,
  COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity,
  COUNT(DISTINCT created_at) / COUNT(*) AS time_selectivity
FROM orders;
-- status: 0.001 (差)  user_id: 0.3 (中)  created_at: 0.95 (好)

-- 原则2: 联合索引遵循最左前缀
CREATE INDEX idx_user_status_created 
ON orders(user_id, status, created_at);

-- 原则3: 覆盖索引避免回表
CREATE INDEX idx_user_covering 
ON orders(user_id, status, amount);

2. 索引失效的常见场景

-- 场景1: 索引列使用函数
-- 错误: 索引失效
SELECT * FROM orders WHERE DATE(created_at) = '2026-07-28';
-- 正确: 索引可用
SELECT * FROM orders WHERE created_at >= '2026-07-28' AND created_at < '2026-07-29';

-- 场景2: 隐式类型转换
-- 错误: user_id是INT但传了字符串
SELECT * FROM orders WHERE user_id = '123';

-- 场景3: LIKE以通配符开头
-- 错误: 索引失效
SELECT * FROM users WHERE name LIKE '%zhang%';
-- 正确: 索引可用（前缀匹配）
SELECT * FROM users WHERE name LIKE 'zhang%';

-- 场景4: OR条件含无索引列
-- 错误: phone列无索引，整个查询索引失效
SELECT * FROM users WHERE email = ? OR phone = ?;
-- 正确: 使用UNION
SELECT * FROM users WHERE email = ?
UNION
SELECT * FROM users WHERE phone = ?;

3. 索引维护

-- 查看索引使用统计
SELECT object_schema, object_name, index_name,
       count_star AS access_count
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'your_db'
ORDER BY count_star DESC;

-- 查找未使用的索引
SELECT s.table_schema, s.table_name, s.index_name, s.column_name
FROM statistics s
LEFT JOIN performance_schema.table_io_waits_summary_by_index_usage i
  ON s.table_schema = i.object_schema 
  AND s.table_name = i.object_name 
  AND s.index_name = i.index_name
WHERE s.table_schema = 'your_db'
  AND s.index_name != 'PRIMARY'
  AND i.count_star IS NULL;

-- 重建碎片化索引
ALTER TABLE orders ENGINE=InnoDB;

MySQL参数调优：配置不是越大越好

1. InnoDB Buffer Pool

-- Buffer Pool大小：物理内存的60-70%
SET GLOBAL innodb_buffer_pool_size = 10737418240; -- 10GB

-- 多实例减少锁争用
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 监控Buffer Pool命中率
SELECT 
  1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) AS hit_ratio;
-- 目标: > 0.99

2. 连接数与线程缓存

SET GLOBAL max_connections = 500;
SET GLOBAL thread_cache_size = 50;
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

-- 监控连接使用
SHOW STATUS LIKE 'Threads%';

3. 日志与刷盘策略

SET GLOBAL innodb_log_file_size = 1073741824; -- 1GB
SET GLOBAL innodb_log_files_in_group = 2;

-- 刷盘策略（1最安全，2性能更好）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 100;

SQL重写：不改索引也能提速

-- 1. 子查询改JOIN
-- 慢: 子查询产生临时表
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level > 3);
-- 快: JOIN利用索引
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.id WHERE u.level > 3;

-- 2. 分页优化
-- 慢: 大偏移量
SELECT * FROM orders ORDER BY id LIMIT 100000, 20;
-- 快: 延迟关联
SELECT o.* FROM orders o 
JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) tmp 
ON o.id = tmp.id;

-- 3. 批量操作替代循环
INSERT INTO orders (user_id, amount, status) VALUES
(1, 100, 'PAID'), (2, 200, 'PAID'), (3, 150, 'PAID');

-- 4. FORCE INDEX强制使用正确索引
SELECT * FROM orders FORCE INDEX(idx_user_status_created) 
WHERE user_id = 123 AND status = 'PAID';

MySQL性能调优没有银弹，关键在于建立从监控→定位→分析→优化的标准流程。先通过慢查询日志和Performance Schema找到瓶颈SQL，再通过EXPLAIN分析执行计划确定根因，最后通过索引设计、SQL重写、参数调优三层递进修复。每次优化后回归压测验证效果，避免优化一个指标恶化另一个。