MySQL 是否消除了 SELECT 和 HAVING/GROUP BY 子句之间的常见子表达式 [英] Does MySQL eliminate common subexpressions between SELECT and HAVING/GROUP BY clause

问题描述

我经常看到人们用这样的查询来回答 MySQL 问题:

I often see people answer MySQL questions with queries like this:

SELECT DAY(date), other columns
FROM table
GROUP BY DAY(date);

SELECT somecolumn, COUNT(*)
FROM table
HAVING COUNT(*) > 1;

我总是喜欢给列一个别名，并在 GROUP BY 或 HAVING 子句中引用它，例如

I always like to give the column an alias and refer to that in the GROUP BY or HAVING clause, e.g.

SELECT DAY(date) AS day, other columns
FROM table
GROUP BY day;

SELECT somecolumn, COUNT(*) AS c
FROM table
HAVING c > 1;

MySQL 是否足够聪明，注意到后面的子句中的表达式与 SELECT 中的表达式相同，并且只执行一次?我不知道如何测试—— EXPLAIN 没有显示任何区别，但它似乎没有显示它首先是如何进行分组或过滤的；它似乎主要用于优化连接和 WHERE 子句.

Is MySQL smart enough to notice that the expressions in the later clauses are the same as in SELECT, and only do it once? I'm not sure how to test this -- EXPLAIN doesn't show any difference, but it doesn't seem to show how it's doing the grouping or filtering in the first place; it seems mainly useful for optimizing joins and WHERE clauses.

我倾向于对 MySQL 优化持悲观态度，所以我喜欢尽可能地提供帮助.

I tend to be pessimistic about MySQL optimization, so I like to give it all the help I can.

推荐答案

我觉得这个可以用 sleep() 函数来测试，
例如看看这个演示:http://sqlfiddle.com/#!2/0bc1b/1

I think this can be tested using sleep() function,
for example take a look at this demo: http://sqlfiddle.com/#!2/0bc1b/1

Select * FROM t;

| X |
|---|
| 1 |
| 2 |
| 2 |

SELECT x+sleep(1)
FROM t
GROUP BY x+sleep(1);

SELECT x+sleep(1) As name
FROM t
GROUP BY name;

两个查询的执行时间约为 3000 毫秒(3 秒).
表中有3条记录，每条记录查询只休眠1秒，
所以这意味着表达式对每条记录只计算一次，而不是两次.

Execution times of both queries are about 3000 ms ( 3 seconds ).
There are 3 records in the table, and for each record the query sleeps for 1 second only,
so it means that the expression is evaluated only once for each record, not twice.

这篇关于MySQL 是否消除了 SELECT 和 HAVING/GROUP BY 子句之间的常见子表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！