Detailed explanation of explain usage for MySQL learning

This article brings you relevant knowledge about mysql, which mainly introduces related issues about explain. The explain command is mainly used to view the execution plan of the SQL statement. Check the execution plan of the SQL statement. I didn't use indexes, did I do a full table scan, I hope it will be helpful to everyone.

Recommended learning: mysql tutorial

The explain command is mainly used to view the execution plan of the SQL statement and check whether the SQL statement has been used. Index, whether there is a full table scan, etc. It can simulate the optimizer to execute SQL query statements to know how MySQL handles user SQL statements.

1. What explain can do

Through the explain statement, we can analyze the following results

Reading order of the table	Operation type of data read operation
References between tables	Which indexes can be used
How many rows of each table are queried by the optimizer	Which indexes are actually used

---

2. How to use explain

## 

Usage: explain SQL statement; type, possible_keys, key, key_len, ref, rows, Extra 　　Summary description:

##Field

DescriptionSelect identifier

	id
select_type	Query Type
table	The table that outputs the result set
type	The connection type of the table
possible_keys	Indices that may be used when querying
key	Index actually used
key_len	The length of the index field
ref	Comparison of columns and indexes
rows	The number of scanned rows (estimated number of rows)
Extra	Description and explanation of the execution
3. Explain the meaning of each field 3.1 id 　id is the sequence number of the select query, including a set of numbers, indicating the execution of the query The order of select clauses or operation tables. The result of id has the following three situations:   ●● The id is the same, the execution order is from top to bottom, regardless of the order in sql If it is a child For queries, the serial number of the id will increase. The larger the id, the higher the priority, and the earlier it will be executed ## ●● If the id is the same, it can be considered as a group, from top to bottom Executed sequentially; in all groups, the larger the id value, the higher the priority and the first to execute. 3.2 select_type 　 select_typeDisplays the type of each select clause in the query, the type of commonly used select_type There are simple, primary, subquery, derived, union, union result   (1) simple (Simple select, no complex queries such as union or subquery are used)  (2) primary (The outermost query in the subquery, if the query contains any complex subparts, the outermost select is marked as primary) Sync (3) subquery (Subquery included in select or where list) Sync (4) derived (The subqueries contained in the from list are marked as derived (derived), MySQL will recursively execute these subqueries and put the results in a temporary table)   (5) union (The second or subsequent select statement in the union)  (6) union result (The result of the union, the second select statement in the union statement All selects after select start) ##3.3 table 　 table Display the name of the table in the database accessed in this step (display Which table does this row of data refer to?). 3.4 type   type shows which type is used in the query. Type includes all, index, range, ref, eq_ref, const, system, NULL, its performance increases in order. # ● all : Full Table Scan, MySQL will traverse the entire table to find matching rows  ## ● ● index : Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree · range : Only retrieves rows in a given range, Use an index to select rows · ref : Indicates the join matching conditions of the above table, that is, which columns or constants are used to find the index columns The value of ● 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 in the table Record matching, simply put, is the use of primary key or unique key as the association condition in multi-table connections A constant is accessed using these types. If the primary key is placed in the where list, MySQL can convert the query into a constant. System is a special case of the const type. Use system when the queried table has only one row. ## ● NULL : MySQL decomposes the statement during the optimization process, and does not even need to access the table or index during execution, such as selecting the minimum value from an index column This can be done via a separate index lookup. 3.5 possible_keys 　 possible_keysDisplays one or more indexes that may be applied to this table. If an index exists on the field involved in the query, the index will be listed, but may not be actually used by the query . (The indexes that this query can utilize, if there are no indexes display null) 3.6 key  keyDisplays the key (index) that MySQL actually decides to use, which must be included in possible_keys. NULL if no index is selected. To force MySQL to use or ignore the index on the possible_keys column, use force index, use index, or ignore index in the query. 3.7 key_len 　key_len represents the number of bytes used in the index. This column can be used to calculate the length of the index used in the query (the value displayed by key_len It is the maximum possible length of the index field, not the actual length used, that is, key_len is calculated based on the table definition, not retrieved from the table). Without loss of accuracy, the shorter the length, the better. ##. 3.8 ref The column that displays the index is used to indicate the connection matching conditions of the above table, that is Which columns or constants are used to find the value on the index column The number of rows indicates that MySQL estimates the number of rows that need to be read to find the required records based on table statistics and index selection. 3.10 Extra 　 Extra This column contains detailed information about MySQL resolving the query. There are the following situations: When the requested columns are all part of the same index, it means that the mysql server will filter the rows after the storage engine retrieves them. ● Using temporary Indicates that MySQL needs to use Temporary tables are used to store result sets, which are commonly used in sorting and grouping queries. Common group by; order by · Using filesort When the Query contains the order by operation, and the sorting completed by the index cannot be used The operation is called "file sorting"  ● Using join bufferThe changed value emphasizes that no index is used when obtaining the join condition, and a join buffer is required to store intermediate results. If this value appears, it should be noted that depending on the specific conditions of the query, you may need to add an index to improve performance. ● Impossible where This value emphasizes that the where statement will result in no rows that meet the condition (a result that cannot exist by collecting statistics).  ● Select tables optimized away This value means that by using indexes only, the optimizer may return only one row from the aggregate function result ● No tables used Query uses from dual or does not contain any from clause 4. Summary  ● explain will not tell you information about triggers, stored procedures or users The impact of custom functions on queries   ● explain does not consider various Cache   ● explain cannot display the optimization work done by MySQL when executing queries   ● explain can only Explain the select operation, other operations should be rewritten as select and then view the execution plan  ● Some statistical information is estimated, not precise values Recommended learning: mysql video tutorial

The above is the detailed content of Detailed explanation of explain usage for MySQL learning. For more information, please follow other related articles on the PHP Chinese website!