优化含多SUM(IF())的MySQL多表关联查询性能

问题背景 #

你编写的这个包含7张表关联、使用SUM(IF())统计美国53个州数据的MySQL查询，执行耗时近20分钟，核心瓶颈在于多表关联时的全表扫描，以及聚合计算阶段的大量数据处理开销。

原查询语句 #

SELECT 
    ag.position_id AS 'position_id', 
    ag.npn AS 'NPN', 
    concat(ag.first_name, ' ', ag.last_name) AS 'full_name', 
    lb.name AS 'lob', 
    location_lic AS 'loc', 
    COUNT(DISTINCT lt.id) AS 'RTS_Total', 
    SUM(IF(st.id = 1, 1, 0)) AS 'AK', 
    SUM(IF(st.id = 2, 1, 0)) AS 'AL', 
    SUM(IF(st.id = 3, 1, 0)) AS 'AR', 
    SUM(IF(st.id = 4, 1, 0)) AS 'AZ', 
    SUM(IF(st.id = 5, 1, 0)) AS 'CA', 
    SUM(IF(st.id = 6, 1, 0)) AS 'CO', 
    SUM(IF(st.id = 7, 1, 0)) AS 'CT', 
    SUM(IF(st.id = 8, 1, 0)) AS 'DC', 
    SUM(IF(st.id = 9, 1, 0)) AS 'DE', 
    SUM(IF(st.id = 10, 1, 0)) AS 'FL', 
    SUM(IF(st.id = 11, 1, 0)) AS 'GA', 
    SUM(IF(st.id = 12, 1, 0)) AS 'HI', 
    SUM(IF(st.id = 13, 1, 0)) AS 'IA', 
    SUM(IF(st.id = 14, 1, 0)) AS 'ID', 
    SUM(IF(st.id = 15, 1, 0)) AS 'IL', 
    SUM(IF(st.id = 16, 1, 0)) AS 'IN', 
    SUM(IF(st.id = 17, 1, 0)) AS 'KS', 
    SUM(IF(st.id = 18, 1, 0)) AS 'KY', 
    SUM(IF(st.id = 19, 1, 0)) AS 'LA', 
    SUM(IF(st.id = 20, 1, 0)) AS 'MA', 
    SUM(IF(st.id = 21, 1, 0)) AS 'MD', 
    SUM(IF(st.id = 22, 1, 0)) AS 'ME', 
    SUM(IF(st.id = 23, 1, 0)) AS 'MI', 
    SUM(IF(st.id = 24, 1, 0)) AS 'MN', 
    SUM(IF(st.id = 25, 1, 0)) AS 'MO', 
    SUM(IF(st.id = 26, 1, 0)) AS 'MS', 
    SUM(IF(st.id = 27, 1, 0)) AS 'MT', 
    SUM(IF(st.id = 28, 1, 0)) AS 'NC', 
    SUM(IF(st.id = 29, 1, 0)) AS 'ND', 
    SUM(IF(st.id = 30, 1, 0)) AS 'NE', 
    SUM(IF(st.id = 31, 1, 0)) AS 'NH', 
    SUM(IF(st.id = 32, 1, 0)) AS 'NJ', 
    SUM(IF(st.id = 33, 1, 0)) AS 'NM', 
    SUM(IF(st.id = 34, 1, 0)) AS 'NV', 
    SUM(IF(st.id = 35, 1, 0)) AS 'NY', 
    SUM(IF(st.id = 36, 1, 0)) AS 'OH', 
    SUM(IF(st.id = 37, 1, 0)) AS 'OK', 
    SUM(IF(st.id = 38, 1, 0)) AS 'OR', 
    SUM(IF(st.id = 39, 1, 0)) AS 'PA', 
    SUM(IF(st.id = 40, 1, 0)) AS 'PR', 
    SUM(IF(st.id = 41, 1, 0)) AS 'RI', 
    SUM(IF(st.id = 42, 1, 0)) AS 'SC', 
    SUM(IF(st.id = 43, 1, 0)) AS 'SD', 
    SUM(IF(st.id = 44, 1, 0)) AS 'TN', 
    SUM(IF(st.id = 45, 1, 0)) AS 'TX', 
    SUM(IF(st.id = 46, 1, 0)) AS 'UT', 
    SUM(IF(st.id = 47, 1, 0)) AS 'VA', 
    SUM(IF(st.id = 48, 1, 0)) AS 'VI', 
    SUM(IF(st.id = 49, 1, 0)) AS 'VT', 
    SUM(IF(st.id = 50, 1, 0)) AS 'WA', 
    SUM(IF(st.id = 51, 1, 0)) AS 'WI', 
    SUM(IF(st.id = 52, 1, 0)) AS 'WV', 
    SUM(IF(st.id = 53, 1, 0)) AS 'WY' 
FROM cxprtsapp_licensetracker lt 
INNER JOIN cxprtsapp_agents ag ON ag.id = lt.agent_id_id AND ag.position_status LIKE 'Active' 
LEFT JOIN cxprtsapp_statelobjit slj ON slj.state_id = lt.state_id_id AND ag.lob_id = slj.lob_id 
LEFT JOIN (
    select npn, rtsreport_appointed, rtsreport_licensed, state_id, lob_id 
    from cxprtsapp_rtslob 
    order BY refresh_number desc 
) rts ON rts.state_id = lt.state_id_id and ag.lob_id = rts.lob_id and ag.npn=rts.npn 
INNER JOIN cxprtsapp_location lc ON lc.id = ag.loc_id 
INNER JOIN cxprtsapp_lineofbussiness lb ON lb.id = ag.lob_id 
INNER JOIN cxprtsapp_states st ON st.id = lt.state_id_id 
LEFT JOIN (
    SELECT DISTINCT npn, state_code, status 
    FROM cxprtsapp_pdbappointments 
    WHERE status LIKE 'Appointed' 
) appt ON appt.npn = ag.npn AND appt.state_code = st.state_code 
WHERE certification_id IS NOT NULL 
AND (expiration_date IS NULL OR expiration_date > CURDATE()) 
AND (slj.jit = 1 OR rts.rtsreport_appointed = 1 OR appt.status IS NOT NULL) 
GROUP BY ag.id, ag.lob_id,loc_id 
ORDER BY ag.hire_date DESC;

性能优化方案：添加复合索引 #

我之前处理过类似的慢查询问题，通过为查询中涉及的过滤条件、关联字段、分组字段创建针对性的复合索引，直接把执行时间从10-15分钟压缩到了10-20秒。你可以先执行以下创建索引的SQL语句：

ALTER TABLE cxprtsapp_agents ADD INDEX cxprtsapp_agents_idx_status_id_id_npn_id (position_status,id,lob_id,npn,loc_id);
ALTER TABLE cxprtsapp_licensetracker ADD INDEX cxprtsapp_licensetra_idx_id_id_id_id (agent_id_id,state_id_id,certification_id,id);
ALTER TABLE cxprtsapp_lineofbussiness ADD INDEX cxprtsapp_lineofbuss_idx_id_name (id,name);
ALTER TABLE cxprtsapp_location ADD INDEX cxprtsapp_location_idx_id_lic (id,location_lic);
ALTER TABLE cxprtsapp_statelobjit ADD INDEX cxprtsapp_statelobji_idx_id_id (state_id,lob_id);
ALTER TABLE cxprtsapp_states ADD INDEX cxprtsapp_states_idx_id_code (id,state_code);
ALTER TABLE cxprtsapp_rtslob ADD INDEX cxprtsapp_rtslob_idx_numbe_npn_appoi_licen_id_id (refresh_number,npn,rtsreport_appointed,rtsreport_licensed,state_id,lob_id);
ALTER TABLE cxprtsapp_pdbappointments ADD INDEX cxprtsapp_pdbappoint_idx_status_npn_code (status,npn,state_code);

索引设计思路 #

这些复合索引不是随便加的，核心逻辑是：

• 优先包含WHERE子句里的过滤条件，让MySQL快速筛选出符合要求的数据，避免全表扫描
• 加入多表JOIN的核心关联字段，加速表与表之间的匹配过程
• 包含GROUP BY和查询返回的字段，实现索引覆盖，不用再回表读取原始数据，大幅减少IO开销

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