在ORDER BY子句中使用聚合函数和聚合函数别名之间是否存在与性能相关的差异？ [英] Is there Performance related difference in using aggregate function in ORDER BY clause and alias of aggregate function?

问题描述

我有一个与 ORDER BY 或 GROUP BY 子句有关的问题。

I have a question related to ORDER BY or GROUP BY clause.

例如我有以下查询

SELECT country_name,COUNT(*) FROM user_location
WHERE country_name IS NOT NULL
GROUP BY country_name 
ORDER BY COUNT(*) DESC

And

SELECT country_name,COUNT(*) As Total FROM user_location
WHERE country_name IS NOT NULL
GROUP BY country_name 
ORDER BY Total DESC

在第二个查询中，我使用别名<$在 ORDER BY 子句中的 COUNT（*）的c $ c> Total 。

In 2nd query I am using alias Total for COUNT(*) in ORDER BY clause.

两个查询中是否存在与性能相关的差异？

Is there any performance related differences in two queries ?

推荐答案

我已经在一个表上运行了以下测试，该表具有100万个与1万个类别随机相关的产品（MariaDB 10.0.19）：

I've run the following test on a table with 1M products randomly related to 10K categories (MariaDB 10.0.19):

select p.categoryId, count(*) as total
from products p
group by p.categoryId
having count(*) = 100

执行时间：156毫秒

select p.categoryId, count(*) as total
from products p
group by p.categoryId
having total = 100

执行时间：156毫秒

因此，性能似乎没有任何差异。

So there doesn't seem to be any difference in performance.

请注意，使用 ORDER BY 引擎会将结果复制到临时表中（请参见说明：使用临时表；使用文件排序）。因此，即使您使用 ORDER BY COUNT（*），也无法重新计算该值。

Note that with ORDER BY the engine will copy the result into a temporary table (See EXPLAIN: Using temporary; Using filesort). So the value can't be recalculated, even when you use ORDER BY COUNT(*).

但是-当我使用 ORDER BY COUNT（DISTINGT ...）时（我无法解释）：

However - There is a difference (which I can not explain) when I use ORDER BY COUNT(DISTINGT ...):

select p.categoryId, count(distinct p.productData) as total
from products p
group by p.categoryId
order by total

配置文件：863毫秒，用于复制到tmp表

Profile: 863 msec for Copying to tmp table

select p.categoryId, count(distinct p.productData) as total
from products p
group by p.categoryId
order by count(distinct p.productData)

配置文件：963毫秒复制到tmp表

Profile: 963 msec for Copying to tmp table

这篇关于在ORDER BY子句中使用聚合函数和聚合函数别名之间是否存在与性能相关的差异？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！