如何在 MySQL 中监控和优化慢 SQL？

在 MySQL 中，慢 SQL（Slow SQL）是导致数据库性能下降、CPU 飙升、连接数耗尽的主要原因。监控和优化慢 SQL 是一个闭环过程：开启监控 -> 定位问题 -> 分析原因 -> 实施优化 -> 验证效果。

以下是详细的操作指南：

一、如何监控慢 SQL

1. 开启慢查询日志 (Slow Query Log)

这是最基础也是最核心的监控手段。

临时开启（重启失效）：

-- 开启慢查询日志SETGLOBALslow_query_log='ON';-- 设置阈值：执行时间超过 2 秒的 SQL 会被记录SETGLOBALlong_query_time=2;-- 设置记录不执行任何扫描行的 SQL（可选，通常用于调试）SETGLOBALlog_queries_not_using_indexes=ON;

永久开启（修改配置文件my.cnf或my.ini）：

[mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log # 日志路径 long_query_time = 2 # 阈值（秒） log_queries_not_using_indexes = 1 # 记录未使用索引的查询 min_examined_row_limit = 1000 # 只记录扫描行数超过 1000 的查询

修改配置后需重启 MySQL 服务。

2. 分析慢查询日志

MySQL 自带工具mysqldumpslow可以快速分析日志。

常用命令：

# 按返回行数排序，取前 10 条mysqldumpslow-sr-t10/var/log/mysql/slow.log# 按查询时间排序，取前 10 条mysqldumpslow-st-t10/var/log/mysql/slow.log# 包含具体 SQL 语句（-a 表示不抽象数字，-n 表示抽象数字）mysqldumpslow-st-t10-a-n10/var/log/mysql/slow.log

参数说明：

• -s: 排序方式 (t=时间,r=返回行数,c=调用次数)
• -t: 返回前 N 条
• -a: 不将数字抽象为N，字符串抽象为S（方便看具体值）
• -n: 抽象数字

3. 使用性能视图 (Performance Schema)

MySQL 5.7+ 和 8.0+ 提供了更实时的监控视图sys.schema_statements或performance_schema.events_statements_summary_by_digest。

查看当前最慢的 SQL（实时）：

SELECTDIGEST_TEXT,COUNT_STAR,SUM_TIMER_WAIT/1000000000000AStotal_latency_sec,AVG_TIMER_WAIT/1000000000000ASavg_latency_sec,MAX_TIMER_WAIT/1000000000000ASmax_latency_secFROMperformance_schema.events_statements_summary_by_digestORDERBYSUM_TIMER_WAITDESCLIMIT10;

使用sys库（更友好）：

-- 查看最耗时的 SQLSELECT*FROMsys.statement_analysisORDERBYavg_latencyDESCLIMIT10;-- 查看全表扫描的 SQLSELECT*FROMsys.schema_tables_with_full_table_scans;

4. 第三方工具 (推荐生产环境)

• Percona Toolkit (pt-query-digest): 业界最强的慢日志分析工具，能生成详细的 HTML 报告，分析 SQL 指纹、并发度、锁等待等。

  pt-query-digest /var/log/mysql/slow.log>report.txt

• Prometheus + Grafana + MySQL Exporter: 实时监控慢 SQL 数量趋势。
• PMMP (Percona Monitoring and Management): 提供可视化的慢 SQL 分析界面。

二、如何分析慢 SQL

找到慢 SQL 后，不要盲目优化，先使用EXPLAIN分析执行计划。

1. 使用 EXPLAIN

EXPLAINSELECT*FROMordersWHEREuser_id=100ANDstatus='PAID';

关键字段解读：

• type: 访问类型。性能从好到坏：system>const>eq_ref>ref>range>index>ALL。
  • 目标：至少达到range，避免ALL（全表扫描）。
• key: 实际使用的索引。如果是NULL，说明没用到索引。
• rows: 预估扫描的行数。数值越小越好。
• Extra:
  • Using index: 覆盖索引，性能极佳。
  • Using where: 使用了 WHERE 过滤。
  • Using temporary: 使用了临时表（通常出现在GROUP BY或ORDER BY），性能差。
  • Using filesort: 文件排序（无法利用索引排序），性能差。
  • Using join buffer: 使用了连接缓冲，通常意味着 Join 效率低。

2. 使用EXPLAIN ANALYZE(MySQL 8.0+)

MySQL 8.0 引入了EXPLAIN ANALYZE，它会实际执行SQL 并返回真实的执行统计信息（如实际扫描行数、实际耗时），比EXPLAIN更准确。

EXPLAINANALYZESELECT*FROMordersWHEREuser_id=100;

三、常见优化策略

根据EXPLAIN的结果，采取针对性的优化措施。

1. 索引优化 (最常见)

• 添加缺失的索引：针对WHERE、JOIN、ORDER BY、GROUP BY的字段。
• 遵循最左前缀法则：联合索引(a, b, c)，查询必须从a开始，否则索引失效。
  • 有效：WHERE a=1 AND b=2
  • 无效：WHERE b=2 AND c=3(跳过 a)
• 避免索引失效：
  • 不要在索引列上做计算或函数操作（如WHERE YEAR(create_time) = 2023应改为范围查询）。
  • 避免隐式类型转换（如字符串字段不加引号WHERE phone = 1380000）。
  • 避免LIKE '%abc'（左模糊），LIKE 'abc%'可以使用索引。
  • OR连接的条件如果有一方没索引，会导致全表扫描。
• 覆盖索引：尽量让查询的字段都在索引树上，避免回表（SELECT id, name而不是SELECT *）。

2. SQL 语句重写

• 避免SELECT *：只查需要的字段，减少网络传输和内存消耗。
• 优化LIMIT深度分页：使用“延迟关联”或“游标法”（参考上一问）。
• 优化UNION：如果不需要去重，使用UNION ALL。
• 小表驱动大表：在JOIN时，确保驱动表（小表）的数据量尽可能小。
• 拆分复杂查询：将一个大 SQL 拆成多个小 SQL，在应用层组装，减少数据库锁竞争和解析开销。

3. 表结构与架构优化

• 字段类型优化：使用最小的数据类型（如TINYINT代替INT，VARCHAR长度适中）。
• 垂直分表：将大字段（如TEXT,BLOB）或不常用字段拆分到扩展表。
• 水平分表：单表数据量超过 2000 万 -5000 万时，考虑分库分表（Sharding）。
• 读写分离：将报表、分析类查询路由到从库。

4. 配置参数调优

• innodb_buffer_pool_size：设置为物理内存的 70%-80%，确保热点数据在内存中。
• sort_buffer_size/join_buffer_size：适当调大，但注意每个连接都会分配，避免内存溢出。
• query_cache：MySQL 8.0 已移除，5.7 中建议关闭（在高并发写场景下，缓存锁会导致性能下降）。

四、优化实战案例

场景：查询某用户最近 10 条订单，执行很慢。

-- 原始 SQLSELECT*FROMordersWHEREuser_id=10001ORDERBYcreate_timeDESCLIMIT10;

分析：

1. EXPLAIN显示type: ALL，key: NULL，Extra: Using filesort。
2. 原因：user_id有索引，但ORDER BY create_time导致索引失效，需要全表扫描并排序。

优化方案：
建立联合索引(user_id, create_time)。

ALTERTABLEordersADDINDEXidx_user_time(user_id,create_time);

优化后：

1. EXPLAIN显示type: ref，key: idx_user_time，Extra: Using index condition。
2. 数据库直接利用索引排序，无需回表排序，速度提升百倍。

五、总结与最佳实践

1. 开启慢日志：生产环境必须开启，long_query_time建议设为 1 秒或更低。
2. 定期巡检：每天/每周使用pt-query-digest分析日志，找出 Top 10 慢 SQL。
3. 开发规范：
   • 禁止SELECT *。
   • 禁止在索引列上做运算。
   • 禁止深度分页。
   • 大事务拆小。
4. 测试环境验证：优化后的 SQL 必须在测试环境用生产数据量级验证效果。
5. 监控告警：配置监控，当慢 SQL 数量突增时立即报警。

通过“监控 -> 分析 -> 优化”的闭环，可以显著提升 MySQL 的查询性能和系统稳定性。