在 MySQL 中慢 SQLSlow SQL是导致数据库性能下降、CPU 飙升、连接数耗尽的主要原因。监控和优化慢 SQL 是一个闭环过程开启监控 - 定位问题 - 分析原因 - 实施优化 - 验证效果。以下是详细的操作指南一、如何监控慢 SQL1. 开启慢查询日志 (Slow Query Log)这是最基础也是最核心的监控手段。临时开启重启失效-- 开启慢查询日志SETGLOBALslow_query_logON;-- 设置阈值执行时间超过 2 秒的 SQL 会被记录SETGLOBALlong_query_time2;-- 设置记录不执行任何扫描行的 SQL可选通常用于调试SETGLOBALlog_queries_not_using_indexesON;永久开启修改配置文件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.logreport.txtPrometheus Grafana MySQL Exporter: 实时监控慢 SQL 数量趋势。PMMP (Percona Monitoring and Management): 提供可视化的慢 SQL 分析界面。二、如何分析慢 SQL找到慢 SQL 后不要盲目优化先使用EXPLAIN分析执行计划。1. 使用 EXPLAINEXPLAINSELECT*FROMordersWHEREuser_id100ANDstatusPAID;关键字段解读type: 访问类型。性能从好到坏systemconsteq_refrefrangeindexALL。目标至少达到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_id100;三、常见优化策略根据EXPLAIN的结果采取针对性的优化措施。1. 索引优化 (最常见)添加缺失的索引针对WHERE、JOIN、ORDER BY、GROUP BY的字段。遵循最左前缀法则联合索引(a, b, c)查询必须从a开始否则索引失效。有效WHERE a1 AND b2无效WHERE b2 AND c3(跳过 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代替INTVARCHAR长度适中。垂直分表将大字段如TEXT,BLOB或不常用字段拆分到扩展表。水平分表单表数据量超过 2000 万 -5000 万时考虑分库分表Sharding。读写分离将报表、分析类查询路由到从库。4. 配置参数调优innodb_buffer_pool_size设置为物理内存的 70%-80%确保热点数据在内存中。sort_buffer_size/join_buffer_size适当调大但注意每个连接都会分配避免内存溢出。query_cacheMySQL 8.0 已移除5.7 中建议关闭在高并发写场景下缓存锁会导致性能下降。四、优化实战案例场景查询某用户最近 10 条订单执行很慢。-- 原始 SQLSELECT*FROMordersWHEREuser_id10001ORDERBYcreate_timeDESCLIMIT10;分析EXPLAIN显示type: ALLkey: NULLExtra: Using filesort。原因user_id有索引但ORDER BY create_time导致索引失效需要全表扫描并排序。优化方案建立联合索引(user_id, create_time)。ALTERTABLEordersADDINDEXidx_user_time(user_id,create_time);优化后EXPLAIN显示type: refkey: idx_user_timeExtra: Using index condition。数据库直接利用索引排序无需回表排序速度提升百倍。五、总结与最佳实践开启慢日志生产环境必须开启long_query_time建议设为 1 秒或更低。定期巡检每天/每周使用pt-query-digest分析日志找出 Top 10 慢 SQL。开发规范禁止SELECT *。禁止在索引列上做运算。禁止深度分页。大事务拆小。测试环境验证优化后的 SQL 必须在测试环境用生产数据量级验证效果。监控告警配置监控当慢 SQL 数量突增时立即报警。通过“监控 - 分析 - 优化”的闭环可以显著提升 MySQL 的查询性能和系统稳定性。
如何在 MySQL 中监控和优化慢 SQL？
在 MySQL 中慢 SQLSlow SQL是导致数据库性能下降、CPU 飙升、连接数耗尽的主要原因。监控和优化慢 SQL 是一个闭环过程开启监控 - 定位问题 - 分析原因 - 实施优化 - 验证效果。以下是详细的操作指南一、如何监控慢 SQL1. 开启慢查询日志 (Slow Query Log)这是最基础也是最核心的监控手段。临时开启重启失效-- 开启慢查询日志SETGLOBALslow_query_logON;-- 设置阈值执行时间超过 2 秒的 SQL 会被记录SETGLOBALlong_query_time2;-- 设置记录不执行任何扫描行的 SQL可选通常用于调试SETGLOBALlog_queries_not_using_indexesON;永久开启修改配置文件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.logreport.txtPrometheus Grafana MySQL Exporter: 实时监控慢 SQL 数量趋势。PMMP (Percona Monitoring and Management): 提供可视化的慢 SQL 分析界面。二、如何分析慢 SQL找到慢 SQL 后不要盲目优化先使用EXPLAIN分析执行计划。1. 使用 EXPLAINEXPLAINSELECT*FROMordersWHEREuser_id100ANDstatusPAID;关键字段解读type: 访问类型。性能从好到坏systemconsteq_refrefrangeindexALL。目标至少达到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_id100;三、常见优化策略根据EXPLAIN的结果采取针对性的优化措施。1. 索引优化 (最常见)添加缺失的索引针对WHERE、JOIN、ORDER BY、GROUP BY的字段。遵循最左前缀法则联合索引(a, b, c)查询必须从a开始否则索引失效。有效WHERE a1 AND b2无效WHERE b2 AND c3(跳过 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代替INTVARCHAR长度适中。垂直分表将大字段如TEXT,BLOB或不常用字段拆分到扩展表。水平分表单表数据量超过 2000 万 -5000 万时考虑分库分表Sharding。读写分离将报表、分析类查询路由到从库。4. 配置参数调优innodb_buffer_pool_size设置为物理内存的 70%-80%确保热点数据在内存中。sort_buffer_size/join_buffer_size适当调大但注意每个连接都会分配避免内存溢出。query_cacheMySQL 8.0 已移除5.7 中建议关闭在高并发写场景下缓存锁会导致性能下降。四、优化实战案例场景查询某用户最近 10 条订单执行很慢。-- 原始 SQLSELECT*FROMordersWHEREuser_id10001ORDERBYcreate_timeDESCLIMIT10;分析EXPLAIN显示type: ALLkey: NULLExtra: Using filesort。原因user_id有索引但ORDER BY create_time导致索引失效需要全表扫描并排序。优化方案建立联合索引(user_id, create_time)。ALTERTABLEordersADDINDEXidx_user_time(user_id,create_time);优化后EXPLAIN显示type: refkey: idx_user_timeExtra: Using index condition。数据库直接利用索引排序无需回表排序速度提升百倍。五、总结与最佳实践开启慢日志生产环境必须开启long_query_time建议设为 1 秒或更低。定期巡检每天/每周使用pt-query-digest分析日志找出 Top 10 慢 SQL。开发规范禁止SELECT *。禁止在索引列上做运算。禁止深度分页。大事务拆小。测试环境验证优化后的 SQL 必须在测试环境用生产数据量级验证效果。监控告警配置监控当慢 SQL 数量突增时立即报警。通过“监控 - 分析 - 优化”的闭环可以显著提升 MySQL 的查询性能和系统稳定性。
