本文介绍如何在 mysql 中高效统计“未被删除的用户”与“已激活且未被删除的课程”之间的关联数量，通过优化 join 顺序、复合索引设计及必要时的索引提示，显著提升 3000 万级参与者表的聚合查询性能。
在处理 courses（约 3 万行）、users（约 3 万行）和 participants（约 3000 万行）三表关联统计时，核心目标是：快速计算满足以下全部条件的唯一用户数：
- 用户 users.deleted_at IS NULL
- 课程 courses.active = 1 AND courses.deleted_at IS NULL
- 关联记录 participants.participant_type = 'Eloomi\\Models\\User'
直觉上，先过滤再连接（filter-before-join）比全量 JOIN 后 WHERE 更高效——而 MySQL 的查询优化器能否自动实现这一点，高度依赖索引结构与统计信息准确性。
✅ 推荐 SQL 写法（语义清晰 + 易于优化）
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 优化器提供了明确的过滤路径。关键在于：让最小的、过滤性最强的表（courses）成为驱动表——因 active = 1 AND deleted_at IS NULL 可能仅保留少量活跃课程，大幅减少后续连接基数。
? 必备复合索引（按执行顺序部署）
为使上述查询真正高效，需建立以下三个针对性索引（注意列序！）：
-- 1. 优化 courses 过滤：覆盖 WHERE 条件，并隐含主键用于 JOIN ALTER TABLE courses ADD KEY idx_active_deleted (active, deleted_at); -- 2. 优化 participants 连接：以 course_id 驱动，同时覆盖 participant_type 过滤， -- 并包含 participant_id（供后续 JOIN users 使用） ALTER TABLE participants ADD KEY idx_course_type_id (course_id, participant_type, participant_id); -- 3. 优化 users 连接：确保 participant_id → users.id 查找高效， -- 同时支持 deleted_at 过滤（避免回表） ALTER TABLE users ADD KEY idx_id_deleted (id, deleted_at);
? 原理说明：idx_active_deleted 是一个覆盖索引，MySQL 可仅扫描索引即可完成 courses 表的过滤与主键获取；idx_course_type_id 让 participants 能基于 course_id 快速定位，再用 participant_type 精筛，最后直接拿到 participant_id；idx_id_deleted 则确保 u.id = ? 查找时能顺便验证 deleted_at IS NULL，无需回查数据行。
⚠️ 进阶调优：当优化器未按预期选择索引时
若 EXPLAIN 显示 users 表仍走主键（PRIMARY）而非 idx_id_deleted，可显式提示（index hint）：
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 USE INDEX (idx_id_deleted) 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';
⚠️ 注意：USE INDEX 是强提示，仅在确认索引更优时使用；过度依赖可能掩盖统计信息陈旧等问题。建议配合 ANALYZE TABLE courses, participants, users; 更新统计信息。
? 验证与迭代方法
-
始终运行 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN（5.7），观察：
- 表访问顺序是否为 courses → participants → users
- key 列是否命中上述新建索引
- rows 预估是否显著下降（如 courses 从 30k → 数百）
-
分步验证过滤效果：
-- 检查活跃课程数量（应远小于 total） SELECT COUNT(*) FROM courses WHERE active = 1 AND deleted_at IS NULL; -- 检查关联用户类型分布 SELECT participant_type, COUNT(*) FROM participants GROUP BY participant_type;
对比执行时间：在生产数据副本上测试优化前后耗时（启用 SQL_NO_CACHE 或重启 MySQL 清理查询缓存）。
✅ 总结：为什么这比应用层处理更优？
- ❌ 应用层 IN 查询：WHERE participant_id IN (...) 在 participants 表达 3000 万行时，即使有索引，IN 列表过大仍易触发全索引扫描或临时表；
- ❌ 全量拉取过滤：传输 3000 万行到 PHP 内存不现实；
- ✅ 数据库内聚合：利用 B+ 树索引局部性、缓冲池、并行扫描（MySQL 8.0+）及优化器成本模型，将计算压给专为此设计的存储引擎。
最终，合理的 JOIN 逻辑 + 精准的复合索引 + 必要的执行计划干预，可在毫秒至秒级完成该统计，无需将复杂性下沉至应用层。
