如何优化包含JOIN不等条件的低效SQL脚本

你当前SQL的性能瓶颈主要来自两个核心点：

• JOIN条件里的非等值比较>=无法利用索引做高效的等值匹配，容易出现全表扫描或者嵌套循环耗时过高的问题
• JOIN条件中用到的coalesce(t1.date, t3.date)函数计算逻辑，无法直接命中t1、t3的date字段索引，每次匹配都需要实时计算值

可行的改写优化方案 #

方案1：新增冗余字段+索引（性能提升最高，成本最低） #

如果业务允许修改表结构加冗余字段，优先选这个方案：

• 给t1表新增冗余字段calc_date，数据写入/更新时自动计算coalesce(t1.date, t3.date)的值（如果t3关联id固定可以同步写入，也可以通过触发器、ETL任务在t1/t3数据变更时同步更新）
• 新增三个联合索引：
  • t1加联合索引idx_num_calcdate(id, num, calc_date)
  • t3的id字段如果不是主键，加索引idx_id(id)
  • t2加联合索引idx_num_date(num, date)
• 改写后的SQL：

select t1.*
from t1
join t3 on t3.id = t1.id
join t2 on t1.num = t2.num and t1.calc_date >= t2.date 

该方案可以让JOIN条件完全命中索引，性能至少提升10倍以上。

方案2：无需改表的SQL改写，提前过滤+计算下推 #

如果不能修改表结构，就把计算逻辑提前到子查询，先缩小数据集再做非等值JOIN：

• 先预计算t1和t3关联后的calc_date，同时在子查询里加业务过滤条件，尽可能缩小中间结果集的大小
• 改写后的SQL：

with t1_t3_mid as (
    select 
        t1.*,
        coalesce(t1.date, t3.date) as calc_date
    from t1
    join t3 on t3.id = t1.id
    -- 此处补充业务过滤条件，比如时间范围、id范围等，提前过滤无效数据
)
select t1_t3_mid.*
from t1_t3_mid
join t2 on t1_t3_mid.num = t2.num 
where t1_t3_mid.calc_date >= t2.date

• 配套索引建议：
  • t1加联合索引idx_id_num_date(id, num, date)
  • t3加联合索引idx_id_date(id, date)
  • t2加联合索引idx_num_date(num, date)
    该方案不需要改表结构，比原生SQL性能提升3~5倍，如果子查询能过滤掉大部分无效数据，提升幅度会更高。

方案3：小表广播（适配分布式SQL引擎场景） #

如果使用的是SparkSQL、Hive这类分布式SQL引擎，或者MySQL支持哈希join，且t2是数据量小于10万行的小维度表，可以直接调整JOIN顺序，把小表广播到所有节点避免shuffle：

• 以SparkSQL为例，改写后：

select /*+ BROADCAST(t2) */ t1.*
from t1
join t3 on t3.id = t1.id
join t2 on t1.num = t2.num and coalesce(t1.date,t3.date) >= t2.date 

该方案不需要改结构也不需要大改SQL，性能可以提升2~3倍。

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