本文介绍如何通过合理设计 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 持续验证执行计划，并根据真实数据分布微调索引顺序。
