count 的语句优化问题

最近在 mysql 中做了一个查询

select count(*) from
(select a,b,count(distinct c) from table group by a,b having count(distinct c)>=2) as t

把 mysql 库跑死掉了

请教下有什么可以优化的空间？
目前想到的是下面

select count(*) from
(select a,b from table group by a,b having count(distinct c)>=2) as t

count

优化

查询

3 replies • 2025-09-12 09:17:15 +08:00

supersf

Sep 11, 2025 via iPhone

Gemini 反馈可以用 min 和 max SELECT
count(*)
FROM
(
SELECT
a,
b
FROM
table
GROUP BY
a,
b
HAVING
min(c) <> max(c)
) AS t;

lyis

Sep 12, 2025

1.加索引 (a,b,c)
2.试试
SELECT a,b FROM table T1 WHERE EIXSTS(
SELECT 1 FRO table T2 WHERE T1.a=T2.a and T1.b=T2.b and T1.c<>T2.c
)

andykuen959595

Sep 12, 2025

数据有多少？按 2 楼的方法，给 abc 加个索引试试