MySQL explain field meaning

Time:2022-5-4
Explain contains ID and select_ type、table、type、possible_ keys、key、key_ Len, ref, rows, extra field ID
The number of the ID column is the serial number of the select. If there are several selections, there are several IDS, and the order of IDS increases in the order in which the select appears.

 

select_type
select_ Type indicates whether the corresponding row is a simple or complex query.

1. Simple: simple query
The most complex query in the outer layer: Primary 2. Select
3. Subquery: subquery contained in the select (not in the from clause)
4. Derived: the subquery contained in the from clause
5. Union: the second and subsequent select in the union
6. Union result: select the result retrieved from the union temporary table
table
This column indicates which table a row of explain is accessing.
type
This column represents the association type or access type, that is, MySQL determines how to find rows in the table
The performance priorities from the best to the worst are: system > const > Eq_ ref>ref>fulltext>ref_ or_ null>index_ merge>unique_ subquery>index_ subquery>range>index>all
1. Null: MySQL can decompose query statements in the optimization stage, and there is no need to access tables or indexes in the execution stage.
2. Const, system: MySQL can optimize a part of the query and convert it into a constant (see the results of show warnings). 
3.eq_ Ref: all parts of the primary key or unique key index are used by connection, and only one qualified record will be returned at most. 
4. Ref: compared with EQ_ Ref is not a unique index, but a partial prefix of an ordinary index or a unique index. If the index is compared with a certain value, it may find a qualified row.
5.ref_ or_ Null: similar to ref, but can search for rows with null value.
6.index_ Merge: indicates that the optimization method of index merging is used.
7. Range: range scanning usually occurs in (), between, >, <, > = and other operations.
8. Index: like all, the difference is that MySQL only needs to scan the index tree, which is usually faster than all.
9. All: full table scanning means that MySQL needs to find the required rows from beginning to end. Usually, it needs to increase the index for optimization.

 

    possible_keys

This column shows which indexes the query may use to find

 

   key

 

This column shows which index MySQL actually uses to optimize access to the table

 

 

   key_len
This column shows the number of bytes used by MySQL in the index. Through this value, you can calculate which columns in the index are used

 

   ref
In the index of the key column record, the columns or constants used by the table to find the value are: const, func, null and field name

 

  rows

MySQL estimates the number of rows to be read and detected. Note that this is not the number of rows in the result set.

 

  extra

It shows additional information
1. Distinct: once MySQL finds the row that is associated with and matches the row, it will not search.
2. Using index: this happens when the requested columns of the table are all part of the same index. The returned column data only uses the information in the index, and does not access the row records in the table. (high performance)
3. Using where: the MySQL server will filter the rows after the storage engine retrieves them. That is, first read the whole line of data, and then check according to the where condition. If it meets the requirements, it will be left behind, and if it does not meet the requirements, it will be discarded.
4. Using temporary: MySQL needs to create a temporary table to process queries. In this case, it is generally necessary to optimize. The first thing is to think of using index to optimize.
5. Using filesort: MySQL will use an external index to sort the results instead of reading rows from the table in index order. At this time, MySQL will browse all qualified records according to the connection type, save the sorting keyword and row pointer, then sort the keyword and retrieve the row information in order. In this case, it is generally necessary to consider using indexes for optimization.

 

 

https://www.cnblogs.com/teach/p/14651041.html