mysql explain详解

by 凯文

简介

MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化.

准备

user_info

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);

order_info

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 select * from user_info where id = 2

上述sql执行结果

各列的含义如下

  • id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符.
  • select_type: SELECT 查询的类型.
  • table: 查询的是哪个表
  • type: join 类型
  • possible_keys: 此次查询中可能选用的索引
  • key: 此次查询中确切使用到的索引.
  • ref: 哪个字段或常数与 key 一起被使用
  • rows: 显示此查询一共扫描了多少行. 这个是一个估计值.
  • extra: 额外的信息

EXPLAIN输出详解

id

每个select语句会分配一个唯一的标识符，如以下两个sql虽然结果相同，但是两个表有不同的id。

  • 连表查询
explain select * from user_info u,order_info o where o.id = 2 and o.id = u.id

  • 子表查询
explain select * from user_info u where exists(
select 1 from order_info o where u.id = o.id and o.id = 2
)

table

查询的表，如果该sql查询了多个表，会有多条记录。示例同上

select_type

select_type 表示了查询的类型, 它的常用取值有:

  • SIMPLE, 表示此查询不包含 UNION 查询或子查询
  • PRIMARY, 当存在子查询或UNION查询时，表示此查询是最外层的查询
  • UNION, 表示此查询是 UNION 的第二或随后的查询，具体Union查询的概念可参考mysql 关键字
  • DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
explain select * from user_info  u1 where 
exists (select u.name from  user_info u where u.name = u1.name union select o.product_name from order_info o where u1.name = o.product_name)
  • UNION RESULT, UNION 的结果
  • SUBQUERY, 子查询中的第一个 SELECT
  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.如 DEPENDENT UNION中的 表u的查询即为DEPENDENT SUBQUERY

type

type 字段比较重要, 它提供了判断查询是否高效的重要依据. 通过 type 字段, 我们判断此次查询是 全表扫描 还是 索引扫描 等.

type 常用的取值

  • system: 表中只有一条数据. 这个类型是特殊的 const 类型.不需要进行磁盘io
explain select * from (select * from t_student_copy where id = 1) tmp;
  • const: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
    例如下面的这个查询, 它使用了主键索引, 因此 type 就是 const 类型的.
explain select * from user_info u where id  =1
  • eq_ref: 此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高. 例如:
EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.id = order_info.user_id;

  • ref: 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.此类型通常出现在多表的 join 查询
EXPLAIN SELECT * FROM user_info, order_info WHERE user_info.name = order_info.product_name;
EXPLAIN SELECT * FROM user_info  WHERE user_info.name = "a";
  • range: 表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 <>, >, >=, <, <=, BETWEEN, IN() 操作中.
    当 type 是 range 时, 那么 EXPLAIN 输出的 ref 字段为 NULL, 并且 key_len 字段是此次查询中使用到的索引的最长的那个.
explain select * from user_info force index(name_index) where  name  in ('a' , 'b')
  • index: 表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.
    index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
-- index
explain select name from user_info
-- all
explain select name,age from user_info
  • ALL: 表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
    下面是一个全表扫描的例子, 可以看到, 在全表扫描时, possible_keys 和 key 字段都是 NULL, 表示没有使用到索引, 并且 rows 十分巨大, 因此整个查询效率是十分低下的.

type性能总结：ALL < index < range ~ index_merge < ref < eq_ref < const < system

possible_keys

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

key

此字段是 MySQL 在当前查询时所真正使用到的索引.如果想使用possible_keys中的索引，可通过force index()强制使用某索引

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

示例1

EXPLAIN SELECT * FROM order_info WHERE user_id < 3 AND product_name = 'p1' AND productor = 'WHH'

上面的例子是从表 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 字段, 因此效率不算高.

示例2

EXPLAIN SELECT * FROM order_info WHERE user_id = 1 AND product_name = 'p1'


这次的查询中, 我们没有使用到范围查询, 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 资源消耗大.

EXPLAIN SELECT * FROM order_info ORDER BY product_name

但是上面的查询中根据 product_name 来排序, 因此不能使用索引进行优化, 进而会产生 Using filesort.
如果我们将排序依据改为 ORDER BY user_id, product_name, 那么就不会出现 Using filesort 了

Using index

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

Using temporary

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

Block Nested Loop

在join查询语句中，如果Join过程中可以用到被驱动表上的索引就会使用Index Nested-Loop算法(NLJ)，如果不能使用被驱动表中的索引，即用Block Nested-Loop Join算法(BNL)，建议优化为Index Nested-Loop算法

  • Index Nested-Loop Join（NLJ）算法是Join算法中最基本的算法之一。在NLJ算法中，MySQL首先选择一个表（通常是小型表）作为驱动表，并迭代该表中的每一行。然后，MySQL在第二个表中搜索匹配条件的行，这个搜索过程通常使用索引来完成。一旦找到匹配的行，MySQL将这些行组合在一起，并将它们作为结果集返回。

  • Block Nested Loop Join（BNL）算法与NLJ算法不同的是，BNL算法使用一个类似于缓存的机制，将表数据分成多个块，然后逐个处理这些块，以减少内存和CPU的消耗。

    • 把表t1的数据读入线程内存join_buffer中，由于我们这个语句中写的是select *，因此是把整个表t1放入了内存；扫描表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
    • 在这个过程中，对表t1和t2都做了一次全表扫描，因此总的扫描行数是1100。由于join_buffer是以无序数组的方式组织的，因此对表t2中的每一行，都要做100次判断，总共需要在内存中做的判断次数是：100*1000=10万次。
    • 虽然Block Nested-Loop Join算法是全表扫描。但是是在内存中进行的判断操作，速度上会快很多。但是性能仍然不如NLJ。
    • join_buffer的大小是由参数join_buffer_size设定的，默认值是256k。如果放不下表t1的所有数据话，策略很简单，就是分段放。

BNL算法还有个较严重的问题是对Buffer Pool影响较大
InnoDb 对BufferPool的LRU算法做了优化，即：第一次从磁盘读入内存的数据页，会先放在old区域，如果1秒之后这个数据页不再被访问了，就不会被移动到LRU链表表头，这样对Buffer Pool命中率影响不大。
但是如果一个使用BNL算法的Join语句，多次扫描一个冷表，而且这个语句执行时间超过1s,就会在再次扫描时，把冷表的数据页移到LRU链表表头

如果不想在被驱动表上建索引，可考虑通过临时表的方式解决join问题，将被驱动表中符合条件的数据保存至临时表中，并对驱动字段添加索引，然后通过join该表查询数据

Using MRR

表示用上了Multi-Range Read(MRR)优化，MRR优化指的是在索引a上进行范围查询时，可以得到足够多的主键id，如果每得到一个id回表随机查询一次，效率较低；通过在read_rnd_buffer中对这些id进行排序，再去主键索引回表，由于按照主键递增顺序查询，对磁盘的读比较接入顺序读，所以可以提升读性能。

MRR是对普通的范围查询回表的优化，而MySQL在5.6版本后引入了Batched Key Acess(BKA)算法了。这个BKA算法，其实就是对NLJ算法的优化，BKA算法正是基于MRR。

NLJ算法执行的逻辑是：从驱动表t1，一行行地取出a的值，再到被驱动表t2去做join。也就是说，对于表t2来说，每次都是匹配一个值。这时，MRR的优势就用不上了。

我们可以从表t1里一次性地多拿些行出来，，先放到一个临时内存，一起传给表t2。这个临时内存不是别人，就是join_buffer。

我们知道join_buffer 在BNL算法里的作用，是暂存驱动表的数据。但是在NLJ算法里并没有用。那么，我们刚好就可以复用join_buffer到BKA算法中。

ref

在key列记录的索引中，表查找值所用到的列或常量，常见的有：const，func，null，字段名

explain select o.* from order_info o ,user_info u  where   o.id=u.id and u.name = 'aa'