Explain in MySQL

Time:2020-11-22

explain
Explain simulates the optimizer to execute SQL statements. In 5.6 and later versions, except for select, others such as insert, update and delete can use explain to view the execution plan, so as to know how MySQL processes SQL statements and analyze the performance bottlenecks of query statements or table structures.
effect
1. Read order of tables
2. The operation type of the data read operation
3. Which indexes can be used
4. Which indexes are actually used
5. References between tables
6. How many rows per table are queried by the optimizer

Explain usage
Explain + SQL statement is enough!
The information contained in the execution plan is as follows

Information description
The ordinal number of the ID query, which contains a set of numbers, represents the order in which the select clause or operation table is executed in the query
Two cases
ID is the same, and the execution order is from top to bottom
ID is different. The higher the ID value, the higher the priority and the earlier the execution
select_ Type query type is mainly used to distinguish complex queries such as ordinary query, joint query and subquery
1. Simple — a simple select query with no subquery or union in the query
2. If any of the outer parts of the query are marked with the most complex child
3. Subquery — contains subqueries in the select or where list
4. Derived — the subqueries contained in the from list are marked as derived, and MySQL will execute these subqueries recursively and put the results into the temporary table
5. Union — if the second select appears after the union, it is marked as a union; if the union is included in the subquery of the from clause, the outer select is marked as derived
6、union result:UNION The results of
Table the table referenced by the row output from table
Type shows the type of join, which type is used by the query, sorted from the best to the worst type
1. System: there is only one row in the table (= system table), which is a special case of const join type.
2. Const: indicates that it is found once through the index. Const is used to compare primary key or unique index. Because only one row of data is matched, if the primary key is placed in the where list, MySQL can convert the query into a constant
3、eq_ Ref: unique index scan. For each index key, only one record in the table matches it. Common for unique index or primary key scanning
4. Ref: non unique index scan, which returns all rows matching a single value, is also an index access in essence. It returns all rows that match a single value, and may find multiple qualified rows, which belongs to the mixture of search and scan
5. Range: retrieve only the rows of a given range, using an index to select rows. The key column shows which index is used. Generally, queries in the range of between and in appear in the where statement. This range scan index scan is better than a full table scan because it starts at one point in the index and ends at another point without a full table scan
6、 index:index Unlike all, the index type only traverses the index tree. Usually faster than all because the index file is much smaller than the data file.
7. All: traverse the entire table to find matching rows
Note: it is generally guaranteed that the query reaches at least the range level, and it is better to achieve Ref.
possible_ Keys indicates which index MySQL can use to find rows in the table
Key displays the key (index) that MySQL actually decides to use. If no index is selected, the key is null. If an overlay index is used in a query, the index overlaps the select field of the query.
key_ Len represents the number of bytes used in the index. This column calculates the length of the index used in the query. The shorter the length, the better without losing precision. If the key is null, the length is null. The field is displayed as the maximum possible length of the index field, not the actual length used.
Ref shows which column of the index is used and, if possible, a constant, which columns or constants are used to query the values on the index column
According to table statistics and index selection, rows roughly estimates the number of rows to be read to find the required records
Extra contains additional information that is not suitable for display in other columns, but is important
1. Using filesort: indicates that MySQL will apply an external index sort to the data. Instead of reading in the order of the table. MySQL can’t use index to complete sorting operation, which is called “file sorting”
2. Using temporary: temporary table is used to save intermediate results, and MySQL uses temporary table when sorting query results. It is commonly used in sorting order by and grouping query group by.
3. Using index: indicates that the corresponding select operation is used to cover the index to avoid accessing the data rows of the table. If using where appears at the same time, the table name index is used to perform the search of index key value; if there is no using where at the same time, the table name index is used to read data rather than perform query actions.
4. Using where: indicates where filtering is used
5. Using join buffer: connection cache is used
6、impossible where:where Clause is always false and cannot be used to get any tuples
7. Select tables optimized away: in the case of no group by clause, optimize min and Max operations based on index, or optimize count (*) for MyISAM storage engine without waiting for the execution phase to calculate, and the query execution plan generation phase completes the optimization.
8. Distinct: optimize the distinct operation to stop looking for the same value after finding the first matching tuple.
SQL execution order
If you want to optimize SQL, you must know the execution order of SQL, so that you can get twice the result with half the effort with explain!
Complete SQL statement

select distinct

    <select_list>

from

<left_table><join_type>

join <right_table> on <join_condition>
where

<where_condition>

group by

<group_by_list>

having

<having_condition>

order by

<order_by_condition>

limit <limit number>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL execution order

1、from <left_table><join_type>
2、on <join_condition>
3、<join_type> join <right_table>
4、where <where_condition>
5、group by <group_by_list>
6、having <having_condition>
7、select
8、distinct <select_list>
9、order by <order_by_condition>
10、limit <limit_number>
1
2
3
4
5
6
7
8
9
10

————————————————
Copyright notice: This article is a CSDN blogger ” [email protected] Please attach a link to the original source of the original article and this statement.
Link to the original text: https://blog.csdn.net/jiadaji…