What is the use of MySQL Explain? (With detailed execution explanation)

                                                                                                               

What is the use of Explain

When Explain is used with the SQL statement, MySQL Information about SQL execution from the optimizer is displayed. That is, MySQL explains how it will process the statement, including how to join the tables and in what order to join the tables, etc.

• The loading sequence of the table
• query type of sql
• Which indexes may be used, and which indexes are actually used
• Reference relationship between tables
• How many rows in a table are queried by the optimizer
  …..

Explain Yes What information

Explain execution plan contains the following field information: id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows , filtered, Extra12 fields.

Explain detailed explanation of execution plan

1. id

id: : Indicates that the query is being executed In the order of the select clause or operation table, the larger the value of id, the higher the priority, and will be executed first. idThere will be roughly three situations:

1, id is the same

See three records The id are all the same. It can be understood that these three tables are a group with the same priority. The execution order is from top to bottom. The specific order is determined by the optimizer.

2, id is different

If there is a subquery in our SQL, then id## The sequence number of # will increase. The larger the id value, the higher the priority and will be executed first. When the three tables are nested in sequence, it is found that the innermost subquery id is the largest and is executed first.

3. The above two exist at the same time

Slightly modify the above

SQL, add a subquery, and find id The above two types exist at the same time. The same id is divided into one group, so there are three groups. Those in the same group are executed sequentially from top to bottom. The larger the value of id in different groups, the higher the priority and the earlier they are executed. .

2. select_type

select_type: Indicates the type of select query, mainly used to distinguish various complex Query, for example: Normal query, Union query, Subquery, etc.

1. SIMPLE

SIMPLE: represents the simplest select query statement, that is, the query does not contain subqueries or unionIntersection and difference sets and other operations.

2, PRIMARY

PRIMARY: When the query statement contains any complex subparts, the outermost query is marked PRIMARY.

3. SUBQUERY

SUBQUERY: When the select or where list contains sub Query, the subquery is marked: SUBQUERY.

4. DERIVED

DERIVED: Represents the select of the subquery contained in the from clause. In our Subqueries contained in the from list will be marked as derived.

5, UNION

UNION: If the select statement appears after union, will be marked as union; if union is included in the subquery of the from clause, the outer select will be marked as derived.

6. UNION RESULT

UNION RESULT: Represents reading data from the temporary table of union, and# The in the ##table column indicates that the union operation is performed using the results of the first and fourth select.

3. table The table name queried is not necessarily a real table. There is an alias to display the alias, or it may be a temporary table, such as the # above. ##DERIVED

,

, etc.

4. Partitions

The partition information matched when querying, for non-partitioned tables, the value is NULL, when querying a partitioned table, partitionsDisplay the partitions hit by the partition table.

5. type

type: What type is used in the query? It is a very important factor in SQL optimization. Important indicators, the following performance from best to worst are: system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

##1, system

system: When the table has only one row of records (system table) , the amount of data is very small, disk IO is often not required, and the speed is very fast.

2, const

const: indicates that the primary key primary key or unique is hit during query The unique index, or concatenated part, is a constant (const) value. This type of scanning is extremely efficient, returns a small amount of data, and is very fast.

3. eq_ref

eq_ref: Hit the primary key primary key or unique key during query Index, type is eq_ref.

4, ref

ref: Different from eq_ref, ref means using non-unique Sexual index, you will find many rows that meet the conditions.

5, ref_or_null

ref_or_null: This connection type is similar to ref, the difference is that MySQL will additionally search for items containing Row with NULL value.

6. index_merge

index_merge: The index merge optimization method is used, and the query uses more than two indexes.

7. unique_subquery

unique_subquery: Replace the following IN subquery, and the subquery returns a unique set.

value IN (SELECT primary_key FROM single_table WHERE some_expr)

8, index_subquery

index_subquery: Different from unique_subquery, it is used for non-unique indexes and can return duplicate values .

value IN (SELECT key_column FROM single_table WHERE some_expr)

9, range

range: Use index to select rows and retrieve only rows within the given range. To put it simply, it is to retrieve data within a given range for an indexed field. Use bettween...and, <, >, <= in the where statement , in and other conditional queries type are all range.

Only performing range retrieval type on fields with indexes set is range.

10, index

index: Index and ALL actually read the entire table , the difference is that index is read by traversing the index tree, while ALL is read from the hard disk.

11, ALL

ALL: The entire table will be traversed to find matching rows, with the worst performance.

6. possible_keys

possible_keys: Indicates which indexes in MySQL allow us to find in the table For the desired record, once an index exists on a field involved in the query, the index will be listed, but this index may not necessarily be the index used when ultimately querying the data. Please refer to the example above for details.

7. key

key: Different from possible_keys, key is the index actually used in the query. If No index is used and is displayed as NULL. Please refer to the example above for details.

When

type is index_merge, multiple indexes may be displayed.

8. key_len

key_len: Indicates the index length (number of bytes) used in the query. In principle, the shorter the length The better.

Single-column index, then the entire index length needs to be included;
• Multi-column index, not all columns can be used, and the actual columns used in the query need to be calculated.

Note:

key_len only calculates the index length used in the where condition, and even if the index is used for sorting and grouping, it will not It will be calculated into key_len.

9. ref

ref: Common ones are: const, func, null, field name.

• When using constant equivalent query, const will be displayed.
• When related query, the related field of the corresponding related table will be displayed.
• If the query condition uses expression, function, or the condition column undergoes internal implicit conversion, it may be displayed as func
• Other situationsnull

##10. rows

rows: Based on the statistical information of the table and Index usage, estimating the number of rows that need to be read to find the records we need.

This is an important data for evaluating

SQL performance. mysqlThe number of rows that need to be scanned is a very intuitive display of SQL performance. Good or bad, generally the smaller the rows value, the better.

11. filtered

filteredThis is a percentage value, the percentage of the number of records that meet the conditions in the table. To put it simply, this field represents the proportion of the remaining records that meet the conditions after filtering the data returned by the storage engine.

Before the

MySQL.5.7 version, if you want to display filtered, you need to use the explain extended command. After MySQL.5.7, by default explain will directly display the information of partitions and filtered.

12. Extra

Extra: Information not suitable for display in other columns, many in Explain Additional information will be displayed in the Extra field.

1. Using index

Using index: We use the covering index in the corresponding select operation, which is popular To put it simply, the queried column is covered by the index. Using the covering index, the query speed will be very fast, which is the ideal state in optimization. What is a covering index?

A

SQL

only needs to be returned through the index. The data we need to query (one or several fields) does not have to Through the secondary index, after finding the primary key, query the entire row of data through the primary key (

select *). Note

: If we want to use the covering index, we only take out the required fields when

select. We cannot select *, and this field Index created.

2. Using where

Using where

: No available index is found during query, and then filtered by

where condition Obtain the required data, but note that not all queries with where statements will display Using where.

3. Using temporary

Using temporary

: Indicates that the results after the query need to be stored in a temporary table, usually used when sorting or grouping queries. arrive.

4. Using filesort

Using filesort

: Indicates that the sorting operation cannot be completed using the index, that is,

ORDER BY There is no index for the field, and usually such SQL needs to be optimized. If the ORDER BY

field has an index, a covering index will be used, which is much faster than execution.

5. Using join buffer

Using join buffer

: When we query joint tables, if the join conditions of the tables do not use indexes , a connection buffer is required to store intermediate results.

6. Impossible where

Impossible where

: It means that we use the incorrect

where statement, resulting in no Rows that match the criteria.

7. No tables used

No tables used

: There is no

FROM clause in our query statement, or There is a FROM DUAL clause. There is a lot of information in the Extra

column, so I won’t list it one by one here. For details, see the

MySQL official document: https://dev.mysql.com /doc/ref…

Summary

Key columns:

possible_keys: May be available The name of the index. The index name here is the index nickname specified when the index was created; if the index does not have a nickname, the name of the first column in the index is displayed by default (in this example, it is "firstname"). The meaning of default index names is often not obvious.

key: It shows the name of the index actually used by MySQL. If it is empty (or NULL), MySQL does not use the index.

key_len: The length of the used part of the index, in bytes

ref: Whether the list is filtered (by key) by a constant (const) or a field of a certain table (if it is a join)
;

rows: what MySQL considers The number of records it must scan before finding the correct result. Obviously, the ideal number here is 1.

Recommended learning: "mysql video tutorial"

The above is the detailed content of What is the use of MySQL Explain? (With detailed execution explanation). For more information, please follow other related articles on the PHP Chinese website!