mysql - 子查询以收集具有限制的分组子集

Question

我正在尝试找出一种无需循环 700,000 个 mysql 查询即可收集数据集的方法。

我有两张 table

用户与

id autoincrement, 
time timestamp, 
username varchar(200), 
email varchar(100), 
ip varchar(20)

和uniq_ip与

ip unique varchar(20), 
most_recent datetime, 
count (int)

users 有 2500 万行，记录了用户在网站上工作时的事件。 uniq_ip 包含所有 IP 编号的列表以及它在用户中列出的次数(在触发更新时)。

目前，在做白日梦编码时，我从 uniq_ip 获取了所有 IP 的列表，并循环它们以获取每个 IP 的最新 2000 条记录。由于 uniq_ip 有 700,000 行，这个循环真的很讨厌，总共进行了 700,000 次查询，使用

select * from users where ip = '$outerloopip' order by `time` desc limit 2000;

我正在尝试获取一个查询，该查询将为每个 IP 获取最近的 2000 个列表。如果 1.2.3.4 被列出 10,000 次，我只想要最近的 2000 次，基于时间字段。

有什么想法可以在一个查询中完成吗？

Answer 1

我对之前的回答感到抱歉，并重新阅读并应用了更新的查询。我错过了并且认为您只需要最近的 2000 个 IP 地址。无论如何，这个会处理所有 IP 地址并将每个 IP 的总记录限制为 2,000 个条目，最近的条目位于顶部。我会确保你有一个索引

(IP,TIME DESC)

然后，尝试这个查询。我错过了澄清的关键事情。 HAVING 子句在任何 group-by 或 order-by 子句之后应用。因此，数据以 IP 地址和日期/时间降序的正确顺序预先返回，然后应用 @sql 变量。一旦记录符合条件并准备好添加到最终结果集中，就会应用 HAVING 子句。在那一刻，它查看序列计数器并说...如果它大于 2000，则将其丢弃并继续处理下一条记录。

根据我最初的查询，它会保存所有内容，然后第二次循环并剔除那些大于 2000 的内容，这可能就是它耗尽您的磁盘空间的原因。

select
      U.*,
      @LastSeq := IF( @LastIP = U.IP, @LastSeq +1, 1 ) as IPSequence,
      @LastIP := U.IP as carryForNextRecord
   from 
      ( select @LastIP := '', @LastSeq := 0 ) sqlvars,
      Users U
   order by
      U.IP,
      U.time DESC
   having 
      IPSequence <= 2000