Introducing MySQL's performance optimization tool Explain-Mysql Tutorial-php.cn

MySQL TutorialColumn introduces the performance optimization artifact Explain

##More related free learning recommendations:
mysql Tutorial(Video)

Introduction

MySQL provides an EXPLAIN command, which can analyze the

SELECTstatement and outputSELECTfor execution Detailed information for developers to optimize.EXPLAIN command usage is very simple, just add Explain before the SELECT statement, for example:

EXPLAIN SELECT * from user_info WHERE id < 300;

Copy after login

Prepare

In order to receive To facilitate the demonstration of the use of EXPLAIN, first we need to create two tables for testing and add 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 after login

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 after login

EXPLAIN output format

The output content 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)

Copy after login

The meaning of each column is as follows:

select_type: SELECT query Type.
table: Which table is being queried
partitions: Matching partition
type: join type
possible_keys: Possible keys in this query Selected index
key: The exact index used in this query.
ref: Which field or constant is used together with key
rows: Displays the total number of this query How many rows were scanned. This is an estimate.
filtered: Indicates the percentage of data filtered by this query condition
extra: Additional information

Continue Let’s focus on the more important fields.

select_type

select_typerepresents the type of query, and its common values are:

PRIMARY, indicates that this query is the outermost query
UNION, indicates that this query is the second query of UNION or subsequent query
DEPENDENT UNION, the second or subsequent query statement in UNION, depends on the outer query
UNION RESULT, the result of UNION
SUBQUERY, The first SELECT in the 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 type should be

SIMPLE. For example, when our query has no subquery and no UNION query, it is usually theSIMPLEtype, 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 after login

If we use a UNION query, the results output by EXPLAIN are 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 |  | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary | +----+--------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 3 rows in set, 1 warning (0.00 sec)

Copy after login

table

indicates the table or derived table involved in the query

type## The

#type

field is more important. It provides an important basis for judging whether the query is efficient. Through thetypefield, we judge that this query is afull table scanOrindex scanetc.type Common types

type Common values are:

system: There is only one entry in the table Data. This type is a special

type.const: Equivalent query scan for primary key or unique index, only returns one row of data at most. const query is very fast because it only Just read it once.

type
is of typeconst.

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 after login

eq_ref: This type usually appears in multi-table join queries, which means that each result in the front table can only match one row of results in the back table. And the comparison operation of the query is usually

, query efficiency is higher. 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)

Copy after login

ref: This type usually appears in join queries of multiple tables, for non-unique or non-primary key indexes, or when

is the query of the rule index.For example, in the following example, theref
type of 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)

Copy after login

range: means Use index range query to obtain some data records in the table through the index field range. This type usually appears in =, <>, >, >=, <, <=, IS NULL, <=> , BETWEEN, IN() operation.

type
isrange, then thereffield output by EXPLAIN is NULL, andkey_lenThe field is the longest of the indexes 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)

Copy after login

index: means full index scan (full index scan), similar to the ALL type, except that the ALL type is a full table scan, while the index type only scans all indexes, without scanning the data.

Using index
.

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 after login

In the above example, the name field we query happens to be an index, so we can meet the query needs by getting the data directly from the index, without querying the data in the table. Therefore, in this case, type The value is

index

, and the value of Extra isUsing index.

ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免. 下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

 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 after login
 type 类型的性能比较
 通常来说, 不同的 type 类型的性能关系如下:
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: 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)
        
         Copy after login
        
       
       上面的例子是从表 order_info 中查询指定的内容, 而我们从此表的建表语句中可以知道, 表order_info有一个联合索引:
       
        KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
        
         Copy after login
        
       
       不过此查询语句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: 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)这次的查询中, 我们没有使用到范围查询, key_len 的值为 161. 为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中, 仅仅使用到了联合索引中的前两个字段, 因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161
 rows
 rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.
 Extra
 EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容:
 Using filesort
当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
例如下面的例子:
 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 after login
        
       
       我们的索引是
       
        KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)
        
         Copy after login
        
       
       但是上面的查询中根据product_name来排序, 因此不能使用索引进行优化, 进而会产生Using filesort.
如果我们将排序依据改为ORDER BY user_id, product_name, 那么就不会出现Using filesort了. 例如:
       
        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 after login
        
       
       
        Using index
"覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
        Using temporary
查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
       
       
        相关免费推荐：编程视频课程
       
       
       
       The above is the detailed content of Introducing MySQL's performance optimization tool Explain. For more information, please follow other related articles on the PHP Chinese website!