如何将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 | -------------------------------------------------------------------------------------------------------------------------------核心问题解读索引使用矛盾WHERE条件使用了create_time字段因此数据库选择了idx_create_time索引来快速定位在时间范围内的数据行。然而ORDER BY子句却要求按id排序。“Using filesort”的产生由于idx_create_time索引只能保证数据按create_time有序而无法保证按id有序。为了满足ORDER BY id的要求MySQL 必须将步骤1中找到的所有数据行(例如30万行)的id和行指针在内存或磁盘上进行一次额外的排序。这个操作就是filesort。性能损耗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一定更大)时可以使用此方案。它将时间范围查询转化为效率极高的主键范围查询。核心思路先查询出时间范围对应的最小和最大id。然后用id BETWEEN min_id AND max_id这个主键范围查询来替代原查询。实操步骤(Java Spring JdbcTemplate 示例)Service public class AccountFlowService { Autowired private JdbcTemplate jdbcTemplate; public PageAccountFlow 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 ?; MapString, 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 ?, ? ; ListAccountFlow 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 ...利用主键聚簇索引是效率最高的查询类型。深度分页时优势巨大。完全避免filesortORDER BY id可以直接利用主键的天然顺序。前提与注意事项强正相关前提必须保证id的增长顺序与create_time基本一致。对于纯自增主键的表此条件成立。IdWorker雪花算法生成的id值通常也可以认为是自增的。处理ID不连续表中如果有删除操作会导致ID不连续BETWEEN范围可能包含无效数据。因此SQL中必须保留AND create_time BETWEEN ...进行二次过滤以保证结果绝对正确。虽然可能多一次筛选但主键范围扫描的成本依然远低于原方案。代码复杂度增加需要在应用层进行两次数据库交互。§总结与选型建议特性方案一(调整order by)方案二(改模式)优化本质让排序走索引避免filesort将条件转化为高效的主键查询改动点SQL语句( 数据库索引)应用层代码 SQL语句性能提升显著(10-50倍)极显著(50倍以上)适用条件通用主键ID与创建时间强正相关推荐度优先尝试在方案一不满足或场景特别符合时使用操作流程建议诊断对所有慢SQL先执行EXPLAIN确认是否存在Using filesort。实施优先采用方案一。修改SQL并创建对应复合索引绝大多数情况可解决问题。进阶如果数据量特别大(千万级以上)深分页需求强烈且表主键是自增ID则采用方案二。验证每次优化后务必再次使用EXPLAIN和实际压测来验证效果。