Basic usage analysis of Explain, a magical tool for MySQL performance optimization

Introduction

MySQL provides an EXPLAIN command that analyzes SELECT statements and outputs detailed information about 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;

Prepare

To demonstrate the use of EXPLAIN, we first 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 is roughly as follows:

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 meaning of each column is as follows:

• id: The identifier of the SELECT query. Each SELECT is automatically assigned a unique identifier.
• select_type: The type of SELECT query.
• table: which table is being queried
• partitions: matching partitions
• type: join type
• possible_keys: possible indexes to use in this query
• key: The exact index used in this query.
• ref: which field or constant is used with the key
• rows: Shows how many rows were scanned by this query. This is an estimate.
• filtered: indicates the percentage of data filtered by this query condition
• extra: additional information

Next, let's take a look at some of the more important fields.

select_type

• select_type indicates the type of query, and its common values ​​are:
• SIMPLE, which means that this query does not contain UNION queries or subqueries
• PRIMARY, indicating that this query is the outermost query
• UNION, indicating that this query is the second or subsequent query of UNION
• DEPENDENT UNION, the second or subsequent query in a UNION depends on the outer query
• UNION RESULT, the result of UNION
• SUBQUERY, the first SELECT in a subquery
• DEPENDENT SUBQUERY: The first SELECT in a subquery depends on the outer query. That is, the subquery depends on the result of the outer query.

The most common query type should be SIMPLE. For example, when our query has no subquery or UNION query, it is usually 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 use UNION query, then the EXPLAIN output is similar to the following:

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 where |
| 2 | UNION | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where |
| NULL | UNION RESULT | <union1,2> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+--------+---------+------+------+------+-----------------+
3 rows in set, 1 warning (0.00 sec)

table

Indicates the table or derived table involved in the query

type

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

type Common types

Common values ​​for type are:

• system: There is only one data in the table. This type is a special const type.
• const: For equal value queries on primary keys or unique indexes, only one row of data is returned at most. Const queries are very fast because they only read 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)

• eq_ref: This type usually appears in multi-table join queries, indicating that for each result of the previous table, only one row of results in the next table can be matched. And the comparison operation of the query is usually =, which has high query efficiency. For example:

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 usually appears in multi-table join queries, for non-unique or non-primary key indexes, or queries using the leftmost prefix rule index.

For example, in the following example, the ref type query 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: PRIMARY
   key: PRIMARY
  key_len: 8
   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.01 sec)

• range: indicates using index range query to obtain partial data records in the table through the index field range. This type usually appears in =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() operations.

When type is range, the ref field in the 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)

• index: indicates full index scan, which is similar to the ALL type, except that the ALL type scans the entire table, while the index type only scans all indexes without scanning data.

The index type usually appears when 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 will display Using index.

For example:

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)

In the above example, the name field we are querying happens to be an index, so we can directly get the data from the index to meet the query requirements without querying the data in the table. Therefore, in this case, the value of type is index, and the value of Extra is Using index.

• ALL: Indicates full table scan. This type of query is one of the worst performing queries. Generally speaking, we should not have ALL type queries, because such queries are a huge disaster for database performance when the amount of data is large. If a query is an ALL type query, then generally speaking, you can add indexes to the corresponding fields to avoid it.

The following is an example of a full table scan. You can see that in the full table scan, both the possible_keys and key fields are NULL, indicating that no index is used, and the rows are very large, so the overall query efficiency is very low.

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

Generally speaking, the performance relationship of different types is as follows:

ALL < index < range ~ index_merge < ref < eq_ref < const < system

The ALL type is the slowest because it is a full table scan under the same query conditions.

Although the index type query does not scan the entire table, it scans all indexes and is therefore slightly faster than the ALL type.

The following types all use indexes to query data, so some or most of the data can be filtered, so the query efficiency is relatively high.

possible_keys

possible_keys indicates the indexes that MySQL can use when querying. Note that even if some indexes appear in possible_keys, it does not mean that this index will actually be used by MySQL. The specific indexes that MySQL uses when querying are determined by the key field.

key

This field is the index that MySQL actually uses in the current query.

key_len

Indicates the number of bytes used by the query optimizer. This field can be used to evaluate whether the composite index is fully used, or only the leftmost fields are used.
The calculation rules for key_len are as follows:

String

• char(n): n bytes in length
• varchar(n): 3n+2 bytes if utf8 encoding; 4n+2 bytes if utf8mb4 encoding.

Value type:

• TINYINT: 1 byte
• SMALLINT: 2 bytes
• MEDIUMINT: 3 bytes
• INT: 4 bytes
• BIGINT: 8 bytes

Time 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, it does not have this attribute.

Let's take two simple examples:

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: 5
   filtered: 11.11
    Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

The above example queries the specified content from the table order_info. From the table creation statement, we can see that the table order_info has a joint index:

KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

However, in the query WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH', the user_id range query is performed first. According to the leftmost prefix matching principle, when a range query is encountered, the index matching stops. Therefore, in fact, the only index field we use is user_id. Therefore, in EXPLAIN, the displayed key_len is 9. Because the user_id field is BIGINT, it occupies 8 bytes, and the NULL attribute occupies one byte, so the total is 9 bytes. If we change the user_id field to BIGINT(20) NOT NULL DEFAULT '0', the key_length should be 8.

Because of the leftmost prefix matching principle, our query only uses the user_id field of the joint index, so the efficiency is not high.

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 range query, and the value of key_len is 161. Why? Because in our query condition WHERE user_id = 1 AND product_name = 'p1', only the first two fields in the joint index are used, 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 that SQL needs to scan to find the result set.
This value directly shows the efficiency of SQL. In principle, the fewer rows, the better.

Extra

A lot of extra information in EXplain will be displayed in the Extra field, the common ones are as follows:

• Using filesort

When Using filesort is in Extra, it means that MySQL needs additional sorting operations and cannot achieve the sorting effect through index order. Generally, it is recommended to optimize and remove Using filesort because such queries consume a lot of CPU resources.

For example, 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`)

However, the query above is sorted by product_name, so the index cannot be used for optimization, which will result in Using filesort.

If we change the sorting basis to ORDER BY user_id, product_name, then Using filesort will not appear. For example:

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)

• Using index

"Covering index scan" means that the query can find the required data in the index tree without scanning the table data file, which often indicates good performance

• Using temporary

The query uses temporary tables, which usually occur in sorting, grouping and multi-table join situations. The query efficiency is not high. It is recommended to optimize.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.