SQL左连接含OR语句致性能极差，如何改写优化？

问题根源 #

你当前的JOIN条件包含9个OR组合，数据库无法利用索引，只能进行全表扫描甚至笛卡尔积运算，导致查询超时。这种多字段OR的连接逻辑会让数据库无法找到高效的执行路径。

优化思路 #

将两张表的多手机号字段行转列，把每个用户的所有有效手机号拆成单独行，再通过手机号做等值连接，最后去重统计用户数。这样可以让数据库利用手机号字段的索引，彻底避免笛卡尔积。

具体SQL实现 #

方案1：支持UNPIVOT的数据库（如Oracle、SQL Server）

WITH conn_phones AS (
    SELECT 
        CU.user_id, -- 替换为表中唯一标识用户的字段（如主键）
        phone
    FROM CONN_UNIVERSE CU
    UNPIVOT (
        phone FOR phone_type IN (
            TELEPHONE_NUMBER,
            PRIMARY_PHONE_NUMBER,
            ALTERNATE_PHONE_NUMBER
        )
    ) AS unpvt
    WHERE phone IS NOT NULL AND phone <> '' -- 过滤空手机号
),
disc_phones AS (
    SELECT 
        DU.user_id,
        phone
    FROM DISC_UNIVERSE DU
    UNPIVOT (
        phone FOR phone_type IN (
            TELEPHONE_NUMBER,
            PRIMARY_PHONE_NUMBER,
            ALTERNATE_PHONE_NUMBER
        )
    ) AS unpvt
    WHERE phone IS NOT NULL AND phone <> ''
)
SELECT COUNT(DISTINCT cp.user_id) AS reconnected_users
FROM conn_phones cp
INNER JOIN disc_phones dp ON cp.phone = dp.phone;

方案2：不支持UNPIVOT的数据库（如MySQL、PostgreSQL）

用UNION ALL手动实现行转列：

WITH conn_phones AS (
    SELECT user_id, TELEPHONE_NUMBER AS phone FROM CONN_UNIVERSE WHERE TELEPHONE_NUMBER IS NOT NULL AND TELEPHONE_NUMBER <> ''
    UNION ALL
    SELECT user_id, PRIMARY_PHONE_NUMBER AS phone FROM CONN_UNIVERSE WHERE PRIMARY_PHONE_NUMBER IS NOT NULL AND PRIMARY_PHONE_NUMBER <> ''
    UNION ALL
    SELECT user_id, ALTERNATE_PHONE_NUMBER AS phone FROM CONN_UNIVERSE WHERE ALTERNATE_PHONE_NUMBER IS NOT NULL AND ALTERNATE_PHONE_NUMBER <> ''
),
disc_phones AS (
    SELECT user_id, TELEPHONE_NUMBER AS phone FROM DISC_UNIVERSE WHERE TELEPHONE_NUMBER IS NOT NULL AND TELEPHONE_NUMBER <> ''
    UNION ALL
    SELECT user_id, PRIMARY_PHONE_NUMBER AS phone FROM DISC_UNIVERSE WHERE PRIMARY_PHONE_NUMBER IS NOT NULL AND PRIMARY_PHONE_NUMBER <> ''
    UNION ALL
    SELECT user_id, ALTERNATE_PHONE_NUMBER AS phone FROM DISC_UNIVERSE WHERE ALTERNATE_PHONE_NUMBER IS NOT NULL AND ALTERNATE_PHONE_NUMBER <> ''
)
SELECT COUNT(DISTINCT cp.user_id) AS reconnected_users
FROM conn_phones cp
INNER JOIN disc_phones dp ON cp.phone = dp.phone;

额外性能建议 #

• 在两张表的TELEPHONE_NUMBER、PRIMARY_PHONE_NUMBER、ALTERNATE_PHONE_NUMBER字段上分别建立单独索引，或者建立包含这三个字段的复合索引。
• 如果查询频率高，可以考虑将行转列后的手机号数据持久化（如建立物化视图），进一步提升查询速度。

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