Introduction to the

MySQL provides an EXPLAIN command that analyzes SELECT statements and outputs details of SELECT execution for developers to optimize. The EXPLAIN command is very simple to use. Just add EXPLAIN before the SELECT statement, for example:

EXPLAIN SELECT * from user_info WHERE  id < 300;Copy the code

To prepare

To facilitate the use of EXPLAIN, 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);Copy the code
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');Copy the code

EXPLAIN output format

The output of the EXPLAIN command looks 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)Copy the code

The meaning of each column is as follows:

  • Id: indicates the id of the SELECT query. Each SELECT is automatically assigned a unique identifier.

  • Select_type: SELECT query type.

  • Table: Indicates the table to be queried

  • Partitions: matches partitions

  • Type: the join type

  • Possible_keys: possible index in this query

  • Key: indicates the exact index used in the query.

  • Ref: Which field or constant is used with the key

  • Rows: Shows how many rows were scanned for this query. This is an estimate.

  • Filtered: indicates the percentage of data filtered by this query condition

  • -Blair: Extra information

Let’s focus on some of the more important fields.

select_type

Select_type specifies the query type. Its common values are as follows:

  • SIMPLE, indicating that the query does not contain a UNION query or subquery

  • PRIMARY, indicating that this query is the outermost query

  • UNION, indicating that the query is the second or subsequent query to the UNION

  • DEPENDENT UNION the second or subsequent query statement in the UNION, depending on the external query

  • The UNION RESULT

  • SUBQUERY, the first SELECT in a SUBQUERY

  • DEPENDENT SUBQUERY: The first SELECT in a SUBQUERY, DEPENDENT on the external query. That is, subqueries depend on the results of outer queries.

The most common query type is SIMPLE, for example when our query has no subqueries and no UNION queries, it is usually SIMPLE, 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)Copy the code

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)Copy the code

table

Represents the table or derived table involved in the query

type

The Type field is important because it provides an important basis for determining whether a query is efficient. Through the type field, we can determine whether the query is a full table scan or an index scan, etc.

Type Common type

The common values of type are:

  • System: There is only one data in the table. This type is a special const type.

  • Const: An equivalent query scan against a primary key or unique index that returns at most one row. Const is very fast because it reads only once. For example, the following query uses the 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)Copy the code

  • Eq_ref: This type is typically found in multi-table JOIN queries and means that for each result of the previous table, only one row of the subsequent table can be matched. In addition, the comparison operation of query is usually =, which indicates high query efficiency. 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)Copy the code

  • Ref: This type typically occurs in join queries with multiple tables, for non-unique or non-primary key indexes, or for queries that use the left-most prefix rule index. For example, in this example, a query of type ref is used:

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: Ref: const rows: 1 filtered: 100.00 Extra: 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: 9 ref: const rows: 1 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.01sec)Copy the code

  • Range: indicates that the index range query is used to obtain some data records in the table by the index field range. This type usually appears IN =, < >, >, > =, <, < =, IS NULL, the < = >, BETWEEN, IN () operation. When type is range, the ref field in EXPLAIN output is NULL and the key_len field is the longest index used in the 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)Copy the code

  • Index: full index scan, similar to ALL except that ALL scans ALL indexes, while index scans only ALL indexes. The data to be queried can be retrieved directly from the index tree without scanning the data. When this is the case, the Extra field shows 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: 152
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)Copy the code

In the above example, the name field of our query happens to be an index, so we can satisfy our query requirements by fetching 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: indicates a full table scan. This type of query is one of the least high-performance queries. In general, our queries should not have all-type queries, because such queries can be a disaster for database performance in the case of large data volumes. If a query is an All-type query, it can generally be avoided by adding indexes to the corresponding fields. Below is an example of a full table scan. It can be seen that the possible_keys and key fields are both NULL during the full table scan, indicating that no index is used and the rows are huge, so the whole query is 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)Copy the code

Type Performance comparison of type

In general, the performance relationship between different types of type is as follows: ALL < index < range ~ index_merge < ref < eq_ref < const < System ALL Type Because it is a full table scan, it is the slowest in the same query conditions. An index query is not a full table scan, but it scans ALL indexes and is therefore slightly faster than an ALL query. The latter types all use indexes to query data, so they can filter some or most of the data, so the query efficiency is relatively high.

possible_keys

Possible_keys specifies the possible_keys index that can be used by MySQL when querying. Note that even though some indexes appear in Possible_keys, it doesn’t mean that the index will actually be used by MySQL. The key field determines which indexes are used in the MySQL query.

key

This field is the actual index used by MySQL in the current query.

key_len

Represents the number of bytes of the index used by the query optimizer. This field evaluates whether the composite index is fully used or only the left-most field is used. Key_len is evaluated as follows:

  • string

    • Char (n): Indicates the length of n bytes

    • Varchar (n): 3 n + 2 bytes if utF8 encoding is used. If it is utF8MB4 encoded, it is 4 n + 2 bytes.

  • Value 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 takes up one byte. If a field is NOT NULL, this property is NOT present.

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)Copy the code

The above example queries the specified contents from the table order_info, and as we know from the table construction clause, the table order_info has a federated index:

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)Copy the code

Select * from user_id WHERE user_id < 3 AND product_name = ‘p1’ AND productor = ‘WHH’ WHERE user_id < 3 AND product_name = ‘p1’ When a range query is encountered, index matching is stopped, so the only field we actually use for the index is user_id. Therefore, in EXPLAIN, key_len is shown as 9. Because the user_id field is BIGINT and takes up eight bytes, and the NULL attribute takes up one byte, the total is nine bytes. If we change the user_id field to BIGINT(20) NOT NULL DEFAULT ‘0’, key_length should be 8.

Because of the left-most prefix matching principle above, our query only uses the user_id field of the union 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)Copy the code

In this query, we do not use the range query, key_len is 161. Why is that? Select * from table WHERE user_id = 1 AND product_name = ‘p1’ WHERE user_id = 1 AND product_name = ‘p1’ So keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

rows

Rows is also an important field. Based on the statistics, the MySQL query optimizer estimates the number of rows of data that the SQL needs to scan and read to find the result set. This value is a very intuitive indication of how efficient SQL is, and in principle the fewer rows the better.

Extra

A lot of additional information in EXplain will be displayed in the Extra field. Common ones are the following:

  • Using filesort When Using filesort is used in Extra, MySQL requires additional sorting operations. Using filesort is recommended to remove this query because it consumes large CPU resources.

For 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)Copy the code

Our index is

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)Copy the code

However, the above query is sorted by product_name, so it cannot be optimized Using indexes, resulting in Using filesort. If we change the sorting criteria to ORDER BY user_id, product_name, then the Using Filesort will not appear. 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: 9
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)Copy the code

  • Using index” overwrite index scan “, indicating that the query can find the required data in the index tree without scanning the table data files, usually indicating good performance

  • Using temporary A query uses temporary tables, which are used for sorting, grouping, and multi-table joins. Optimization is recommended because the query efficiency is not high.