多表COUNT(*)查询性能优化：单项目订阅数统计提速方案

一、优先优化索引，杜绝全表扫描 #

• 给关联核心字段加联合索引，精准命中数据：
  • participant_subscription_project表：建(project_id, participant_subscription_id)联合索引，直接定位目标项目的所有订阅关联记录，避免全表遍历
  • participant_subscriptions表：建(id, subscription_detail_id)联合索引，关联时快速拿到对应的订阅详情ID
  • subscription_details表：如果统计的是有效订阅，加(id, is_active)联合索引，过滤无效数据更快
• 清理冗余单字段索引，减少数据库的索引维护开销

二、重构SQL，干掉多层子查询 #

把原来的4层子查询改成JOIN关联，同时提前过滤目标项目，减少数据处理量：

SELECT COUNT(DISTINCT ps.id) AS total_subscriptions
FROM projects p
JOIN participant_subscription_project psp ON p.id = psp.project_id
JOIN participant_subscriptions ps ON psp.participant_subscription_id = ps.id
JOIN subscription_details sd ON ps.subscription_detail_id = sd.id
WHERE p.id = 123 -- 替换成你的目标项目ID
  AND sd.is_active = 1; -- 按需保留，统计有效订阅时用

• 用COUNT(DISTINCT)替代子查询去重，减少多次子查询的嵌套开销
• 先通过WHERE过滤目标项目，后续关联的数据集直接缩小，效率自然提升

三、盯着执行计划调优 #

• 看执行计划里的type列，关联操作要确保是ref或range类型，要是出现ALL（全表扫描），就得查原因：
  • 检查字段类型是否匹配（比如项目ID是INT，别传字符串参数）
  • 要是索引选择性太差（比如某个字段重复值太多），就调整索引结构

四、分页场景适配技巧 #

• 别把统计总条数和分页查询揉在一个SQL里，拆分两步走：先跑上面优化后的统计SQL拿总条数，再单独执行分页查询
• 分页查询用JOIN替代子查询，同时加索引支持排序分页：

SELECT ps.*
FROM participant_subscription_project psp
JOIN participant_subscriptions ps ON psp.participant_subscription_id = ps.id
WHERE psp.project_id = 123 -- 目标项目ID
ORDER BY ps.create_time DESC
LIMIT 0, 10; -- 偏移量和每页条数

• 给participant_subscription_project表加(project_id, participant_subscription_id, create_time)联合索引，让排序分页直接走索引，不用做文件排序

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