如何将WHERE create_time ORDER BY id的低效查询，优化为极致性能的WHERE id ORDER BY id查询？

§ 引言：一个经典的性能困境

在开发订单流水、记账流水、操作日志、监控数据等按时间分页查询的应用时，下面这条SQL非常常见，但在数据量增长后极易成为性能瓶颈：

SELECT * FROM account_flow 
WHERE create_time BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY id DESC  -- 按主键ID倒序
LIMIT 100000, 20;  -- 深分页查询

本文将先分析其变慢的根本原因，然后提供两个可直接上手的优化方案。

§Part1：问题根因分析 —— “Using filesort”是性能杀手

首先需要说明的是，在account_flow表中，create_time字段有普通索引idx_create_time。

然后，使用 EXPLAIN 命令查看数据库执行计划：

EXPLAIN 
SELECT * FROM account_flow
WHERE create_time BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY id DESC 
LIMIT 100000, 20;

你可能会看到类似下面的输出（关键看 Extra 列）：

+----+-------------+--------------+-------+---------------------+---------+------+--------+----------+-----------------------------------+
| id | select_type | table        | type  | key                 | key_len | ref  | rows   | filtered | Extra                             |
+----+-------------+--------------+-------+---------------------+---------+------+--------+----------+-----------------------------------+
|  1 | SIMPLE      | account_flow | range | idx_create_time     | 4       | NULL | 300000 |   100.00 | Using index condition; Using filesort |
+----+-------------+--------------+-------+---------------------+---------+------+--------+----------+-----------------------------------+

核心问题解读：

1. 索引使用矛盾：WHERE 条件使用了 create_time 字段，因此数据库选择了 idx_create_time 索引来快速定位在时间范围内的数据行。然而，ORDER BY 子句却要求按 id 排序。
2. “Using filesort”的产生：由于 idx_create_time 索引只能保证数据按 create_time 有序，而无法保证按 id 有序。为了满足 ORDER BY id 的要求，MySQL 必须将步骤1中找到的所有数据行（例如30万行）的 id 和行指针，在内存或磁盘上进行一次额外的排序。这个操作就是 filesort。
3. 性能损耗：
   • CPU消耗：对大量临时数据进行排序计算。
   • 内存/磁盘IO：如果排序数据量超出 sort_buffer_size，会使用磁盘临时文件，速度急剧下降。
   • 深分页灾难：LIMIT 100000, 20 意味着数据库需要先排序并丢弃前10万条数据，然后才能返回你想要的20条。这是一个 O(N log N) 的昂贵操作。

结论：filesort 和低效的 OFFSET 是导致此SQL慢的核心。优化目标就是消除 filesort，并优化分页逻辑。

BTW：这个慢sql如果是“order by id asc”(这种需要正序的场景很少），也一样是慢sql，两者都会产生”Using filesort“。

§Part2：解决方案一：调整查询（推荐首选）

将 ORDER BY 的顺序改为与 WHERE 条件中的范围查询列 create_time 对齐。

-- 将 ORDER BY id DESC 改为 ORDER BY create_time DESC, id DESC
SELECT * FROM account_flow 
WHERE create_time BETWEEN '2025-03-01' AND '2025-03-31'
ORDER BY create_time DESC, id DESC  -- 主要按时间排，时间相同再按ID排
LIMIT 100000, 20;

验证优化效果
再次使用 EXPLAIN 验证，Extra 列中的 Using filesort 应该已经消失，变为 Using index condition，表明排序已通过索引完成。

方案优势：

• 改动小：仅修改SQL，应用层逻辑基本不变。
• 效果显著：通常能消除 filesort，性能提升一个数量级。

BTW，如果能创建一个与新的 ORDER BY 子句顺序完全一致的索引，上面SQL将会更快！

-- 如果业务需求是倒序查看最新数据（常见）
CREATE INDEX idx_crtime_id_desc ON account_flow(create_time DESC, id DESC);

-- 如果业务需求是正序查看历史数据（不常见）
CREATE INDEX idx_crtime_id_asc ON account_flow(create_time ASC, id ASC);

注意事项：

• 排序结果集从“严格按ID排序”变为“先按时间排，同时间再按ID排”。在业务上，这通常是更合理的展示顺序（先看最新的记录），且不影响分页功能。
• 如果添加索引，应确保索引的列顺序和排序方向（ASC/DESC）与 ORDER BY 子句完全一致，否则优化可能失效。

§Part3：解决方案二：重构查询模式（性能极致，适用特定场景）

当你的表主键 id 是自增的，且与 create_time 严格正相关（即：后插入的数据ID一定更大）时，可以使用此方案。它将时间范围查询，转化为效率极高的主键范围查询。

核心思路：

1. 先查询出时间范围对应的最小和最大 id。
2. 然后用 id BETWEEN min_id AND max_id 这个主键范围查询来替代原查询。

实操步骤（Java + Spring JdbcTemplate 示例）：

@Service
public class AccountFlowService {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public Page<AccountFlow> queryByTimeRangeOptimized(LocalDateTime start, LocalDateTime end, int page, int size) {
        // 1. 获取时间范围内的ID边界 (此查询极快，利用了create_time的索引)
        String sqlGetIdRange = "SELECT MIN(id) as minId, MAX(id) as maxId FROM account_flow WHERE create_time BETWEEN ? AND ?";
        Map<String, Object> idRange = jdbcTemplate.queryForMap(sqlGetIdRange, start, end);
        Long minId = (Long) idRange.get("minId");
        Long maxId = (Long) idRange.get("maxId");
        
        if (minId == null || maxId == null) {
            return Page.empty(); // 时间范围内无数据
        }

        // 2. 使用ID范围进行高效的主键查询
        long offset = (long) (page - 1) * size;
        String sqlOptimized = """
            SELECT * FROM account_flow 
            WHERE id BETWEEN ? AND ?           -- 主键范围扫描，效率最高
              AND create_time BETWEEN ? AND ?  -- 二次过滤，确保因ID不连续导致的误差
            ORDER BY id DESC                   -- 主键索引天然有序，无需filesort
            LIMIT ?, ?
            """;

        List<AccountFlow> data = jdbcTemplate.query(
            sqlOptimized,
            new Object[]{minId, maxId, start, end, offset, size},
            new BeanPropertyRowMapper<>(AccountFlow.class)
        );

        // 3. (可选)获取总数
        String sqlCount = "SELECT COUNT(*) FROM account_flow WHERE create_time BETWEEN ? AND ?";
        Long total = jdbcTemplate.queryForObject(sqlCount, Long.class, start, end);

        return new PageImpl<>(data, PageRequest.of(page, size), total);
    }
}

方案优势：

• 性能极致：WHERE id BETWEEN ... 利用主键聚簇索引，是效率最高的查询类型。深度分页时优势巨大。
• 完全避免filesort：ORDER BY id 可以直接利用主键的天然顺序。

前提与注意事项：

1. 强正相关前提：必须保证 id 的增长顺序与 create_time 基本一致。对于纯自增主键的表，此条件成立。IdWorker雪花算法生成的id值，通常也可以认为是自增的。
2. 处理ID不连续：表中如果有删除操作，会导致ID不连续，BETWEEN 范围可能包含无效数据。因此SQL中必须保留 AND create_time BETWEEN ... 进行二次过滤，以保证结果绝对正确。虽然可能多一次筛选，但主键范围扫描的成本依然远低于原方案。
3. 代码复杂度增加：需要在应用层进行两次数据库交互。

§ 总结与选型建议

操作流程建议：

1. 诊断：对所有慢SQL先执行 EXPLAIN，确认是否存在 Using filesort。
2. 实施：优先采用方案一。修改SQL并创建对应复合索引，绝大多数情况可解决问题。
3. 进阶：如果数据量特别大（千万级以上），深分页需求强烈，且表主键是自增ID，则采用方案二。
4. 验证：每次优化后，务必再次使用 EXPLAIN 和实际压测来验证效果。