7、MySQL 慢查询排查与优化实战指南

一、认识慢查询1.1 定义SQL 执行时间超过 long_query_time 阈值的语句会被记录到慢查询日志用于事后排查。默认值 10 秒生产建议 1 秒。log_queries_not_using_indexes 可额外抓取未走索引的语句。慢不等于错排查要找性价比最高的那条而不是最慢的那条。1.2 阈值怎么定行业参考业务类型建议阈值典型场景普通业务系统1 ~ 2 秒OA 办公、数据报表电商领域0.5 ~ 1 秒ERP销售/库存/采购、商城、医院挂号、制造业 MES专项高压业务0.1 ~ 0.5 秒证券、银行核心、保险投保理赔1.3 危害长期占用连接拖垮连接池。持锁时间久引发连锁阻塞。大事务回放慢拖出主从延迟。1.4 三种慢法类型典型成因偶发慢锁等待、统计信息抖动持续慢缺索引、写法差突发慢数据量增长、并发突增、参数变更二、慢查询日志2.1 常用命令速查SHOW VARIABLES LIKE slow_query_log; -- 是否开启 SHOW VARIABLES LIKE slow_query_log_file; -- 日志文件位置 SHOW GLOBAL STATUS LIKE Slow_queries; -- 慢查询累计数量 SET GLOBAL slow_query_log ON; -- 即时开启 SET GLOBAL long_query_time 1; -- 设置阈值秒2.2 开启三步slow_query_log 设为 ON。long_query_time 设为 1按业务场景调整。log_output 选 FILE 或 TABLE用 min_examined_row_limit 过滤小表扫描噪音。2.3 日志记录的关键字段字段含义Query_time实际执行耗时Lock_time锁等待耗时Rows_sent / Rows_examined返回行数 / 扫描行数比值越低越好另含时间戳、用户、主机等上下文信息。2.4 扩展参数log_slow_extra8.0 起记录更多指标如 Rows_affected、Bytes_sent。log_slow_admin_statements记录 DDL 等管理语句。log_slow_replica_statements从库也记录。2.5 注意事项SET GLOBAL 即时生效无需重启但需写入配置文件才能持久化。log_queries_not_using_indexes 不要盲开日志量会被放大。slow.log 持续增长需要配置轮转与定期清理。三、分析工具工具定位要点mysqldumpslow自带、轻量-s t 按总耗时排序-t 10 取前十聚合粗糙精度有限pt-query-digest生产首选按语句指纹聚合输出 95 分位与样本适合周报复盘performance_schema8.0 优先查 events_statements_summary_by_digest按 sum_timer_wait 排序取 TopN比日志实时sys schema场景化视图statements_with_full_table_scans、statements_sorting_using_temp_tables补充分析主要靠三件套——慢查询日志找慢 SQL、mysqldumpslow聚合统计、EXPLAIN看是否合理使用索引。生产环境下建议把 pt-query-digest 作为标准入口。四、EXPLAIN 执行计划4.1 核心字段字段读法id越大越先执行相同则从上到下select_type出现 DERIVED 需警惕派生表type七档访问类型见下表key / key_len实际用到的索引及参与的列数rows / filtered估算扫描量与过滤比例越小越好Extra信息量最大见 4.34.2 type 七档system、const、eq_ref、ref、range、index、ALL从左到右依次变差优秀system、const、eq_ref可接受ref、range必须优化index、ALL4.3 Extra 关键取值取值判断Using index覆盖索引好事Using index condition索引下推好事Using filesort排序未走索引需优化Using temporary用了临时表需优化Using join buffer被驱动表缺索引4.4 速查口诀type 从 const 到 ALL越左越好出现 ALL 必须优化。key 为 NULL 说明优化器没走索引。Extra 出现 filesort 或 temporary 需要优化。4.5 进阶手段EXPLAIN ANALYZE8.0.18看真实耗时与真实行数。EXPLAIN FOR CONNECTION id分析正在运行的会话。SHOW WARNINGS查看优化器改写后的语句。optimizer_trace查看成本估算过程。五、语句级剖析5.1 SHOW PROFILE 用法SET profiling 1; SHOW PROFILES; -- 列出各语句耗时 SHOW PROFILE FOR QUERY 1; -- 查看单条各阶段耗时5.2 各阶段怎么读Sending data 占大头 → 扫描行数多或回表多。Sorting result → 排序耗时。converting HEAP to MyISAM → 临时表已落盘。局限8.0 已弃用转向 performance_schema由 events_statements_history_long 逐步替代。六、索引优化6.1 建索引四原则选择性区分度在 10% 以上才有价值。覆盖性尽量覆盖查询列消除回表。顺序等值在前、范围在后、排序跟上。克制单表索引控制在 5 ~ 6 个。6.2 修复索引失效失效原因修法列上做函数运算把运算移到常量一侧隐式类型转换如 varchar 未加引号补引号或对齐列类型OR 连接无索引列拆成 UNION取反条件!、NOT IN改为正向枚举6.3 加索引前先算账回表占比过大时优化器宁可选全表扫描。索引越多写越慢且挤占 Buffer Pool。6.4 索引维护ANALYZE TABLE 更新统计信息。FORCE INDEX 只作临时救急手段。定期清理冗余索引与从未被使用的索引。七、SQL 改写7.1 深分页延迟关联子查询先用覆盖索引取主键再回表取数据。游标法条件改为 id 上一页末尾值避免 OFFSET。7.2 JOIN被驱动表的关联列必须有索引。小表驱动大表。关联字段的字符集与类型保持一致否则索引失效。7.3 子查询8.0 已有半连接semi-join自动改写一般不必手动转换。确实慢时再改写为 JOIN。7.4 通用写法大事务拆批每批约 1000 行。批量插入按 500 ~ 1000 行一批。只取需要的列不用 SELECT *。不对列包函数让索引可用。八、配置与架构层优化8.1 参数配置参数建议innodb_buffer_pool_size给到物理内存 50% ~ 70%redo 日志容量4 ~ 8G防止刷盘周期卡顿tmp_table_size调大防止临时表落盘8.2 架构手段由轻到重读写分离 → 缓存挡热点 → 归档历史数据 → 分区表 → 分库分表最后手段。8.3 MySQL 优化四方向方向内容系统层增加 CPU、内存换 SSD重点关注磁盘 IO数据库配置调整内存、连接数、日志刷盘参数让 MySQL 更合理地使用现有资源SQL 与索引建立合适索引、优化 SQL、用 EXPLAIN 分析计划架构与运维主从复制、读写分离、高可用、备份恢复、监控告警优化目标始终是三件事查询更快、运行更稳、故障更少。九、排查方法论9.1 数据库突然变慢怎么办应急响应看服务器资源CPU、内存、磁盘是否异常。看连接是否连接数过多、打满连接池。看 SQL找出执行时间长的语句。看执行计划用 EXPLAIN 分析索引与调优情况。优化改 SQL、建索引、加缓存或调整架构。一句话总结发现问题靠监控分析问题靠 EXPLAIN解决问题靠索引、SQL、配置。9.2 慢查询五步法步骤动作第一步拿到具体语句与耗时第二步EXPLAIN 看执行计划第三步对比预估 rows 与实际行数第四步定位问题类型没索引 / 回表多 / 排序差第五步改写并验证纳入监控防复发9.3 现场辅助命令SHOW PROCESSLIST; -- 查看正在执行的会话 SELECT * FROM performance_schema.data_lock_waits; -- 查看锁阻塞 SELECT * FROM information_schema.innodb_trx; -- 查看长事务十、监控与预防10.1 企业主要监控指标指标说明连接数最大连接数假设 1000超过 800 报警QPS / TPSQPS 每秒查询次数TPS 每秒完成的事务数事务 一次完整操作流程慢查询数超过阈值需告警CPU / 内存服务器资源是否够用磁盘空间与 IO磁盘是否枯竭、IO 是否变慢主从延迟从库数据是否落后于主库锁等待是否有事务互相等待导致业务卡顿10.2 上线前规范开发约束每表必须有主键。新 SQL 上线前必须通过 EXPLAIN 评审。大表变更先在预发环境压测。10.3 上线后运营慢查询数量设阈值告警。digest TopN 周报复盘。优化完成后回归观察至少一个完整业务周期。十一、典型案例案例现象解法深分页拖垮接口LIMIT 100000, 20每次扫十万行延迟关联或游标法隐式转换全表扫varchar 列查询未加引号类型对齐或修改列类型排序出现 filesortExtra 出现 Using filesort联合索引把排序列带上优化器选错索引rows 预估与实际相差十倍以上ANALYZE TABLE仍错则临时 FORCE INDEX附录、命令速查表-- 采集 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SHOW VARIABLES LIKE slow_query_log%; SHOW GLOBAL STATUS LIKE Slow_queries; -- 分析 mysqldumpslow -s t -t 10 /path/to/slow.log pt-query-digest /path/to/slow.log -- Top SQL8.0 SELECT digest_text, count_star, ROUND(sum_timer_wait/1e12, 2) AS total_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10; -- 定位 EXPLAIN EXPLAIN ANALYZE SHOW PROCESSLIST; SELECT * FROM information_schema.innodb_trx; -- 维护 ANALYZE TABLE