MySQL基于DATE BETWEEN查询指定日期区间匹配多行记录的优化方法

原有查询的资源浪费根源 #

原实现通过5组0-9的数字字典表做笛卡尔积，生成近10万天的连续日期序列，再通过IN匹配ToDate字段做过滤。这套逻辑完全绕开了最基础的区间比较规则，中间生成的海量临时日期数据会占用大量内存和计算资源，同时无法利用表字段上的索引，查询性能极差。

这类需求根本不需要逐天枚举日期做匹配，直接基于区间包含的数学判定规则写过滤条件即可，没有任何额外计算开销。

核心判定规则 #

要筛选出「自身日期区间完全落在输入参数指定范围内」的记录，只需要同时满足两个大小比较条件：

• 表记录的FromDate 大于等于 输入参数的起始日期：保证记录的开始时间没有早于查询范围的左边界
• 表记录的ToDate 小于等于 输入参数的结束日期：保证记录的结束时间没有晚于查询范围的右边界

用题目给出的示例做验证：输入参数为2020-01-01 至 2020-06-01时

• ID1记录：FromDate=2020-01-02 ≥ 2020-01-01，ToDate=2020-06-01 ≤ 2020-06-01，符合条件
• ID2记录：FromDate=2020-08-01 > 2020-06-01，超出查询范围右边界，排除
• ID3记录：ToDate=2020-11-28 > 2020-06-01，超出查询范围右边界，排除
• ID4记录：FromDate=2020-04-01 ≥ 2020-01-01，ToDate=2020-05-28 ≤ 2020-06-01，符合条件
  最终返回ID1、ID4的结果，和预期完全匹配。

可直接落地的高效查询写法 #

直接移除冗余的日期生成子查询，把过滤条件替换为上述两个大小比较逻辑即可，参考代码如下：

SELECT lt.*
FROM 你的业务日期表 lt
WHERE
  lt.FromDate >= :传入的起始查询参数
  AND lt.ToDate <= :传入的结束查询参数
ORDER BY lt.name ASC, lt.ID ASC

• 进一步性能优化：可以为表创建(FromDate, ToDate)的联合索引，查询时数据库可直接通过索引范围扫描定位符合条件的记录，完全不需要全表遍历，性能较原方案提升几个数量级。

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