MYSQL Tutorial: Explain usage _ MySQL

MYSQL Tutorial: Explain usage MySQLexplain

I. Syntax
Explain <table_name>
Example: explain select * from t3 where id = 3952602;
II. explain the output

Program code
+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------- +
1. id

1. my understanding is the mark of the smooth execution of SQL statements, from large to small.
For example:

Program 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 | system | NULL | 1 |
| 2 | DERIVED | system | NULL | 1 |
| 3 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | 1 |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
Obviously, this SQL statement is executed from the inside out, that is, from id = 3 up.
2. select_type is the select type, which may include the following types:
(1) SIMPLE
Simple Select (without UNION or subquery) example:

Program 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
In my understanding, select is the outermost layer. for example:

Program code
Mysql> explain select * from (select * from t3 where id = 3952602);
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| 1 | PRIMARY | system | NULL | 1 |
| 2 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | 1 |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +

(3). UNION
The second or subsequent Select statement in UNION. for example:

Program 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 | 1000 |
| NULL | union result | ALL | NULL |
+ ---- + -------------- + ------------ + ------- + ------------------- + --------- + ------- + ------ + ------- +

(4). DEPENDENT UNION
The second or subsequent Select statement in UNION depends on the external query.

Program code
Mysql> explain select * from t3 where id in (select id from t3 where id = 3952602 union all select id from t3 );
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------ + ------------------------ +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------ + ------------------------ +
| 1 | PRIMARY | t3 | ALL | 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 | ALL | NULL |
+ ---- + -------------------- + ------------ + -------- + ------------------- + --------- + ------- + ------ + ------------------------ +

(5). UNION RESULT
The result of UNION.

Program 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 | 1000 |
| NULL | union result | ALL | NULL |
+ ---- + -------------- + ------------ + ------- + ------------------- + --------- + ------- + ------ + ------- +

(6). SUBQUERY
The first Select in the subquery.

Program 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 |
+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------------- +

(7). DEPENDENT SUBQUERY
The first Select in the subquery depends on the external query.

Program 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 | Using where; Using index |
| 2 | dependent subquery | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
+ ---- + -------------------- + ------- + ------------------- + --------- + ------- + ------ + -------------------------- +

(8). DERIVED
Select (subquery of the FROM clause) of the derived table)

Program code
Mysql> explain select * from (select * from t3 where id = 3952602);
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| 1 | PRIMARY | system | NULL | 1 |
| 2 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | 1 |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +

3. table
Which table is the data of this row.
Sometimes it is not a real table name, but derivedx is displayed (x is a number and I understand it as the result of the first step)

Program 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 | system | NULL | 1 |
| 2 | DERIVED | system | NULL | 1 |
| 3 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | 1 |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +

4. type
This column is very important and shows the category used for the connection and whether the index is used.
The connection types from the best to the worst are const, eq_reg, ref, range, indexhe, and ALL.
(1). system
This is a special case of the const join type. Only one row in the table meets the following conditions. (the id of table t3 is the primary key)

Program code
Mysql> explain select * from (select * from t3 where id = 3952602);
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
| 1 | PRIMARY | system | NULL | 1 |
| 2 | DERIVED | t3 | const | PRIMARY, idx_t3_id | PRIMARY | 4 | 1 |
+ ---- + ------------- + ------------ + -------- + ------------------- + --------- + ------ + ------- +
(2). const
A table can have at most one matching row, which will be read at the beginning of the query. Because there is only one row, the column value in this row can be considered as a constant by the rest of the optimizer. Const tables are fast because they are read only once!
Const is used to compare all the parts of a primary key or UNIQUE index with a constant value. In the following query, tbl_name can be used for the const table:

Program code
Select * from tbl_name Where primary_key = 1;
Select * from tbl_name Where primary_key_part1 = 1 and primary_key_part2 = 2;

For example:

Program 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 |
+ ---- + ------------- + ------- + ------------------- + --------- + ------- + ------ + ------- +

(3). eq_ref
For each row combination from the preceding table, read a row from the table. This may be the best join type except the const type. It is used to join all parts of an index and the index is UNIQUE or primary key.
Eq_ref can be used to compare indexed columns with the = operator. The comparison value can be a constant or an expression that uses the column of the table read before the table.
In the following example, MySQL can use the eq_ref join to process ref_tables:

Program code
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

Program code
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 | 1000 |
| 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 only uses 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), use ref. If the key used matches only a few rows, the join type is good.
Ref can be used for indexed columns using the = or <=> operator.
In the following example, MySQL can use the ref join to process ref_tables:

Program code
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:

Program code
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 | 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 MySQL can search for rows containing NULL values. The optimization of this Join type is often used in solving subqueries.
In the following example, MySQL can use the ref_or_null join to process ref_tables:
Select * FROM ref_table
Where key_column = expr or key_column is null;

(6). index_merge
The join type indicates that the index merge optimization method is used. In this case, the key column contains the list of indexes used, and key_len contains the longest key element of the index used.
For example:

Program code
Mysql> explain select * from t4 where id = 3952602 or accountid = 31754306;
+ ---- + ------------- + ------- + ------------- + ---------------------------- + --------- + ------ + Accept +
| Id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+ ---- + ------------- + ------- + ------------- + ---------------------------- + --------- + ------ + Accept +
| 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, random); Using where |
+ ---- + ------------- + ------- + ------------- + ---------------------------- + --------- + ------ + Accept +
1 row in set (0.00 sec)

(7). unique_subquery
This type replaces the ref of the IN subquery IN the following format:
Value IN (Select primary_key FROM single_table Where some_expr)

Unique_subquery is an index lookup function that can replace subqueries completely, improving efficiency.
(8). index_subquery
The join type is similar to unique_subquery. An IN subquery can be replaced by an IN subquery, but it is only applicable to non-unique indexes IN the following forms:

Program code
Value IN (Select key_column FROM single_table Where some_expr)

(9). range
Only retrieve rows in a given range and use an index to select rows. The key column shows the index used. Key_len contains the longest key element of the index used. In this type, the ref column is NULL.
When using the =, <>,> =, <, <=, is null, <=>, BETWEEN, or IN operator, you can use the range

Program code
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)
(10). index

The join type is the same as that of ALL except that 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 querying only columns that are part of a single index.
(11). ALL
Perform a full table scan for each row combination from the previous table. If the table is the first table without the const Mark, this is usually not good, and it is usually very bad. Generally, you can 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 specifies which index MySQL can use to find rows in the table. Note that this column is completely independent of the Order of the tables shown in the EXPLAIN output. This means that some keys in possible_keys cannot be used in the order of the generated table.
If this column is NULL, there is no relevant index. In this case, you can check the Where clause to see if it references certain columns or columns suitable for indexing to improve your query performance. If so, create an appropriate index and use EXPLAIN again to check the query
6. key
The key column displays the keys (Indexes) actually determined by MySQL ). If no index is selected, the key is NULL. To FORCE MySQL to USE or IGNORE the indexes in the possible_keys column, use force index, use index, or ignore index in the query.
7. key_len
The key_len column displays the key length determined by MySQL. If the key is NULL, the length is NULL.
The length of the index used. The shorter the length, the better.
8. ref
The ref column shows which column or constant is used together with the key to select rows from the table.
9. rows
The rows column displays the number of rows that MySQL considers to be required for query execution.
10. Extra
This column contains detailed information about MySQL queries.
(1). Distinct
Once MYSQL finds the row that matches the row, it does not search any more.
(2). Not exists
MYSQL optimizes left join. once it finds a row that matches the left join standard,
No more search
(3). Range checked for each
Record (index map :#)
No ideal index is found. Therefore, for each row combination in the preceding table, MYSQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using indexes.
(4). Using filesort
When you see this, the query needs to be optimized. MYSQL requires additional steps to find out how to sort the returned rows. It sorts all rows according to the connection type and the row pointer that stores the sort key value and all rows matching the condition.
(5). Using index
The column data is returned from a table that only uses the information in the index but does not read the actual action. This occurs when all the request columns in 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. this usually happens when order by is applied to different column sets, rather than group.
(7). Using where
The Where clause is used to limit which rows match the next table or are returned to the user. If you do not want to return ALL rows in the table and the connection type is ALL or index, this may occur or the query is faulty.