如何使用 group by having limit？

Question

请问有人可以解释一下构建 group by + having + limit 查询的具体工作原理吗？MySQL 查询语句：

    SELECT
        id,
        avg(sal)
    FROM
        StreamData 
    WHERE
        ...
    GROUP BY
        id 
    HAVING
        avg(sal)>=10.0 
        AND avg(sal)<=50.0   
    LIMIT 100

没有限制和having子句的查询会在7秒内执行，有限制的查询如果条件涵盖大量数据则立即执行，否则约为7秒。 文档称，limit在having之后、group by之前执行，这意味着查询应该总是需要约7秒才能执行。请帮忙弄清楚LIMIT子句的限制是什么。

- Иван Кирилюк

---

不确定MySQL中的缓存，但在Oracle中，我观察到如果您运行相同的查询超过一次，则第二次执行所需的时间较少。因此，如果限制只是在查询执行后添加“top 100”，您可以尝试先使用limit运行查询，然后再次运行没有它的查询并进行比较？ - Utsav

不是关于你的问题，但是每当你运行一个没有order by子句的“top n”查询时，你会得到随机记录。这可能是你想要的，也可能不是。 - Dan Bracuk

功能限制通常放在最后，但是一个好的编译器会实施一个计划，如果可能的话，在流水线中更早地应用限制以提高性能。 - Hogan

Answer 1

使用LIMIT 100仅告诉MySQL从结果集中仅返回前100条记录。假设您将查询时间测量为从Java的往返行程时间，则查询时间的一个组成部分是在MySQL和Java之间通过网络传输结果集所需的网络时间。对于大型结果集，这可能需要相当长的时间，而使用LIMIT 100可以将此时间减少到零或接近零。

- Tim Biegeleisen

Answer 2

SQL中以一定的流程逻辑应用事务：

1. 生成和执行表达式 (FROM, JOIN)
2. 筛选行 (WHERE)
3. 应用投影和聚合 (列列表、聚合函数、GROUP BY)
4. 过滤聚合函数结果 (HAVING)
5. 限制结果 (LIMIT, OFFSET)

如果安全的话，规划者可以将它们组合成不同的执行顺序，但如果按照这个顺序进行思考，你总能得到正确的数据。

所以先分组，然后使用having过滤这些分组，最后对结果进行截断。

- Chris Travers

---

我有1000万行数据，只使用group by和having查询需要几秒钟，而加上limit后立即执行。如何解释这一事实，知道limit是最后运行的呢？ - Иван Кирилюк

现在，如果安全的话，计划者可以将它们组合成不同的执行顺序，但是如果您按照这个顺序仔细考虑，您总会得到正确的数据。Limit并不总是最后运行的。但它始终是逻辑上最后运行的。 - Chris Travers

通常情况下，如果速度更快，您将执行group by、order by和limit操作，因为这样您就可以进行有限的索引扫描 :-) - Chris Travers

Answer 3

一旦MySQL向客户端发送了所需数量的行，除非使用SQL_CALC_FOUND_ROWS，否则它将中止查询。然后可以使用SELECT FOUND_ROWS()检索行数。请参见第13.14节“信息函数”。

http://dev.mysql.com/doc/refman/5.7/en/limit-optimization.html

这实际上意味着，如果您的表格有相当多的行，服务器不需要查看所有行。它可以在找到100行后停止，因为它知道那是您所需的全部内容。

- e4c5