MySQL关联查询执行过慢（耗时超100秒）求助排查

原查询的核心问题 #

1. 关联子查询重复执行：你写的子查询是关联子查询，主查询每返回一条结果，就会单独执行一次子查询，相当于多次扫描customer_migration_details表，这是导致查询慢的直接原因。而且子查询里的GROUP BY accounts.email和LIMIT 1完全多余——已经通过Assigned_to = accounts.email限定了单个用户，直接COUNT(ID)就能得到结果。
2. RIGHT JOIN逻辑冗余：原查询用了RIGHT JOIN，但WHERE子句里加了customer_migration_details.Assigned_to = accounts.email和date_opened过滤条件，这会把RIGHT JOIN自动转成INNER JOIN（只有匹配到customer_migration_details数据的accounts才会被保留），写法完全没必要。
3. 不必要的JOIN后聚合：先JOIN两张表再GROUP BY，会先生成大量中间数据，再做聚合，浪费资源。应该先在customer_migration_details里统计好每个销售的客户数，再关联accounts表拿用户信息。
4. 缺失关键索引：如果customer_migration_details没有针对date_opened和Assigned_to的联合索引，查询时会全表扫描，数据量大时必然慢。

优化后的查询语句 #

方案1：用JOIN+聚合替代子查询

SELECT
    a.first_name,
    a.last_name,
    a.email,
    COUNT(cmd.ID) AS `New Customers`
FROM
    accounts a
INNER JOIN customer_migration_details cmd 
    ON a.email = cmd.Assigned_to
WHERE
    cmd.date_opened BETWEEN '2022-01-01' AND '2022-12-31'
    AND a.role LIKE '%Sales%'
GROUP BY
    a.email, a.first_name, a.last_name;

方案2：先统计再关联（适合大表场景）

如果customer_migration_details数据量极大，可以先预统计每个销售的客户数，再关联用户表：

WITH customer_counts AS (
    SELECT
        Assigned_to,
        COUNT(ID) AS customer_count
    FROM customer_migration_details
    WHERE date_opened BETWEEN '2022-01-01' AND '2022-12-31'
    GROUP BY Assigned_to
)
SELECT
    a.first_name,
    a.last_name,
    a.email,
    COALESCE(cc.customer_count, 0) AS `New Customers`
FROM accounts a
LEFT JOIN customer_counts cc ON a.email = cc.Assigned_to
WHERE a.role LIKE '%Sales%';

注：如果你的数据库不支持CTE（如MySQL 5.7及以下），可以把CTE换成子查询。

必须添加的索引 #

1. 给customer_migration_details建联合索引，覆盖过滤和聚合字段：

CREATE INDEX idx_cmd_date_assigned ON customer_migration_details(date_opened, Assigned_to, ID);

这个索引能让数据库直接通过索引过滤日期、匹配销售邮箱，同时直接获取ID进行计数，不需要回表查原数据。

2. 给accounts建索引，加速角色过滤和关联：

CREATE INDEX idx_accounts_role_email ON accounts(role, email, first_name, last_name);

这个索引能快速筛选出所有Sales角色的用户，同时直接从索引中获取需要返回的用户信息，避免全表扫描。

验证优化效果 #

执行EXPLAIN + 你的查询语句，查看执行计划：

• 确认type列不是ALL（全表扫描），最好是range或ref
• 确认key列显示的是我们创建的索引
• 查看rows列，扫描的行数是否大幅减少

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