本文介绍如何通过合理设计 join 查询与针对性索引，高效统计“未被删除的用户”中、至少参与过一门“已激活且未被删除”的课程的人数，避免全表扫描与中间结果膨胀。

在处理 courses（约3000万行）、users（约3万行）和 participants（约3千行）三表关联统计时，核心目标是：快速获取满足以下全部条件的用户去重数量：

• 用户 users.deleted_at IS NULL
• 课程 courses.active = 1 AND courses.deleted_at IS NULL
• 关联记录 participants.participant_type = 'Eloomi\\Models\\User'
• 且该用户通过 participants 表与上述课程存在有效绑定

直觉上，先过滤再连接（filter-before-join）比先连接后过滤（join-then-filter）更高效。MySQL 的查询优化器虽能自动重排执行顺序，但其效果高度依赖索引支持。以下是经过结构化优化的完整方案：

✅ 推荐查询写法（语义清晰 + 易于索引利用）

SELECT COUNT(DISTINCT p.participant_id)
FROM courses AS c
INNER JOIN participants AS p ON c.id = p.course_id
INNER JOIN users AS u ON p.participant_id = u.id
WHERE u.deleted_at IS NULL
  AND c.active = 1 
  AND c.deleted_at IS NULL
  AND p.participant_type = 'Eloomi\\Models\\User';

该写法明确表达了业务逻辑（三表自然关联 + 精确过滤），同时为后续索引设计提供清晰依据。

✅ 关键索引设计（按执行路径逐层优化）

表名	推荐索引	说明
courses	INDEX(active, deleted_at)	最优先创建。可快速定位满足 active=1 AND deleted_at IS NULL 的少量课程（即使总行数达3000万，符合条件的可能仅数千）。复合索引首列 active 支持等值筛选，第二列 deleted_at 支持 IS NULL 条件（MySQL 8.0+ 对 IS NULL 在二级索引中高效支持）。因 InnoDB 聚簇索引特性，该索引隐含包含主键 id，可直接用于 JOIN participants。
participants	INDEX(course_id, participant_type, participant_id)	按 course_id（外键）升序排列，确保能用上 courses 过滤后的 id 集合快速查找；加入 participant_type 实现覆盖过滤条件，避免回表；末尾 participant_id 用于后续关联 users 表及 COUNT(DISTINCT ...) 计算。
users	INDEX(id, deleted_at)	显式引导优化器使用该二级索引（而非默认主键聚簇索引）完成 p.participant_id = u.id AND u.deleted_at IS NULL 的联合查找。若发现执行计划仍走主键扫描，可添加索引提示：JOIN users AS u USE INDEX (id, deleted_at)。

? 验证索引有效性：执行 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN 查看实际访问类型（应为 ref 或 range，避免 ALL/index 全扫）；重点关注 key 列是否命中预期索引，rows 是否显著减少。

⚠️ 注意事项与进阶建议

• 避免子查询嵌套：如原方案中 (SELECT ... FROM courses WHERE ...) 子查询生成临时表，易引发性能瓶颈。现代 MySQL 优化器对显式 JOIN 的处理通常优于派生表。
• COUNT(DISTINCT) 的代价：当匹配用户量极大时（如超百万），DISTINCT 去重本身有开销。若业务允许近似统计，可考虑 APPROX_COUNT_DISTINCT()（MySQL 8.0.19+）。
• 分区与归档策略：若 deleted_at IS NOT NULL 的历史数据占比高，可对 users 和 courses 表按 deleted_at 分区，将无效数据物理隔离。
• 应用层兜底场景：仅当数据库层优化已达极限（如 participants 表无合适索引、或 participant_type 值分布极不均衡导致索引失效），才考虑分批拉取 course_id 列表，在 PHP 中构造 WHERE course_id IN (...) 批量查询 —— 但需严格控制 IN 列表长度（建议 ≤ 1000），并启用 prepared statement 防止 SQL 注入。

✅ 总结

高效达成该统计的核心在于：以最小集合作为驱动表（courses），通过精准复合索引实现“先筛后连”，全程避免中间结果膨胀。三张表各一个针对性复合索引，配合简洁 JOIN 查询，即可在毫秒级响应大规模数据关联计数。务必通过 EXPLAIN 持续验证执行计划，并根据真实数据分布微调索引顺序。