所以我有个不寻常的要求。我在处理一张有数十亿行的桌子。
该表有一个列'id‘,它不是唯一的,并且有一个列'data’。
我想要做的是对按“id”分组的行数进行计数,但将计数限制在仅150个条目。我只需要知道给定的id是否有150行。
这是为了优化查询和性能。
不一定要算。我只需要知道给定的id是否有150个条目,而不需要MySQL在查询期间继续计算条目。如果这有意义的话。
我知道如何计数,我知道如何分组,我知道如何两者都做,但计数将返回一个数以百万计的数字,这是浪费的处理时间,查询需要在成千上万的ids上运行。
发布于 2019-11-14 21:19:19
你不能为这个优化性能--我不认为。
select id, (count(*) >= 150)
from t
group by id;如果您碰巧有一个单独的表,每个id有一行,t(id)上有一个索引,那么这可能会更快:
select ids.id,
((select count(*)
from t
where t.id = ids.id
) >= 150
)
from ids;不幸的是,MySQL不支持对相关子查询进行双重嵌套,因此这是不可能的:
select ids.id,
((select count(*)
from (select 1
from t
where t.id = ids.id
limit 150
) t
) >= 150
)
from ids;如果是这样,这可能会更快。
编辑:
如果您在id上有一个索引,并且只需要有150个或更多的ids,那么变量可能会更快:
select id,
(@rn := if(@id = id, @rn + 1,
if(@id := id, 1, 1)
)
) as rn
from (select id
from t
order by id
) t cross join
(select @id := 0, @rn := 0) params
having rn = 150;这里的想法是,使用索引对表进行排序、物化和再次扫描可能比group by更快。我不认为row_number()具有相同的性能特征。
编辑二:
上面的一个细微的变化可以用来获得带标志的所有ids:
select id, (max(id) = 150)
from (select id,
(@rn := if(@id = id, @rn + 1,
if(@id := id, 1, 1)
)
) as rn
from (select id
from t
order by id
) t cross join
(select @id := 0, @rn := 0) params
having rn in (1, 150)
) t
group by id;编辑三:
啊!如果您有一个单独的ids表,那么这可能是最好的方法:
select ids.id,
(select id
from t
where t.id = ids.id
limit 1 offset 149
) is not null
from ids;这将从索引中获取第150行。如果它不在那里,则不会返回任何行。
发布于 2019-11-14 21:21:47
我觉得这不可能。您必须扫描整个表,才能知道哪个id至少有150个条目。
所以:
select id
from mytable
group by id
having count(*) >= 150有了id上的索引,这应该是尽可能有效的。
https://stackoverflow.com/questions/58866652
复制相似问题