【SQL性能优化篇】有了！治理慢SQL“WHERE create_time ORDER BY id”的良药---规避“Using filesort”性能杀手

如何将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和实际压测来验证效果。