Explain详解

简介

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 。在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息。

示例表

CREATE TABLE `user` (
  `id` SERIAL,
  `username` VARCHAR(64) NOT NULL COMMENT '用户名',
  `nickname` VARCHAR(64) NOT NULL COMMENT '昵称',
  `age` INT(11) NULL DEFAULT NULL COMMENT '年龄',
  `phone` VARCHAR(32) NULL DEFAULT NULL COMMENT '手机号',
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  UNIQUE KEY `idx_nickname_phone`(`nickname`, `phone`)
)ENGINE=InnoDB COMMENT='用户表';
INSERT INTO `user`(`id`, `username`, `nickname`, `age`, `phone`) VALUES (1, 'tacy', 'tacy', 18, '13211111111');
INSERT INTO `user`(`id`, `username`, `nickname`, `age`, `phone`) VALUES (2, 'tacy1126', 'tacy', 18, '13222222222');

CREATE TABLE `org` (
  `id` SERIAL,
  `name` VARCHAR(64) NOT NULL COMMENT '部门名称'
   PRIMARY KEY (`id`),
   KEY `idx_name` (`name`)
)ENGINE=InnoDB COMMENT='部门表';
INSERT INTO `org`(`id`, `name`) VALUES (1, '技术部');

CREATE TABLE `user_org` (
  `id` SERIAL,
  `user_id` INT(10) NOT NULL COMMENT '用户ID',
  `org_id` INT(10) NOT NULL COMMENT '部门ID',
   PRIMARY KEY (`id`),
   KEY `idx_user_org_id` (`user_id`, `org_id`)
)ENGINE=InnoDB COMMENT='用户部门关系表';
INSERT INTO `user_org`(`id`, `user_id`, `org_id`) VALUES (1, 1, 1);

type列举例

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL

• NULL
  mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。

EXPLAIN SELECT min(`id`) FROM `user`;

• const,system
  mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是 const的特例，表里只有一条元组匹配时为system

EXPLAIN SELECT * FROM `user` WHERE `nickname` = 'tacy' AND phone = '13222222222';

• eq_ref
  primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

EXPLAIN SELECT * FROM `user_org` LEFT JOIN `user` ON `user_org`.`user_id` = `user`.id;

• ref
  相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会 找到多个符合条件的行

EXPLAIN SELECT * FROM `user` WHERE username = 'tacy';

• range
  范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

EXPLAIN SELECT * FROM `user` WHERE id > 1;

• index
  扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接 对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这 种通常比ALL快一些。

EXPLAIN SELECT * FROM org;

• ALL
  即全表扫描，扫描你的聚簇索引的所有叶子节点。

EXPLAIN SELECT * FROM `user`;

脑图