Detailed explanation of explain usage for MySQL learning

3. Explain the meaning of each field

3.1 id

idis 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

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

simple(Simple select, no complex queries such as union or subquery are used)

primary(The outermost query in the subquery, if the query contains any complex subparts, the outermost select is marked as primary)

subquery(Subquery included in select or where list)

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)

union(The second or subsequent select statement in the union)

union result(The result of the union, the second select statement in the union statement All selects after select start)

Display the name of the table in the database accessed in this step (display Which table does this row of data refer to?).

shows which type is used in the query. Type includes all, index, range, ref, eq_ref, const, system, NULL, its performance increases in order.# ●

: Full Table Scan, MySQL will traverse the entire table to find matching rows

## ● ●index

·range

·ref

●eq_ref

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

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, useforce index,use index, orignore indexin the query.

3.7 key_len

key_lenrepresents 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. ##.

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

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.●

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

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.

●

This value emphasizes that the where statement will result in no rows that meet the condition (a result that cannot exist by collecting statistics). ●

This value means that by using indexes only, the optimizer may return only one row from the aggregate function result ●

uses from dual or does not contain any from clause

 ● explain will not tell you information about triggers, stored procedures or users The impact of custom functions on queries

  ● explain cannot display the optimization work done by MySQL when executing queries

 ● Some statistical information is estimated, not precise values