How to use MySQL explain

Time:2020-7-13

In our daily work, we sometimes slow down the query to record some SQL statements that have been executed for a long time. Finding out these SQL statements does not mean that it is finished. Sometimes, we often use the command explain to check the execution plan of these SQL statements to see whether the SQL statement has used the index and whether the full table scan has been done. All these can be done through the explain command To see. So we can learn more about MySQL’s overhead based optimizer, and we can also get a lot of details of access policies that may be considered by the optimizer, and which strategy is expected to be adopted by the optimizer when running SQL statements.

--Actual SQL, find the employee whose user name is jefabc
			select * from emp where name = 'Jefabc';
			--Check whether SQL uses index or not, just add explain before it
			explain select * from emp where name = 'Jefabc';

There are 10 columns of information from expand, which are ID and select_ type、table、type、possible_ keys、key、key_ len、ref、rows、Extra

Brief description:
ID: select identifier
select_ Type: indicates the type of query.
Table: the table that outputs the result set
Partitions: matching partitions
Type: indicates the connection type of the table
possible_ Keys: represents the index that may be used when querying
Key: represents the actual index used
key_ Len: the length of the index field
Ref: comparison of columns and indexes
Rows: the number of rows scanned (estimated)
Filtered: percentage of rows filtered by table criteria
Extra: description and description of implementation

The following explains the possible occurrences of these fields:

1、 ID

Select identifier. This is the query sequence number of the select

My understanding is the identification of the order of SQL execution, SQL execution from large to small

1. When the ID is the same, the execution order is from top to bottom

2. If it is a subquery, the sequence number of ID will be increased. The higher the ID value, the higher the priority, and the earlier it is executed

3. If the ID is the same, it can be regarded as a group and executed from top to bottom; in all groups, the higher the ID value, the higher the priority, the earlier the execution

--See employees in R & D department with names beginning with Jef
			explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = 'R & D department';

 

2、 Select_ type

Shows the type of each select clause in the query

(1) Simple (simple select, no union or subquery, etc.)

(2) Primary (the outermost query in the subquery. If the query contains any complex sub parts, the outermost select is marked as primary)

(3) Union (the second or subsequent select statement in a union)

(4) Dependent Union (the second or subsequent select statement in the union, depending on the external query)

(5) Union result (the result of union, all the selections after the beginning of the second select in the union statement)

(6) Subquery (first select in subquery, result independent of external query)

(7) Dependent subquery (first select in subquery, dependent on external query)

(8) Derived (sub query of select, from clause of derived table)

(9) Uncacheable subquery (the result of a subquery cannot be cached, the first row of the outer link must be reevaluated)

3、 Table

Display the name of the table in the database accessed in this step (which table the data in this row is about). Sometimes it is not the real name of the table. It may be the abbreviation, such as e and D above, or the abbreviation of the result executed in the next step

4、 Type

Table access method, which means the way MySQL finds the required row in the table, is also called “access type”.

Common types are: all, index, range, ref, Eq_ Ref, const, system, null (from left to right, performance from poor to good)

All: full table scan, MySQL will traverse the whole table to find matching rows

Index: full index scan. The difference between index and all is that index type only traverses the index tree

Range: retrieve only the rows in the given range, and select the rows with one index

Ref: represents the join matching criteria of the above table, that is, which columns or constants are used to find values on index columns

Eq_ Ref: similar to ref, the difference is that the index used is a unique index. For each index key value, there is only one record matching in the table. In short, primary key or unique key is used as the association condition in multi table join

Const, system: when MySQL optimizes a part of the query and converts it to a constant, these types of access are used. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of const type. When the query table has only one row, use system

Null: MySQL decomposes statements in the optimization process, and does not even need to access the table or index when executing. For example, selecting the minimum value from an index column can be completed through a single index lookup.

5、 Possible_ keys

Indicates which index MySQL can use to find records in the table. If there is an index on the fields involved in the query, the index will be listed, but not necessarily used by the query (the available indexes of the query will display null if there is no index)

This column is completely independent of the order of the tables shown in the explain output. This means that in possible_ Some of the keys in keys cannot actually be used in the order of the generated table.
If the column is null, there is no associated index. In this case, you can improve your query performance by checking the where clause to see if it references some columns or columns suitable for indexing. If so, create an appropriate index and check the query again with explain

6、 Key

The key column shows the key (index) that MySQL actually decides to use, which must be included in possible_ Keys

If no index is selected, the key is null. To force Mysql to use or ignore possible_ The index in the keys column. Use force index, use index or ignore index in the query.

7、 Key_ Len

Represents the number of bytes used in the index. The length (key) of the index used in the query can be calculated by this column_ The value displayed by Len is the maximum possible length of the index field, not the actual length, i.e. key_ Len is calculated according to the table definition, not retrieved from the table)

The shorter the length, the better without losing accuracy

8、 Ref

Column to index comparison represents the join matching criteria of the above table, that is, which columns or constants are used to find values on index columns

9、 Rows

The number of rows in the result set is estimated by MySQL according to the table statistics and index selection

10、 Extra

This column contains the details of MySQL solving the query, which can be found in the following cases:

Using where: without reading all the information in the table, the required data can be obtained only through the index. This occurs when all the requested columns of the table are part of the same index, which means that the MySQL server will filter the rows after the storage engine retrieves the rows

Using temporary: indicates that MySQL needs to use temporary tables to store result sets, which are commonly used in sorting and grouping queries, such as group by; order by

Using filesort: when the query contains an order by operation and cannot be completed by using the index, it is called “file sorting”

--Test extra's filesort
	explain select * from emp order by name;

Using join buffer: the value change emphasizes that the index is not used when getting the connection conditions, and the connection buffer is needed to store the intermediate results. If this value appears, it should be noted that, depending on the specific situation of the query, you may need to add indexes to improve the performance.

This result is not possible to be collected by the where statement.

Select tables optimized away: this value means that by using the index only, the optimizer may return only one row from the aggregate function result

No tables used: use from dual or no from clause in query statement


	-- explain select now() from dual;

Summary:

  • Explain doesn’t tell you about triggers, stored procedures, or the impact of user-defined functions on queries
  • Explain does not consider various caches
  • Explain does not display the optimization work done by MySQL during query execution
  • Some statistics are estimated, not exact
  • Expalin can only interpret the select operation. Other operations should be rewritten to view the execution plan after select.

No results are possible by collecting statistics

Author: Jack sler
source: http://www.cnblogs.com/tufujie/

The above is the whole content of this article, I hope to help you in your study, and I hope you can support developeppaer more.