Mysql explain 的使用和分析

explain 使用

explain 主要提供一个功能，就是对执行的 sql 语句进行分析，可以得到select语句的详细信息，以供DBA或者开发针对性能进行优化。

用法也很简单，就是在执行的语句前面加上 ‘explain’ 就好了。

例：explain select * from user where id=2;
mysql> explain select * from user;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user where id = 2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

带条件和不带条件的区别。

数据准备

CREATE TABLE `user` (
  `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 (name, age) VALUES ('xys', 20);
INSERT INTO user (name, age) VALUES ('a', 21);
INSERT INTO user (name, age) VALUES ('b', 23);
INSERT INTO user (name, age) VALUES ('c', 50);
INSERT INTO user (name, age) VALUES ('d', 15);
INSERT INTO user (name, age) VALUES ('e', 20);
INSERT INTO user (name, age) VALUES ('f', 21);
INSERT INTO user (name, age) VALUES ('g', 23);
INSERT INTO user (name, age) VALUES ('h', 50);
INSERT INTO user (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 标注

mysql> explain select * from user where id = 2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user_info | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

1. id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
2. select_type: SELECT 查询的类型.
3. table: 查询的是哪个表
4. partitions: 匹配的分区
5. type: join 类型
6. possible_keys: 此次查询中可能选用的索引
7. key: 此次查询中确切使用到的索引.
8. ref: 哪个字段或常数与 key 一起被使用
9. rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
10. filtered: 表示此查询条件所过滤的数据的百分比
11. extra: 额外的信息

各个主要字段的常用取值和含义

select_type

主要表示查询类型

1. SIMPLE, 表示此查询不包含 UNION 查询或子查询
2. PRIMARY, 表示此查询是最外层的查询
3. UNION, 表示此查询是 UNION 的第二或随后的查询
4. DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
5. UNION RESULT, UNION 的结果
6. SUBQUERY, 子查询中的第一个 SELECT
7. DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

通常都是SIMPLE查询，在没有子查询，和 不是UNION 查询的时候，都是SIMPLE 查询。

Table

这个字段主要是可以看查询的类型，从而判断是否高效，通过这里，可以看出事全表查询还是索引扫描查询。

1. system: 表中只有一条数据. 这个类型是特殊的 const 类型.
2. const: 针对主键或唯一索引的等值查询扫描,最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.

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 的, 则没有此属性.

Row

rows 也是一个重要的字段. MySQL 查询优化器根据统计信息, 估算 SQL 要查找到结果集需要扫描读取的数据行数.
这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好.

Extra

额外的信息会在extra 里面显示出来。

Using filesort 当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作,
不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.

Using index： “覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错

Using temporary： 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.