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

问题描述

  SELECT DAY（date），其他列
 FROM table 
 GROUP BY DAY（date）; 
 
 SELECT somecolumn，COUNT（*）
 FROM table 
 HAVING COUNT（*）> 1; 
  

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

  SELECT DAY（date）AS day，其他列
 FROM table 
 GROUP BY日; 
 
 SELECT somecolumn，COUNT（*）AS c 
 FROM table 
 HAVING c> 1; 
  

MySQL是否足够聪明地注意到后面的子句中的表达式与 SELECT ，只做一次？我不确定如何测试 - EXPLAIN 没有显示任何区别，但似乎没有显示它是如何在第一个分组或过滤中进行的地点;它似乎主要用于优化连接和 WHERE 子句。

我倾向于对MySQL优化感到悲观，所以我我想这可以使用sleep（）函数进行测试，








例如
来看看这个演示： http://sqlfiddle.com/#！ 2 / 0bc1b / 1

 选择* FROM t; 
 
 | X | 
 | --- | 
 | 1 | 
 | 2 | 
 | 2 | 
 
 SELECT x + sleep（1）
 FROM t 
 GROUP BY x + sleep（1）; 
 
 SELECT x + sleep（1）作为名称
 FROM t 
 GROUP BY名称; 
  

这两个查询的执行时间大约为3000毫秒（3秒）。

表中有3条记录，对于每条记录，查询只休眠1秒，

，这意味着表达式对于每条记录只计算一次，而不是两次。

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;

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;

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.

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

解决方案

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;

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屋！