Home >Database >Mysql Tutorial >What is the use of MySQL Explain? (With detailed execution explanation)
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.
query type of sql
Explain
execution plan contains the following field information: id
, select_type
, table
, partitions
, type
, possible_keys
, key
, key_len
, ref
, rows
, filtered
, Extra
12 fields.
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. id
There will be roughly three situations:
id
is the sameSee 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.
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.
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. .
select_type: Indicates the type of
select query, mainly used to distinguish various complex Query, for example:
Normal query,
Union query,
Subquery, etc.
SIMPLE: represents the simplest select query statement, that is, the query does not contain subqueries or
unionIntersection and difference sets and other operations.
PRIMARY: When the query statement contains any complex subparts, the outermost query is marked
PRIMARY.
SUBQUERY: When the
select or
where list contains sub Query, the subquery is marked:
SUBQUERY.
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.
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.
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
.
, etc.
The partition information matched when querying, for non-partitioned tables, the value is NULL
, when querying a partitioned table, partitions
Display the partitions hit by the partition table.
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
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.
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.
eq_ref: Hit the primary key
primary key or
unique key during query Index,
type is
eq_ref.
ref: Different from
eq_ref,
ref means using non-unique Sexual index, you will find many rows that meet the conditions.
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.
index_merge: The index merge optimization method is used, and the query uses more than two indexes.
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)
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)
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.
on fields with indexes set is
range.
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.
ALL: The entire table will be traversed to find matching rows, with the worst performance.
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.
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.
Whentype
is
index_merge, multiple indexes may be displayed.
key_len: Indicates the index length (number of bytes) used in the query. In principle, the shorter the length The better.
Note:key_len
only calculates the index length used in the
wherecondition, and even if the index is used for sorting and grouping, it will not It will be calculated into
key_len.
ref
: Common ones are: const
, func
, null
, field name.
const
will be displayed. related field
of the corresponding related table will be displayed. expression
, function
, or the condition column undergoes internal implicit conversion, it may be displayed as func
null
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.
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.
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.
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.
Extra: Information not suitable for display in other columns, many in
Explain Additional information will be displayed in the
Extra field.
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?
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
select. We cannot select *, and this field Index created.
where condition Obtain the required data, but note that not all queries with
where statements will display
Using where.
ORDER BY There is no index for the field, and usually such SQL needs to be optimized.
If the
ORDER BY
where statement, resulting in no Rows that match the criteria.
FROM clause in our query statement, or There is a
FROM DUAL clause. There is a lot of information in the
Extra
MySQL official document: https://dev.mysql.com /doc/ref…
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!