MySQL Architecture overview-Query execution Process->sql parse order

Preface:Always want to know how a SQL statement is executed, the order in which it executes, and then review the summary of the information, there is the following blog post. This article explores the knowledge from the MySQL overall architecture---> Query execution process---> statement execution sequence. First, MySQL Architecture overview:The architecture is best viewed, with the necessary explanatory text. 　　According to the reference book in a picture as originally, and then added on its own understanding. As we can see, the entire architecture is divided into two layers, the upper layer is called the ' SQL Layer ', the lower layer is a variety of mysqld interface on the storage engine, known as the ' Storage engine Layer '. The other modules and components, from the name of the simple understanding of their role, here is no longer to be described. second, the query execution processAnd then go ahead and let me tell you what the process of query execution is like: 1. Connect1.1 The client initiates a query request and listens to the client's ' Connection Management module ' to receive request 1.2 to forward the request to ' Connect to/thread module ' 1.3 Call ' user module ' to perform authorization check 1.4 after checking, ' connect the In/thread module ' from ' Thread connection pool ' takes out the free cached connection thread and the client request docking, and if it fails, creates a new connection request 2. Handling2.1 First query the cache, check whether the query statement exactly match, and then check whether there is permission, all successful then directly fetch the data returned 2.2 The previous step has failed to the ' command parser ', after lexical analysis, parsing parse tree 2.3 is followed by the preprocessing phase, Processing parser cannot resolve the semantics, check permissions, etc., generate a new parse tree 2.4 and then to the corresponding module processing 2.5 if the Select query will also be a lot of optimization through the ' query optimizer ', Generate execution Plan 2.6 module received the request, through the ' The access control module ' checks if the connected user has permission to access the target table and Target fields 2.7 have the call to the ' Table Management module ', first to see if the table cache exists, there are direct corresponding tables and get the lock, otherwise reopen the table file 2.8 based on the meta data of the table, Gets information such as the storage engine type of the table, calls the corresponding storage engine through the interface to process 2.9 data changes during the above process, if the log function is turned on, it will be recorded in the corresponding binary log file 3. ResultsAfter the 3.1Query request is completed, return the result set to the ' Connect to/thread module ' 3.2 returned can also be the corresponding status identification, such as success or failure, etc. 3.3 ' Connect to/thread module ' for subsequent cleanup work, and continue to wait for request or disconnect the client connection   a small summary of the figure third, SQL parsing orderNext step, let's take a look at a SQL statement of the past life. First look at the sample statement

SELECT DISTINCT    < select_list >from    < left_table > < Join_type >join < right_table > on &L T Join_condition >where    < where_condition >group by    < group_by_list >having    < Having_ Condition >order by    < order_by_condition >limit < Limit_number >

But the order of its execution is like this

1 from <left_table> 2 on <join_condition> 3 <join_type> join <right_table> 4 WHERE <where_condi Tion> 5 GROUP by <group_by_list> 6 have 
Although I did not think so, but a look is still very natural and harmonious, from where to obtain, constant filtration conditions, to choose the same or different, the order, that only know how to take the first few. That being the case, let's take a step-by-step look at the details.preparatory work1. Create a test database
Create DATABASE Testquery
2. Create a test table
CREATE TABLE table1 (    uid varchar) NOT NULL,    name VARCHAR (TEN) not NULL,    PRIMARY KEY (UID)) Engine=innodb DEFAULT Charset=utf8; CREATE TABLE table2 (    oid INT not NULL auto_increment,    uid VARCHAR (Ten),    PRIMARY KEY (OID)) Engine=innodb DEFAULT Charset=utf8;
3. Inserting data
Insert INTO table1 (uid,name) VALUES (' AAA ', ' Mike '), (' BBB ', ' Jack '), (' CCC ', ' Mike '), [' DDD ', ' Mike '), insert into table2 ( UID) VALUES (' AAA '), (' AAA '), (' BBB '), (' BBB '), (' BBB '), (' CCC '), (NULL);
4. The final desired result
SELECT    A.uid,    count (b.oid) as Totalfrom    table1 as Aleft JOIN table2 as B on a.uid = B.uidwhere    A. NAME = ' Mike ' GROUP by    a.uidhaving    count (b.oid) < 2ORDER to total    desclimit 1;
！ Start the SQL Resolution tour now!1. fromWhen multiple tables are involved, the output from the left table is used as input to the right table and a virtual table VT1 is generated. (1-J1) Cartesian product calculates the Cartesian product (cross JOIN) of two associated tables, generating a virtual table vt1-j1.
Mysql> SELECT * FROM table1,table2;+-----+------+-----+------+| UID | name | OID | UID  |+-----+------+-----+------+| AAA | mike |   1 | AAA |  | bbb | jack |   1 | AAA |  | ccc | mike |   1 | AAA  | | ddd | mike |   1 | AAA |  | aaa | mike |   2 | AAA |  | bbb | jack |   2 | AAA |  | ccc | mike |   2 | AAA  | | ddd | mike |   2 | AAA |  | aaa | mike |   3 | BBB |  | bbb | jack |   3 | BBB |  | ccc | mike |   3 | BBB |  | ddd | mike |   3 | BBB  | | aaa | mike |   4 | BBB |  | bbb | jack |   4 | BBB |  | ccc | mike |   4 | BBB |  | ddd | mike |   4 | BBB  | | aaa | mike |   5 | BBB |  | bbb | jack |   5 | BBB |  | ccc | mike |   5 | BBB |  | ddd | mike |   5 | BBB  | | aaa | mike |   6 | CCC |  | bbb | jack |   6 | CCC |  | | CCC | mike |   6 | CCC  | | ddd | mike |   6 | CCC |  | aaa | mike |   7 | NULL | | BBB | Jack |   7 | NULL | | CCC | Mike |   7 | NULL | | DDD | Mike |   7 | NULL |+-----+------+-----+------+28 rows in Set (0.00 sec)
(1-J2) On filtering is based on the virtual table vt1-j1, which filters out all the columns that satisfy the ON predicate condition and generates the virtual table vt1-j2. Note: Because of the grammatical constraints, the use of ' where ' instead, the reader can also feel the subtle relationship between the two;
mysql> SELECT    ------Table1, table2, WHERE--    table1.uid = Table2.uid +    -----+------+-----+------+| UID | name | oid | UID  |+-----+------+-----+------+| AAA | Mike |
   1 | AAA |  | aaa | mike |   2 | AAA |  | bbb | jack |   3 | BBB |  | bbb | jack |   4 | BBB |  | bbb | jack |   5 | BBB |  | ccc | mike |   6 | CCC  |+-----+------+-----+------+6 rows in Set (0.00 sec)
(1-J3) To add an outer column if an outer join (Left,right,full) is used, the column in the primary table (reserved table) that does not meet the on condition is also added to the vt1-j2 as an outer row, generating the virtual table vt1-j3.
Mysql> SELECT, *, from table1 as a, left    OUTER joins table2 as B on a.uid = b.uid;+ -----+------+------+------+| UID | name | OID  | UID  |+-----+------+------+------+| AAA | mike |    1 | AAA |  | aaa | mike |    2 | AAA |  | bbb | jack |    3 | BBB |  | bbb | jack |    4 | BBB |  | bbb | jack |    5 | BBB |  | ccc | mike |    6 | CCC  | | ddd | mike | NULL | NULL |+-----+------+------+------+7 rows in Set (0.00 sec)
Below from the Internet to find a very image of the ' SQL JOINS ' interpretation, if the infringement of your interests, please ask to delete, thank you.2. WHEREThe temporary tables generated during the VT1 process are filtered, and the columns that satisfy the WHERE clause are inserted into the VT2 table. Note: The aggregation operation cannot be used at this time because of grouping, and the alias created in select cannot be used; the difference between on and on is that if there is an external column, on is the associated table for filtering, the primary table (the reserved table) returns all columns, and if no external columns are added, the effect is the same. ; application: The filter of the main table should be placed in where, for the correlation table, the first condition query after the connection is used on, the first connection after the conditional query with where;
Mysql> SELECT, *, from table1 as a, left    OUTER joins table2 as B on a.uid = B.uid
   -> WHERE    , a. Name = ' Mike ', +-----+------+------+------+| UID | NAME | oid  | UID  |+-----+------+--- ---+------+| AAA | Mike |    1 | AAA |  | aaa | mike |    2 | AAA |  | ccc | mike |    6 | CCC  | | ddd | mike | NULL | NULL |+-----+------+------+------+4 rows in Set (0.00 sec)
3. GROUP byThis clause groups the tables generated in VT2 by the columns in group by. Generates a VT3 table. Note: Subsequent statements of the process, such as select,having, must be included in the GROUP by, and the aggregate function is used if it is not present, because GROUP by changes the reference to the table, converts it to a new reference method, Columns with the ability to perform the next level of logical operations are reduced; my understanding is that the records with the same grouping fields are merged into one record according to the grouping fields, because each grouping can only return one record, unless it is filtered out, and the fields that are not in the Group field may have multiple values, and multiple values cannot be placed in a single record. , these columns with multivalued values must be converted to single values through aggregate functions;
Mysql> SELECT, *, from table1 as a, left    OUTER joins table2 as B on a.uid = B.uid
   ->    . A. Name = ' Mike ' GROUP by,    a.uid;+-----+------+------+------+| UID | name | oi D  | uid  |+-----+------+------+------+| AAA | mike |    1 | AAA |  | ccc | mike |    6 | CCC  | | ddd | mike | NULL | NULL |+-----+------+------+------+3 rows in Set (0.00 sec)
4. havingThis clause filters the different groups in the VT3 table, only the data that is used for grouping, and the clauses that satisfy the having condition are added to the VT4 table.
Mysql> SELECT, *, from table1 as a, left    OUTER joins table2 as B on a.uid = B.uid
   -> WHERE    , a. NAME = ' Mike '    , GROUP by    , A.uid    , have    count (b.oid) < 2;+-----+------+------+------+| UID | name | OID  | UID  |+-----+------+------+------+| CCC | mike |    6 | CCC  | | ddd | mike | NULL | NULL |+-----+------+------+------+2 rows in Set (0.00 sec)
5. SELECTThis clause processes the elements in the SELECT clause and generates a VT5 table. (5-J1) evaluates the expression in the SELECT clause, generates VT5-J1 (5-J2) distinct the duplicate column in the vt5-1, and deletes it, generating vt5-j2 if a distinct clause is specified in the query, A temporary memory table is created (if the memory doesn't fit, it needs to be stored on the hard drive). The table structure of this temporary table is the same as the virtual table VT5 generated in the previous step, except for the addition of duplicate data by adding a unique index to the column that performed the distinct operation.
Mysql> SELECT    , A.uid,    count (b.oid) as total,    table1 as    a    ER JOIN table2 as B on a.uid = B.uid,    WHERE    -A. NAME = ' Mike '    GROUP by,    a.uid    -&G T Have    count (b.oid) < 2;+-----+-------+| UID | total |+-----+-------+| CCC |     1 | | DDD |     0 |+-----+-------+2 rows in Set (0.00 sec)
6.ORDER byFrom a table in Vt5-j2, the results are sorted according to the conditions of the ORDER BY clause, resulting in a VT6 table. Note: The only place where you can use aliases in select;
Mysql> SELECT    , A.uid,    count (b.oid) as total,    table1 as    a    ER JOIN table2 as B on a.uid = B.uid,    WHERE    -A. NAME = ' Mike '    GROUP by,    a.uid    -&G T Have    , COUNT (b.oid) < 2, ORDER by, total    desc;+-----+-------+| UID | total |+-----+---- ---+| CCC |     1 | | DDD |     0 |+-----+-------+2 rows in Set (0.00 sec)
7.LIMITThe LIMIT clause selects the specified row data starting at the specified location from the VT6 virtual table obtained in the previous step. Note: The positive and negative effects of offset and rows, when the offset is very high efficiency is very low, you can do this: the Sub-query optimization, in the subquery first obtained from the index to the maximum ID, then the reverse row, and then fetch n rows result set using inner join optimization, The join clause also takes precedence from the index to get the list of IDs, and then directly associates the query to get the final result
Mysql> SELECT    , A.uid,    count (b.oid) as total, table1 as a, and left    JOI N table2 as B on a.uid = B.uid,    WHERE    -A. NAME = "Mike", GROUP by,    A.uid,    HAV    count (B.oid) < 2, ORDER by, total    DESC,    LIMIT 1;+-----+-------+| UID | tot Al |+-----+-------+| CCC |     1 |+-----+-------+1 row in Set (0.00 sec)
So this is the end of the parsing trip to SQL, to summarize:Reference Books:MySQL Performance tuning and architecture practices MySQL Technology Insider: SQL Programming 
MySQL Architecture overview-Query execution Process->sql parse order