① 调优案例：semi join 翻车 与 选错索引

对应第 109 ~ 114 讲 · 千万级用户运营系统 + 亿级商品系统两次线上事故全程复盘

总览 ① 案例·半连接与选错索引 ② 案例·深分页与慢查询 ③ 主从复制架构 ④ 高可用故障转移 ⑤ 分库分表实战 执行计划篇 85~108 架构与实践篇 01~20
案例一 · 千万级用户运营系统（109 ~ 111 讲） 案例二 · 亿级数据量商品系统（112 ~ 114 讲） 同类问题换个马甲：优化器"自作聪明" 业务背景 日活百万 · 注册千万 · 用户表千万级未分表 运营按条件筛用户推消息 · 先 COUNT 再分批 1000 条推送 问题 SQL（先跑 COUNT 就几十秒） select count(id) from users where id in (select user_id from users_extent_info where latest_login_time < x) 执行计划揭示的真相 ① 子查询 range 走 idx_login_time 查出 4561 条 ② MATERIALIZED 物化成临时表（落磁盘 · 慢） ③ users 全表扫 49651 条 · 每条再去临时表里匹配 Extra：Using join buffer (Block Nested Loop) show warnings 一锤定音 EXPLAIN 之后执行 show warnings 发现 SQL 被自动改写成 semi join 半连接 users 每行都去无索引的物化临时表里匹配 → 灾难 实验验证：关掉半连接优化 set optimizer_switch='semijoin=off' 恢复 SUBQUERY + PRIMARY 主键查询的正常计划 几十秒 → 100 多 ms（性能提升几十倍） 生产落地（线上不能改参数） 改写 SQL 骗过优化器：加一个永假条件 OR id IN (select ... where latest_login_time < -1) 语义不变 · 不再 semi join · 上线后几十秒 → 几百毫秒 线上事故现场 晚高峰每分钟慢查询 10w+ · 数据库连接池打满 查询阻塞超时 · 商品系统濒临崩溃 问题 SQL（1 亿数据 · 跑几十秒） select * from products where category='xx' and sub_category='xx' order by id desc limit xx,xx　（品类筛选 + 倒序 + 分页） 执行计划：possible_keys 有它，key 却没用它 possible_keys = index_category · key = PRIMARY 扫聚簇索引 + Using where 逐条过滤 优化器认为：二级索引回表 + filesort 磁盘排序更慢 为什么以前不慢，突然就慢了？ 按 id 倒序扫聚簇索引 · 凑满 limit 就返回 运营新增了无商品的分类组合 where 永远查不到数据 → 聚簇索引全扫 1 亿条 紧急止血 = 面试标准答案 select * from products force index(index_category) ... 几十秒 → 100 多毫秒 · MySQL 用错执行计划就用 force index 两个案例共同的方法论 看懂执行计划 → 找到慢的根因 → 针对性改造 让 SQL 用上索引是王道 图例 背景 事故 / 问题 SQL 执行计划分析 解决方案 方法论沉淀

案例一速记：semi join 翻车

  • • IN 子查询被优化器自动改写成 semi join
  • • 子查询结果物化成临时表（落磁盘）
  • • 主表全表扫描 + 每行去临时表里匹配
  • • 排查利器：EXPLAIN 后跟 show warnings
  • • 解法：改写 SQL 结构（加永假 OR 条件）绕开 semi join

案例二速记：选错索引

  • • possible_keys 有二级索引，key 却是 PRIMARY
  • • 优化器算账：二级索引回表 + filesort 不划算
  • • 扫聚簇索引凑满 limit 就返回 —— 平时确实快
  • • 查不到数据的条件 → 1 亿条全扫 → 慢查询风暴
  • • 解法：force index 强制走二级索引

可复用的排查套路

  • • EXPLAIN 看访问方式 / 索引选择 / Extra
  • • show warnings 看优化器改写后的真实 SQL
  • • 怀疑优化器误判 → 实验环境关开关验证
  • • 生产落地优先改写 SQL，不动全局参数
  • • 上线后持续观察慢查询日志确认效果

① 调优案例 · 半连接与选错索引 · MySQL 实战与高可用知识图谱 · 依据第 109 ~ 114 讲整理