SQL不等值连接查询指南 非等号运算符实现区间匹配优化

用 BETWEEN + 复合索引提升区间匹配效率

虽然非等值连接天生对优化器不友好，但并不意味着我们只能坐以待毙。合理的索引设计是提升性能的关键。这里有个要点：针对区间查询，有效的往往不是单列索引，而是复合索引。

假设我们有这样一个查询：
SELECT * FROM events e JOIN periods p ON e.ts BETWEEN p.start_ts AND p.end_ts;

那么，在periods表上，最有帮助的索引应该是：
CREATE INDEX idx_periods_range ON periods (start_ts, end_ts);

为什么是这个顺序？因为数据库在执行BETWEEN判断时，会优先利用左边界（start_ts）进行快速筛选，定位那些“可能”包含目标时间点的区间起点，然后再在这些候选区间里，用右边界（end_ts）进行二次过滤。如果把顺序反过来，索引的效率就会大打折扣。

• 在MySQL 8.0+或PostgreSQL中，如果时间格式不统一，可以考虑使用函数索引（如按小时或天截断）来归一化。
• 注意，像SQL Server这类数据库，可能无法对BETWEEN子句直接进行索引下推优化，有时需要将其显式改写为e.ts >= p.start_ts AND e.ts <= p.end_ts才能触发索引。
• 当然，如果业务场景中区间大量重叠（例如每秒都有新策略生效），传统索引的收益会下降。这时就需要考虑更高级的方案，比如预计算覆盖关系，或者使用PostGIS的gist这类专用的时空索引。

避免 CROSS JOIN + WHERE 写法引发性能雪崩

还有一种危险的写法，是把连接和过滤混在一起：
SELECT * FROM a, b WHERE a.val > b.low AND a.val < b.high;

这本质上是一个隐式的CROSS JOIN（笛卡尔积），然后再用WHERE条件进行过滤。当表比较小时，可能看不出问题。可一旦a表和b表的行数都上万，这个中间产生的笛卡尔积就会瞬间膨胀到亿级，直接耗尽内存，导致查询崩溃。

• 务必使用显式的JOIN ... ON ...语法。这不仅是好习惯，更是给数据库优化器一个明确信号，让它有机会选择更优的连接算法，比如PostgreSQL就可能会尝试使用针对范围连接的Merge Join。
• 别指望用LIMIT来挽救这种写法。优化器通常会先计算出完整的中间结果集，然后再进行截取，LIMIT并不能避免中间过程的性能灾难。
• 如果业务逻辑只关心“每个a记录匹配到的第一个（或最优的一个）b记录”，可以尝试在子查询中使用窗口函数（如ROW_NUMBER()）进行提前排序和剪枝，大幅减少需要参与连接的数据量。

说到底，处理区间匹配从来不只是语法问题，它是一场关于数据分布、索引设计和数据库引擎能力的综合博弈。最后提一个容易被忽略却至关重要的点：务必确保业务逻辑的约束在数据库层面得到保障。比如，确保start_ts <= end_ts这个基本规则，通过检查约束（CHECK Constraint）或应用层逻辑被严格维护。一旦数据中混入了“开始时间晚于结束时间”的反向区间，那么所有基于BETWEEN的查询逻辑都会悄然失效，而这种错误往往极其隐蔽，难以排查。