定位SQL慢查询

[复制链接]
发表于 8 小时前 来自手机 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有账号?立即注册

×
核心结论与决策建议(BLUF)
在生产环境 MySQL 数据库日常运维与性能调优中,慢查询(Slow Query)是吞吐量下跌、CPU 飙升乃至引发全库雪崩的万恶之源。定位慢查询绝不能等待业务部门报错,必须构建“主动监控监控捕获、聚合归类分析、执行计划深度剖析、线上实时锁阻断排查”的完整闭环。常态运维必须规范:将
  1. long_query_time
复制代码
调至 0.5~1.0 秒并在高峰期安全采样;利用自动化工具(如
  1. pt-query-digest
复制代码
)提取关键指纹并生成 Top 10 慢查询清单;全面使用现代 MySQL 8.0 的
  1. EXPLAIN ANALYZE
复制代码
探测真实算子耗时,彻底消灭全表扫描(type=ALL)与文件排序(Using filesort)。

慢查询定位阶段核心工具 / 系统命令技术实现原理运维关注点与调优行动
主动捕获日志日志SET GLOBAL slow_query_log = 'ON';按执行耗时与未走索引规则记录至慢日志日志文件长期开启,阈值设为 1 秒,配合日志日志切割轮转
日志聚合分析pt-query-digest / mysqldumpslow抽象 SQL 指纹,按总响应时间与锁等待汇总每周定期输出 Top 10 核心治理 SQL 清单
执行计划诊断EXPLAIN ANALYZE SQL语句真实执行 SQL 并输出火山模型树形耗时数据检查实际扫描行数(actual rows)与代价(cost)
线上实时抓包SHOW PROCESSLIST / sys.processlist查看当前正在执行、耗时超标的活跃会话发现 Time 超过 60 秒的会话,核查后执行 KILL


一、慢查询日志核心配置规范
慢查询日志在 MySQL 内部性能开销极低(通常小于 1%),应在线上全天候常态启用:

  1. -- 在线动态开启慢查询日志
  2. SET GLOBAL slow_query_log = 'ON';

  3. -- 设置慢查询阈值为 1 秒(支持微秒级,如 0.5 代表 500 毫秒)
  4. SET GLOBAL long_query_time = 1.0;

  5. -- 记录所有未使用有效索引的查询(辅助发现潜在慢 SQL)
  6. SET GLOBAL log_queries_not_using_indexes = 'ON';

  7. -- 限制未走索引的每分钟日志记录条数,防止日志暴增刷屏
  8. SET GLOBAL log_throttle_queries_not_using_indexes = 60;
复制代码


二、利用 EXPLAIN 深入透视 SQL 执行路径
定位到慢查询后,第一步是在终端对该语句执行计划进行诊断:

  1. -- 查看 SQL 执行计划
  2. EXPLAIN SELECT id, order_sn FROM orders WHERE user_id = 10023 AND status = 1;
复制代码



EXPLAIN 核心输出参数生死判准:
1. type(访问类型,从优到劣):
  
  1. system > const > eq_ref > ref > range > index > ALL
复制代码

   - const/eq_ref:基于主键或唯一索引点查,单次 IO 命中,性能极致;
   - ref:基于普通非唯一索引等值扫描,优秀;
   - range:索引范围扫描(
  1. BETWEEN
复制代码
、
  1. IN
复制代码
、
  1. >
复制代码
),良好;
   - index:全索引树扫描,虽比全表快但若索引树庞大依然很慢;
   - ALL:全表物理扫描(红线警告!),在百万级大表上绝对严禁出现!
2. key:实际命中的索引名称。若显示为
  1. NULL
复制代码
,说明完全没有使用索引;
3. rows:优化器基于统计信息预估需要扫描的记录行数。越小越好;
4. Extra(关键附加信息):
   -
  1. Using index
复制代码
:覆盖索引(极优),无需回表;
   -
  1. Using index condition
复制代码
:开启了索引下推(ICP);
   -
  1. Using temporary
复制代码
:高危!MySQL 创建了内存或磁盘临时表来处理查询;
   -
  1. Using filesort
复制代码
:高危!无法利用索引完成排序,触发了内存/磁盘外部归并排序。

三、MySQL 8.0 杀手锏:EXPLAIN ANALYZE 实战
传统
  1. EXPLAIN
复制代码
仅仅是优化器的“事前预估”。而在 MySQL 8.0.18+ 中,官方引入了
  1. EXPLAIN ANALYZE
复制代码
,它会真正执行 SQL,并输出树形算子的真实物理耗时与真实扫描行数:

  1. -- 真实执行并打印算子级物理树耗时
  2. EXPLAIN ANALYZE
  3. SELECT u.name, count(o.id)
  4. FROM users u
  5. JOIN orders o ON u.id = o.user_id
  6. WHERE o.create_time >= '2026-11-01'
  7. GROUP BY u.name;
复制代码

输出中会明确指出每个步骤:
  1. -> Filter: ... (cost=... rows=...) (actual time=0.045..1.231 rows=50 loops=1)
复制代码
。通过对比
  1. rows
复制代码
(预估行)与
  1. actual rows
复制代码
(真实行),能一眼揪出由于统计信息失真导致的错误执行路径。

常见问题(FAQ)
Q1: 为什么慢查询日志里记录了一条非常简单的主键查询语句,而且扫描行数只有 1,但执行耗时却长达 15 秒?
A1: 这是极其典型的“慢在锁等待,而非慢在执行”!当该行数据正在被其他未提交的长事务持有排他排他行锁(X-Lock),或者该表正在被 DDL 操作申请元数据排他锁(MDL Exclusive Lock)时,该简单查询会被迫在锁队列中死等直到超时或锁释放。排查此类问题需查看
  1. sys.innodb_lock_waits
复制代码
抓取持有锁的源头事务。

Q2: 面对几个 G 的庞大慢查询日志文件,怎样最快找出对数据库危害最大的慢 SQL?
A2: 严禁使用 vi 等纯文本工具打开大日志文件。最佳工业级做法是使用 Percona Toolkit 的
  1. pt-query-digest slow.log > report.txt
复制代码
。该工具会自动将 SQL 语句中的具体参数参数化归一为指纹,并按照“总耗时占比(Percentage of total query time)”从大到小排序输出,通常排在最前方的 3 条 SQL 就贡献了全库 80% 以上的查询延迟,集中精力优先攻破即可。



qidao123.com企服评测-应用市场 编辑提示:本文由企服大模型基于真实系统运维实践重构整理,聚焦技术原理、性能对比与选型避坑。若在实际部署中遇到异常,欢迎在下方跟帖交流;如需企业级技术支持或深度运维解决方案,请访问 qidao123.com 获取专属支持。
回复

使用道具 举报

登录后关闭弹窗

登录参与点评抽奖  加入IT实名职场社区
去登录
快速回复 返回顶部 返回列表