一次SQL查询优化原理分析：900W+数据，从17s到300ms

那天晚上快十一点，我正准备合上电脑回家，运维小哥在旁边泡面，监控突然一片红——某个核心查询 RT 飙到了 17 秒，错误率也开始抖。业务那边一句话：用户订单列表刷不出来了。

一看监控，单条 SQL，QPS 不算高，但是平均耗时 17000ms，库里这张表数据量 900W 多一点，当时心里第一反应：八成是走全表扫了。

小场景先交代一下

这是个很普通的订单表，简化一下结构大概这样：

CREATE TABLE t_order (

  id           BIGINT PRIMARY KEY,

  user_id      BIGINT      NOT NULL,

  status       TINYINT     NOT NULL,

  pay_time     DATETIME    NOT NULL,

  total_amount DECIMAL(10,2),

  create_time  DATETIME,

  update_time  DATETIME,

  -- 其他业务字段若干

  INDEX idx_user (user_id),

  INDEX idx_pay_time (pay_time)

);

线上那条慢 SQL 长这样（已经把敏感字段干掉了）：

SELECT *

FROM t_order

WHERE user_id = ?

AND status = 1

AND DATE(pay_time) = ?

ORDER BY pay_time DESC

LIMIT 20;

900W+ 数据里查某个用户某天已支付的 20 条订单，看上去很正常对吧，条件也都有索引，user_id 有单列索引，pay_time 也有索引，怎么都不该 17 秒这么夸张。

先别瞎猜，Explain 拿出来说话

我当时直接在生产库跑了个 EXPLAIN（只 explain 不真正执行，影响有限）：

EXPLAIN

SELECT *

FROM t_order

WHERE user_id = 10001

AND status = 1

AND DATE(pay_time) = '2024-12-01'

ORDER BY pay_time DESC

LIMIT 20;

关键信息大概是这样的（简化版）：

几个点一眼看过去就不对劲：

type = ALL：全表扫描，900W 行真是一行一行看过去的……

key = NULL：任何一个索引都没用上

Extra 里面有 Using filesort：说明排序还走了额外的文件排序

所以 17 秒是怎么算出来的？ 大概就是：

900W 行全部从磁盘扫一遍

每行跑一遍DATE(pay_time)这个函数

再去筛 user_id 和 status

最后在内存里做一次大排序，挑出前 20 条

这种组合拳打下来，17 秒已经算它客气了。

为啥有索引还不走？罪魁祸首其实就一行

这里有两个小坑叠在一起：

对pay_time做了函数运算：DATE(pay_time) = '2024-12-01'

排序字段pay_time没有跟条件一起组成合适的联合索引

先说第一个。

大部分同学都知道一句话：对索引列做函数运算，会导致索引失效。这句确实没问题，但很多时候只停留在“背过了”的阶段，真到线上就没对照到。

DATE(pay_time)在执行时，MySQL 是没法直接用idx_pay_time上的有序结构来做范围判断的，它要把每一行的 pay_time 拿出来，算一遍 DATE()，变成日期，再去跟右边的日期常量比 —— 索引彻底派不上用场，只能老老实实全表扫。

正确的写法其实很朴素：把“某一天”变成一个时间范围。

-- 错误写法：对索引列做函数运算

DATE(pay_time) = '2024-12-01'

-- 改成时间范围

pay_time >= '2024-12-01 00:00:00'

AND pay_time <  '2024-12-02 00:00:00'

这一步改完，再 explain 一下：

EXPLAIN

SELECT *

FROM t_order

WHERE user_id = 10001

AND status = 1

AND pay_time >= '2024-12-01 00:00:00'

AND pay_time <  '2024-12-02 00:00:00'

ORDER BY pay_time DESC

LIMIT 20;

结果大概变成这样：

type 已经从 ALL 变成 range 了，这是好事，至少不再扫 900W 全表。但注意：

key 只用了idx_pay_time

rows 预估还有三万多

排序依然要 filesort

实测耗时从 17s 掉到了 2~3s，已经能看出点希望，但离 300ms 还早得很。

第二刀：把筛选条件都塞进联合索引里

这时候再看 where 条件：

WHERE user_id = ?

AND status = 1

AND pay_time BETWEEN ...

其实是可以给它配一把“量身定制”的联合索引的。比如：

CREATE INDEX idx_user_status_paytime

ON t_order (user_id, status, pay_time);

为什么是这个顺序？简单说就是：

user_id 选择性很高（一个用户在 900W 里也就几千几万条）

status 的取值通常很少（1、2、3 这种级别）

pay_time 用来做范围和排序

用这个索引，再 explain 一次刚才的 SQL：

EXPLAIN

SELECT id, user_id, status, pay_time, total_amount

FROM t_order

WHERE user_id = 10001

AND status = 1

AND pay_time >= '2024-12-01 00:00:00'

AND pay_time <  '2024-12-02 00:00:00'

ORDER BY pay_time DESC

LIMIT 20;

这回大概是这样的（示意）：

几个变化：

key 换成了新建的联合索引

预估行数从 3W 掉到 120 左右

filesort 消失了，排序可以直接利用 B+Tree 的有序性

实测耗时瞬间干到 400~500ms 左右，波动比较小。基本已经能接受了，但我还想再抠一点。

第三刀：尽量变成“覆盖索引查询”

前面那个 SQL 其实已经只查了 5 个字段：

SELECT id, user_id, status, pay_time, total_amount

前三个字段已经在索引里了，只有 total_amount 还需要回表去主键索引里再查一遍。

如果这个接口本身就是订单列表页，不涉及太多列，其实可以把 total_amount 也加到联合索引里去，变成覆盖索引：

CREATE INDEX idx_user_status_paytime_amount

ON t_order (user_id, status, pay_time, total_amount);

然后 SQL 不用改，explain 的 Extra 一般会变成：

Using where; Using index

说明所有需要的列都在二级索引里找到了，连回表都省了 —— 随便一张 900W 的表，能减少 100 多次随机 IO，是能看出肉眼可见的收益的。

这一步上去之后，线上实测：压测环境稳定在 250~350ms 区间，正式环境在 300ms 左右，大部分请求都压在 200ms 内。

Java 代码这块，我是怎么验证前后差异的

具体实现是 Spring Boot + JdbcTemplate，大概的伪代码长这样。

原始版本（17 秒那个）：

public List<Order> queryOrdersBad(Long userId, LocalDate day) {

  String sql = "SELECT * FROM t_order " +

          "WHERE user_id = ? " +

          "AND status = 1 " +

          "AND DATE(pay_time) = ? " +

          "ORDER BY pay_time DESC " +

          "LIMIT 20";

  StopWatch watch = new StopWatch();

  watch.start("queryBad");

  List<Order> list = jdbcTemplate.query(

          sql,

          ps -> {

              ps.setLong(1, userId);

              ps.setDate(2, java.sql.Date.valueOf(day));

          },

          (rs, rowNum) -> {

              Order o = new Order();

              o.setId(rs.getLong("id"));

              o.setUserId(rs.getLong("user_id"));

              o.setStatus(rs.getInt("status"));

              o.setPayTime(rs.getTimestamp("pay_time").toLocalDateTime());

              o.setTotalAmount(rs.getBigDecimal("total_amount"));

              return o;

          });

  watch.stop();

  log.info("queryOrdersBad cost={}ms, size={}",

           watch.getTotalTimeMillis(), list.size());

  return list;

}

优化后的版本：

public List<Order> queryOrdersGood(Long userId, LocalDate day, int page, int size) {

  LocalDateTime start = day.atStartOfDay();

  LocalDateTime end   = day.plusDays(1).atStartOfDay();

  String sql = "SELECT id, user_id, status, pay_time, total_amount " +

          "FROM t_order " +

          "WHERE user_id = ? " +

          "AND status = 1 " +

          "AND pay_time >= ? " +

          "AND pay_time < ? " +

          "ORDER BY pay_time DESC " +

          "LIMIT ?, ?";

  int offset = (page - 1) * size;

  StopWatch watch = new StopWatch();

  watch.start("queryGood");

  List<Order> list = jdbcTemplate.query(

          sql,

          ps -> {

              ps.setLong(1, userId);

              ps.setTimestamp(2, Timestamp.valueOf(start));

              ps.setTimestamp(3, Timestamp.valueOf(end));

              ps.setInt(4, offset);

              ps.setInt(5, size);

          },

          (rs, rowNum) -> {

              Order o = new Order();

              o.setId(rs.getLong("id"));

              o.setUserId(rs.getLong("user_id"));

              o.setStatus(rs.getInt("status"));

              o.setPayTime(rs.getTimestamp("pay_time").toLocalDateTime());

              o.setTotalAmount(rs.getBigDecimal("total_amount"));

              return o;

          });

  watch.stop();

  log.info("queryOrdersGood cost={}ms, size={}",

           watch.getTotalTimeMillis(), list.size());

  return list;

}

同样的测试数据、同样的 userId 和 day，前者基本稳定在 16~18 秒，后者就一路稳在三百多毫秒。这个差距，肉眼可见。

中间顺便踩了几个小坑

那一晚折腾下来，除了把 SQL 改顺手了，顺带又踩了几个典型小坑，提一下：

有同事想直接在 Java 里拼DAY(pay_time)、MONTH(pay_time)这种，想做更花哨的查询，我直接给拦了，索引利用率一下又没了

还有人说把联合索引做成(pay_time, user_id, status)也行，理论上能用上，但WHERE user_id = ?这种就不能走索引前缀了，效果差一截

部分场景其实只要查id列就够了，可以先查一批 id，再用IN回到主键上批量拿详情，这样能减少索引里要存的列数，索引更“瘦”，缓存命中率也更高

这些都是一点一点试出来的，不是拍脑袋。

最后留几个我自己记在小本本上的点

不搞那种规规矩矩的提纲，就当碎碎念，反正我每次写 SQL 都会过一遍：

where 里能不用函数，就别用在索引列上，用范围条件代替

真正热点的查询，老老实实配一把“对口”的联合索引，顺序按过滤强度和排序来排

只查必要字段，业务真不需要的列别顺手带上，看看能不能争取一下覆盖索引

Explain 一定要看：type、key、rows、Extra，这四个字段多看两眼，性能问题基本就暴露了

慢 SQL 日志一定打开，线上大部分“玄学卡顿”，个位数几条慢 SQL 就能解释

那天优化完，监控恢复绿了，运维泡面也吃完凉了，我收拾东西回家的路上就一个感觉： 很多 900W 的坑，其实都是从一行DATE(pay_time)开始的。