"Go" MySQL Execution plan analysis

I. Grammar
Explain <sql statement >
For example: Explain select * from T3 where id=3952602;

Two. Explain output interpretation

+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+| ID | Select_type | Table | Type  | Possible_keys     | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

1.id

The SQL execution sequence is identified, and SQL is executed from large to small.
For example:

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) b;+----+-------------+------------+ --------+-------------------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  3 | DERIVED |     T3 |         Const  | primary,idx_t3_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

It is clear that this SQL is executed from the inside out, that is, from the id=3.

2. Select_type
Is the Select type, you can have several

(1) Simple
Simple select (Do not use union or subquery, etc.) for example:

Mysql> explain select * from T3 where id=3952602;+----+-------------+-------+-------+-------------------+--------- +---------+-------+------+-------+| ID | Select_type | Table | Type  | Possible_keys     | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+|  1 | Simple      | T3    | const | primary,idx_t3_id | PRIMARY | 4       | const |    1 |       | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(2). PRIMARY

The outermost select. For example:

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) A; +----+-------------+------------+--------+--------- ----------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED |     T3 |         Const  | primary,idx_t3_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(3). UNION

A second or subsequent SELECT statement in the Union. For example,

Mysql> explain select * from T3 where id=3952602 union ALL SELECT * from T3, +----+--------------+------------+-------+ -------------------+---------+---------+-------+------+-------+| ID | Select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |  1 | PRIMARY |      T3 |         Const | primary,idx_t3_id | PRIMARY | 4       | const |    1 | |       |  2 | UNION        | T3         | All   | NULL              | NULL    | NULL    | NULL | | |       NULL | UNION RESULT | <union1,2> | All   | NULL              | NULL    | NULL    | NULL  | NULL |      | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4). DEPENDENT UNION

The second or subsequent SELECT statement in the Union, depending on the outside query

Mysql> explain select * from T3 where IDs in (select IDs from T3 where id=3952602 union ALL select IDs from T3); +----+-- ------------------+------------+--------+-------------------+---------+---------+-------+------+--------------- -----------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+--------------------+------------+--------+-------------------+---------+---------+-------  +------+--------------------------+| 1 | PRIMARY | T3 | All | NULL | NULL | NULL | NULL | 1000 |  Using where | | 2 | DEPENDENT subquery | T3 | Const | primary,idx_t3_id | PRIMARY | 4 |    Const | 1 |  Using Index | | 3 | DEPENDENT UNION | T3 | Eq_ref | primary,idx_t3_id | PRIMARY | 4 |    Func | 1 | Using where; Using Index | | NULL | UNION RESULT | <union2,3> | All | NULL | NULL | NULL | NULL |                         NULL | |+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------ --------------------+

(5). UNION RESULT

The result of the Union.

Mysql> explain select * from T3 where id=3952602 union ALL SELECT * from T3, +----+--------------+------------+-------+ -------------------+---------+---------+-------+------+-------+| ID | Select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra |+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |  1 | PRIMARY |      T3 |         Const | primary,idx_t3_id | PRIMARY | 4       | const |    1 | |       |  2 | UNION        | T3         | All   | NULL              | NULL    | NULL    | NULL | | |       NULL | UNION RESULT | <union1,2> | All   | NULL              | NULL    | NULL    | NULL  | NULL |      | +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(5). Subquery

The first select in a subquery.

Mysql> explain select * from t3 where id = (select id from t3 where id=3952602)  , +----+-------------+-------+----- --+-------------------+---------+---------+-------+------+-------------+| ID | Select_type | Table | Type  | Possible_keys     | key     | key_len | ref   | rows | Extra       |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+--------- ----+|  1 | PRIMARY |     T3 |    Const | primary,idx_t3_id | PRIMARY | 4       | const |    1 | |             |  2 | subquery |    T3 |    Const | primary,idx_t3_id | PRIMARY | 4       |       |    1 | Using index |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------- -----+

(6). DEPENDENT subquery

The first select in a subquery, depending on the outside query

Mysql> explain select ID from T3 where ID in (select id from t3 where id=3952602)  , +----+--------------------+---- ---+-------+-------------------+---------+---------+-------+------+--------------------------+| ID | Select_type        | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra                    |+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+-- ------------------------+|  1 | PRIMARY            | t3 |    Index | NULL              | PRIMARY | 4       | NULL  | 1000 | Using where; Using Index | |  2 | DEPENDENT subquery | T3    | const | primary,idx_t3_id | PRIMARY | 4       | const |    1 | Using index              |+----+--------------------+-------+-------+-------------------+---------+---------+-------+-- ----+--------------------------+

(7). DERIVED

Select of derived table (subquery FROM clause)

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) A; +----+-------------+------------+--------+--------- ----------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED |     T3 |         Const  | primary,idx_t3_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

3.table

Shows which table the data for this row is about.
Sometimes not the real table name, see Derivedx (X is a number, my understanding is the result of the first few steps)

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) b;+----+-------------+------------+ --------+-------------------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  3 | DERIVED |     T3 |         Const  | primary,idx_t3_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

4.type

This column is important to show which category the connection is using, and if there are any indexes to use.
The best to worst connection types are const, EQ_REG, ref, range, Indexhe, and all

(1). System

This is a special case of the const join type. Only one row of the table satisfies the condition. The following (the ID on the T3 table is primary key)

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) A; +----+-------------+------------+--------+--------- ----------+---------+---------+------+------+-------+| ID | Select_type | Table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 | |       |  2 | DERIVED |     T3 |         Const  | primary,idx_t3_id | PRIMARY | 4       |      |    1 |       | +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(2). const

The table has a maximum of one matching row, which will be read at the beginning of the query. Because there is only one row, the column values in this row can be considered constants by the remainder of the optimizer. The const table is fast because they are read only once!

Const is used to compare all parts of a primary key or unique index with a constant value. In the following query, Tbl_name can be used with the const table:
SELECT * from Tbl_name WHERE primary_key=1;
SELECT * from Tbl_name WHERE primary_key_part1=1 and primary_key_part2=2;

For example:

Mysql> explain select * from T3 where id=3952602;+----+-------------+-------+-------+-------------------+--------- +---------+-------+------+-------+| ID | Select_type | Table | Type  | Possible_keys     | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+|  1 | Simple      | T3    | const | primary,idx_t3_id | PRIMARY | 4       | const |    1 |       | +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+

(3). Eq_ref

For each row combination from the preceding table, a row is read from the table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY.

Eq_ref can be used for indexed columns that use the = operator comparison. The comparison value can be a constant or an expression that uses a column of a table that was read earlier in the table.

In the following example, MySQL can use the EQ_REF join to handle Ref_tables:

SELECT * from ref_table,other_table  WHERE ref_table.key_column=other_table.column; SELECT * from ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column and    Ref_table.key_ Column_part2=1;

For example

Mysql> Create unique index  idx_t3_id on t3 (ID); Query OK, affected (0.03 sec) records:1000  duplicates:0 warnings:0 mysql>  explain select * from T3, T4 where t3.id=t4.accountid;+----+-------------+-------+--------+-------------------+-----------+---------+----- -----------------+------+-------+| ID | Select_type | Table | Type   | Possible_keys     | key       | key_len | ref                  | rows | Extra |+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+--- ---+-------+|  1 | Simple      | T4    | All    | NULL              | NULL      | NULL    | NULL | | |  1 | Simple      | t3 |    EQ_REF | primary,idx_t3_id | idx_t3_id | 4       | dbatest.t4.accountid |    1 |       | +----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-- -----+

(4). ref

For each row combination from the preceding table, all rows with matching index values are read from this table. If the join uses only the leftmost prefix of the key, or if the key is not unique or primary key (in other words, if the join cannot select a single row based on the keyword), ref is used. If you use a key that matches only a few rows, the join type is good.

Ref can be used with indexed columns that use the = or <=> operator.

In the following example, MySQL can use a ref join to handle Ref_tables:

SELECT * from ref_table WHERE key_column=expr; SELECT * from ref_table,other_table  WHERE ref_table.key_column=other_table.column; SELECT * from ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column and    Ref_table.key_ Column_part2=1;

For example:

Mysql> DROP index idx_t3_id on T3; Query OK, affected (0.03 sec) records:1000 duplicates:0 warnings:0 mysql> CREATE index idx_t3_id on t3 (ID ) ; Query OK, $ rows affected (0.04 sec) records:1000 duplicates:0 warnings:0 mysql> explain select * from T3,t4 whe Re t3.id=t4.accountid;+----+-------------+-------+------+-------------------+-----------+---------+------------ ----------+------+-------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+------+-------------------+-----------+---------+----------------------+-----  -+-------+| 1 | Simple | T4 | All | NULL | NULL | NULL | NULL |       1000 |  || 1 | Simple | T3 | Ref | primary,idx_t3_id | idx_t3_id | 4 |    Dbatest.t4.accountid |       1 | |+----+-------------+-------+------+-------------------+-----------+---------+----------------------+------+--- ----+2 ROWS in Set (0.00 sec) 

(5). Ref_or_null

The join type is like ref, but adding MySQL can specifically search for rows that contain null values. The optimization of the join type is often used in the resolution subquery.

In the following example, MySQL can use the Ref_or_null join to handle Ref_tables:

SELECT * from Ref_tablewhere key_column=expr OR Key_column is NULL;

(6). Index_merge

The join type represents the use of the index merge optimization method. In this case, the key column contains the list of indexes used, and Key_len contains the longest key element for the index used.

For example:

Mysql> explain select * from T4 where id=3952602 or accountid=31754306, +----+-------------+-------+-------------+--- -------------------------+----------------------------+| ID | Select_type | Table | Type | Possible_keys | Key |+----+-------------+-------+-------------+----------------------------+-----------------------  -----+| 1 | Simple | T4 | Index_merge | Idx_t4_id,idx_t4_accountid | Idx_t4_id,idx_t4_accountid |+----+-------------+-------+-------------+----------------------------+------------ ----------------++---------+------+------+------------------------------------------------------+| Key_len | Ref | Rows | Extra |+---------+------+------+------------------------------------------- -----------+| bis |    NULL | 2 | Using Union (Idx_t4_id,idx_t4_accountid); Using where |+---------+------+------+------------------------------------------------------+1 row in Set (0.00 sec)

(7). Unique_subquery

This type replaces the ref of the in subquery in the following form:

Value in (SELECT primary_key from single_table WHERE some_expr)
Unique_subquery is an index lookup function that can completely replace a subquery and be more efficient.

(8). Index_subquery

The join type is similar to Unique_subquery. You can replace in subqueries, but only for non-unique indexes in the following form of subqueries:

Value in (SELECT key_column from single_table WHERE some_expr)

(9). Range

Retrieves only the rows for a given range, using an index to select rows. The key column shows which index is used. The Key_len contains the longest key element of the index being used. In this type, the ref column is null.

When you use the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators to compare key columns with a constant, you can use the range

Mysql> explain select * from T3 where id=3952602 or id=3952603, +----+-------------+-------+-------+----------------- --+-----------+---------+------+------+-------------+| ID | Select_type | Table | Type  | Possible_keys     | key       | key_len | ref  | rows | Extra       |+----+-------------+-------+-------+-------------------+-----------+---------+------+------+-------- -----+|  1 | Simple      | T3    | range | primary,idx_t3_id | idx_t3_id | 4       | NULL |    2 | Using where |+----+-------------+-------+-------+-------------------+-----------+---------+------+------+------- ------+1 row in Set (0.02 sec)

(Ten). Index

The join type is the same as all except that only the index tree is scanned. This is usually faster than all, because the index file is usually smaller than the data file.

When a query uses only columns that are part of a single index, MySQL can use that join type.

(one). All

For each row combination from the previous table, complete the table scan. If the table is the first table that is not marked const, this is usually not good and is usually poor in its case. You can usually add more indexes instead of all, so that the rows can be retrieved based on the constant values or column values in the preceding table.


5.possible_keys

The Possible_keys column indicates which index MySQL can use to find rows in the table. Note that the column is completely independent of the order of the tables shown in the explain output. This means that some keys in Possible_keys are not actually used in the generated table order.

If the column is null, there is no index associated with it. In this case, you can improve your query performance by examining the WHERE clause to see if it references some columns or columns that fit the index. If so, create an appropriate index and check the query again with explain

6. Key

The key column shows the keys (indexes) that MySQL actually decides to use. If no index is selected, the key is null. To force MySQL to use or ignore the indexes in the Possible_keys column, use the forces Index, using index, or ignore index in the query.

7.key_len

The Key_len column shows the key lengths that MySQL decides to use. If the key is null, the length is null.
The length of the index to use. The shorter the length the better, without loss of accuracy

8. Ref

The ref column shows which column or constant is used together with key to select rows from the table.

9. Rows

The Rows column shows the number of rows that MySQL must check when it executes the query.

Ten. Extra

This column contains the details of the MySQL resolution query, as detailed below.

(1). Distinct
Once MySQL finds a row that matches the row, it no longer searches for

(2). NOT EXISTS
MySQL optimizes the left join, and once it finds a row that matches the left join standard, it no longer searches

(3). Range checked for each

Record (Index map:#)
The ideal index was not found, so for each combination of rows from the preceding table, MySQL examines which index to use and uses it to return rows from the table. This is one of the slowest connections to use the index

(4). Using Filesort www.2cto.com
When you see this, the query needs to be optimized. MySQL requires additional steps to find out how to sort the rows that are returned. It sorts all rows based on the connection type and the row pointers for all rows that store the sort key values and matching criteria.

(5). Using Index
Column data is returned from a table that uses only the information in the index and does not read the actual action, which occurs when all the request columns of the table are part of the same index

(6). 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 occurs on an order by on a different set of columns, rather than on the group by

(7). Using where
A WHERE clause is used to restrict which rows will match the next table or are returned to the user. This occurs if you do not want to return all rows in the table, and the connection type all or index.

"Go" MySQL Execution plan analysis