sn: 24batch: 5round: 9topic: 数据库慢查询优化索引、执行计划、覆盖索引运营导出页面的查询是ORDER BY create_time LIMIT 100000, 20单次执行 4.8 秒运营一天点几十次这一条 SQL 吃掉了订单库 40% 的 CPU。开发同学的第一反应是给 create_time 加索引加了还是 4.8 秒。第二反应是上缓存缓存的失效逻辑写起来比 SQL 还复杂。最后的解法是一行 SQL 改写延迟关联deferred join耗时降到 80ms。慢查询优化的知识网上铺天盖地但生产事故里真正反复出现的是三类深分页、隐式类型转换、以及以为加了索引就能用。这篇用三个真实案例把这三类一次讲透。环境MySQL 8.0.32InnoDB。深分页先搞清楚 MySQL 在为谁白干活LIMIT 100000, 20的执行过程取出前 100020 行扔掉 100000 行返回最后 20 行。而且如果排序字段有索引但查询列不全在索引里每一行都要回表——100020 次回表为的只是取最后 20 行。这就是那 4.8 秒的去向。延迟关联的思路是让定位 20 行和取完整列分离-- 优化前100020 次回表 SELECT * FROM orders ORDER BY create_time LIMIT 100000, 20; -- 优化后先在索引内部完成排序和偏移只回表 20 次 SELECT o.* FROM orders o INNER JOIN ( SELECT id FROM orders ORDER BY create_time LIMIT 100000, 20 ) t ON o.id t.id;逐行拆原理内层子查询走 create_time 索引索引的叶子节点存的是 (create_time, id)排序和偏移全在索引内部完成不需要回表子查询只输出 20 个 id外层按主键拿这 20 个 id 回表取完整行20 次回表。从 10 万次回表到 20 次这就是 4.8 秒到 80ms 的全部来源。再进一步如果业务允许比如只看最近数据用游标分页彻底消灭 OFFSET-- 上一页最后一条的 create_time 和 id 作为游标传入 SELECT * FROM orders WHERE (create_time, id) (2026-09-30 18:00:00, 8842130) ORDER BY create_time DESC, id DESC LIMIT 20;这个写法每页都是常数成本翻 100 万行和翻第 1 页一样快。代价是只能顺序翻页不能跳页。我的取舍运营后台允许跳页的用延迟关联用户端无限滚动的用游标分页——两类场景别混用一套方案。隐式类型转换索引失效的头号惯犯第二个案例来自优惠券系统。查询字段 phone 是 varchar开发传参用了 long-- phone 列类型 varchar(20)参数是数字 EXPLAIN SELECT * FROM coupons WHERE phone 13800138000; -- type: ALL全表扫描rows: 890 万 -- 改成字符串传参 EXPLAIN SELECT * FROM coupons WHERE phone 13800138000; -- type: refrows: 1原理列是 varchar、参数是数字时MySQL 对列做 CAST 转换等价于CAST(phone AS DOUBLE) 13800138000列上套函数索引直接废掉。反过来如果列是 int、参数传了字符串MySQL 会转换参数而不是列索引不受影响。记住方向MySQL 永远优先转换字符串参数而不是数字列——但别依赖这个记忆最稳的做法是应用层严格按列类型传参MyBatis 的#{}传 String 就好。这类问题最难防在定时任务和脚本里手写 SQL 联调时用的参数类型和上线代码不一致测试环境没事、生产全表扫。我们后来在 DBA 审核流程里加了上线 SQL 必须附 EXPLAIN 截图这一条隐式转换类事故再没漏过。EXPLAIN 的字段最容易被误读的是 rows 和 ExtraEXPLAIN 里大多数人都看 typeALL/index/range/ref/const这没错。但我发现真正造成误判的是两个地方。一是 rows 只是估算值基于索引统计信息。统计信息过旧时大量写入后没跑 ANALYZE TABLE优化器可能拿着错的估算选错索引——明明有更好的索引它不走。解法不是 force index 滥用而是定期维护统计信息 监控information_schema.INDEX_STATISTICS的偏差。二是 Extra 字段的信息量比 type 大。挑三个必须认识的Extra 值含义优化方向Using index覆盖索引不回表好事保持Using filesort排序在内存/磁盘完成考虑让排序走索引Using temporary用了临时表多为 GROUP BY/DISTINCT检查分组列是否在索引里覆盖索引值得单独说。第三个案例是订单列表页原本SELECT *改成只取列表页实际展示的 7 个字段并建了联合索引(user_id, create_time, status, amount)后Extra 从 Using filesort 变成 Using index耗时从 300ms 到 8ms。代价是索引变宽存储和写入成本上升我的判断标准列表类高频查询值得建宽索引换覆盖低频管理页不值得——为运营一个月用一次的页面加 4 列联合索引是拿写放大换不存在收益。优化流程的顺序纪律我的慢查询优化顺序固定为五步先看慢日志聚合一词定位 Top SQL别凭感觉猜EXPLAIN 确认访问路径先改写 SQL 再考虑加索引延迟关联、游标分页、改写 JOIN 顺序往往零成本加索引要评估写放大和空间上线后持续观察执行计划漂移8.0 的 histogram 和优化器开关可以辅助。顺序纪律的价值在于防加索引依赖症——很多团队遇到慢查询条件反射建索引索引堆到十几列后写入延迟上升、优化器选错索引的概率也上升进入负循环。SQL 改写永远比加索引便宜这是我优化过 200 多条慢 SQL 后最确定的一条经验。用 Java 代码固化优化检查参数类型与连接池观测慢查询的防线要落到代码层。第一道防线是参数类型纪律用 MyBatis 时特别注意// Mapper 接口参数类型必须与列类型严格一致 public interface CouponMapper { // 1. phone 列是 varchar参数声明 String——传 Long 就会触发隐式转换杀掉索引 ListCoupon selectByPhone(Param(phone) String phone); // 2. 范围查询的日期参数用 LocalDateTime与 datetime 列对齐 // 传 String 虽然多数情况能走索引但边界行为依赖时区解析埋雷 ListCoupon selectByCreateTimeRange(Param(start) LocalDateTime start, Param(end) LocalDateTime end); }第 1 行的注释就是我们优惠券事故的修复产物——接口层所有手机号参数统一 String在 service 入口做格式校验而不是让 DB 层去转换。第 2 行日期参数的坑更隐蔽String 传参在时区配置不一致的主从环境里同一条 SQL 主从返回结果可能差 8 小时参数类型严格化之后这类灵异事件直接消失。第二道防线是把慢查询观测做进连接池层HikariConfig config new HikariConfig(); config.setJdbcUrl(url); config.setMaximumPoolSize(20); // 1. 连接泄漏检测拿连接超过 60 秒不还打印持有线程堆栈 config.setLeakDetectionThreshold(60_000); // 2. HikariCP 自带的慢 SQL 观测执行超过 3 秒记录 WARN 级日志 DataSource ds new HikariDataSource(config); // 3. 配合 Micrometer 把连接池指标暴露给 Prometheus // hikaricp_connections_active活跃连接数、pending排队等待数 MeterRegistry registry getRegistry(); new HikariDataSourceMetricsBinder((HikariDataSource) ds, orders, registry);逐行拆第 1 行的 leak detection 是抓连接泄漏的神器——业务代码忘了关 ResultSet/Connection连接池耗尽时它能直接打印出持有连接的代码行我们把连接泄漏的平均定位时间从半天缩到 10 分钟第 3 行的 pending 指标比 DB 侧的慢日志更早发现问题——应用侧开始排队等待连接时DB 可能还没到慢查询阈值排队数上升就是容量告警的前兆。我们的告警规则里pending 5持续 1 分钟的优先级高于任何单条慢 SQL。第三道防线是回归保护核心查询的 EXPLAIN 计划固化在集成测试里CI 跑测试时用EXPLAIN输出断言 type 不是 ALL、rows 低于阈值。索引被误删、SQL 被无意改坏在合并代码时就拦截不用等线上慢日志报警。测试代码的骨架Test void orderListQuery_mustUseIndexNotFullScan() throws Exception { // 1. 造一批测试数据让优化器有真实的统计信息可用 seedOrders(10_000); // 2. 对被测 SQL 执行 EXPLAIN拿到的就是真实执行计划 try (ResultSet rs stmt.executeQuery( EXPLAIN SELECT id, status, amount FROM orders WHERE user_id 42 ORDER BY create_time DESC LIMIT 20)) { while (rs.next()) { String type rs.getString(type); // 3. 访问类型ALL 即全表扫直接判失败 String key rs.getString(key); // 4. 实际使用的索引名 long rows rs.getLong(rows); // 5. 估算扫描行数 assertNotEquals(ALL, type, 订单列表查询退化为全表扫描); assertEquals(idx_user_ctime, key, 未命中预期索引); assertTrue(rows 5000, 扫描行数异常膨胀: rows); } } }逐行拆第 1 步的数据量有讲究——太少了优化器可能因为统计不齐走全表也更快误报测试库固定灌 1 万行左右比较稳。第 3-5 行的断言就是上一节讲的 EXPLAIN 字段知识变成自动门禁type、key、rows 三项卡住后有人在代码里把索引提示删了这类变更会在 CI 阶段被打回。这套门禁上线两年拦下过 4 次真实的执行计划退化平均每次节省一次线上 P2 排查——测试写一次守的是所有后续变更。思考题延迟关联里子查询SELECT id ... LIMIT 100000, 20仍然要扫过前 10 万个索引项虽然不回表。如果你来设计一个支持跳页 深偏移都常数时间的方案你会怎么利用业务特性比如只允许按时间倒序重新建模这个问题