两张约1600万条记录的表LEFT JOIN查询过慢求助

核心问题分析 #

LEFT JOIN 引入后，由于patients和receivable_transactions是一对多关系，关联后数据行数大幅膨胀，聚合阶段需要处理的数据集远大于原查询，这是性能暴跌的主因。同时当前查询逻辑存在错误：原查询的count(*)统计的是patients表的行数，而LEFT JOIN后count(*)统计的是关联后的总行数（包含receivable_transactions的重复行），会导致计算出的'Sent Back'比例失真。

1. 修正逻辑并避免数据膨胀 #

如果添加LEFT JOIN是为了后续引入receivable_transactions的统计字段，先对receivable_transactions按关联字段预聚合，再和patients关联，彻底解决一对多导致的行数暴增问题：

-- 预聚合receivable_transactions的统计数据（按需调整统计字段）
WITH rec_agg AS (
    SELECT 
        patient_seq,
        COUNT(*) AS total_transactions -- 示例统计项
    FROM receivable_transactions
    GROUP BY patient_seq
)
SELECT
    p.radiologist, 
    CONCAT((SUM(p.qa_report_sendback)/COUNT(*))*100, '%') AS 'Sent Back'
    -- 可添加receivable的统计字段，比如 SUM(rec_agg.total_transactions) AS total_records
FROM patients AS p
INNER JOIN radiologist AS rad ON p.radiologist = rad.lastname
LEFT JOIN rec_agg ON p.seq = rec_agg.patient_seq
WHERE rad.company = 1
GROUP BY p.radiologist;

如果暂时不需要receivable_transactions的字段，直接移除该LEFT JOIN即可恢复原查询性能。

2. 优化索引策略 #

针对radiologist表 #

添加联合索引(company, lastname)，让数据库快速过滤出company=1的放射科医生，再关联patients表，缩小patients的扫描范围：

CREATE INDEX idx_rad_company_lastname ON radiologist(company, lastname);

针对patients表 #

创建覆盖索引(radiologist, qa_report_sendback)，让数据库直接从索引中获取聚合所需字段，无需回表查询：

CREATE INDEX idx_pat_rad_sendback ON patients(radiologist, qa_report_sendback);

3. 调整查询执行顺序 #

先筛选出符合条件的放射科医生列表，再查询patients表，避免全表扫描patients：

SELECT
    p.radiologist, 
    CONCAT((SUM(p.qa_report_sendback)/COUNT(*))*100, '%') AS 'Sent Back'
FROM patients AS p
WHERE p.radiologist IN (
    SELECT lastname FROM radiologist WHERE company = 1
)
-- 如需关联receivable，替换为预聚合后的表
LEFT JOIN rec_agg ON p.seq = rec_agg.patient_seq
GROUP BY p.radiologist;

4. 验证数据膨胀程度 #

执行以下语句查看关联后的总行数，确认膨胀规模：

SELECT COUNT(*) 
FROM patients AS p
INNER JOIN radiologist AS rad ON p.radiologist = rad.lastname
LEFT JOIN receivable_transactions AS rec ON p.seq = rec.patient_seq
WHERE rad.company = 1;

对比原查询数据集大小：

SELECT COUNT(*) 
FROM patients AS p
INNER JOIN radiologist AS rad ON p.radiologist = rad.lastname
WHERE rad.company = 1;

如果前者是后者的数倍甚至数十倍，说明数据膨胀是性能问题的核心，预聚合receivable_transactions是最优解决方案。

内容的提问来源于stack exchange，提问作者J.T.