Mysql Explain Detailed Introduction _mysql

Mysql Explain here to do a comprehensive collation of the data.

I. Grammar

Explain < table_name >

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

My understanding is that SQL performs smoothly with the identification of SQL from large to small executions.

For example:

Mysql&gt; Explain SELECT * FROM (SELECT * from T3 where id=3952602) a) b;


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+


| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+


| 1 | PRIMARY | &lt;derived2&gt; | System | NULL | NULL | NULL |    NULL |       1 | |


| 2 | DERIVED | &lt;derived3&gt; | 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, or from the id=3 up.

2. Select_type

Is the select type, which can have the following

(1) Simple

Simple select (Do not use union or subqueries, 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

My understanding is 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 | &lt;derived2&gt; | System | NULL | NULL | NULL |    NULL |       1 | |


| 2 | DERIVED | T3 | Const | primary,idx_t3_id | PRIMARY |      4 |    |       1 | |


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(3). UNION

The 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 |       1000 | |


| NULL | UNION Result | &lt;union1,2&gt; | All | NULL | NULL | NULL | NULL |       NULL | |


+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(4). DEPENDENT UNION

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

Mysql> explain select * from T3 where ID 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 | The Using where; Using Index |


| NULL | UNION Result | &lt;union2,3&gt; | All | NULL | NULL | NULL | NULL |                          NULL | |


+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------- -------------------+

(4). 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 |       1000 | |


| NULL | UNION Result | &lt;union1,2&gt; | All | NULL | NULL | NULL | NULL |       NULL | |


+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+

(5). Subquery

The first select in the 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 query outside

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 | The Using where; Using Index |


| 2 | DEPENDENT subquery | T3 | Const | primary,idx_t3_id | PRIMARY | 4 |    Const | 1 | Using Index |


+----+--------------------+-------+-------+-------------------+---------+---------+-------+------+------------- -------------+

7). DERIVED

A select from a derived table (a 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 | &lt;derived2&gt; | System | NULL | NULL | NULL |    NULL |       1 | |


| 2 | DERIVED | T3 | Const | primary,idx_t3_id | PRIMARY |      4 |    |       1 | |


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

3.table

Shows which table the data in this row is about.

Sometimes not the real table name, see Derivedx (X is a number, my understanding is the result of the first step)

Mysql> Explain SELECT * FROM (SELECT * from T3 where id=3952602) a) b;

+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+


| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+


| 1 | PRIMARY | &lt;derived2&gt; | System | NULL | NULL | NULL |    NULL |       1 | |


| 2 | DERIVED | &lt;derived3&gt; | 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 is used by the connection, and if there are any indexes used.

The connection types from best to worst are const, EQ_REG, ref, range, Indexhe, and all

(1). System

This is a special case of the const join type. The table has only one row for the condition. as follows (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 | &lt;derived2&gt; | System | NULL | NULL | NULL |    NULL |       1 | |


| 2 | DERIVED | T3 | Const | primary,idx_t3_id | PRIMARY |      4 |    |       1 | |


+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

(2). const

The table has at most one matching row, and it will be read at the start 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 quick because they are only read once!

Const is used to compare all parts of a primary key or a unique index with a constant value. In the following query, Tbl_name can be used in 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

Read a row from the table for each combination of rows from the previous table. This may be the best join type, 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 to compare indexed columns with the = operator. A comparison value can be a constant or an expression that uses a column of a table that is read before the table.

In the following example, MySQL can use the EQ_REF join to handle the 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, 1000 rows 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 |       1000 | |


| 1 | Simple | T3 | Eq_ref | primary,idx_t3_id | idx_t3_id | 4 |    Dbatest.t4.accountid |       1 | |


+----+-------------+-------+--------+-------------------+-----------+---------+----------------------+------+-- -----+

(4). ref

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

In the example below, 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, 1000 rows affected (0.03 sec)
records:1000 duplicates:0 warnings:0 mysql>

CREATE INDEX idx_t3_id on t3 (ID);
Query OK, 1000 rows affected (0.04 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 |       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 it is added that MySQL can specifically search for rows that contain null values. Optimization of this join type is often used in resolving subqueries.

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

SELECT * from Ref_table
WHERE key_column=expr OR Key_column is NULL;

(6). Index_merge

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

For example:

Mysql> explain select * from T4 where id=3952602 or accountid=31754306;

+----+-------------+-------+-------------+----------------------------+----------------------------+---------+- -----+------+------------------------------------------------------+


| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |


+----+-------------+-------+-------------+----------------------------+----------------------------+---------+- -----+------+------------------------------------------------------+


| 1 | Simple | T4 | Index_merge | Idx_t4_id,idx_t4_accountid | Idx_t4_id,idx_t4_accountid | 4,4 |    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 ref for 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 subqueries and is 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 subqueries of the following forms:

Value in (SELECT key_column from single_table WHERE some_expr)

(9). Range

Retrieves only the rows of a given range, using an index to select rows. The key column shows which index is used. Key_len contains the longest critical element of the index used. Ref column is NULL in this type.

When using the =, <>, >, >=, <, <=, is NULL, <=>, between, or in operators, you can use the range when you compare key columns with constants

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)

(a). Index

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

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

(one). All

Complete table scans for each row combination from the previous table. This is usually bad if the table is the first table that does not have a const, and is usually poor in its case. You can usually add more indexes instead of all so that rows can be retrieved based on constant values or column values in the previous 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 cannot actually be 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 checking the WHERE clause to see if it references certain 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 index in the Possible_keys column, use force index, using index, or ignore index in the query.

7.key_len

The Key_len column shows the length of the key that MySQL decided to use. If the key is null, the length is null.
The length of the index used. Without loss of accuracy, the shorter the length the better.

8. Ref

The ref column shows which column or constant is used with the 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.

Extra

This column contains detailed information about the MySQL resolution query, below.

(1). Distinct

Once MySQL finds a row that matches the row, it stops searching.

(2). NOT EXISTS

MySQL optimizes the left join, and once it finds a row that matches the left join criteria,

We stop searching.

(3). Range checked for each

Record (index map:#)

The ideal index was not found, so for each row from the previous table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using the index

(4). Using Filesort

When you see this, the query needs to be optimized. MySQL needs to take extra steps to find out how to sort the rows 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 without reading the actual action, which occurs when all of the table's request columns 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 typically occurs when a different set of columns is on an order by, not a group by

(7). Using where

The WHERE clause is used to restrict which rows match the next table or return to the user. If you do not want to return all the rows in the table, and the connection type all or index, this occurs, or the query has a problem

Thank you for reading, I hope to help you, thank you for your support for this site!