mysql - SQL查询以计算按ID分组的行，但限制每个组的计数

Question

所以我有一个不寻常的请求。我正在处理一个包含数十亿行的表。

该表有一个不唯一的“id”列，还有一个“data”列

我想做的是对按“id”分组的行数进行计数，但将计数限制为仅 150 个条目。我只需要知道任何给定 ID 是否有 150 行。

这是为了优化查询和性能。

它不一定是计数。我只需要知道给定的 id 是否有 150 个条目，而不需要 MySQL 在查询期间继续计算条目。如果这是有道理的话。

我知道如何计数，我知道如何分组，我知道如何两者兼顾，但计数将返回数百万，这浪费了处理时间，查询需要在数百上运行数千个 ID。

Answer 1

您无法为此真正优化性能——我不认为。

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 上有一个索引并且只想要 id 有 150 或更多，那么变量可能会更快:

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() 不会具有相同的性能特征。

编辑二:

上面的一个细微变化可用于获取所有带有标志的 id:

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;

编辑三:

啊!如果您有一个单独的 ID 表，那么这可能是最好的方法:

select ids.id,
       (select id
        from t
        where t.id = ids.id
        limit 1 offset 149
       ) is not null
from ids;

这将从索引中获取第 150 行。如果不存在，则不会返回任何行。