SQL子查询单独运行快但JOIN关联执行极慢问题排查

• 关联逻辑设计存在双重问题：一是使用concat(first_name, last_name, address)拼接字段作为关联键，既没有分隔符会引发匹配错误（例如first_name='TomLo', last_name='cke', address='50 Market St'的拼接结果和first_name='Tom', last_name='Locke', address='50 Market St'完全一致，会产生错误匹配），二是拼接生成的派生列无法利用表上已有的原始字段索引，900万行的表B需要逐行实时计算拼接值，CPU开销极高。
• 优化器无法生成最优执行计划：表A仅3000行属于极小维度表，表B是900万行的事实大表，正常场景下优化器会自动选择小表驱动大表的HASH JOIN或嵌套循环连接，但使用派生拼接列做关联时，优化器无法识别原始关联字段的统计信息（比如字段区分度、数据分布），很容易选错JOIN顺序、关联算法，甚至触发大表全表扫描+磁盘临时表交换，IO和计算量陡增。
• 冗余计算放大性能损耗：两个子查询都提前做了全量字符串拼接，尤其是表B的900万行拼接属于CPU密集型操作，单独运行子查询速度快是因为仅需完成单表扫描+字段计算，不需要做跨表匹配；进入JOIN阶段后，这些拼接生成的长字符串还要参与哈希计算、内存排序，内存不足时会反复刷写磁盘，耗时会指数级上升。

• 替换关联逻辑，直接使用原始字段关联：彻底放弃拼接列的写法，从根源避免匹配错误和额外计算开销，基础写法如下：

SELECT *
FROM table_A A
INNER JOIN table_B B
ON A.first_name = B.first_name
AND A.last_name = B.last_name
AND A.address = B.address;

如果存在字段首尾空格导致匹配失败的情况，可以在关联条件中对字段加trim()处理，例如trim(A.first_name) = trim(B.first_name)，不要通过拼接字段实现关联。

• 给大表建立适配关联的联合索引：针对900万行的表B，在三个关联字段上建立联合索引，将全表扫描转换为索引精准查找，性能可提升2~3个数量级，建索引参考语句如下：

-- 可根据字段区分度调整顺序，区分度越高（重复值越少）的字段越靠前
CREATE INDEX idx_b_join_key ON table_B(first_name, last_name, address);

如果业务查询只需要表B的固定几个字段，可以将这些字段补充到联合索引中做成覆盖索引，避免索引命中后的回表查询，速度会进一步提升。

• 显式指定小表驱动大表的执行策略：如果数据库支持执行Hint（例如MySQL、PostgreSQL、Hive、Spark SQL等均支持），可以强制指定3000行的表A作为驱动表，执行逻辑会变为遍历表A的每一组关联键，直接到表B的索引中检索匹配行，全程不需要全量扫描表B，正常场景下数秒即可返回结果。
• 分布式数仓额外优化：如果使用的是ClickHouse、Spark SQL、BigQuery等分布式分析引擎，可以对两个表按照三个关联字段设置分桶/重分布规则，将相同关联键的数据调度到同一个计算节点，避免跨节点数据Shuffle的网络传输开销。

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