Detailed explanation of the use of mysql explain (analysis index)

EXPLAIN shows how MySQL uses indexes to process select statements and join tables. It can help choose better indexes and write more optimized query statements.

To use it, just add explain before the select statement, such as:

explain select * from statuses_status where id=11;

Explanation of the explain column

table: Displays which table the data in this row is about

type: This is the important column and shows what type of connection is used. The join types from best to worst are const, eq_reg, ref, range, indexhe, and all

possible_keys: Displays the indexes that may be applied to this table. If empty, no index is possible. You can select an appropriate statement from the where clause for the relevant domain

key: The actual index used. If null, no index is used. In rare cases, MySQL may choose an index that is not optimal enough. In this case, you can use use index (indexname) in the select statement to force the use of an index or use ignore index (indexname) to force MySQL to ignore the index.

key_len: The length of the index used. The shorter the length, the better without losing accuracy.

ref: shows which column of the index is used, if possible, a constant

rows: The number of rows that MySQL considers necessary to check to return the requested data

extra: Additional information about how MySQL parses the query. This will be discussed in Table 4.3, but the bad examples you can see here are using temporary and using filesort, which means that MySQL cannot use the index at all, and the result is that retrieval will be very slow.

The meaning of the description returned in the extra column

Distinct: Once MySQL finds a row that matches the row in the join, it no longer searches.

not exists: MySQL optimizes left join. Once it finds a row that matches the left join criteria, it no longer searches.

range checked for each record (index map: #): No ideal index was found, so for each combination of rows from the previous tables, MySQL checks which index to use and uses it to return rows from the table. This is one of the slowest connections using the index

using filesort: When you see this, the query needs to be optimized. MySQL needs to perform an extra step to discover how to order the returned rows. It sorts all rows based on the join type and stores the sort key value and row pointers for all rows matching the condition.

using index: Column data is returned from the table using only the information in the index without actually reading it. This happens when all requested columns for the table are part of the same index.

using temporary When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which usually happens when you do an order by on a different set of columns, rather than a group by.

where used The where clause is used to restrict which rows will be matched with the next table or returned to the user. This can happen if you do not want to return all rows in the table and the join type is all or index, or if there is a problem with the query. Explanation of different join types (sorted in order of efficiency)

The system table has only one row: system table. This is a special case of const connection type

const: The maximum value of a record in the table that can match this query (the index can be a primary key or a unique index). Because there is only one row, this value is actually a constant, because MySQL reads the value first and then treats it as a constant.

eq_ref: In a connection, MySQL reads a record from the previous table for each record in the query. It is used when the query uses an index for the primary key or unique key.

ref: This join type occurs only when the query uses a key that is not a unique or primary key, or a part of one of these types (for example, using a leftmost prefix). For each row join with the previous tables, all records will be read from the table. This type depends heavily on how many records are matched against the index - the fewer the better.

range: This join type uses an index to return a range of rows, such as what happens when you use > or < to find something.

index: This join type performs a full scan of every record in the previous table (better than all, because the index is generally smaller than the table data)

all: This join type performs a full scan of each of the previous records. This is generally bad and should be avoided.

Analysis Index Analysis

Here we analyze each field of explain in detail to help you analyze whether the SQL you wrote makes optimal use of indexes.

First is select_type: select queries are divided into simple and complex types

Complex types are divided into subqueries (subquery) and from lists containing subqueries (drivend)

simple:

driven:

A detailed introduction to type:

System,const,eq_ref,ref,range,index,all
all : full table scan
index : Scan in index order, read the index first, then read the actual row, the result is still a full table scan, the main advantage is to avoid sorting. Because the index is sorted.
range : Scan in the form of a range.

explain select * from a where a_id > 1\G

ref : non-unique index access (only normal index)

create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id=1\G

eq_ref : Use unique index lookup (primary key or unique index)
const : constant query

During the entire query process, this table will have at most one matching row. For example, if the primary key id=1, there will definitely be only one row. The required results can be obtained by reading the table data only once, and the table data is read when the execution plan is decomposed.

When the result is not one, it becomes other types such as index or range

system : system query

null : The result has been obtained during the optimization process, and the table or index is not accessed.

possible_keys : possible indexes to use

key : the actual index used

key_line : The maximum possible length of the index field

ref :

Indicates the search method for the index selected for the key column. Common values ​​include const, func, NULL, and specific field names. When the key column is NULL, that is, when the index is not used, this value is also NULL accordingly.

rows : Estimated number of rows to scan

Extra : Displays additional information besides the above information

Using index

This query uses a covering index, which means that the results can be returned through the index without accessing the table.

If "Using index" is not displayed, it means that the table data has been read.

Using where

Indicates that the MySQL server performs "post-filtering" after receiving the row from the storage engine. The so-called "post-filtering" is to read the entire row of data first, and then check whether this row meets the conditions of the where clause. If it meets the conditions, it will be kept, and if it does not meet the conditions, it will be discarded. Because the check is done after the row has been read, it is called post-filtering.

Using temporary

Using temporary tables

Create a table and insert data:

create table a(a_id int, b_id int);
insert into a values(1,1),(1,1),(2,1),(2,2),(3,1);
mysql> explain select distinct a_id from a\G

Extra : Using temporary

MySQL uses temporary tables to implement distinct operations.

Using filesort

If the order required by the query is consistent with the order of the index used, because the index is sorted, the results are read and returned in the order of the index. Otherwise, after obtaining the results, they need to be sorted in the order required by the query. At this time, Using filesort will appear.

select * from a order by id;

For columns without indexes, filesort will appear when order by is performed