MySQL多子查询响应过慢，如何优化电话号码匹配查询？

原查询因多次独立的NOT IN子查询导致重复扫描contacts表，性能低下。以下是两种优化方案，搭配索引调整可大幅提升查询速度：

方案一：使用NOT EXISTS + UNION ALL合并号码集合 #

将contacts表中所有非空的电话号码合并为一个集合，通过NOT EXISTS判断号码是否不在该集合中，仅需扫描contacts表一次：

SELECT 
    from_number AS phone_number, 
    COUNT(from_number) AS number_of_calls
FROM calllist cl
WHERE 
    received_at BETWEEN '$startDate' AND DATE_ADD('$endDate', INTERVAL 1 DAY)
    AND NOT EXISTS (
        SELECT 1
        FROM (
            SELECT businessPhone1 AS phone FROM contacts WHERE businessPhone1 IS NOT NULL
            UNION ALL
            SELECT businessPhone2 AS phone FROM contacts WHERE businessPhone2 IS NOT NULL
            UNION ALL
            SELECT homePhone1 AS phone FROM contacts WHERE homePhone1 IS NOT NULL
            UNION ALL
            SELECT homePhone2 AS phone FROM contacts WHERE homePhone2 IS NOT NULL
            UNION ALL
            SELECT mobilePhone AS phone FROM contacts WHERE mobilePhone IS NOT NULL
        ) AS all_contacts
        WHERE all_contacts.phone = cl.from_number
    )
GROUP BY phone_number
ORDER BY number_of_calls DESC
LIMIT 10

注：UNION ALL比UNION更高效，无需额外去重操作

方案二：使用LEFT JOIN + IS NULL #

通过左关联合并后的号码集合，筛选未匹配的记录，逻辑与方案一一致，部分数据库优化器对该写法的执行效率更友好：

SELECT 
    cl.from_number AS phone_number, 
    COUNT(cl.from_number) AS number_of_calls
FROM calllist cl
LEFT JOIN (
    SELECT businessPhone1 AS phone FROM contacts WHERE businessPhone1 IS NOT NULL
    UNION ALL
    SELECT businessPhone2 AS phone FROM contacts WHERE businessPhone2 IS NOT NULL
    UNION ALL
    SELECT homePhone1 AS phone FROM contacts WHERE homePhone1 IS NOT NULL
    UNION ALL
    SELECT homePhone2 AS phone FROM contacts WHERE homePhone2 IS NOT NULL
    UNION ALL
    SELECT mobilePhone AS phone FROM contacts WHERE mobilePhone IS NOT NULL
) AS all_contacts ON all_contacts.phone = cl.from_number
WHERE 
    cl.received_at BETWEEN '$startDate' AND DATE_ADD('$endDate', INTERVAL 1 DAY)
    AND all_contacts.phone IS NULL
GROUP BY phone_number
ORDER BY number_of_calls DESC
LIMIT 10

关键索引优化 #

添加以下索引可进一步提升查询效率：

• 给calllist表创建复合索引：CREATE INDEX idx_calllist_received_from ON calllist(received_at, from_number);
  该索引可直接覆盖日期过滤和号码关联的需求，避免全表扫描。
• 给contacts表的各电话号码列分别创建索引：

  CREATE INDEX idx_contacts_biz1 ON contacts(businessPhone1);
  CREATE INDEX idx_contacts_biz2 ON contacts(businessPhone2);
  CREATE INDEX idx_contacts_home1 ON contacts(homePhone1);
  CREATE INDEX idx_contacts_home2 ON contacts(homePhone2);
  CREATE INDEX idx_contacts_mobile ON contacts(mobilePhone);
  

  这些索引能让UNION ALL中的子查询快速定位非空号码，无需扫描整个contacts表。

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