Mysql > Mysql > Mysql

  1. Connector: Management connection (TCP protocol). Verify the identity (username/password). After the authentication, the connection is established. According to whether the database has not been operated for a long time, the connection is disconnected due to timeout.
  2. Cache: Cache results. Before sending a query statement, the client checks whether the SQL has been executed in the cache. If yes, the client returns the result. Mysql8.0 because the hit ratio is not high, removed.
  3. Profiler: Analyzes the SQL syntax for correctness. If not, a syntax error is reported
  4. The optimizer:SQLThe optimizer will be used before execution, and mysql will select the best query scheme
  5. Executor: Executes SQL. It first verifies that the SQL has permission to manipulate the table, and then calls the storage engine API to manipulate the data.

SQL Execution Procedure

In fact, mysql does not execute in the order we wrote the SQL, it has its own internal SQL execution order. In order to use SQL clearly, we need to go inside and know ourselves and the enemy. During execution, temporary tables are generated.

  1. from
  2. join
  3. on
  4. where
  5. Group by: Start using the alias in SELECT, or later
  6. avg,sum…
  7. having
  8. select
  9. distinct
  10. order by
  11. limit

Execution process: Step 1: Execute the from clause. If there are two tables, cartesian product will be performed to generate a virtual table. Step 2: According to the join algorithm, filter the data and generate a virtual table. Step 3: If there are more than two tables in the form clause, repeat Steps 1 and 2 to generate a new virtual table. Step 4: Execute the WHERE statement to conditionally filter the final virtual table generated earlier and generate a new virtual table. Step 5: Run the Group by statement to group virtual tables and generate virtual tables. If group by is used, the columns of the resulting virtual table consist of the columns defined after group BY and the aggregate function, and only the columns of the virtual table can be used thereafter. Step 6: Execute the having statement to create a new virtual table. Step 7: Execute the SELECT statement to filter out the required data and generate a new virtual table. Step 8: Execute a DISTINCT statement to remove the same rows and create a new virtual table. If group by is used, this is an unnecessary operation. Because grouping groups the unique values in a column and returns only one row for each group, all the records will be different. Step 9: Execute the Order by clause. SQL is based on set theory. It is a logical set. The order of members is irrelevant. Then, if sorted, an object (containing the logical organization of the physical order, the cursor) is returned. So it’s not necessary not to sort. This is the only place where you can use a column alias. Step 10: Apply the top option and return the result to the client.

The sample

select name,class_name,max(grade)
from student t1
left join class t2
on t1.cid = t2.id
where name= 'Zhang SAN'limit(0, 2)Copy the code
  1. Get the data from the student table and produceVirtual table v1
  2. Virtual table V1 and class table, according toThe join algorithmTo produceVirtual table v2
  3. V2 is filtered according to the WHERE condition, resulting inVirtual table v3
  4. Execute select, filter columns, produceVirtual table v4
  5. Executing LIMIT on V4 willThe result is returned to the client.