1. 线上慢SQL问题概述慢SQL是数据库性能问题的常见表现也是DBA日常工作中最常处理的故障类型之一。当数据库响应变慢时约70%的情况都与低效SQL语句有关。这些慢查询会占用大量数据库资源导致连接堆积、CPU飙升最终影响整个系统的稳定性。在实际生产环境中慢SQL的危害主要体现在三个方面直接影响用户体验页面加载时间延长接口响应变慢消耗过多数据库资源一个慢查询可能占用整个实例80%以上的CPU引发连锁反应单个慢查询可能导致连接池耗尽影响其他正常业务2. 慢SQL发现与监控2.1 慢查询日志配置MySQL中开启慢查询日志是最基础的监控手段配置参数如下-- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置慢查询阈值单位秒 SET GLOBAL long_query_time 1; -- 记录未使用索引的查询 SET GLOBAL log_queries_not_using_indexes ON; -- 慢查询日志文件位置 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;提示生产环境建议将long_query_time设置为0.5-1秒可根据业务特点调整2.2 性能监控工具除了慢查询日志还可以使用以下工具进行实时监控Performance SchemaMySQL内置的性能监控工具-- 查看执行时间最长的SQL SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;sys Schema提供更友好的视图-- 查看全表扫描的查询 SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;第三方监控平台如NineData、PMM等提供的可视化监控3. 慢SQL分析流程3.1 问题SQL定位当收到数据库性能告警时可按以下步骤快速定位问题SQL使用SHOW PROCESSLIST查看当前执行的SQL通过top命令确认服务器CPU、IO使用情况检查数据库监控仪表盘识别异常指标分析慢查询日志找出执行时间长的SQL3.2 EXPLAIN执行计划分析对定位到的慢SQL使用EXPLAIN分析执行计划是关键步骤EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status pending;重点关注以下字段字段说明优化建议type访问类型最好达到range或ref级别key使用的索引确认是否使用了合适的索引rows预估扫描行数数值过大可能需要优化Extra额外信息避免出现Using filesort、Using temporary3.3 性能诊断工具MySQL提供了更深入的分析工具EXPLAIN ANALYZEMySQL 8.0EXPLAIN ANALYZE SELECT * FROM large_table WHERE create_time 2023-01-01;Optimizer TraceSET optimizer_traceenabledon; SELECT * FROM products WHERE price BETWEEN 100 AND 200; SELECT * FROM information_schema.optimizer_trace; SET optimizer_traceenabledoff;4. 常见慢SQL优化方案4.1 索引优化索引是解决慢SQL最有效的手段之一常见优化方法添加缺失索引-- 为常用查询条件添加索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);优化索引顺序-- 将选择性高的列放在前面 ALTER TABLE logs ADD INDEX idx_level_date (log_level, create_date);避免索引失效不要在索引列上使用函数避免隐式类型转换注意LIKE查询的通配符位置4.2 SQL语句重写优化SQL写法也能显著提升性能**避免SELECT ***-- 不推荐 SELECT * FROM users; -- 推荐 SELECT id, name, email FROM users;合理使用JOIN-- 不推荐笛卡尔积 SELECT * FROM table1, table2 WHERE table1.id table2.id; -- 推荐显式JOIN SELECT * FROM table1 JOIN table2 ON table1.id table2.id;分页优化-- 不推荐偏移量大时性能差 SELECT * FROM orders ORDER BY id LIMIT 10000, 20; -- 推荐使用游标分页 SELECT * FROM orders WHERE id 10000 ORDER BY id LIMIT 20;4.3 数据库设计优化合理分表垂直分表将大字段拆分到单独表水平分表按时间、ID范围等拆分字段类型选择使用合适的数据类型如INT vs VARCHAR避免使用TEXT/BLOB存储频繁查询的数据适当使用冗余在需要频繁JOIN的场景可考虑适当冗余数据5. 高级优化技巧5.1 执行计划绑定对于复杂SQL可以使用执行计划绑定-- 创建执行计划绑定 CREATE OUTLINE ln_order_query ON SELECT/* INDEX(orders idx_user_status) */ * FROM orders WHERE user_id? AND status?; -- 使用绑定 SELECT * FROM orders WHERE user_id100 AND statuspending;5.2 参数优化调整数据库参数也能改善SQL性能-- 增大排序缓冲区 SET GLOBAL sort_buffer_size 4*1024*1024; -- 调整JOIN缓冲区 SET GLOBAL join_buffer_size 2*1024*1024;5.3 查询重写插件MySQL 8.0支持查询重写插件-- 安装rewriter插件 INSTALL PLUGIN rewriter SONAME rewriter.so; -- 添加重写规则 INSERT INTO rewrite_rules (pattern, replacement) VALUES ( SELECT * FROM orders WHERE user_id ?, SELECT id, amount, status FROM orders WHERE user_id ? ); -- 刷新规则 CALL flush_rewrite_rules();6. 慢SQL治理体系6.1 事前预防开发规范制定SQL编写规范建立Code Review机制使用SQL审核工具测试验证压力测试验证SQL性能使用EXPLAIN分析新上线SQL6.2 事中监控实时告警设置慢查询阈值告警监控数据库关键指标应急处理建立慢SQL终止流程准备常见问题的应急预案6.3 事后优化定期分析每周分析慢查询日志识别高频慢SQL持续改进建立性能基线跟踪优化效果7. 实战案例分析7.1 案例一电商订单查询优化问题描述 订单列表页加载缓慢查询语句SELECT * FROM orders WHERE user_id 123 AND status IN (paid, shipped) ORDER BY create_time DESC LIMIT 10;优化步骤分析发现缺少(user_id, status, create_time)的复合索引执行计划显示全表扫描添加索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);优化后查询时间从1.2s降至0.02s7.2 案例二报表查询优化问题描述 月度报表生成缓慢查询语句SELECT product_id, SUM(amount) FROM order_details WHERE order_date BETWEEN 2023-01-01 AND 2023-01-31 GROUP BY product_id;优化方案创建(order_date, product_id, amount)的复合索引使用物化视图预计算考虑使用专门的OLAP系统处理报表8. 工具推荐pt-query-digest分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.logMySQLTuner数据库配置优化wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.plPercona PMM全面的监控解决方案NineData云原生的数据库管理平台9. 经验总结在实际优化工作中我发现以下几点特别重要索引不是越多越好每个索引都会增加写入开销需要平衡读写比例理解业务场景同样的SQL在不同业务场景下可能有不同的优化策略关注执行计划变化数据量增长后原先高效的执行计划可能不再适用定期维护定期执行ANALYZE TABLE更新统计信息整体视角有时优化数据库不如优化应用逻辑或架构设计慢SQL优化是一个持续的过程需要建立完善的监控、分析和优化机制。通过系统化的方法可以显著提升数据库性能保障业务稳定运行。
MySQL慢SQL问题分析与优化实战指南
1. 线上慢SQL问题概述慢SQL是数据库性能问题的常见表现也是DBA日常工作中最常处理的故障类型之一。当数据库响应变慢时约70%的情况都与低效SQL语句有关。这些慢查询会占用大量数据库资源导致连接堆积、CPU飙升最终影响整个系统的稳定性。在实际生产环境中慢SQL的危害主要体现在三个方面直接影响用户体验页面加载时间延长接口响应变慢消耗过多数据库资源一个慢查询可能占用整个实例80%以上的CPU引发连锁反应单个慢查询可能导致连接池耗尽影响其他正常业务2. 慢SQL发现与监控2.1 慢查询日志配置MySQL中开启慢查询日志是最基础的监控手段配置参数如下-- 开启慢查询日志 SET GLOBAL slow_query_log ON; -- 设置慢查询阈值单位秒 SET GLOBAL long_query_time 1; -- 记录未使用索引的查询 SET GLOBAL log_queries_not_using_indexes ON; -- 慢查询日志文件位置 SET GLOBAL slow_query_log_file /var/log/mysql/mysql-slow.log;提示生产环境建议将long_query_time设置为0.5-1秒可根据业务特点调整2.2 性能监控工具除了慢查询日志还可以使用以下工具进行实时监控Performance SchemaMySQL内置的性能监控工具-- 查看执行时间最长的SQL SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;sys Schema提供更友好的视图-- 查看全表扫描的查询 SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;第三方监控平台如NineData、PMM等提供的可视化监控3. 慢SQL分析流程3.1 问题SQL定位当收到数据库性能告警时可按以下步骤快速定位问题SQL使用SHOW PROCESSLIST查看当前执行的SQL通过top命令确认服务器CPU、IO使用情况检查数据库监控仪表盘识别异常指标分析慢查询日志找出执行时间长的SQL3.2 EXPLAIN执行计划分析对定位到的慢SQL使用EXPLAIN分析执行计划是关键步骤EXPLAIN SELECT * FROM orders WHERE user_id 100 AND status pending;重点关注以下字段字段说明优化建议type访问类型最好达到range或ref级别key使用的索引确认是否使用了合适的索引rows预估扫描行数数值过大可能需要优化Extra额外信息避免出现Using filesort、Using temporary3.3 性能诊断工具MySQL提供了更深入的分析工具EXPLAIN ANALYZEMySQL 8.0EXPLAIN ANALYZE SELECT * FROM large_table WHERE create_time 2023-01-01;Optimizer TraceSET optimizer_traceenabledon; SELECT * FROM products WHERE price BETWEEN 100 AND 200; SELECT * FROM information_schema.optimizer_trace; SET optimizer_traceenabledoff;4. 常见慢SQL优化方案4.1 索引优化索引是解决慢SQL最有效的手段之一常见优化方法添加缺失索引-- 为常用查询条件添加索引 ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);优化索引顺序-- 将选择性高的列放在前面 ALTER TABLE logs ADD INDEX idx_level_date (log_level, create_date);避免索引失效不要在索引列上使用函数避免隐式类型转换注意LIKE查询的通配符位置4.2 SQL语句重写优化SQL写法也能显著提升性能**避免SELECT ***-- 不推荐 SELECT * FROM users; -- 推荐 SELECT id, name, email FROM users;合理使用JOIN-- 不推荐笛卡尔积 SELECT * FROM table1, table2 WHERE table1.id table2.id; -- 推荐显式JOIN SELECT * FROM table1 JOIN table2 ON table1.id table2.id;分页优化-- 不推荐偏移量大时性能差 SELECT * FROM orders ORDER BY id LIMIT 10000, 20; -- 推荐使用游标分页 SELECT * FROM orders WHERE id 10000 ORDER BY id LIMIT 20;4.3 数据库设计优化合理分表垂直分表将大字段拆分到单独表水平分表按时间、ID范围等拆分字段类型选择使用合适的数据类型如INT vs VARCHAR避免使用TEXT/BLOB存储频繁查询的数据适当使用冗余在需要频繁JOIN的场景可考虑适当冗余数据5. 高级优化技巧5.1 执行计划绑定对于复杂SQL可以使用执行计划绑定-- 创建执行计划绑定 CREATE OUTLINE ln_order_query ON SELECT/* INDEX(orders idx_user_status) */ * FROM orders WHERE user_id? AND status?; -- 使用绑定 SELECT * FROM orders WHERE user_id100 AND statuspending;5.2 参数优化调整数据库参数也能改善SQL性能-- 增大排序缓冲区 SET GLOBAL sort_buffer_size 4*1024*1024; -- 调整JOIN缓冲区 SET GLOBAL join_buffer_size 2*1024*1024;5.3 查询重写插件MySQL 8.0支持查询重写插件-- 安装rewriter插件 INSTALL PLUGIN rewriter SONAME rewriter.so; -- 添加重写规则 INSERT INTO rewrite_rules (pattern, replacement) VALUES ( SELECT * FROM orders WHERE user_id ?, SELECT id, amount, status FROM orders WHERE user_id ? ); -- 刷新规则 CALL flush_rewrite_rules();6. 慢SQL治理体系6.1 事前预防开发规范制定SQL编写规范建立Code Review机制使用SQL审核工具测试验证压力测试验证SQL性能使用EXPLAIN分析新上线SQL6.2 事中监控实时告警设置慢查询阈值告警监控数据库关键指标应急处理建立慢SQL终止流程准备常见问题的应急预案6.3 事后优化定期分析每周分析慢查询日志识别高频慢SQL持续改进建立性能基线跟踪优化效果7. 实战案例分析7.1 案例一电商订单查询优化问题描述 订单列表页加载缓慢查询语句SELECT * FROM orders WHERE user_id 123 AND status IN (paid, shipped) ORDER BY create_time DESC LIMIT 10;优化步骤分析发现缺少(user_id, status, create_time)的复合索引执行计划显示全表扫描添加索引ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time);优化后查询时间从1.2s降至0.02s7.2 案例二报表查询优化问题描述 月度报表生成缓慢查询语句SELECT product_id, SUM(amount) FROM order_details WHERE order_date BETWEEN 2023-01-01 AND 2023-01-31 GROUP BY product_id;优化方案创建(order_date, product_id, amount)的复合索引使用物化视图预计算考虑使用专门的OLAP系统处理报表8. 工具推荐pt-query-digest分析慢查询日志pt-query-digest /var/log/mysql/mysql-slow.logMySQLTuner数据库配置优化wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl perl mysqltuner.plPercona PMM全面的监控解决方案NineData云原生的数据库管理平台9. 经验总结在实际优化工作中我发现以下几点特别重要索引不是越多越好每个索引都会增加写入开销需要平衡读写比例理解业务场景同样的SQL在不同业务场景下可能有不同的优化策略关注执行计划变化数据量增长后原先高效的执行计划可能不再适用定期维护定期执行ANALYZE TABLE更新统计信息整体视角有时优化数据库不如优化应用逻辑或架构设计慢SQL优化是一个持续的过程需要建立完善的监控、分析和优化机制。通过系统化的方法可以显著提升数据库性能保障业务稳定运行。
