② 调优案例：深分页优化 与 慢查询排查

对应第 115 / 116 / 117 / 120 讲 · 十亿级评论系统 + 千万级删除事故的完整推理链

总览 ① 案例·半连接与选错索引 ② 案例·深分页与慢查询 ③ 主从复制架构 ④ 高可用故障转移 ⑤ 分库分表实战 执行计划篇 85~108 架构与实践篇 01~20
案例三 · 十亿级评论系统深分页（115 讲） 案例四 · 千万级数据删除引发的慢查询（116 / 117 / 120 讲） 慢查询不一定姓 SQL：先查语句，再查机器，再查内核状态 场景 评论十亿级 · 分库分表后单表百万 · 每商品评论放一个库一张表 热门商品几十万评论 · 用户翻到第 5001 页 问题 SQL（1 ~ 2 秒） select * from comments where product_id='xx' and is_good_comment='1' order by id desc limit 100000,20 为什么慢 index_product_id 筛出商品全部评论（几十万条） is_good_comment 不在索引里 → 几十万次回表 十几万条好评再 filesort 磁盘排序 → 取 20 条 优化：子查询延迟关联（几百毫秒） select * from comments a, (select id from comments where ... order by id desc limit 100000,20) b where a.id=b.id 子查询走 PRIMARY 倒序扫 → 只回表 20 次 没有银弹：案例二要避开聚簇索引全扫，案例三反而要利用聚簇索引顺序扫 单表百万级时全扫不慢，二级索引回表才慢 —— 慢在哪，就优化哪，具体情况具体分析 排查① SQL 本身 单行查询走索引本应极快 · 执行计划无异常 结论：不是 SQL 的锅 排查② 服务器负载 磁盘 IO / 网络带宽 / CPU 过高也会拖慢正常 SQL 离线灌数据要放凌晨低峰 · 本次负载正常，排除 排查③ profiling 神器 set profiling=1 → show profiles 找 query id show profile cpu, block io for query xx 发现 Sending Data 占耗时 99%（约 1s） 排查④ 引擎状态 show engine innodb status history list length 高达上万 undo 多版本链条没被 purge → 有长事务在跑 真相大白 定时任务在一个事务里删上千万数据（长事务） 删除只是打标记 · 并发查询的 ReadView 认为它还活跃 新事务必须扫描上千万被标记删除的数据 → 慢查询 解决与预防 kill 长事务 → 所有 SQL 立刻恢复正常 大批量数据清理放到凌晨执行 永远不要在业务高峰期删大量数据 / 开大事务 图例 背景 / 排查步骤 问题 / 异常线索 根因分析 解决方案 方法论沉淀

深分页优化的通用招式

  • • limit 深偏移 = 先扫掉前面几十万行再丢弃
  • • 延迟关联：子查询只查 id（走覆盖索引/主键）
  • • 外层拿 20 个 id 回表取完整数据
  • • 分页越深扫得越多，浅分页代价很小

慢查询排查四板斧

  • • ① 看执行计划：SQL 是否该走索引
  • • ② 看服务器负载：磁盘 / 网络 / CPU
  • • ③ profiling：定位最耗时的执行环节
  • • ④ show engine innodb status：找引擎级异常指标

长事务的隐性杀伤

  • • 大事务删除 = 长时间持有活跃状态
  • • 删除只是打标记，数据还在（MVCC 需要）
  • • 新事务 ReadView 包含长事务 → 必须扫标记数据
  • • history list length 异常升高是关键信号
  • • 大批量写删操作：拆小批 + 放低峰期

② 调优案例 · 深分页与慢查询排查 · MySQL 实战与高可用知识图谱 · 依据第 115 ~ 120 讲整理