Mysql数据库的优化技术-explain使用分析

简介

---

在日常工作中，我们会开启慢查询，找到执行比较慢的 SQL语句，找到这些SQL语句后，我们可以使用 explain这个命令来查看这些 sql语句的执行计划，查看该 sql上有没有使用上了索引，有没有使用全表扫描，这些都可以通过 explain命令来查看。

使用规则 ：

explain select * from user_info where id < 30;

准备数据

---

为了下面的测试，需要建立两张表，并添加相应的数据。

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输出格式

---

EXPAIN 命令的输出格式的内容如下：

mysql> explain select * from user_info where id = 2\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec)

各列的含义如下： - id: 代表select语句的编号，如果是连接查询，表之间是平等关系，select编号都是1，从1开始，如果某select中有子查询，则编号递增。 - select_type ： SELECT查询类型 - table： 查询的是哪个表 - type: join类型 - possible_keys : 此次查询中 可能选用的索引 - key: 此次查询汇总确切使用到的索引 - ref: 哪个字段或常数与key一起被使用 - rows: 显示此查询一共扫描了多少行，这个是一个估值 - key_len: 使用索引的最大长度 - extra: 额外的信息

接下来我们看一下比较重要的几个字段：

1. select_type

select_type 表示了查询的类型，它的常用取值有：

SIMPLE 表示此查询不包含UNION查询或子查询PRIMARY 表示此查询时最外层的查询UNION 表示此查询时UNION的第二或随后的查询UNION RESULT: UNION 的结果SUBQUERY: 子查询中的第一个SELECTDEPENDENT SUBQUERY: 子查询中的第一个SELECT，取决于外面的查询，即子查询依赖于外层查询的结果

最常见的查询类别应该是SIMPLE 了，比如我们的查询没有子查询，也没有UNION查询时，那么通常就是SIMPLE类型，例如：

mysql> explain select * from user_info where id = 2\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec)

如果我们使用了 UNION 查询, 那么 EXPLAIN 输出 的结果类似如下:

mysql> EXPLAIN (SELECT * FROM user_info WHERE id IN (1, 2, 3)) UNION (SELECT * FROM user_info WHERE id IN (3, 4, 5))\G; *************************** 1. row *************************** id: 1 select_type: PRIMARY table: user_info type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 3 Extra: Using where *************************** 2. row *************************** id: 2 select_type: UNION table: user_info type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 3 Extra: Using where *************************** 3. row *************************** id: NULL select_type: UNION RESULT table: <union1,2> type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: NULL Extra: Using temporary 3 rows in set (0.00 sec)

2.table

表示查询设计的表或衍生表

实际的表名 如select * from t1表的别名 如 select * from t2 as tempderived 如from子查询null 直接计算的结构，不用走表

3.type

type列： 是指查询的方式，非常重要，是分析“查询数据过程”的 重要依据。 all:意味着从表的第一行，往后，逐行做全表 扫描，运气不好扫描到最后一行。

mysql> EXPLAIN SELECT age FROM user_info WHERE age = 20 \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 10 Extra: Using where 1 row in set (0.00 sec)

index: 表示全索引扫描(full index scan),和ALL类型类似，只不过ALL类型是全表扫描，而index类型则扫描所有的索引，而不是扫描数据。 index类型通常出现在：所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据，如果是这种情况，Extra会显示using index. 以下2中情况可能出现：

索引覆盖查询下，能利用上索引，但是又必须全索引扫描 mysql> EXPLAIN SELECT name FROM user_info \G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: index possible_keys: NULL key: name_index key_len: 152 ref: NULL rows: 10 Extra: Using index 1 row in set (0.01 sec)

上面的例子中，我们查询的name字段恰好是一个索引，因此我们直接从索引中获取数据就可以满足查询的需求了，而不需要查询表中的 数据，因此这样的情况下，type的值是index,并且Extra 的值是Using index。

利用索引来排序，但取出所有的节点 EXPLAIN SELECT name FROM user_info group by name \G; 分析: 没有加where条件，就得取所有索引节点，同时，又没有回行，只取索引节点，再排序，经过所有索引节点。 mysql> EXPLAIN SELECT name FROM user_info group by name\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: index possible_keys: name_index key: name_index key_len: 152 ref: NULL rows: 10 Extra: Using index 1 row in set (0.00 sec)

range: 表示使用索引范围查询，通过索引字段范围获取表中 部分数据记录，这个类型通常 出现在=,<>,>,>=,<,<=,BETWEEN ,IN()操作中 当type是range时，那么EXPLAIN输出的ref字段为NULL,并且key_len字段是此次查询中使用到的索引的最长的那个。

mysql> explain select * from user_info where id > 2\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: range possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: NULL rows: 8 Extra: Using where 1 row in set (0.00 sec)

ref: 此类型通常出现在多表的join查询，针对非唯一、非主键索引，或者是使用了最左前缀规则索引的查询。

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 type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Extra: NULL *************************** 2. row *************************** id: 1 select_type: SIMPLE table: order_info type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: const rows: 1 Extra: Using index 2 rows in set (0.00 sec)

eq_ref: 此类型通常出现在多表的join查询，表示对于前表的每一个结果，都只能匹配到后表的 一行结果，并且查询的比较操作通常是=,查询效率较高，例如：

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 type: index possible_keys: user_product_detail_index key: user_product_detail_index key_len: 254 ref: NULL rows: 9 Extra: Using where; Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: user_info type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: world.order_info.user_id rows: 1 Extra: NULL 2 rows in set (0.00 sec)

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

mysql> explain select * from user_info where id = 2\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_info type: const possible_keys: PRIMARY key: PRIMARY key_len: 8 ref: const rows: 1 Extra: NULL 1 row in set (0.00 sec)

4. possible_key

可能用到的索引， 注意： 系统估计可能用 的几个索引，但最终只能使用1个。

5. key

最终使用到的索引

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

eg:

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 type: range possible_keys: user_product_detail_index key: user_product_detail_index key_len: 9 ref: NULL rows: 4 Extra: Using where; Using index 1 row in set (0.00 sec)

不过此查询语句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 type: ref possible_keys: user_product_detail_index key: user_product_detail_index key_len: 161 ref: const,const rows: 2 Extra: Using where; Using index 1 row in set (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。

7. rows

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

8. 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 type: index possible_keys: NULL key: user_product_detail_index key_len: 254 ref: NULL rows: 9 Extra: Using index; Using filesort 1 row in set (0.00 sec)

但是上面的查询中根据 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 type: index possible_keys: NULL key: user_product_detail_index key_len: 254 ref: NULL rows: 9 Extra: Using index 1 row in set (0.00 sec) index: 是指用到了索引覆盖，效率非常高Using where :光靠索引定位不了，还得where判断一下Using temporary: 是指用上了临时表 如果group by 的列没有索引，必产生内部临时表如果order by 与 group by 为不同列时，或多表联查时order by ,group by包含的列不是 在同一张表里，将会产生临时表。distinct 与 order by 一起使用可能会产生临时表union 合并查询时会用到临时表

如果一开始在内存中产生的临时表变大，会自动转化为磁盘临时表，内存中临时表的最大值为tmp_table_size和max_heap_size中的最小值，

当服务器创建内部临时表(无论在内存还是在磁盘)，create_tmp_tables变量都会增加。如果创建了在磁盘上内部临时表(无论是初始创建还是由memory转化)，create_tmp_disk_tables变量都会增加。

一些情况下限制了内部临时表的使用，而使用磁盘临时表。

语句中存在BLOB或TEXT列。在GROUP BY 或DISTINCT子句中有大于512字节的string列在UNION或UNION ALL时，SELECT 语句里有大于512字节的string 列

·原文地址：·MySQL 性能优化神器 Explain 使用分析