慢SQL,压垮团队的最后一根稻草!

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

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

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

×
核心结论与决策建议(BLUF)
在互联网核心业务系统运行体系中,未经优化的“慢 SQL(Slow Query)”是压垮团队与打满数据库 CPU 的最后一根稻草。一条执行长达数十秒的慢查询,不仅自身霸占系统计算线程,还会长时间持有行锁或元数据锁,导致后续正常的毫秒级请求成千上万地积压在等待队列中,最终在数秒钟内彻底耗尽数据库连接池(HikariCP/Druid)引发全栈服务雪崩。团队必须建立“前置参数防御捕获、执行计划深度剖析、覆盖索引科学重构、以及深分页延迟关联优化”四位一体的慢 SQL 歼灭机制,坚决将核心接口查询耗时压制在 50 毫秒以内。

治理阶段核心配置 / 优化手段底层技术突破机制生产目标成效
慢查询捕获slow_query_log=1 & long_query_time=0.2将监控监控颗粒度提升至 200 毫秒,提前发现亚健康查询建立每日慢 SQL 自动指纹归档与团队认领通报
全表扫描消除EXPLAIN 诊断,建立精准复合索引将 type 从 ALL(全表物理扫描)提升至 ref / range 级别磁盘物理读降低 99%,CPU 占用率从 100% 降至 15% 以下
深分页穿透优化延迟关联 (Deferred Join) / Seek Pagination先依靠主键聚簇索引完成行跳过,再执行精准单点回表百万级偏移量查询耗时由 39 秒缩减至 0.05 秒
紧急在线熔断SHOW PROCESSLIST 抓取会话并 KILL强行中断正在全表扫描或死锁的源头慢连接线程秒级释放被占满的连接池,避免全局服务彻底瘫痪


一、慢 SQL 事故现场:一条查询如何让系统彻底瘫痪
MySQL 默认配置下,只有当 SQL 执行耗时超过 10 秒才会被认定为慢查询并落盘:


真实事故惨状:在百万级数据表未优化的情况下,执行一条普通的分页查询,耗时竟高达整整 39 秒!

在每秒并发达到几百的场景下,只要连续产生几条此类慢查询,连接池瞬间打死,所有接口集体报
  1. 504 Gateway Timeout
复制代码
。

二、开启主动防御:精细化慢日志日志配置实战

  1. -- 查看慢日志日志当前配置
  2. SHOW VARIABLES LIKE 'slow_query_log%';
复制代码



动态开启慢查询监控监控并调优阈值:

  1. -- 1. 开启慢 SQL 捕获
  2. SET GLOBAL slow_query_log = 1;
复制代码




  1. -- 2. 查看当前耗时阈值
  2. SHOW VARIABLES LIKE "long_query_time";
复制代码



将阈值配置为 0.2 秒(生产环境黄金参数):

  1. -- 将阈值收紧至 200 毫秒
  2. SET GLOBAL long_query_time = 0.2;
复制代码





  1. -- 验证阈值已动态更新生效
  2. SHOW VARIABLES LIKE "long_query_time";
复制代码



三、SQL 执行底层链路与深分页调优
一条 SQL 从客户端发出到返回,经历连接器、分析器、优化器与存储引擎:


3.1 为什么 LIMIT 1000000, 10 极其缓慢?
许多工程师写出的分页 SQL:

  1. SELECT * FROM product_order WHERE shop_id = 88 ORDER BY id ASC LIMIT 1000000, 10;
复制代码

MySQL 必须在磁盘上按顺序扫描 1,000,010 条记录,并将整行的所有字段载入内存,最后仅截取最后 10 行并抛弃前 100 万行!

3.2 延迟关联(Deferred Join)终极优化写法
利用覆盖索引直接在聚簇索引树上跳过百万行,仅回表目标 10 条:

  1. -- 延迟关联秒级优化
  2. SELECT o.*
  3. FROM product_order o
  4. JOIN (
  5.     SELECT id FROM product_order
  6.     WHERE shop_id = 88
  7.     ORDER BY id ASC
  8.     LIMIT 1000000, 10
  9. ) AS page_o ON o.id = page_o.id;
复制代码

执行耗时直接从 39 秒骤降至 0.04 秒,彻底根治深分页性能灾难。

常见问题(FAQ)
Q1: 为什么在 my.cnf 配置文件中修改了 long_query_time = 0.2 并重启生效,但是进入 mysql 客户端执行 SHOW VARIABLES 看到的还是 10?
A1: 因为
  1. long_query_time
复制代码
既是一个全局变量(GLOBAL),又是一个会话变量(SESSION)。在客户端连接建立时,会话变量默认继承全局变量的值。若在已打开的会话中使用
  1. SET GLOBAL long_query_time = 0.2;
复制代码
,当前已存在的会话窗口中该值并不会同步改变;必须重新打开一个新连接窗口,或者在当前会话中显式执行
  1. SET SESSION long_query_time = 0.2;
复制代码
即可查看到最新生效值。

Q2: 为什么给字段加了索引,但慢查询日志日志中依然经常捕获到该 SQL,且 Rows_examined 极其庞大?
A2: 说明该查询触发了“索引未有效过滤(Index Skip / Low Cardinality)”。常见原因包括:1. 过滤条件的区分度极低(例如在包含 1000 万行的表中,查询
  1. status = 1
复制代码
,而状态为 1 的记录占了 900 万行),此时优化器认为走索引回表成本远高于全表扫描,因而自主放弃使用索引;2. 复合索引违背了最左前缀原则导致中途断开;3. 查询字段中使用了
  1. !=
复制代码
或
  1. NOT IN
复制代码
破坏了 B+ 树的等值二分匹配。



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

使用道具 举报

登录后关闭弹窗

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