Shulou(Shulou.com)06/01 Report--

This article mainly introduces what is the life cycle of sql in mysql. It is very detailed and has a certain reference value. Friends who are interested must finish it!

MYSQL Query Processing

The execution process of sql is basically consistent with that of mysql architecture.

Execution process:

Connector:

Establish a connection with MySQL to query SQL statements and determine permissions.

Query cache: if the statement is not in the query cache, the execution phase continues later. After the execution is completed, the execution result will be stored in the query cache. If the query hits the cache, MySQL can directly return the result without performing the following complex operations, thus improving the efficiency analyzer:

For hard parsing of SQL statements, the parser will first do lexical analysis. Analyze the components of the SQL statement. Determine whether the input SQL statement satisfies the syntax rules.

Optimizer:

The optimizer decides which index to use when there are multiple indexes in the table, or the join order of each table when a statement has multiple table associations (join). The logical results of different execution methods are the same, but the efficiency of execution will be different, and the role of the optimizer is to decide which scheme to use.

Executor: indexed: the API that fetches the first row that satisfies the condition is called for the first time, then the API of the next row that meets the condition is looped, and finally the query result is returned to the client without index: call the InnoDB engine API to take the first row of the table, determine the sql query condition, if not, skip it, if so, store the row in the result set. Call the engine interface to fetch the next row and repeat the same judgment logic until the last row of the table is fetched. The executor returns the recordset of all the rows that meet the criteria as a result set to the client to understand the execution plan.

The EXPLAIN command outputs how MySQL will execute your SQL statements, but will not return data

How to use [root@localhost] [(none)] > explain select * from table name where project_id = 36 +-+- -+ | 1 | SIMPLE | Table name | NULL | ref | project_id | project_ Id | 4 | const | 797964 | 100.00 | NULL | +-+ -- + copy code idid the same execution order is different from top to bottom id The higher the id value, the higher the priority, the more select_typeSIMPLE is executed first: simple select query, the query does not include subqueries or unionPRIMARY: the query contains subqueries, and the outermost query is marked as primaryDERIVED: is part of the subquery from DEPENDENT SUBQUERY: the first SELECT in the subquery, the subquery depends on the results of the outer query SUBQUERY indicates that the subquery contains the subquery in the select or where list MATERIALIZED: subquery UNION that represents the in condition after where: represents the result of the second or subsequent select statement UNION RESULT:union in union table table object type

System > const > eq_ref > ref > range > index > ALL (query efficiency)

System: there is only one piece of data in the table, and this type is the special const type const: an equivalent query scan for a primary key or unique index returns at most one row of data. The speed is very fast because it can be read only once. Eq_ref: this type usually appears in multi-table join queries, indicating that for each result of the previous table, only one row of the latter table can be matched, and the comparison operation of the query is usually =, the query is more efficient ref: this type usually appears in multi-table join queries, for non-unique or non-primary key indexes Or the query range that uses the leftmost prefix rule index: range scan this type usually appears in, >, > =

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.