MySql 性能优化神器 Explain

一、简介

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息， 以供开发人员针对性优化，explain是SQL优化中最常用的工具，搞定type和Extra，explain也就基本搞定了。EXPLAIN 命令用法十分简单，在 SELECT 语句前加上 Explain 就可以了， 例如：

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

EXPLAIN 命令的输出内容大致如下：

mysql> explain select * from user_info where id = 2

各列的含义如下：

  • id： SELECT 查询的标识符。每个 SELECT 都会自动分配一个唯一的标识符

  • select_type：SELECT 查询的类型

  • table:：查询的是哪个表

  • partitions：匹配的分区

  • type： join 类型

  • possible_keys：此次查询中可能选用的索引

  • key： 此次查询中确切使用到的索引

  • ref：哪个字段或常数与 key 一起被使用

  • rows： 显示此查询一共扫描了多少行. 这个是一个估计值

  • filtered：表示此查询条件所过滤的数据的百分比

  • extra：额外的信息

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

1、select_type

select_type 表示了查询的类型，最常见的查询类别应该是 SIMPLE 了，比如当我们的查询没有子查询，也没有 UNION 查询时，那么通常就是 SIMPLE 类型。它的常用取值有：

  • SIMPLE，表示此查询不包含 UNION 查询或子查询

  • PRIMARY，表示此查询是最外层的查询

  • UNION，表示此查询是 UNION 的第二或随后的查询

  • DEPENDENT UNION，UNION 中的第二个或后面的查询语句，取决于外面的查询

  • UNION RESULT，UNION 的结果

  • SUBQUERY，子查询中的第一个 SELECT

  • DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询，即子查询依赖于外层查询的结果

如果我们使用了 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));

2、type

type 字段比较重要，它提供了判断查询是否高效的重要依据依据。通过 type 字段，我们判断此次查询是 全表扫描 还是 索引扫描 等。type 常用的取值如下：

（1）system：表中只有一条数据. 这个类型是特殊的 const 类型

（2）const：针对主键或唯一索引的等值查询扫描，最多只返回一行数据。const 查询速度非常快。因为它仅仅读取一次即可。例如下面的这个查询，它使用了主键索引，因此 type 就是 const 类型的

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

（4）ref： 此类型通常出现在多表的 join 查询，针对于非唯一或非主键索引，或者是使用了 最左前缀 规则索引的查询。例如下面这个例子中， 就使用到了 ref 类型的查询

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

（6）index：表示全索引扫描(full index scan)。和 ALL 类型类似，只不过 ALL 类型是全表扫描。而 index 类型则仅仅扫描所有的索引， 而不扫描数据。index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到，而不需要扫描数据。当是这种情况时，Extra 字段 会显示 Using index.

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

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

type 类型的性能比较

通常来说，不同的 type 类型的性能关系如下：ALL < index < range ~ index_merge < ref < eq_ref < const < system
ALL 类型因为是全表扫描，因此在相同的查询条件下，它是速度最慢的。而 index 类型的查询虽然不是全表扫描，但是它扫描了所有的索引， 因此比 ALL 类型的稍快。后面的几种类型都是利用了索引来查询数据， 因此可以过滤部分或大部分数据，因此查询效率就比较高了。

3、possible_keys

possible_keys 表示 MySQL 在查询时， 能够使用到的索引。注意即使有些索引在 possible_keys 中出现，但是并不表示此索引会真正地被 MySQL 使用到。 MySQL 在查询时具体使用了哪些索引，由 key 字段决定

4、key

此字段是 MySQL 在当前查询时所真正使用到的索引

5、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 的，则没有此属性

我们来举两个简单的栗子:

 

上面的例子是从表 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 字段，因此效率不算高。接下来我们来看一下下一个例子：

这次的查询中，我们没有使用到范围查询， key_len的值为 161。为什么呢? 因为我们的查询条件 WHERE user_id = 1 AND product_name = 'p1' 中，仅仅使用到了联合索引中的前两个字段，因此 keyLen(user_id) + keyLen(product_name) = 9 + 50 * 3 + 2 = 161

6、rows

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

四、Extra分析

Extra字段比较重要，它提供了判断查询是否高效的重要依据。

数据准备：

复制代码
create table user (
    id int primary key,
    name varchar(20),
    sex varchar(5),
    index(name)
)engine=innodb;
复制代码

数据说明：
用户表：id主键索引，name普通索引（非唯一），sex无索引；四行记录：其中name普通索引存在重复记录list；

【Using where】

实验语句：

explain select * from user where sex='no';

结果说明：Extra为Using where说明，SQL使用了where条件过滤数据。

需要注意的是：
（1）返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需要进行优化；
（2）使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接类型）来综合判断；

本例虽然Extra字段说明使用了where条件过滤，但type属性是ALL，表示需要扫描全部数据，仍有优化空间。

常见的优化方法为，在where过滤属性上添加索引。

画外音：本例中，sex字段区分度不高，添加索引对性能提升有限。

【Using index】

实验语句：

explain select id,name from user where name='shenjian';

结果说明：

Extra为Using index说明，SQL所需要返回的所有列数据均在一棵索引树上，而无需访问实际的行记录。

这类SQL语句往往性能较好。

【Using index condition】

实验语句：

explain select id,name,sex from user where name='shenjian';

画外音：该SQL语句与上一个SQL语句不同的地方在于，被查询的列，多了一个sex字段。

结果说明：
Extra为Using index condition说明，确实命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。这类SQL语句性能也较高，但不如Using index。

【Using filesort】

实验语句：

explain select * from user order by sex;

结果说明：

Extra为Using filesort说明，得到所需结果集，需要对所有记录进行文件排序。

这类SQL语句性能极差，需要进行优化。典型的，在一个没有建立索引的列上进行了order by，就会触发filesort，常见的优化方案是，在order by的列上添加索引，避免每次查询都全量排序。

【Using temporary】

实验语句：

explain select * from user group by name order by sex;

结果说明：

Extra为Using temporary说明，需要建立临时表(temporary table)来暂存中间结果。

这类SQL语句性能较低，往往也需要进行优化。典型的，group by和order by同时存在，且作用于不同的字段时，就会建立临时表，以便计算出最终的结果集。

【Using join buffer (Block Nested Loop)】

实验语句：

explain select * from user where id in (select id from user where sex='no');

结果说明：

Extra为Using join buffer (Block Nested Loop)说明，需要进行嵌套循环计算。

画外音：内层和外层的type均为ALL，rows均为4，需要循环进行4*4次计算。

这类SQL语句性能往往也较低，需要进行优化。典型的，两个关联表join，关联字段均未建立索引，就会出现这种情况。常见的优化方案是，在关联字段上添加索引，避免每次嵌套循环计算。

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/qtiger/p/14377906.html