Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the parameters and meaning of the MySQL implementation plan. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

View the execution plan

You can use explain + SQL or desc + SQL

1. Use explain to view the execution plan mysql > explain select * from employee +-+ | id | select _ type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | employee | NULL | ALL | NULL | 8 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.01 sec) 2. Use desc to view the execution plan mysql > desc select * from employee +-+ | id | select _ type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | employee | NULL | ALL | NULL | 8 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec) 1, ID

The sequence number of the select query; contains a set of numbers indicating the order in which the select clause or action table is executed in the query.

The value of id is divided into three cases:

Id is exactly the same

Id is completely different.

The same part of id is different.

1. Id is exactly the same

Associate three tables

EXPLAINSELECT * FROM EMPLOYEE E, DEPARTMENT D, CUSTOMER CWHERE E.DEP_ID = D.ID AND E.CUS_ID = C.ID +-- + -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+- -+ | * * 1employees * | SIMPLE | C | NULL | ALL | PRIMARY | NULL | 1 | 100.00 | NULL | | 1 | SIMPLE | E | NULL | ALL | NULL | 8 | 12.50 | Using where Using join buffer (Block Nested Loop) | | 1 | SIMPLE | D | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.E.dep_id | 1 | 100.00 | NULL | +- +- -+ 3 rows in set 1 warning (0.01 sec)

You can see that the data in the id column is all the same, all 1, and the execution order is from top to bottom (scan c first, scan e, and finally scan d).

2. Id is completely different.

Use nested subqueries

EXPLAIN SELECT * FROM DEPARTMENTWHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)) +- -+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | 4 | const | 1 | 100.00 | Using index | + -+ 3 rows in set 1 warning (0.01 sec)

Id is 1, 2, 3. If it is a subquery, the sequence number of id will be incremented. The higher the id value, the higher the priority, and it will be executed first.

3. The same part of id is different.

Use a subquery as a table

EXPLAIN SELECT * FROM DEPARTMENT D, (SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) EWHERE D.ID = E.DEP_ID +- -+ | 1 | PRIMARY | D | NULL | ALL | PRIMARY | NULL | NULL | NULL | | 5 | 100.00 | NULL | | 1 | PRIMARY | | NULL | ref | 5 | demo.D.id | 2 | 100.00 | Using index | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | Using temporary | +-| +-+ 3 rows in set 1 warning (0.00 sec)

Part of the id is the same, part is different. Can be thought of as a group, (id=1 is one group, id=2 is another group) is executed sequentially from top to bottom; in all groups, the higher the id value, the higher the priority, the first execution is; first, the execution of 2 groups, and then the execution of group 1, within group 1, the same execution order is still from top to bottom.

Final execution order:

① id = 2 execute first

The first line in the group with ② id = 1 executes first (d)

The second line in the group with ③ id = 1 finally executes ()

For ID fields, the same values follow the order of execution from top to bottom; different values follow large values first.

II. Select_type

Query types, mainly used to distinguish between ordinary queries, federated queries, subqueries and other complex queries

SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT (DEPENDENT SUBQUERY, DEPENDENT UNION)

1. SIMPLE simple query

Simple select query, which does not include subqueries or UNION.

Query employee

EXPLAIN SELECT * FROM EMPLOYEE +-+ | id | select _ type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+ | 1 | SIMPLE | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | NULL | + -+ 1 row in set 1 warning (0.00 sec)

The type is simple and only a single table is queried.

2. PRIMARY outer layer query

If the query contains any complex subqueries, the outermost query is marked as primary.

EXPLAIN SELECT * FROM DEPARTMENTWHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)) +- -+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | 4 | const | 1 | 100.00 | Using index | + -+ 3 rows in set 1 warning (0.00 sec)

The select_type of Id=1 is primary, the table department is scanned, and the query is the outermost query, so it is marked primary.

3. SUBQUERY subquery 3.1, SUBQUERY does not depend on external queries

A subquery is included in the select or where, and the first select query in the subquery does not depend on the external query results.

EXPLAIN SELECT * FROM DEPARTMENTWHERE ID = (SELECT ID FROM EMPLOYEE WHERE ID = (SELECT ID FROM CUSTOMER WHERE ID = 1)) +- -+ | id | select_type | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -+-+ | 1 | PRIMARY | DEPARTMENT | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | 2 | SUBQUERY | EMPLOYEE | NULL | const | PRIMARY | PRIMARY | | 4 | const | 1 | 100.00 | Using index | | 3 | SUBQUERY | CUSTOMER | NULL | const | PRIMARY | 4 | const | 1 | 100.00 | Using index | + -+ 3 rows in set 1 warning (0.00 sec)

The type of Id = 2 and id = 3 is subquery, is a subquery, is marked subquery, and because the subquery does not depend on external query results, only subquery is displayed.

3.2.The SUBQUERY depends on external queries

The content of the subquery depends on the content of the external query, the most obvious scalar quantum query.

EXPLAIN SELECT ID, (SELECT DEP_IDFROM EMPLOYEE E WHERE E.ID = D.ID) DEP_IDFROM DEPARTMENT D +- -- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | D | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | 2 | DEPENDENT SUBQUERY | E | NULL | eq_ref | PRIMARY | PRIMARY | 4 | demo.D.id | 1 | 100.00 | NULL | +- -+ 2 rows in set 2 warnings (0.00 sec)

The subquery in select depends on the results of the external query d table, so id = 2 is a subquery and is a subquery that depends on the external query, marked as DEPENDENT SUBQUERY.

4. DERIVED derivative table (temporary table)

The subquery contained in the from list is marked as derived (derivation), and the result is placed in the temporary table.

EXPLAIN SELECT * FROM DEPARTMENT D, (SELECT DEP_ID FROM EMPLOYEE GROUP BY DEP_ID) EWHERE D.ID = E.DEP_ID +- -+ | 1 | PRIMARY | D | NULL | ALL | PRIMARY | NULL | NULL | NULL | | 5 | 100.00 | NULL | | 1 | PRIMARY | | NULL | ref | 5 | demo.D.id | 2 | 100.00 | Using index | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | 8 | 100.00 | Using temporary | +-| +-+ 3 rows in set 1 warning (0.00 sec)

As you can see from id=2, execute 2 first, and from table, you can see that the table being scanned is employee, the table of the subquery, and the results of the subquery are placed in a temporary table and marked as DERIVED.

5. UNION5.1 and UNION do not depend on external queries

If the second select appears after the union, it is marked as union and is independent of the external query.

EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | | 2 | UNION | DEPARTMENT | NULL | | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+-+ 3 rows in set 1 warning (0.00 sec)

The query is a union made by two separate select, and there is no subquery, so mark the type of id=2 as UNION directly, and the first select will be marked as primary by default, and the select used for union will be executed before that select.

5.2.The UNION relies on external queries

If the second select appears after union, it is marked as union and is dependent on an external query.

EXPLAIN SELECT ID, NAMEFROM CUSTOMERWHERE ID IN (SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT) +- -- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+-+ | 1 | PRIMARY | CUSTOMER | NULL | ALL | NULL | 1 | | 100.00 | Using where | | 2 | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | 4 | func | 1 | 100.00 | Using index | NULL | UNION RESULT | NULL | ALL | NULL | | | NULL | Using temporary | +-+ -- + 4 rows in set 1 warning (0.01 sec)

Again, the emp table and the dep table have union operations, but now they are in the subquery, and the result set is now associated with the result set of the external query. The id=3 executed first is the department table, and the query is after the union. Because of the connection with the external query, what is marked as the second step of DEPENDENT UNION; is the table employee of id= 2, this part is a subquery part, and is related to the external query, so it is a dependent subquery, marked as the DEPENDENT SUBQUERY; last execution is the id=1 part, the outermost query, marked as primary.

5.3.The UNION in the from clause

If union is included in a subquery of the from clause, the outer select will be marked as deriver (the deriver table must have an alias), and an error will be reported if there is no alias.

If you don't write an alias, you will report an error:

EXPLAINSELECT * FROM (SELECT ID, NAME FROM EMPLOYEE UNION SELECT ID, DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias

Write it correctly and write an alias:

EXPLAIN SELECT * FROM (SELECT ID, NAME FROM EMPLOYEE UNION SELECT ID, DEPTNAME FROM DEPARTMENT) T +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | | NULL | ALL | NULL | 13 | 100.00 | NULL | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | 8 | 100.00 | NULL | 3 | UNION | DEPARTMENT | NULL | ALL | NULL | 5 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + +-+ 4 rows in set 1 warning (0.00 sec)

Slightly different from the above, the subquery of union is now placed directly in from as a table, rather than going to in or exists joins based on the fields of a table, and the execution plan will be different.

The first point: the writing of select * from (select * from b) must give an alias to the content in parentheses. If there is no alias, an error of "1248-Every derived table must have its own alias" will be reported.

Second point: when using this method, the query in parentheses is generated into a derived table (temporary table) marked DERIVED, and the subquery is further analyzed. The department table of id=3 is executed first, because after union, the select_type of the operation on the table is marked as union;, followed by the employee table of id=2, where it is marked as DERIVED. Finally, the id=3 is executed, and the outermost layer is select * from (), marked as primary, external query, and because it is the record of the derived table in query parentheses, the table scanned is the derived table deriverd2.

6 、 UNION RESULT

Get the result select; from the union table. The result set of the two UNION merging is at the end.

If the second select appears after the union, it is marked as union and is independent of the external query.

EXPLAIN SELECT ID FROM EMPLOYEE UNION SELECT ID FROM DEPARTMENT +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -- + | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | | 2 | UNION | DEPARTMENT | NULL | | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + -+-+ 3 rows in set 1 warning (0.00 sec)

The query is a union carried out by two separate select, there is no subquery, the id=2 is executed first, the department is scanned, the employee table is scanned, and then the two separate query result sets are merged, using UNION RESULT to represent the merge step of the union operation, and union needs to sort the two parts of the data and then merge them, so there will be UNION RESULT only when there is sorting.

7. UNION ALL7.1 and UNION ALL do not depend on external queries

If the second select appears after the union, it is marked as union and is independent of the external query.

EXPLAIN SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+-- -+-+ | 1 | PRIMARY | EMPLOYEE | NULL | index | NULL | PRIMARY | 4 | NULL | 8 | 100.00 | Using index | 2 | UNION | DEPARTMENT | NULL | index | NULL | PRIMARY | 4 | NULL | 5 | 100.00 | Using index | +- +-+-+ 2 rows in set 1 warning (0.00 sec)

The query is a union made by two separate select, and there is no subquery, so mark the type of id=2 as UNION directly, and the first select will be marked as primary by default, and the select used for union will be executed before that select. The Id part and the select_type part are the same as the union, except for the last UNION RESULT. This part is generated because the data of the two parts are sorted in the merge, while the union all does not need to sort, only the two parts need to be merged, so there will be no UNION RESULT.

7.2. UNION ALL depends on external queries

If the second select appears after union, it is marked as union and is dependent on an external query.

EXPLAIN SELECT ID, NAMEFROM CUSTOMERWHERE ID IN (SELECT ID FROM EMPLOYEE UNION ALL SELECT ID FROM DEPARTMENT) +- -- +-+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | + -+ | 1 | PRIMARY | CUSTOMER | NULL | ALL | NULL | 1 | 100.00 | Using where | | 2 | | DEPENDENT SUBQUERY | EMPLOYEE | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | 3 | DEPENDENT UNION | DEPARTMENT | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using index | + -+ 3 rows in set 1 warning (0.00 sec)

It's still the same as UNION, except for the UNION RESULT part.

7.3.The UNION ALL in the from clause

If union all is included in a subquery of the from clause, the outer select will be marked as deriver (the deriver table must have an alias), and an error will be reported if there is no alias.

If you don't write an alias, you will report an error:

EXPLAIN SELECT * FROM (SELECT ID, NAME FROM EMPLOYEE UNION SELECT ID, DEPTNAME FROM DEPARTMENT); ERROR 1248 (42000): Every derived table must have its own alias

Write it correctly:

EXPLAIN SELECT * FROM (SELECT ID, NAME FROM EMPLOYEE UNION SELECT ID, DEPTNAME FROM DEPARTMENT) T +- -+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +-+ -- +-+ | 1 | PRIMARY | | NULL | ALL | NULL | 13 | 100.00 | NULL | | 2 | DERIVED | EMPLOYEE | NULL | ALL | NULL | NULL | 8 | 100.00 | NULL | 3 | UNION | DEPARTMENT | NULL | ALL | NULL | 5 | 100.00 | NULL | | NULL | UNION RESULT | | NULL | ALL | NULL | Using temporary | + +-+ 4 rows in set 1 warning (0.00 sec)

It's the same as union, but without the UNION RESULT part.

These are the parameters and meanings of the MySQL implementation plan shared by the editor. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.