Introduction to the
MySQL provides an EXPLAIN command that analyzes SELECT statements and outputs detailed information about SELECT execution for developers to optimize. The EXPLAIN command is easy to use by prefacing the SELECT statement with EXPLAIN, for example:
EXPLAIN SELECT * from user_info WHERE id < 300;To prepare
In order to demonstrate the use of EXPLAIN next, first we need to create two test tables and add the corresponding data:
CREATE TABLE `user_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '',
`age` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_index` (`name`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8
INSERT INTO user_info (name, age) VALUES ('xys', 20);
INSERT INTO user_info (name, age) VALUES ('a', 21);
INSERT INTO user_info (name, age) VALUES ('b', 23);
INSERT INTO user_info (name, age) VALUES ('c', 50);
INSERT INTO user_info (name, age) VALUES ('d', 15);
INSERT INTO user_info (name, age) VALUES ('e', 20);
INSERT INTO user_info (name, age) VALUES ('f', 21);
INSERT INTO user_info (name, age) VALUES ('g', 23);
INSERT INTO user_info (name, age) VALUES ('h', 50);
INSERT INTO user_info (name, age) VALUES ('i', 15);CREATE TABLE `order_info` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`user_id` BIGINT(20) DEFAULT NULL,
`product_name` VARCHAR(50) NOT NULL DEFAULT '',
`productor` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');
INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');
INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');
INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');EXPLAIN output format
The output of the EXPLAIN command looks something like this:
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 REF: const Rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 WARNING (0.00 SEC)The meanings of the columns are as follows:
-
Id: The identifier of the SELECT query. Each SELECT is automatically assigned a unique identifier.
-
SELECT_TYPE: Select the type of the query.
-
TABLE: Which table is being queried
-
Partitions: The number of matching partitions
-
Type: the join type
-
Possible_keys: What index might be used in this query
-
Key: The exact index used in this query.
-
Ref: Which field or constant is used with the key
-
Rows: Shows the total number of rows scanned by this query. This is an estimate.
-
Filtered: Represents the percentage of data filtered by this query condition
-
-Sheldon: I have extra information
Let’s focus on a few of the more important fields.
select_type
SELECT_TYPE represents the type of the query. Its common values are:
-
Simple, which means that this query does not contain a UNION query or a subquery
-
Primary, which indicates that this query is the outermost query
-
UNION, indicating that this query is the second or subsequent query to the UNION
-
Dependent Union, the second or subsequent query in the UNION, depends on the external query
-
UNION RESULT, UNION RESULT
-
SubQuery, the first SELECT in the SUBQUERY
-
Dependent SubQuery: The first SELECT in a SUBQUERY, depending on the external query. That is, the subquery depends on the result of the outer query.
The most common type of query would be Simple. For example, if our query has no subquery and no UNION query, then it is usually of the SIMPLE type. For example:
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 REF: const Rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 WARNING (0.00 SEC)If we used the UNION query, the EXPLAIN output would look something like this:
mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) -> UNION -> (SELECT * FROM user_info WHERE id IN (3, 4, 5)); +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+---- -------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+---- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY 8 | NULL | | 3 | | 100.00 Using the where | | 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY 8 | NULL | | 3 | | 100.00 Using the where | | NULL | UNION RESULT | < union1, 2 > | NULL | | NULL ALL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+---- -- -- -- -- -- -- -- -- -- -- -- -- -- + 3 rows in the set, 1 warning (0.00 SEC)table
Represents the table or derived table involved in the query
type
The Type field is important because it provides an important basis for judging whether a query is efficient or not. By the type field, we can determine whether this query is a full table scan or index scan.
Type Common Type
The commonly used values of TYPE are:
-
SYSTEM: There is only one data in the table. This type is a special const type.
-
Const: A query scan of equal value for a primary key or unique index that returns at most one row of data. For example, the following query uses a primary key index, so type is const.
mysql> explain select * from user_info where id = 2\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 REF: const Rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 WARNING (0.00 SEC)-
EQ_REF: This type typically appears on join queries with multiple tables, indicating that for each result in the front table, only one row of results in the back table can be matched. And the comparison operation of the query is usually =, the query efficiency is higher. Such as:
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: index
possible_keys: user_product_detail_index
key: user_product_detail_index
key_len: 314
ref: NULL
rows: 9
filtered: 100.00
Extra: Using where; Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: test.order_info.user_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)-
Ref: This type typically appears on multi-table JOIN queries for indexes that are not unique or primary key, or that use the leftmost prefix regular index. For example, the ref type query is used in the following example:
mysql> EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id AND order_info.user_id = 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: const possible_keys: PRIMARY KEY: PRIMARY KEY_LEN: 8 REF: CONST ROWS: 1 filtered: 1 NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 10 REF: const rows: 1 filtered: 100.00 Extra: Using index 2 rows in set, 1 WARNING (0.01sec)-
Range: Indicates the use of index range query, through the index field range to obtain some data records in the table. This type usually appears IN =, < >, >, > =, <, < =, IS NULL, the < = >, BETWEEN, IN () operation. When the type is range, the REF field of the EXPLAIN output is NULL and the KEY_LEN field is the longest index used in this query.
For example, the following example is a range query:
mysql> EXPLAIN SELECT *
-> FROM user_info
-> WHERE id BETWEEN 2 AND 8 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: NULL
rows: 7
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)-
INDEX: A full index scan is similar to an ALL scan, except that AN ALL scan is a full table scan, while AN INDEX scan only scans ALL the indexes, not the data. The data to be queried can be obtained directly in the index tree without scanning the data. When this is the case, the Extra field displays the Using Index.
Such as:
mysql> EXPLAIN SELECT name FROM user_info \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info partitions: NULL type: index possible_keys: NULL key: name_index key_len: REF: NULL ROWS: 10 filtered: 100.00 Extra: Using index 1 row in set, 1 WARNING (0.00 SEC)In the example above, the name field of our query happens to be an index, so we can satisfy the query by getting the data directly from the index without querying the data in the table. So in this case, the value of type is index and the value of Extra is Using index.
-
ALL: Represents a full table scan, and this type of query is one of the worst performing. In general, we should not have an ALL query on our query, because such a query can be a disaster for database performance when there is a large amount of data. If a query is of type ALL, it can generally be avoided by adding an index to the corresponding field. Here is an example of a full table scan. You can see that while full table scan was possible, the possible_keys and key fields were NULL, indicating that no index was used, and the rows were very large, so the whole query was very inefficient.
mysql> EXPLAIN SELECT age FROM user_info WHERE age = 20 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user_info
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)Performance comparison of type Type
In general, the performance relationships between different types are as follows: All < index < range ~ index_merge < ref < eq_ref < const < System All is the slowest for the same query because it is a full table scan. An INDEX query is not a full table scan, but it scans ALL the indexes and is therefore slightly faster than an ALL query. The latter types all use indexes to query the data, so you can filter some or most of the data, so the query is more efficient.
possible_keys
Possible_keys is an index that MySQL can use when it queries. Note that even though some indexes may appear in possible_keys, it does not mean that the index will actually be used by MySQL. The key field determines which indexes MySQL uses when querying.
key
This field is the index that MySQL actually uses for the current query.
key_len
Indicates the number of bytes used by the query optimizer for the index. This field evaluates whether the composite index is fully used, or if only the leftmost part of the index is used.
-
string
-
Char (n): length of n bytes
-
Varchar (n): 3 n + 2 bytes if UTF8; If the encoding is UTF8MB4, it is 4 N + 2 bytes.
-
-
Number type:
-
TINYINT: 1 byte
-
SMALLINT: 2 bytes
-
MEDIUMINT: 3 bytes
-
INT: 4 bytes
-
BIGINT: 8 bytes
-
-
Time to type
-
DATE: 3 bytes
-
TIMESTAMP: 4 bytes
-
DATETIME: 8 bytes
-
-
Field attributes: The NULL attribute occupies one byte. If a field is NOT NULL, this property is NOT available.
Let’s take two simple chestnuts:
mysql> EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: range possible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: NULL rows: Filtered: 11.11 Extra: Using where; Using index 1 row in set, 1 warning (0.00 SEC)The above example looks up the specified content from table ORDER_INFO, and we can see from this table that ORDER_INFO has a federated index:
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)WHERE user_id < 3 AND product_name = ‘p1’ AND productor = ‘WHH’ When we encounter a range query, we stop matching the index, so we are actually using only the index field user_id, so in EXPLAIN, the key_len is shown as 9. Because the user_id field is BigInt and takes 8 bytes, and the NULL attribute takes one byte, there are 9 bytes in total. BIGINT(20) NOT NULL DEFAULT ‘0’, key_length should be 8.
Because of the leftmost prefix matching principle, our query only uses the user_id field of the federated index, so it is not very efficient.
Let’s look at the next example:
mysql> EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1' \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 161 ref: CONST, CONST ROWS: 2 Filtered: 100.00 Extra: Using Index 1 ROW IN SET, 1 WARNING (0.00 SEC)In this query, we did not use a range query. The value of key_len is 161. Why is that? SELECT * FROM ‘p1’ WHERE user_id = 1 AND product_name = ‘p1’ WHERE product_name = ‘p1’; KeyLen (user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161
rows
Rows is also an important field. The MySQL query optimizer estimates the number of rows that the SQL needs to scan and read to find the result set based on statistics. This value is a straightforward indication of how efficient the SQL is, and in principle the fewer rows the better.
Extra
A lot of additional information in Explain is displayed in the Extra field. Common examples are the following:
-
MySQL > use filesort (Extra) MySQL > use filesort (Extra) MySQL > use filesort (Extra); In general, there is a Using filesort, it is recommended to optimize the removal, because such a query CPU consumption.
Take the following example:
mysql> EXPLAIN SELECT * FROM order_info ORDER BY product_name \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: order_info
partitions: NULL
type: index
possible_keys: NULL
key: user_product_detail_index
key_len: 253
ref: NULL
rows: 9
filtered: 100.00
Extra: Using index; Using filesort
1 row in set, 1 warning (0.00 sec)Our index is
KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)The above query is sorted according to PRODUCT_NAME, so it cannot be optimized Using the index, and will result in Using FILESORT. If we change the sort to ORDER BY user_id, product_name, then we will not have Using filesort. Such as:
mysql> EXPLAIN SELECT * FROM order_info ORDER BY user_id, product_name \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: order_info partitions: NULL type: index possible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: Filtered: 100.00 Extra: Using index 1 row in set, 1 warning (0.00 SEC)-
Using index to scan the data in the index tree. Using index to scan the data in the index tree. Using index to scan the data in the index tree
-
When sorting, grouping, and joining multiple tables, the query is not efficient. We recommend optimization.