优化复杂MySQL查询 降低campaign调研响应数据导出查询耗时

问题核心根源 #

• 原查询使用了6个关联子查询，外层每返回1行就要执行6次子查询，limit 500就累计执行3000次查询，全量5万行时会执行30万次，是耗时过高的核心原因
• 现有索引仅为单列索引，无法覆盖查询所需的过滤、排序、返回字段，每次查询都需要回表读取数据，IO成本极高
• 表使用MyISAM存储引擎，查询时会触发表锁，阻塞其他读写操作，且缓存效率远低于InnoDB

优化方案 #

1. 新增覆盖索引（成本最低，见效最快） #

给两张表新增联合覆盖索引，所有查询逻辑可直接通过索引完成，无需回表：

-- 响应表覆盖索引，包含所有过滤、分组、排序、返回字段
ALTER TABLE campaign_survey_responses ADD INDEX idx_campaign_msisdn_question(campaign_id, msisdn, question_id, answer);
-- 问题表覆盖索引，包含关联、过滤字段
ALTER TABLE campaign_survey_questions ADD INDEX idx_parent_sort(id, parent_id, sort_order);

2. 改写SQL为条件聚合（核心优化） #

将关联子查询改写为单次扫描+条件聚合，仅需扫描1次响应表即可完成所有计算，性能提升至少10倍以上：

SELECT 
    a.msisdn,
    GROUP_CONCAT(CASE WHEN a.question_id = 14750 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS answer,
    GROUP_CONCAT(CASE WHEN a.question_id = 14751 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a1,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5128 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur1,
    GROUP_CONCAT(CASE WHEN a.question_id = 14768 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a2,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5108 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur2,
    GROUP_CONCAT(CASE WHEN a.question_id = 14785 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a3,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5148 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur3
FROM campaign_survey_responses a
-- 过滤出仅包含父问题14750响应的用户，和原查询逻辑完全一致
INNER JOIN (
    SELECT DISTINCT msisdn 
    FROM campaign_survey_responses 
    WHERE campaign_id = 11559 AND question_id = 14750
) filter ON a.msisdn = filter.msisdn
LEFT JOIN campaign_survey_questions q ON a.question_id = q.id
WHERE a.campaign_id = 11559
GROUP BY a.msisdn
LIMIT 500;

3. 存储引擎优化 #

将表从MyISAM改为InnoDB，解决表锁问题，同时利用InnoDB的缓冲池提升热点数据的查询效率：

ALTER TABLE campaign_survey_responses ENGINE=InnoDB;

4. 导出效率优化 #

如果仅需要导出CSV文件，直接使用MySQL原生导出语法，避免应用层数据传输开销，导出全量5万行仅需几秒：

SELECT 
    a.msisdn,
    GROUP_CONCAT(CASE WHEN a.question_id = 14750 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS answer,
    GROUP_CONCAT(CASE WHEN a.question_id = 14751 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a1,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5128 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur1,
    GROUP_CONCAT(CASE WHEN a.question_id = 14768 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a2,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5108 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur2,
    GROUP_CONCAT(CASE WHEN a.question_id = 14785 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS a3,
    GROUP_CONCAT(CASE WHEN q.parent_id = 5148 AND q.sort_order = 0 THEN COALESCE(a.answer, 'skip') END ORDER BY a.question_id SEPARATOR ',') AS sur3
FROM campaign_survey_responses a
INNER JOIN (
    SELECT DISTINCT msisdn 
    FROM campaign_survey_responses 
    WHERE campaign_id = 11559 AND question_id = 14750
) filter ON a.msisdn = filter.msisdn
LEFT JOIN campaign_survey_questions q ON a.question_id = q.id
WHERE a.campaign_id = 11559
GROUP BY a.msisdn
INTO OUTFILE '/var/lib/mysql-files/survey_result.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

注意MySQL 5.7默认仅允许导出到/var/lib/mysql-files目录，需确保该目录存在且MySQL有写入权限。

额外优化建议 #

如果该导出任务是定期执行的，可以新增一张汇总表，用定时任务提前将聚合结果写入汇总表，导出时直接查询汇总表即可，响应时间可以降到毫秒级。

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