SQL statement execution sequence analysis in MySQL

From: http://blog.sina.com.cn/s/blog_4a1f76860100x985.html

First, the basic syntax of the SELECT statement is as follows:

Select selection_list # What columns to select

From table_list # which tables to select rows from

Where primary_constraint # What conditions rows must satisfy

Group by grouping_columns # How to group results

Having secondary_constraint # secondary conditions rows must satisfy

Order by sorting_columns # How to sort results

Limit from, count; # limiting row count on resultsMySQL, Except for the first line, other parts are optional, and some DBMS require that the from clause is also required.

Complete SQL SELECT statementExecution sequence[From the DBMS user perspective ]:

1. The from clause assembles data from different data sources;

2. The WHERE clause filters record rows based on specified conditions;

3. The group by clause divides data into multiple groups;

4. Use Aggregate functions for computation;

5. Use the having clause to filter groups;

6. Calculate all expressions;

7. Use order by to sort the result set.

SQL SELECT statementRunStep [from the perspective of DBMS implementers, this is of little significance to our users ]:

1) analyze the syntax and analyze whether the syntax of the statement complies with the specifications, to measure the meaning of each expression in the statement.

2) perform Semantic Analysis to check whether all database objects involved in the statement exist and the user has the corresponding permissions.

3) view conversion: converts query statements related to views into corresponding query statements for base tables.

4) expression conversion: converts complex SQL expressions into simple equivalent join expressions.

5) Select the optimizer. Different optimizers generally generate different"RunPlan"

6) Select the connection mode. Oracle has three connection modes. You can select an appropriate connection mode for multi-Table Oracle connection.

7) Select connectionSequenceWhich table to connect to Oracle for multi-Table connection is connected first, and which table in the two tables is selected as the source data table.

8) Select the data search path and select the appropriate data search path based on the preceding conditions. If you choose full table search or index or other methods.

9) Run"RunPlan ".

Example:

Selectstate as state, AVG (to_days (death)-to_days (birth)/365) as age

From President

Where death is not null

Group by state

Order by age;

This SQL query means:

Query the dead presidents in the Presidential table, group them by state, and calculate the average life cycle of the Presidents for each group (AVG (to_days (death)-to_days (birth )) /365), and then arranged in ascending order of life.

The final result is similar to this:

+ ------- + ----------- +

| State | age |

+ ------- + ----------- +

| Ky| 56.208219 |

| VT | 58.852055 |

| NCS | 60.141096 |

| H | 62.866145 |

| NH | 64.917808 |

| Ny| 69.342466 |

| J | 71.315068 |

| TX | 1, 71.476712 |

| Ma | 1, 72.642009 |

| Va | 72.822945 |

| PA | 1, 77.158904 |

| SC | 78.284932 |

| Ca | 81.336986 |

| Mo | 88.693151 |

| Infrequent access | 90.254795 |

| Il | 1, 93.391781 |

+ ------- + ----------- +