Detailed explanation of explain usage for MySQL learning

WBOY
Release: 2022-03-07 16:56:13
forward
2404 people have browsed it

This article brings you relevant knowledge aboutmysql, 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.

Detailed explanation of explain usage for MySQL learning

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, ExtraSummary description:

##Field

Description Select 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

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

Detailed explanation of explain usage for MySQL learning

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.

Detailed explanation of explain usage for MySQL learning


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)

Detailed explanation of explain usage for MySQL learning

 (2)

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

Detailed explanation of explain usage for MySQL learning

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)

Detailed explanation of explain usage for MySQL learning

 (6)

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

Detailed explanation of explain usage for MySQL learning

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

## ● ●Detailed explanation of explain usage for MySQL learningindex

: Full Index Scan, the difference between index and ALL is that the index type only traverses the index tree

·Detailed explanation of explain usage for MySQL learningrange

: Only retrieves rows in a given range, Use an index to select rows

·Detailed explanation of explain usage for MySQL learningref

: 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

●Detailed explanation of explain usage for MySQL learningeq_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_keysDetailed explanation of explain usage for MySQL learning

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

Detailed explanation of explain usage for MySQL learning

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 ExtraDetailed explanation of explain usage for MySQL learning


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 byDetailed explanation of explain usage for MySQL learning

·
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!

Related labels:
source:csdn.net
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!