MySQL MySQL查询/子句执行顺序

MySQL query / clause execution order

提问by ericsicons

What is the predefined order in which the clauses are executed in MySQL? Is some of it decided at run time, and is this order correct?

在 MySQL 中执行子句的预定义顺序是什么？其中一些是在运行时决定的，这个顺序是否正确？

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

回答by Gordon Linoff

The actual execution of MySQL statements is a bit tricky. However, the standard does specify the order of interpretation of elements in the query. This is basically in the order that you specify, although I think HAVINGand GROUP BYcould come after SELECT:

MySQL 语句的实际执行有点棘手。但是，该标准确实指定了查询中元素的解释顺序。这基本上是按照您指定的顺序，尽管我认为HAVING并且GROUP BY可能会出现在SELECT：

• FROMclause
• WHEREclause
• SELECTclause
• GROUP BYclause
• HAVINGclause
• ORDER BYclause

• FROM条款
• WHERE条款
• SELECT条款
• GROUP BY条款
• HAVING条款
• ORDER BY条款

This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECTin the WHEREclause, for instance, because the WHEREis parsed before the SELECT. On the other hand, such an alias can be in the ORDER BYclause.

这对于理解查询的解析方式很重要。例如，您不能SELECT在WHERE子句中使用 a 中定义的列别名，因为WHERE在SELECT. 另一方面，这样的别名可以在ORDER BY子句中。

As for actual execution, that is really left up to the optimizer. For instance:

至于实际执行，这真的留给优化器。例如：

. . .
GROUP BY a, b, c
ORDER BY NULL

and

和

. . .
GROUP BY a, b, c
ORDER BY a, b, c

both have the effect of the ORDER BYnot being executed at all -- and so not executed after the GROUP BY(in the first case, the effect is to remove sorting from the GROUP BYand in the second the effect is to do nothing more than the GROUP BYalready does).

两者都具有ORDER BY根本不执行的效果——因此不在之后执行GROUP BY（在第一种情况下，效果是从 中删除排序，GROUP BY而在第二种情况下，效果是只做GROUP BY已经做的事情）。

回答by Pradeep Kumar Tiwari

This is how you can get the rough idea about how mysql executes the select query

这是您如何大致了解 mysql 如何执行选择查询的方法

DROP TABLE if exists new_table;

CREATE TABLE `new_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`testdecimal` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`id`));

INSERT INTO `new_table` (`testdecimal`) VALUES ('1234.45');
INSERT INTO `new_table` (`testdecimal`) VALUES ('1234.45');

set @mysqlorder := '';

select @mysqlorder := CONCAT(@mysqlorder," SELECT ") from new_table,(select @mysqlorder := CONCAT(@mysqlorder," FROM ")) tt
JOIN (select @mysqlorder := CONCAT(@mysqlorder," JOIN1 ")) t on ((select @mysqlorder := CONCAT(@mysqlorder," ON1 ")) or rand() < 1)
JOIN (select @mysqlorder := CONCAT(@mysqlorder," JOIN2 ")) t2 on ((select @mysqlorder := CONCAT(@mysqlorder," ON2 ")) or rand() < 1)
where ((select @mysqlorder := CONCAT(@mysqlorder," WHERE ")) or IF(new_table.testdecimal = 1234.45,true,false))
group by (select @mysqlorder := CONCAT(@mysqlorder," GROUPBY ")),id
having (select @mysqlorder := CONCAT(@mysqlorder," HAVING "))
order by (select @mysqlorder := CONCAT(@mysqlorder," ORDERBY "));

select @mysqlorder;

And here is the output from above mysql query, hope you can figure out the mysql execution of a SELECTquery :-

这是上面 mysql 查询的输出，希望您能弄清楚SELECT查询的 mysql 执行：-

FROM JOIN1 JOIN2 WHERE ON2 ON1 ORDERBY GROUPBY SELECT WHERE ON2 ON1 ORDERBY GROUPBY SELECT HAVING HAVING

从 JOIN1 JOIN2 WHERE ON2 ON1 ORDERBY GROUPBY SELECT WHERE ON2 ON1 ORDERBY GROUPBY SELECT HAVING HAVING

回答by astarring

I think the execution order is like this:

我认为执行顺序是这样的：

(7)     SELECT 
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>[, <offset_number>]