explain详解

建表语句

CREATE TABLE `user` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

CREATE TABLE `game` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3

CREATE TABLE `game_user` (
  `id` int NOT NULL,
  `game_id` int NOT NULL,
  `user_id` int NOT NULL,
  `remark` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_game_user_id` (`game_id`,`user_id`),
  KEY `index_remark` (`remark`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

在执行sql的时候，前面加上explain，就能得到sql的执行情况，

mysql> explain select * from user;

简单查询的执行情况，如下图

可以发现，explain里有
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
这些字段

1.id

表示select 的序列号，有几个select语句就有几个id
id列的值越大，越先执行，如果id相同，从上到下依次执行，id为null时最后执行

2.select_type

表示对应的查询sql语句是什么类型的
（1）simple：简单查询，不包含子查询和union
（2）primary：复杂查询中最外层的 select
（3）subquery：子查询，where后面
（4）derived：from后面的查询。MySQL会将结果存放在一个临时表中，也称为派生表

# 关闭mysql的衍生表的合并优化
mysql> set session optimizer_switch='derived_merge=off';
mysql> explain select (select 1 from user where id = 1) from (select * from game where id = 1) a;

（5）union：在 union 后面的查询

mysql> explain select id,name from user union all select id,name from game;

3.table列

表示 explain 的每个查询语句正在查询的是哪个表。
如上图

4.type列

表示查询语句的类型，一般sql优化，就是优化这个字段，优化从优到差是

system > const > eq_ref > ref > range > index > ALL

一般查询至少要达到range级别，最好达到ref级别

• （1）NULL
  mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。
  如下查询索引最小值，在mysql优化阶段就能得到结果，不用再查询表和索引

mysql> explain select min(id) from user;

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

mysql> explain select * from (select * from user where id = 1) a;

• （3）eq_ref
  使用主键索引或者唯一索引连接使用 ，只会返回一条符合条件的记录。

mysql> explain select * from game_user left join game on game_user.game_id = game.id;

• （4）ref
  使用普通索引，可能会找到多个符合条件的行。

• （5）range
  使用一个索引来检索给定范围的行。如in(), between ,> ,<, >= 等

mysql> explain select * from user where id>0;

• （6）index
  直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的,，但是比ALL快，二级索引比聚簇索引小。
  查询存在二级索引的game表

mysql> explain select * from game;

• （7）ALL
  全表扫描你的聚簇索引的所有叶子节点。
  查询不存在二级索引的user表

mysql> explain select * from user;

5.possible_keys列

表示mysql认为这条查询语句可能使用的索引有哪些。

6.key列

表示mysql实际查询中，使用了哪些索引.
可能会出现possible_keys有值，但是key无值的情况，这种是由于mysql觉的不使用索引的时候查询反而快一点，自动进行了优化。
如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。

7.key_len列

表示mysql在索引里使用的字节数，可以算出具体使用了索引中的哪些列。

• key_len计算规则如下：

类型	长度
字符串	char(n)：3n varchar(n)：3n + 2 ，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型	tinyint：1字节 smallint：2字节 int：4字节 bigint：8字节
时间类型	date：3字节 timestamp：4字节 datetime：8字节

注意：如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
示例：

mysql> explain select * from game where name='game0';

如下，key_len值为33，是因为使用了索引idx_name，name是varchar(10)类型的，根据上面的规则，3*10+2=32，又由于name字段允许为null，所以32+1=32

8.ref列

表示在key列记录的索引中，所用到的列或常量，值为const（常量）或者字段名（例：EXPLAINTEST.game_user.game_id）

9.rows列

表示mysql估计要扫描的行数，不是实际行数。

10.Extra列

表示额外的信息。常见的重要值如下：

• （1）Using index：使用覆盖索引
  覆盖索引定义：如果select后面查询的字段都可以从key字段里的索引树中获取，extra里就会有using index；就是整个查询结果只通过一个索引树就能拿到结果，不需要回表，索引树包含要查询的全部字段

mysql> explain select name from game where name=1;

• （2）Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”，数据较小时从内存排序，否则需要在磁盘完成排序。需要使用索引优化。

user表name未使用索引

mysql> explain select * from user order by name;

game表name使用了索引

mysql> explain select * from game order by name;

• （3）Using where：使用 where 语句来处理结果，并且查询的列未被索引覆盖

mysql> explain select * from user where name = '1';

• （4）Using index condition：查询的列不完全被索引覆盖，where条件中是联合索引的一部分条件；

mysql> explain select * from game_user where game_id>1;

• （5）Using temporary：创建来了一张临时表来处理查询。需要使用索引优化。

user表没有name索引

mysql> explain select distinct name from user;

game表有name索引

mysql>  explain select distinct name from game;

• 6）Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

mysql> explain select min(id) from user;