Basic usage analysis of MySQL performance optimization artifact explain

Time:2021-3-9

brief introduction

MySQL provides an explain command, which can analyze the select statement and output the detailed information of select execution for developers to optimize

The use of the explain command is very simple. Just add explain before the select statement


EXPLAIN SELECT * from user_info WHERE id < 300;

get ready

In order to demonstrate the use of explain, we need to create two tables for testing 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 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 will automatically assign a unique identifier
  • select_ Type: the type of select query
  • Table: which table is queried
  • Partitions: matching partitions
  • Type: join type
  • possible_ Keys: the index that may be selected in this query
  • Key: the index exactly used in this query
  • Ref: which field or constant is used with 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
  • Extra: extra information

Next, let’s focus on the more important fields

select_type

  • select_ Type refers to the type of query, and its common values are:
  • Simple, indicating that the query does not contain union queries or subqueries
  • Primary, indicating that the query is the outermost query
  • Union, indicating that this query is the second or subsequent query of union
  • The second or subsequent query statement in a unit depends on the external query
  • Union result, union result
  • Subquery, the first select in a subquery
  • Dependent subquery: the first select in the subquery depends on the outer query, that is, the subquery depends on the result of the outer query

The most common query category should be simple. For example, when our query has no sub query 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, the output of explain 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

Represents the table or derived table involved in the query

type

The type field is more important. It provides an important basis to judge whether the query is efficient or not. Through the type field, we can judge whether the query is full table scan or index scan

Type common types

The common values of type are:

  • System: there is only one data in the table. This type is a special const type
  • Const: the equivalent query scan for primary key or unique index returns only one row of data at most. Const query is very fast because it can only read once
    For example, the following query uses the primary key index, so the 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 the join query of multiple tables, which means that each result of the former table can only match one row of the latter table. Moreover, the comparison operation of the query is usually =, which makes the query more efficient

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 join queries of multiple tables, for queries with non unique or non primary key indexes, or queries with leftmost prefix rule indexes

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: refers to using index range query to obtain partial data records in the table through index field range. This type usually appears in =, < >, >, > =, < =, is null, < = >, between, in() operations

When the type is range, the ref field of explain output is null and the key is empty_ The 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: refers to full index scan, similar to all type, except that all type is full table scan, while index type only scans all indexes, not data

The index type usually appears in: the data to be queried can be obtained directly in the index tree without scanning the data. In this 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 of our query happens to be an index, so we can get the data directly from the index to meet the needs of the query 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: refers to full table scan. This type of query is one of the worst performance queries. Generally speaking, we should not have all type queries in our query, because such a query is a huge disaster to the database performance in the case of large amount of data. If a query is all type query, then generally speaking, we 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 a full table scan, the possible_ The keys and key fields are null, which means that the index is not used, and the rows is huge, so the 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 relationships of different type types are as follows:

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

All is the slowest under the same query condition because it is full table scan

The index query does not scan all the indexes, so it is faster than all query

The latter types all use index to query data, so they can filter part or most of the data, so the query efficiency is relatively high

possible_keys

possible_ Keys refers to the indexes that MySQL can use when querying. Note that even some indexes are in the possible_ It does not mean that this index will be used by mysql. The key field determines which index MySQL uses when querying

key

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

key_len

Indicates the number of bytes of the index used by the query optimizer. This field can evaluate whether the composite index is fully used or only the leftmost field is used
key_ The calculation rules of len are as follows:

character string

  • Char (n): n byte length
  • Varchar (n): 3 N + 2 bytes for UTF 8 encoding and 4 N + 2 bytes for UTF 8 mb4 encoding

Value type:

  • Tinyint: 1 byte
  • Smartint: 2 bytes
  • Mediumint: 3 bytes
  • Int: 4 bytes
  • Bigint: 8 bytes

Time type

  • Date: 3 bytes
  • Timestamp: 4 bytes
  • Datetime: 8 bytes

Field property: the null property takes one byte. If a field is not null, there is no such property

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

The above example is from the table order_ Info, and we can know from the table building statement that the table order_ Info has a union index:


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

However, the query statement where user_ id < 3 AND product_ Name =’p1 ‘and producer =’whh’, because the user_ According to the leftmost prefix matching principle, when we encounter a range query, we stop the index matching. Therefore, in fact, we only use user as the index field_ ID, so in explain, the key displayed is_ Because of user_ The ID field is bigint, which takes 8 bytes, while the null property takes one byte, so the total is 9 bytes_ If the ID field is changed to bigint (20) not null default ‘0’, the key_ The length should be 8

Because of the leftmost prefix matching principle, our query only uses the user of the union index_ ID field, so the efficiency is not high

Let’s take a 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 didn’t use range query, key_ The value of len is 161. Why? Because our query condition is where user_ id = 1 AND product_ Name =’p1 ‘, only the first two fields in the union index are used, so keylen (user_ id) + keyLen(product_ name) = 9 + 50 * 3 + 2 = 161

rows

Rows is also an important field. According to the statistics, MySQL query optimizer estimates the number of data rows that SQL needs to scan and read to find the result set
This value is very intuitive to show 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 contents are as follows:

  • Using filesort

When using filesort is included in extra, it means that MySQL needs additional sorting operation and cannot achieve the sorting effect by index order. Generally, using filesort is recommended to be removed by optimization, because such query consumes 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`)

But in the above query, according to product_ Name, so you can’t use the index for optimization, which will result in using filesort

If we change the sort basis to order by user_ id, product_ Name, then using filesort will not appear


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

“Overlay index scan” means that the query can find the required data in the index tree without scanning the table data file, which often indicates that the performance is good

  • Using temporary

Temporary table is used in query, which usually appears in sorting, grouping and multi table join

summary

The above is the whole content of this article, I hope the content of this article has a certain reference learning value for your study or work, thank you for your support to developer.