MySQL慢查询开启与分析优化案例

一、前言1.1 什么是慢查询日志慢查询日志是MySQL提供的一种性能诊断工具用于记录执行时间超过指定阈值的SQL语句。通过分析这些“慢SQL”可以精准定位数据库性能瓶颈优化索引、SQL写法或表结构。1.2 基础知识要求MySQL基础熟悉配置文件、基本SQL命令权限要求需要SUPER或PROCESS权限查看运行状态运维经验了解磁盘空间、日志轮转等基本概念二、慢查询日志的开启方式2.1 临时开启当前会话/全局重启失效sql-- 查看当前慢查询状态 SHOW VARIABLES LIKE %slow_query%; SHOW VARIABLES LIKE %long_query_time%; -- 开启慢查询日志全局立即生效重启失效 SET GLOBAL slow_query_log ON; -- 设置慢查询阈值秒建议设为0.1~2秒之间 SET GLOBAL long_query_time 1; -- 设置日志文件路径可选默认在数据目录下 SET GLOBAL slow_query_log_file /var/lib/mysql/slow-query.log; -- 设置未使用索引的SQL也记录 SET GLOBAL log_queries_not_using_indexes ON;2.2 永久开启修改配置文件Linux/Mac/etc/my.cnf或/etc/mysql/my.cnfWindowsmy.ini[mysqld] # 开启慢查询日志 slow_query_log 1 # 日志文件路径 slow_query_log_file /var/lib/mysql/slow-query.log # 慢查询阈值秒 long_query_time 1 # 记录未使用索引的查询 log_queries_not_using_indexes 1 # 日志输出格式FILE或TABLE默认FILE # log_output FILE配置完成后重启MySQL服务bash# systemctl sudo systemctl restart mysqld # service sudo service mysql restart三、参数解析参数类型默认值说明建议值slow_query_logBooleanOFF是否开启慢查询日志ON生产环境建议开启long_query_timeFloat10.0慢查询阈值秒1~2秒业务敏感可设为0.5slow_query_log_fileStringhostname-slow.log日志文件路径独立目录便于监控log_queries_not_using_indexesBooleanOFF是否记录未使用索引的查询ON找出索引缺失的SQLlog_outputEnumFILE日志输出方式FILE 或 TABLEmin_examined_row_limitInteger0扫描行数超过此值才记录1000过滤小表扫描log_slow_admin_statementsBooleanOFF是否记录慢管理语句如OPTIMIZEON全面监控四、慢查询日志分析工具4.1 使用mysqldumpslow工具MySQL自带日志分析工具可对慢查询日志进行聚合统计。bash# 基本用法 mysqldumpslow /var/lib/mysql/slow-query.log # 常用参数 mysqldumpslow -s t -t 10 /var/lib/mysql/slow-query.log # 按查询时间排序取前10条 mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log # 按执行次数排序 mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log # 按返回行数排序 mysqldumpslow -a /var/lib/mysql/slow-query.log # 不抽象数字显示具体SQL4.2 使用pt-query-digestPercona Toolkit更强大的第三方分析工具提供详细的统计报告。bash# 安装percona-toolkit # Ubuntu/Debian sudo apt-get install percona-toolkit # CentOS/RHEL sudo yum install percona-toolkit # 分析慢查询日志 pt-query-digest /var/lib/mysql/slow-query.log slow_report.txt # 分析当前运行的查询实时 pt-query-digest --processlist hlocalhost,uroot,ppassword五、实际案例电商订单慢查询优化5.1 案例背景某电商平台订单表orders数据量约500万行业务反馈订单列表页面加载缓慢超过5秒需要定位并优化。5.2 步骤一开启慢查询并复现问题sql-- 临时开启慢查询记录阈值0.5秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.5; SET GLOBAL log_queries_not_using_indexes ON; -- 确认日志文件位置 SHOW VARIABLES LIKE slow_query_log_file; -- 结果/var/lib/mysql/slow-query.log执行慢的订单查询SQLsqlSELECT o.order_id, o.user_id, o.order_amount, o.order_status, o.created_at, u.user_name, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE o.order_status pending AND o.created_at 2024-01-01 AND o.created_at 2024-02-01 ORDER BY o.created_at DESC LIMIT 20;5.3 步骤二分析慢查询日志bash# 查看慢查询日志 mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log日志输出textCount: 156 Time3.52s (549s) Lock0.01s (1.56s) Rows_sent20.0 (3120), Rows_examined5234567.0 (816M), root[root]localhost SELECT o.order_id, o.user_id, o.order_amount, o.order_status, o.created_at, u.user_name, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE o.order_status S AND o.created_at YYYY-MM-DD AND o.created_at YYYY-MM-DD ORDER BY o.created_at DESC LIMIT N关键信息平均耗时3.52秒平均扫描行数523万行几乎全表扫描执行次数156次总耗时549秒5.4 步骤三使用EXPLAIN分析执行计划sqlEXPLAIN SELECT o.order_id, o.user_id, o.order_amount, o.order_status, o.created_at, u.user_name, u.phone FROM orders o LEFT JOIN users u ON o.user_id u.user_id WHERE o.order_status pending AND o.created_at 2024-01-01 AND o.created_at 2024-02-01 ORDER BY o.created_at DESC LIMIT 20\GEXPLAIN结果idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEoALLidx_created_atNULLNULL5,234,567Using where; Using filesort1SIMPLEueq_refPRIMARYPRIMARY41NULL问题诊断typeALLorders表全表扫描未使用任何索引rows≈523万扫描全部数据行Extra包含Using filesortORDER BY需要额外排序无法利用索引possible_keys显示idx_created_at虽然有created_at索引但优化器未选择5.5 步骤四深入分析索引失效原因sql-- 查看orders表现有索引 SHOW INDEX FROM orders;现有索引PRIMARY KEY (order_id)INDEX idx_user_id (user_id)INDEX idx_created_at (created_at)INDEX idx_status (order_status)索引失效分析WHERE条件包含order_status和created_at两个字段MySQL优化器判断使用任一单列索引都需要回表过滤另一个条件扫描行数依然很大最终选择了全表扫描5.6 步骤五制定优化方案方案一创建联合索引推荐sql-- 创建联合索引将等值查询字段放前面范围查询放后面 CREATE INDEX idx_status_created ON orders (order_status, created_at); -- 验证索引效果 EXPLAIN SELECT ...同原SQL\G优化后EXPLAIN结果tabletypekeykey_lenrowsExtraorangeidx_status_created102185,000Using where;Using index conditionueq_refPRIMARY41NULL优化效果扫描行数从523万降到18.5万减少96.5%执行时间从3.5秒降至0.08秒方案二使用覆盖索引进一步优化sql-- 创建覆盖索引避免回表查询 -- 注意 -- 创建索引需要在线上业务停止时进行避免死锁 -- 覆盖索引需要包含所有查询字段 -- 重建索引可能需要很长时间可能破坏数据建议先备份数据 CREATE INDEX idx_status_created_cover ON orders (order_status, created_at, order_id, user_id, order_amount); -- 但orders表字段较多覆盖索引可能过大需权衡方案三SQL语句改写sql-- 使用子查询先筛选出订单ID再关联用户表 SELECT o.order_id, o.user_id, o.order_amount, o.order_status, o.created_at, u.user_name, u.phone FROM ( SELECT order_id, user_id, order_amount, order_status, created_at FROM orders WHERE order_status pending AND created_at 2024-01-01 AND created_at 2024-02-01 ORDER BY created_at DESC LIMIT 20 ) o LEFT JOIN users u ON o.user_id u.user_id;5.7 步骤六验证优化效果sql-- 再次查看慢查询日志mysqldumpslow -s t -t 5 /var/lib/mysql/slow-query.log优化后日志textCount: 156 Time0.08s (12.48s) Lock0.00s (0s) Rows_sent20.0 (3120), Rows_examined185000.0 (28.86M), root[root]localhost SELECT ...优化成果总结指标优化前优化后提升平均耗时3.52秒0.08秒97.7%↓扫描行数523万18.5万96.5%↓总耗时/天549秒12.5秒97.7%↓六、更多实际案例6.1 案例二隐式类型转换导致索引失效问题SQLsql-- phone字段定义为varchar(20)但传入数字类型 SELECT * FROM users WHERE phone [phone];EXPLAIN分析typeALLkeyNULLrows全表原因MySQL将phone字段自动转换为数字类型导致索引失效优化sql-- 正确写法传入字符串 SELECT * FROM users WHERE phone [phone];6.2 案例三函数操作导致索引失效(和mysql版本有关系)问题SQLsqlSELECT * FROM orders WHERE DATE(created_at) 2024-01-15;优化sqlSELECT * FROM orders WHERE created_at 2024-01-15 AND created_at 2024-01-16;6.3 案例四分页查询深度过大问题SQLsql-- 第10000页每页20条 SELECT * FROM orders ORDER BY order_id LIMIT 200000, 20;优化方案延迟关联sqlSELECT * FROM orders o INNER JOIN ( SELECT order_id FROM orders ORDER BY order_id LIMIT 200000, 20 ) t ON o.order_id t.order_id;七、生产环境最佳实践7.1 慢查询阈值设置建议OLTP系统高并发0.5~1秒OLAP系统分析查询2~5秒核心交易链路0.1~0.3秒配合监控告警7.2 日志管理定期轮转避免占满磁盘使用logrotate工具管理日志生产环境建议将log_output设为TABLE便于SQL查询分析sql-- 将日志输出到mysql.slow_log表 SET GLOBAL log_output TABLE; -- 查询慢日志表 SELECT * FROM mysql.slow_log WHERE query_time 2 ORDER BY start_time DESC LIMIT 10;7.3 监控告警接入Prometheus/Grafana监控慢查询数量趋势设置告警每分钟慢查询数 10 或 某SQL耗时 5秒7.4 慢查询分析流程总结text开启慢查询 → 收集日志 → 分析TOP慢SQL → EXPLAIN执行计划 → 定位问题 ↑ ↓ 监控告警 ← 验证效果 ← 上线变更 ← 制定优化方案 ← 索引失效/扫描行数多八、学习建议循序渐进先从mysqldumpslow入手掌握基础分析后再引入pt-query-digest结合EXPLAIN每个慢SQL都要用EXPLAIN分析理解MySQL优化器的选择建立知识库记录常见慢查询模式及优化方案隐式转换、函数操作、排序问题等预防为主上线前通过EXPLAIN审核新SQL避免慢查询流入生产定期巡检每周分析慢查询日志发现潜在性能隐患