I. Grammar
Explain < table_name >
For example: Explain select * from T3 where id=3952602;
Two. Explain output interpretation
| The code is as follows |
Copy Code |
| +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| 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> 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 | <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, or from the id=3 up.
2. Select_type
Is the Select type, you can have the following
(1) Simple
Simple select (Do not use union or subqueries, etc.) for example:
| code is as follows |
copy code |
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:
| The code is as follows |
Copy Code |
| 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
The second or subsequent SELECT statement in the Union. For example
| The code is as follows |
Copy Code |
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 | <union1,2> | All | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |
(4). DEPENDENT UNION
The second or subsequent SELECT statement in the Union, depending on the query outside
| The code is as follows |
Copy Code |
| 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 | <union2,3> | All | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+------- -------------------+ |
(4). UNION result
The result of the Union.
| The code is as follows |
Copy Code |
| 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 | <union1,2> | All | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |
(5). Subquery
The first select in the subquery.
| The code is as follows |
Copy Code |
| 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
| The code is as follows |
Copy Code |
| 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)
| The code is as follows |
Copy Code |
| 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 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)
| The code is as follows |
Copy Code |
| 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 | <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 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
