如何优化关联三表统计userID的MySQL查询？大库查询过慢

需求说明 #

需要统计满足以下条件的去重userID数量：

• 从records表筛选指定日期范围内的RefID
• 通过这些RefID匹配refs表对应的AddressID
• 统计addresses表中匹配该AddressID且userID以174%开头的去重userID数

原查询问题 #

原使用多层IN子查询的语句在大数据量下运行极慢，原语句如下：

select count(DISTINCT (userID)) 
from db.addresses t 
where t.AddressID IN (
    select AddressID 
    from db.refs c 
    where c.RefID IN (
        select RefID 
        from db.records r         
        where r.StartDate >= '2022-06-30'
        and r.StartDate < '2022-07-01'
        )
    ) 
and t.userID like '174%';

多层IN子查询在数据量较大时，MySQL难以生成高效执行计划，容易触发全表扫描，导致查询卡顿。

优化后的查询语句 #

改用JOIN关联表的方式替代子查询，执行效率会显著提升：

SELECT COUNT(DISTINCT a.userID)
FROM db.records r
JOIN db.refs c ON r.RefID = c.RefID
JOIN db.addresses a ON c.AddressID = a.AddressID
WHERE r.StartDate >= '2022-06-30'
  AND r.StartDate < '2022-07-01'
  AND a.userID LIKE '174%';

优化原理 #

1. JOIN替代子查询：MySQL对JOIN的执行优化更成熟，能更好地利用表间的关联索引，减少不必要的数据扫描
2. 前置过滤条件：先从records表筛选出日期范围内的数据，再关联后续表，大幅缩减需要处理的数据量

索引优化建议 #

为了进一步提升大数据量下的查询速度，建议创建以下联合索引：

• 给records表创建StartDate+RefID的联合索引：

  CREATE INDEX idx_records_startdate_refid ON db.records(StartDate, RefID);
  

• 给refs表创建RefID+AddressID的联合索引：

  CREATE INDEX idx_refs_refid_addressid ON db.refs(RefID, AddressID);
  

• 给addresses表创建AddressID+userID的联合索引：

  CREATE INDEX idx_addresses_addressid_userid ON db.addresses(AddressID, userID);
  

这些索引可以让MySQL直接通过索引定位匹配的数据，避免全表扫描，大幅缩短查询时间。

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