COUNT(DISTINCT)+GROUP BY慢的主因是每个分组需独立维护哈希表或排序结构，导致内存溢出落盘；应通过子查询预聚合、拆分多字段去重、建立覆盖索引（WHERE+GROUP BY+DISTINCT字段顺序）并严格下推过滤条件来优化。
为什么COUNT(DISTINCT) + GROUP BY一跑就卡住
不是数据量大本身导致慢，而是数据库对每个分组都得独立维护一个哈希表或排序结构——比如 COUNT(DISTINCT user_id) 配合 GROUP BY channel，若 channel 有 10 万个值，就得做 10 万次去重。EXPLAIN 中一旦出现 Using temporary; Using filesort，基本等于 MySQL 正在内存建哈希表，撑不住就落磁盘，性能断崖下跌。
把去重操作“上推”到子查询预聚合
核心是避免在 JOIN 或大结果集上直接跑 COUNT(DISTINCT)，必须让去重发生在关联之前，把大表压成小结果集再拼接。
- 坏写法：
SELECT b.name, COUNT(DISTINCT a.user_id) FROM table_a a JOIN table_b b ON a.dashboard_id = b.id GROUP BY b.name—— 先全量关联，中间结果可能爆炸 - 好写法：用子查询先按
dashboard_id聚合，再 JOIN：SELECT b.name, new_a.ct FROM table_b b JOIN ( SELECT dashboard_id, COUNT(DISTINCT user_id) AS ct FROM table_a WHERE dt = '2026-09' GROUP BY dashboard_id ) new_a ON new_a.dashboard_id = b.id
- 子查询结果通常只有几百行，JOIN 极快；
WHERE dt = '2026-09'还能触发分区裁剪（如果表按dt分区）
多字段 COUNT(DISTINCT) 必须拆开算
同时写 COUNT(DISTINCT user_id), COUNT(DISTINCT order_no) 会让执行计划并行维护多个哈希结构，CPU 和内存开销翻倍，且无法共享中间状态。
- 错误做法：
SELECT channel_code, COUNT(DISTINCT user_id), COUNT(DISTINCT order_no) FROM user_order GROUP BY channel_code - 正确做法：两个独立子查询分别聚合，再用
channel_code关联SELECT t1.channel, t1.user_cnt, t2.order_cnt FROM ( SELECT channel_code AS channel, COUNT(DISTINCT user_id) AS user_cnt FROM user_order WHERE del_flag = '0' AND create_date BETWEEN '2026-01-01' AND '2026-06-30' GROUP BY channel_code ) t1 JOIN ( SELECT channel_code AS channel, COUNT(DISTINCT order_no) AS order_cnt FROM user_order WHERE del_flag = '0' AND create_date BETWEEN '2026-01-01' AND '2026-06-30' GROUP BY channel_code ) t2 ON t1.channel = t2.channel
索引不匹配，优化全白搭
没索引，预聚合也救不了；索引顺序错，照样走临时表。关键不是“有没有索引”，而是它是否覆盖整个查询路径：WHERE 条件 + GROUP BY 字段 + DISTINCT 字段。
- 对
COUNT(DISTINCT user_id) GROUP BY dashboard_id，索引必须是(dashboard_id, user_id)，反过来无效 - 若还有
WHERE del_flag = '0' AND create_date BETWEEN '2026-01-01' AND '2026-06-30'，索引应扩展为(del_flag, create_date, dashboard_id, user_id) - 避免在
TEXT、超长VARCHAR或含大量NULL的列上直接COUNT(DISTINCT)；真要处理，先用WHERE col IS NOT NULL过滤
WHERE 条件漏进子查询——比如忘记加 dt = '2026-09'，导致子查询扫全表，预聚合反而更慢。 
