cacheable (false) | UNCACHEABLE subquery in UNION The second or subsequent selection (please refer to UNCACHEABLE SUBQUERY) |
|
Table information (for demonstration later):
mysql> show create table t_a;
------+
| t_a | CREATE TABLE `t_a` (
`id` bigint(20) NOT NULL DEFAULT '0',
`age` int(20) DEFAULT NULL,
`code` int(20) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_code` (`code`),
KEY `age_key` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------
------+
1 row in set (0.03 sec)
Copy after login
SIMPLE: Simple SELECT (without using UNION or subquery, etc.)
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)
Copy after login
PRIMARY: The outermost query when nesting queries
mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)
Copy after login
Copy after login
UNION: The second or subsequent SELECT statement in UNION
mysql> explain select * from t_a where id =9 union all select * from t_a;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 2 | UNION | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.04 sec)
Copy after login
DEPENDENT UNION: The second or subsequent SELECT statement in UNION, depending on external QUERY
mysql> explain select * from t_a where id in (select id from t_a where id >8 union all select id from t_a where id =5);
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t_a | NULL | eq_ref | PRIMARY | PRIMARY | 8 | func | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.08 sec)
Copy after login
UNION RESULT: RESULTS OF UNION
mysql> explain select num from t_a where id = 3 union select num from t_a where id =4;
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| 1 | PRIMARY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 2 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.03 sec)
Copy after login
SUBQUERY: FIRST SELECT IN SUBQUERY
mysql> explain select * from t_a where num >(select num from t_a where id = 3);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | range | num_key | num_key | 5 | NULL | 6 | 100.00 | Using where; Using index |
| 2 | SUBQUERY | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
2 rows in set, 1 warning (0.03 sec)
Copy after login
Copy after login
DEPENDENT SUBQUERY: The first selection in the subquery, depending on the outer query
mysql> explain select * from t_a where num in(select num from t_a where id = 3 union select num from t_a where id =4);
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | t_a | NULL | index | NULL | num_key | 5 | NULL | 9 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| 3 | DEPENDENT UNION | t_a | NULL | const | PRIMARY,num_key | PRIMARY | 8 | const | 1 | 100.00 | NULL |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+-------+-----------------+---------+---------+-------+------+----------+--------------------------+
4 rows in set, 1 warning (0.12 sec)
Copy after login
DERIVED: Derived table (temporary table generated in the subquery)
mysql> explain select a.id from (select id from t_a where id >8 union all select id from t_a where id =5) a;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 2 | DERIVED | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where; Using index |
| 3 | UNION | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+--------------------------+
3 rows in set, 1 warning (0.12 sec)
Copy after login
2.3 table
Shows which table this row of data refers to, sometimes it is the real table name, sometimes it may be the following results
- : refers to the id The union of M and N row results
- : This row refers to the derived table result of the row with id value n. A derived table may come from a subquery in a from clause, for example.
- : This row refers to the result of the materialized subquery of the row with id value n.
2.4 partitions
The partition to which the queried record belongs. For unpartitioned tables, this value is NULL.
2.5 type
Which category is used for the connection, and whether an index is used. Commonly used types are: system, const, eq_ref, ref, range, index, ALL (from left to right, Performance is getting worse), for details, see EXPLAIN Join Types
NULL: MySQL decomposes the statement during the optimization process, and does not even need to access the table or index during execution. For example, the minimum value from an index column can be selected through a separate index Search completed
system: This table (it may also be the temporary table queried) has only one row of data (= system table). It is a special case of const
const : The table has at most 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 treated as constants by the rest of the optimizer. const tables are fast because they are read only once! const is used for all parts of the query when the condition is a PRIMARY KEY or UNIQUE index and compared with a constant value.
In the following query, tbl_name can be used for const tables:
SELECT * from tbl_name WHERE primary_key=1；
SELECT * from tbl_name WHERE primary_key_part1=1和 primary_key_part2=2；
--例子
mysql> explain select * from t_a where id =1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | const | PRIMARY | PRIMARY | 8 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.07 sec)
Copy after login
eq_ref: For each combination of rows in the previous tables, read one row from this table. Besides system and const, this is the best connection type. It is used when the join uses all parts of the index, and the index is a primary key or a unique non-null index. eq_ref can be used on indexed columns compared using the = operator. The comparison value can be a constant or an expression that uses a column from a table that was read before this table.
In the following example, MySQL can use the eq_ref join to process 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;
--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.code=t_b.code;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| 1 | SIMPLE | t_a | NULL | ALL | uk_code | NULL | NULL | NULL | 9 | 100.00 | NULL |
| 1 | SIMPLE | t_b | NULL | eq_ref | uk_code | uk_code | 4 | test.t_a.code | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.03 sec)
Copy after login
ref For each combination of rows from the previous table, all rows with matching index values will be read from this table . Use ref if the join only uses the leftmost prefix of the key, or if the key is not a UNIQUE or PRIMARY KEY (in other words, if the join cannot result in a single row based on the key query). This join type is good if you are using keys that only match a small number of rows. ref can be used on indexed columns using the = or <=> operators.
In the following example, MySQL can use ref joins 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;
--例子（t_b为t_a的复制表，表结构相同）
mysql> explain select * from t_a,t_b where t_a.age=t_b.age;
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
| 1 | SIMPLE | t_a | NULL | ALL | age_key | NULL | NULL | NULL | 9 | 100.00 | Using where |
| 1 | SIMPLE | t_b | NULL | ref | age_key | age_key | 5 | test.t_a.age | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+---------+---------+--------------+------+----------+-------------+
2 rows in set, 1 warning (0.03 sec)
Copy after login
fulltext: Use the FULLTEXT index to perform the join
ref_or_null: The join type ref is similar, but Added MySQL ability to specifically search for rows containing NULL values. This join type of optimization is often used in solving subqueries.
In the following example, MySQL can use ref_or_null joins to process ref_tables:
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
--例子
mysql> explain select * from t_a where t_a.age =3 or t_a.age is null;
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
| 1 | SIMPLE | t_a | NULL | ref_or_null | age_key | age_key | 5 | const | 2 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+---------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)
Copy after login
index_merge: This 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.
SELECT * FROM ref_table
WHERE idx1=expr1 OR idx2 =expr2;
--例子
mysql> explain select * from t_a where t_a.code =3 or t_a.age = 3;
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
| 1 | SIMPLE | t_a | NULL | index_merge | uk_code,age_key | uk_code,age_key | 4,5 | NULL | 2 | 100.00 | Using union(uk_code,age_key); Using where |
+----+-------------+-------+------------+-------------+-----------------+-----------------+---------+------+------+----------+-------------------------------------------+
1 row in set, 1 warning (0.03 sec)
Copy after login
unique_subquery: This type replaces the ref of the IN subquery of the following form:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery is a The index lookup function can completely replace the subquery and is more efficient. index_subquery: This join type is similar to unique_subquery. IN subqueries can be replaced, but only for non-unique indexes in subqueries of the following form:
value IN (SELECT key_column FROM single_table WHERE some_expr)range: retrieve only given A range of rows, using an index to select rows. The key column shows which index was used. key_len contains the longest key element of the index used. The ref column is NULL in this type. When using =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN or IN operators, you can use range# when comparing key columns with constants. ##
mysql> explain select * from t_a where id > 8;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_a | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)
Copy after login
ALL: Perform a complete table scan for each combination of rows from the previous table. This is usually not good if the table is the first one not marked const, and is usually bad in that case. It is usually possible to add more indexes without using ALL so that rows can be retrieved based on constant values or column values in the previous table.
2.6 possible_keys
The possible_keys column indicates 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 actually be used in the generated table order.
If the column is NULL, there is no related index. In this case, you can improve the performance of your query by checking the WHERE clause to see if it refers to certain columns or columns that are suitable for indexing. If so, create an appropriate index and check query
2.7 key
# with EXPLAIN again. The key column shows the key (index) that MySQL actually decided to use. If no index is selected, the key is NULL. To force MySQL to use or ignore the index on the possible_keys column, use FORCE INDEX, USE INDEX, or IGNORE INDEX in the query.
2.8 key_len
The key_len column displays the key length that MySQL decides to use. If the key is NULL, the length is NULL.
The length of the index used. Without losing accuracy, the shorter the length, the better
2.9 ref
The ref column shows which column or constant is used with the key to select rows from the table.
2.10 rows
The rows column displays the number of rows that MySQL thinks it must examine when executing the query.
2.11 Extra
This column contains the details of how MySQL solved the query, as detailed below.
- Distinct: Once MYSQL finds a row that matches the row union, No more searching
- Not exists: MYSQL optimizes LEFT JOIN. Once it finds a row matching the LEFT JOIN criteria, it no longer searches
- Range checked for each: No ideal found index, so for each combination of rows from the previous table, MYSQL checks which index was used and uses it to return the rows from the table. This is one of the slowest connections using an index
- Using filesort: When you see this, the query needs to be optimized. MYSQL requires an extra step to discover how to sort the returned rows. It sorts all rows based on the connection type and the row pointers that store the sort key value and all rows matching the condition
- Using index: Column data is obtained from just using the information in the index without reading the actual action The table is returned. This occurs when all requested columns of 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. This usually occurs when ORDER BY is performed on different column sets instead of GROUP BY.
- Using where: A WHERE clause is used to limit which rows will be compared with the next A table is matched or returned to the user. This will happen if you do not want to return all rows in the table and the connection type is ALL or index, or there is a problem with the query
[Related recommendations:MySQL Video Tutorial]
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
