Shulou(Shulou.com)06/01 Report--

Brief introduction

MySQL provides an EXPLAIN command that analyzes SELECT statements and outputs details of SELECT execution for developers to optimize.

The use of the EXPLAIN command is very simple, just add Explain before the SELECT statement, for example:

EXPLAIN SELECT * from user_info WHERE id

< 300; 准备 为了接下来方便演示 EXPLAIN 的使用, 首先我们需要建立两个测试用的表, 并添加相应的数据: 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 = utf8INSERT 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 = utf8INSERT 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 输出格式 EXPLAIN 命令的输出内容大致如下: mysql>

Explain select * from user_info where id = 2\ select_type * 1. Row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)

The meanings of each column are as follows:

Id: the identifier of the SELECT query. Each SELECT automatically assigns a unique identifier .select _ type: type of SELECT query .table: which table is queried partitions: matching partition type: join type possible_keys: possible index in this query key: exact index used in this query .ref: which field or constant is used with key rows: shows how many rows have been scanned in this query. This is an estimate. Filtered: indicates the percentage of data filtered by this query condition extra: additional information

Next, let's focus on the more important fields.

Select_type

Select_type indicates the type of query. Its common values are: SIMPLE, indicating that this query does not contain a UNION query or subquery PRIMARY, indicating that this query is the outermost query UNION, indicating that this query is the second or subsequent query DEPENDENT UNION of UNION, the second or subsequent query statement in UNION, depending on the external query UNION RESULT, the result of UNION SUBQUERY, the first SELECTDEPENDENT SUBQUERY in the subquery: the first SELECT in the subquery. It depends on the external inquiry. That is, the subquery depends on the results of the outer query.

The most common query category should be SIMPLE, for example, when our query does not have a subquery or a UNION query, then it is usually the SIMPLE type, for example:

Mysql > explain select * from user_info where id = 2\ id * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set 1 warning (0.00 sec)

If we use a UNION query, the result of the EXPLAIN output is similar to 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 | Extra | +-+-- -+-+ | 1 | PRIMARY | user_info | NULL | range | PRIMARY | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | 2 | UNION | user_info | NULL | range | PRIMARY | 8 | NULL | 3 | 100.00 | Using where | | NULL | UNION RESULT | | NULL | ALL | 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, which provides an important basis for judging whether the query is efficient or not. Through the type field, we determine whether the query is a full table scan or an index scan.

Common types of type

The commonly used values for type are:

System: there is only one piece of data in the table. This type is a special const type. Const: an equivalent query scan for a primary key or unique index that returns at most one row of data. Const query is very fast because it can be read only once.

For example, the following query uses the primary key index So type is of type const. MySQL > explain select * from user_info where id = 2\ row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const Rows: 1 filtered: 100.00 Extra: NULL1 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 the latter table can be matched. And the comparison operation of the query is usually =, the query efficiency is high. For example: mysql > EXPLAIN SELECT * FROM user_info Order_info WHERE user_info.id = order_info.user_id\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: order_info partitions: NULL type: indexpossible_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_refpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: test.order_info.user_id rows: 1 filtered: 100.00 Extra: NULL2 rows in set 1 warning (0.00 sec) ref: this type usually occurs in multi-table join queries for non-unique or non-primary key indexes, or for queries that use leftmost prefix rule indexes.

For example, in the following 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\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: constpossible_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: refpossible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 Ref: const rows: 1 filtered: 100.00 Extra: Using index2 rows in set 1 warning (0.01 sec) range: use index range query to obtain part of the data records in the table through index field range. This type usually appears in =, >, > = FROM user_info-> WHERE id BETWEEN 2 AND 8\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 7 filtered: 100.00 Extra: Using where1 row in set 1 warning (0.00 sec) index: represents a full index scan (full index scan), which is similar to the ALL type, except that the ALL type is a full table scan, while the index type only scans all indexes, not data.

The index type usually appears: 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 Using index.

For example:

Mysql > EXPLAIN SELECT name FROM user_info\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: indexpossible_keys: NULL key: name_index key_len: 152ref: NULL rows: 10 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)

In the above example, the name field we queried happens to be an index, so we can obtain data directly from the index to meet the needs of the query 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: stands for full table scan, this type of query is one of the worst performing queries. Generally speaking, our query should not appear ALL type query, because such a query in the case of a large amount of data, it is a great disaster to the performance of the database. If a query is an ALL type query, it can generally be avoided by adding indexes to the corresponding fields.

The following is an example of a full table scan, you can see that in the full table scan, the possible_keys and key fields are NULL, indicating that the index is not used, and the rows is very large, so the whole query efficiency is very inefficient.

Mysql > EXPLAIN SELECT age FROM user_info WHERE age = 20\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: user_info partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 filtered: 10.00 Extra: Using where1 row in set 1 warning (0.00 sec)

Performance comparison of type types

In general, the performance relationships between different type types are as follows:

ALL

< index < range ~ index_merge < ref < eq_ref < const < system ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的. 而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快. 后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了. possible_keys possible_keys 表示 MySQL 在查询时, 能够使用到的索引. 注意, 即使有些索引在 possible_keys 中出现, 但是并不表示此索引会真正地被 MySQL 使用到. MySQL 在查询时具体使用了哪些索引, 由 key 字段决定. key 此字段是 MySQL 在当前查询时所真正使用到的索引. key_len 表示查询优化器使用了索引的字节数. 这个字段可以评估组合索引是否完全被使用, 或只有最左部分字段被使用到. key_len 的计算规则如下: 字符串 char(n): n 字节长度varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节. 数值类型: TINYINT: 1字节SMALLINT: 2字节MEDIUMINT: 3字节INT: 4字节BIGINT: 8字节 时间类型 DATE: 3字节TIMESTAMP: 4字节DATETIME: 8字节 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性. 我们来举两个简单的栗子: 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: rangepossible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: NULL rows: 5 filtered: 11.11 Extra: Using where; Using index1 row in set, 1 warning (0.00 sec) 上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表 order_info 有一个联合索引: KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`) 不过此查询语句 WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH' 中, 因为先进行 user_id 的范围查询, 而根据 最左前缀匹配 原则, 当遇到范围查询时, 就停止索引的匹配, 因此实际上我们使用到的索引的字段只有 user_id, 因此在 EXPLAIN 中, 显示的 key_len 为 9. 因为 user_id 字段是 BIGINT, 占用 8 字节, 而 NULL 属性占用一个字节, 因此总共是 9 个字节. 若我们将user_id 字段改为 BIGINT(20) NOT NULL DEFAULT '0', 则 key_length 应该是8. 上面因为 最左前缀匹配 原则, 我们的查询仅仅使用到了联合索引的 user_id 字段, 因此效率不算高. 接下来我们来看一下下一个例子: 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: refpossible_keys: user_product_detail_index key: user_product_detail_index key_len: 161ref: const Const rows: 2 filtered: 100.00 Extra: Using index1 row in set, 1 warning (0.00 sec)

In this query, we did not use the range query, the value of key_len is 161. Why? Because only the first two fields in the federated index are used in our query condition WHERE user_id = 1 AND product_name = 'p1', keyLen (user_id) + keyLen (product_name) = 9 + 50 * 3 + 2 = 161

Rows

Rows is also an important field. According to the statistical information, the MySQL query optimizer estimates the number of data rows that SQL needs to scan to find the result set.

This value is very intuitive to show the efficiency of SQL, in principle, the less rows, the better.

Extra

A lot of additional information in EXplain is displayed in the Extra field, and the common ones are as follows:

Using filesort

When there is Using filesort in Extra, it means that MySQL requires additional sorting operations and cannot be sorted through the index order. Generally there are Using filesort, it is recommended to optimize the removal, because such a query CPU resources consumption.

For example, the following example:

Mysql > EXPLAIN SELECT * FROM order_info ORDER BY product_name\ gateway * 1. Row * * id: 1 select_type: SIMPLE table: order_info partitions: NULL type: indexpossible_keys: NULL key: user_product_detail_index key_len: 253 Ref: NULL rows: 9 filtered: 100.00 Extra: Using index Using filesort1 row in set, 1 warning (0.00 sec)

Our index is

KEY `user_product_detail_ index` (`user_ id`, `product_ name`, `productor`)

However, the above query is sorted according to product_name, so it cannot be optimized using an index, which results in Using filesort.

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

Mysql > EXPLAIN SELECT * FROM order_info ORDER BY user_id Product_name\ SIMPLE table * 1. Row * * id: 1 select_type: SIMPLE table: order_info partitions: NULL type: indexpossible_keys: NULL key: user_product_detail_index key_len: 253 ref: NULL rows: 9 filtered : 100.00 Extra: Using index1 row in set 1 warning (0.00 sec) Using index

"override index scan", which means that the query can find the required data in the index tree, without scanning table data files, which often shows good performance.

Using temporary

Temporary tables are used in queries, which generally appear in sorting, grouping and multi-table join cases. Query efficiency is not high, so optimization is recommended.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.