17370845950

本文介绍如何在 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; 更新统计信息。

? 验证与迭代方法

1. 始终运行 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN（5.7），观察：

   • 表访问顺序是否为 courses → participants → users
   • key 列是否命中上述新建索引
   • rows 预估是否显著下降（如 courses 从 30k → 数百）

2. 分步验证过滤效果：

   -- 检查活跃课程数量（应远小于 total）
   SELECT COUNT(*) FROM courses WHERE active = 1 AND deleted_at IS NULL;
   -- 检查关联用户类型分布
   SELECT participant_type, COUNT(*) FROM participants GROUP BY participant_type;

3. 对比执行时间：在生产数据副本上测试优化前后耗时（启用 SQL_NO_CACHE 或重启 MySQL 清理查询缓存）。

✅ 总结：为什么这比应用层处理更优？

• ❌ 应用层 IN 查询：WHERE participant_id IN (...) 在 participants 表达 3000 万行时，即使有索引，IN 列表过大仍易触发全索引扫描或临时表；
• ❌ 全量拉取过滤：传输 3000 万行到 PHP 内存不现实；
• ✅ 数据库内聚合：利用 B+ 树索引局部性、缓冲池、并行扫描（MySQL 8.0+）及优化器成本模型，将计算压给专为此设计的存储引擎。

最终，合理的 JOIN 逻辑 + 精准的复合索引 + 必要的执行计划干预，可在毫秒至秒级完成该统计，无需将复杂性下沉至应用层。