MySQL查询优化：获取日期区间数据及区间前一条记录

咱们先把你的需求和疑问拆解开来，一步步解决：

需求实现的核心逻辑 #

你需要的是：

1. 取2018-01-01 11:10:44到2018-01-02 01:03:57之间的所有记录
2. 额外加上小于起始时间的最后一条记录（也就是起始时间的前一条）

你的两个查询逻辑上都是对的，但性能差异的关键在于索引，而不是子查询的写法。

两个子查询的性能对比 #

先看你的两个子查询：

1. 第一个：SELECT updatetime FROM his WHERE updatetime < "2018-01-01 11:10:44" ORDER BY updatetime DESC LIMIT 1
2. 第二个：SELECT MAX(updatetime) FROM his WHERE updatetime < "2018-01-01 11:10:44"

无索引的情况 #

如果updatetime上没有索引，两个子查询都会慢到离谱：

• 第一个需要全表扫描后排序，4600万行的排序会占用大量磁盘IO和内存，性能极差
• 第二个需要全表扫描来计算最大值，同样是全表遍历，速度也好不到哪去

有索引的情况 #

如果给updatetime加了B-tree索引（这是必须的优化），两个子查询的性能几乎没有区别：

• 索引本身是有序的，ORDER BY DESC LIMIT 1会直接定位到索引中小于目标值的最后一个节点，不需要排序
• MAX(updatetime)同样会利用索引的有序性，直接取到符合条件的最大值，不需要全表遍历

另外，第二个查询里的LIMIT 1是多余的，因为MAX()只会返回一个值，可以去掉，简化成：

SELECT * FROM his 
WHERE updatetime >= (SELECT MAX(updatetime) FROM his WHERE updatetime < "2018-01-01 11:10:44") 
  AND updatetime <= "2018-01-02 01:03:57";

关于“updatetime递增”的疑问 #

你问“若将updatetime列设为递增，能否让MySQL从条件的最后值开始遍历而非全表扫描？”
其实核心不是递增，而是索引：

• 只要updatetime上有索引，MySQL就会用索引做范围扫描，不会全表遍历
• 而updatetime本身是递增的（作为更新时间，自然是越来越大），会让索引的维护成本更低（不需要频繁分裂节点），查询时的范围扫描也会更顺畅，但这是锦上添花，不是必须的前提

关键优化步骤 #

1. 给updatetime加索引 #

这是解决4600万大表性能问题的核心，执行这条语句创建索引：

CREATE INDEX idx_his_updatetime ON his(updatetime);

如果你的表是InnoDB，且updatetime是主键（或者是主键的一部分），那聚簇索引已经包含了这个字段，不需要额外创建。

2. 验证执行计划 #

用EXPLAIN查看你的查询执行计划，确保用到了索引：

EXPLAIN SELECT * FROM his 
WHERE updatetime >= (SELECT MAX(updatetime) FROM his WHERE updatetime < "2018-01-01 11:10:44") 
  AND updatetime <= "2018-01-02 01:03:57";

如果输出中type列是range，key列是idx_his_updatetime，说明索引生效了，性能会大幅提升。

3. 边界情况处理 #

如果你的起始时间是表中最早的记录，那子查询会返回NULL，这时候主查询会没有结果。可以用COALESCE处理这种情况：

SELECT * FROM his 
WHERE updatetime >= COALESCE((SELECT MAX(updatetime) FROM his WHERE updatetime < "2018-01-01 11:10:44"), "1970-01-01 00:00:00") 
  AND updatetime <= "2018-01-02 01:03:57";

这样如果没有前置记录，就从表的最早时间开始取。

总结 #

• 没有索引的话，任何写法都会慢，先加索引是第一要务
• 两个子查询写法在有索引时性能相近，选你觉得更易读的即可
• updatetime递增会优化索引维护，但不是让查询避免全表扫描的关键，索引才是

内容的提问来源于stack exchange，提问作者adosorioarrieta