Execution order of SQL in MySQL

Article transferred from https://www.cnblogs.com/huminxxl/p/3149097.html

In the query statement, select from where the group by has an order by order of execution 1. The keyword used in the query consists of six main words, and their order is Select--from--where--group By--having--order by where select and from are required, other keywords are optional, and the order of execution of these six keywords is not the same as the order in which the SQL statements are written, but is executed in the following order From--where--group  By--having--select--order by, from: Which data table needs to retrieve the data where: conditions for filtering data in a table group by: How to group the data filtered above having: the criteria for filtering the data already grouped above Select: View which column in the result set, or the calculated result of the column, order by: In what order to view the table associations that follow the returned data 2.from, are parsed from right to left and the parse order of the Where condition is bottom-up.conclusion: When writing SQL, try to associate the data-heavy table on the right-hand side .and the conditions that can filter out large amounts of data are placed at the bottom of the where statement. The SQL SELECT statement is complete Execution Order"From the DBMS user perspective":
1. The FROM clause assembles data from different data sources;
2. The WHERE clause filters the record rows based on the specified criteria;
3. The GROUP BY clause divides the data into multiple groupings;
4, using the aggregation function to calculate;
5, using having clauses to filter the grouping;
6, the calculation of all expressions;
7. Use order by to sort the result set.

Of the SQL SELECT statement ExecutionStep "from the DBMS perspective, this does not mean much to our users":
1) parsing, analyzing whether the syntax of the statement conforms to the specification, and measures the meaning of the expressions in the statement.
2) semantic analysis, check whether all the database objects involved in the statement exist, and the user has the appropriate permissions.
3) View transformation, converting a query statement involving a view into a corresponding base table query statement.
4) expression conversion, converting a complex SQL expression to a simpler equivalent join expression.
5) Select the optimizer, the different optimizer generally produces the different " ExecutionPlan
6) Choose the connection method, Oracle has three kinds of connection methods, for multi-table connection Oracle can choose the appropriate connection mode.
7) Select the connection Order, for multiple table connections ORACLE chooses which pair of tables to connect to first, select which table in both tables is the source data table.
8) Select the search path of the data, according to the above criteria to select the appropriate data search path, whether the use of full table search or index or other way.
9) Run " ExecutionPlan ".

FROM clause-The order of execution is from forward, right to left
Table name (the last table name is the driver table, the order of execution is from back to front, so the table with less data is placed as soon as possible)

The Oracle parser is processed in a right-to-left order, the table name in the FROM clause, and the last table written in the FROM clause (the underlying table, driving tables) will be processed first, that is, the final table is the driver table, and in the case that the FROM clause contains more than one table, You must select the table with the lowest number of records as the base table. If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the tables referenced by other tables

For multiple table joins, use the alias of the table and prefix the aliases on each column. You can reduce the time to parse and reduce the syntax errors caused by column ambiguity.

Where clause-the execution order is bottom-up, right-to-left

ORACLE parses the WHERE clause from the bottom-right to the left, according to which the connection between the tables must be written before the other where conditions, and the criteria that can filter out the maximum number of records must be written at the end of the WHERE clause.

Group by--execution sequence from left to right

The efficiency of the group BY statement can be improved by filtering out unwanted records before group by. That is, use where to filter before group by, and try to avoid group by after having filtering.

Having clauses----very resource-intensive, as little as possible.

Avoid having a HAVING clause that filters the result set only after all records have been retrieved. This processing requires sorting, totals, and so on.

If you can limit the number of records by the WHERE clause before group BY, you can reduce this overhead.
(Non-Oracle) on, where, have the three clauses that can be added conditionally, on is the first execution, where the second, having the last, because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to process the data, It's supposed to be the fastest speed,

Where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared.

In the case of this single-table query statistic, if the conditions to be filtered are not related to the fields to be computed, then the results are the same, except where the Rushmore technique can be used, and the having is not, the latter is slower in speed.
If a calculated field is to be involved, it means that the value of the field is indeterminate before it is calculated, where the action time is done before the calculation, and having is calculated before it works, so in this case the results will be different.

On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having.

Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put there.

SELECT clause--Use the * number sparingly to take the field name as much as possible .

During the parsing process, ORACLE will turn to all column names, which are done by querying a data dictionary, and using column names means reducing the time spent.

SQL statements are capitalized, because Oracle always parses the SQL statements first, converting lowercase letters to uppercase and then executing

ORDER BY Clause-the order of execution is ordered from left to right, consuming resources

Execution order of SQL in MySQL