在数据库查询中，基于时间范围进行表关联是一个高频且容易踩坑的需求。很多人第一反应就是使用 BETWEEN AND 子句，看似简单直接，但如果不理解其底层逻辑和优化要点，很容易写出语法正确但性能灾难的SQL。
用 BETWEEN AND 做时间区间连接，本质是“非等值连接”
直接把 JOIN ... ON t1.ts BETWEEN t2.start_ts AND t2.end_ts 写在连接条件里，数据库优化器往往会很头疼。这种写法本质上是一种非等值连接，数据库很难高效地利用索引，尤其是在区间表（t2）数据量庞大时，极易触发全表扫描，产生类似笛卡尔积的糟糕性能。要想让它反赌，核心前提是：至少有一侧的时间字段建立了有效的索引，并且查询逻辑能够被优化器识别为可索引的范围扫描。
先打好基础：时间字段类型与索引策略
很多性能问题其实源于数据模型的设计缺陷。一个常见的翻车点就是时间字段存储不当——比如用 VARCHAR 存时间戳，或者时区没有统一处理。这会导致 BETWEEN 比较时发生隐式类型转换，索引直接失效。
因此，动手之前务必确认以下几点：
- 字段类型要对：确保
start_ts和end_ts是TIMESTAMP或DATETIME这类原生的时间类型，而不是字符串。 - 时区要统一：比较两端的时间必须处于相同时区。通常的做法是在存储和比较前全部转换为UTC时间。避免在
WHERE或ON子句中使用CONVERT_TZ()等函数，这同样会导致索引无法使用。 - 索引要建对：在区间表上为
start_ts和end_ts建立联合索引，顺序很关键：INDEX idx_time_range (start_ts, end_ts)。因为BETWEEN查询通常是先根据下界（start_ts）进行筛选，所以把start_ts放在前面。
根据数据规模，选择 EXISTS 还是 JOIN
不同的数据分布，适合不同的写法。这里有个简单的选择策略：
当你的主表（例如订单表）远小于区间表（例如营销活动表）时，使用 EXISTS 往往更可控，语义也更清晰。
SELECT o.*
FROM orders o
WHERE EXISTS (
SELECT 1
FROM campaigns c
WHERE o.created_at BETWEEN c.start_ts AND c.end_ts
AND c.status = 'active' -- 利用其他条件快速过滤
);
这个写法的精妙之处在于，子查询中额外增加了 c.status = 'active' 这样的过滤条件。如果 campaigns 表有百万行，但当前有效的活动只有几十个，数据库就可以先通过 status 索引快速定位到这几十行，然后再对这少量数据做时间范围判断，效率极高。
反过来，如果情况相反：主表（orders）是亿级大表，而区间表（campaigns）只有寥寥几十行，那么使用 JOIN 可能更合适，并且记得在 orders.created_at 上建立索引。
小心边界：NULL 值与日期精度陷阱
BETWEEN a AND b 是闭区间，这本身没问题。但实际业务中，边界情况处理不好就会丢数据。
- 处理“长期有效”：如果
end_ts为NULL表示活动永久有效，那么BETWEEN x AND NULL的结果会是UNKNOWN，导致该记录被排除。这时需要显式补充逻辑：(o.created_at >= c.start_ts AND (c.end_ts IS NULL OR o.created_at <= c.end_ts))。 - 注意日期精度：如果字段是
DATE类型，BETWEEN '2024-01-01' AND '2024-01-02'实际上只包含1号00:00:00到2号00:00:00，这意味着1号午夜之后的数据会被遗漏。对于需要精确到天的范围，建议统一使用TIMESTAMP类型，或者明确处理时间边界。 - 避免函数包裹：在MySQL 5.7及以上版本中，如果时间字段被函数包裹（如
DATE(created_at)），即使有索引也无法使用。要尽量避免在连接条件或过滤条件中对索引列进行函数操作。
说到底，时间区间关联从来不是简单地套一个 BETWEEN 关键字就能高枕无忧的。索引策略的选择、NULL值的处理、时区的对齐、以及根据主被动表的数据量决定查询写法，这些细节缺一不可。忽略任何一点，都可能让一个线上查询从毫秒级响应跌落到数秒甚至更久。
