本文针对 3000 万级 participants 表场景，详解如何通过合理 join 顺序、复合索引设计及可选索引提示（index hint），在 mysql 层高效统计“未删除用户 + 活跃未删除课程”的有效参与人数，避免全表扫描与中间结果膨胀。
在处理 courses（30k）、users（30k）和 participants（30M）三表关联统计时，性能瓶颈往往不在于逻辑复杂度，而在于执行计划是否能尽早过滤、索引是否覆盖 JOIN 与 WHERE 条件、以及中间结果集是否可控。直接使用子查询或嵌套 IN 容易导致临时表膨胀（如先生成全部活跃课程 ID 再 JOIN），尤其当 participants 表超大时，会显著拖慢 COUNT(DISTINCT ...)。
推荐写法：显式三表 INNER JOIN + 条件下推
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';
该写法优势在于：
- 语义清晰：明确表达“课程→参与者→用户”的业务链路；
- 条件下推友好：MySQL 优化器可将 WHERE 中的过滤条件（如 c.active = 1、u.deleted_at IS NULL）尽可能提前应用到对应表的访问阶段；
- 避免隐式笛卡尔积：相比 FROM participants, courses, users 等写法，显式 ON 子句更利于优化器选择驱动表。
⚠️ 注意：participants.participant_type = 'Eloomi\Models\User' 是关键筛选条件，必须纳入 WHERE，不可遗漏。
核心索引策略：让 JOIN 和 FILTER 都走索引
仅靠 SQL 改写不够，必须配合精准索引。以下三组索引构成完整加速链：
1. courses 表：优先缩小驱动表范围
ALTER TABLE courses ADD KEY idx_active_deleted (active, deleted_at);
- ✅ 覆盖 WHERE c.active = 1 AND c.deleted_at IS NULL；
- ✅ 隐含包含主键 id，可高效用于 JOIN participants ON c.id = p.course_id；
- ✅ 因 courses 仅 30k 行且过滤后更少，它极可能成为最优驱动表（即最先被访问）。
2. participants 表：高效承接课程 ID 并复用 participant_type 过滤
ALTER TABLE participants ADD KEY idx_course_type_pid (course_id, participant_type, participant_id);
- ✅ course_id 作为首列，支持与 courses.id 的等值 JOIN；
- ✅ participant_type 紧随其后，使 WHERE p.participant_type = ... 可在索引内完成过滤，无需回表；
- ✅ participant_id 作为第三列，既满足 JOIN users ON p.participant_id = u.id，又为 COUNT(DISTINCT p.participant_id) 提供有序/可跳过重复值的基础（虽非绝对去重优化，但大幅减少扫描行数）。
3. users 表：加速基于 ID 的 JOIN + deleted_at 判断
ALTER TABLE users ADD KEY idx_id_deleted (id, deleted_at);
- ✅ id 是主键，此索引本质是“带 deleted_at 的主键覆盖索引”；
- ✅ JOIN ... ON p.participant_id = u.id 可直接利用 id 列定位；
- ✅ WHERE u.deleted_at IS NULL 可在索引中快速判定，避免回表查 deleted_at 字段。
? 验证效果：执行 EXPLAIN FORMAT=JSON 查看 key 和 rows 字段，确认三表均命中上述索引，且 rows 值远小于表总行数。
进阶技巧：必要时使用 INDEX HINT 强制索引选择
若 EXPLAIN 显示 users 表仍使用主键（PRIMARY）而非 idx_id_deleted，可添加 USE INDEX 提示（仅当确认该索引更优时）：
-- 在 users 表 JOIN 子句中显式指定 INNER JOIN users AS u USE INDEX (idx_id_deleted) ON p.participant_id = u.id
⚠️ 警告：USE INDEX 是强干预，应以 EXPLAIN 对比为依据；过度依赖可能在未来 MySQL 版本升级或数据分布变化后失效。
性能对比与关键结论
| 方案 | 数据库压力 | 应用层开销 | 可维护性 | 推荐度 |
|---|---|---|---|---|
| 全 JOIN + 合理索引 | ★★☆☆☆（低） | 零 | 高（SQL 单一） | ⭐⭐⭐⭐⭐ |
| WHERE IN (subquery) | ★★★★☆（高，IN 列表膨胀） | 低 | 中（需拼接 ID 列表） | ⚠️ 不推荐（>1k 结果时） |
| 应用层分页拉取 + PHP 过滤 | ★☆☆☆☆（极低单次） | ★★★★★（N 次查询+内存计算） | 低（逻辑分散） | ❌ 拒绝（30M 表无法分页枚举） |
终极建议：
✅ 优先采用三表 JOIN + 上述三索引组合；
✅ 务必用 EXPLAIN 验证执行计划，重点关注 type: ref 或 range、key 字段值、rows 是否显著下降；
✅ 若 participants 表 participant_type 值高度倾斜（如 99% 是 'Eloomi\Models\User'），可考虑将其移出索引，改用 WHERE 过滤并优化其他列；
✅ 生产环境上线前，在副本库用真实数据压测 QPS 与响应时间。
通过将“过滤下推”与“索引覆盖”深度结合，该方案可在毫秒级返回结果，彻底规避应用层遍历或中间结果集爆炸风险。
