神奇的 SQL 之 MySQL 执行计划 → EXPLAIN，让我们了解 SQL 的执行过程！

开心一刻

某人养了一头猪，烦了想放生，可是猪认识回家的路，放生几次它都自己回来了
一日，这个人想了个狠办法，开车带着猪转了好多路进山区放生，放生后又各种打转
最后掏出电话给家里人打了个电话，问道：“猪回去了吗？”
家里人：“早回来了，你在哪了，怎么还没回来？”
他大怒道：“让它来接我，我特么迷路了！！！”
猪道：“还不如我了”

背景

某一天，楼主打完上班卡，坐在工位逛园子的时候，右下角的QQ闪了起来，而且还是个美女头像！我又惊又喜，脑中闪过我所认识的可能联系我的女性，得出个结论：她们这会不可能联系我呀，图像也没印象，到底是谁了？打开聊天窗口聊了起来
她：您好，我是公司客服某某某，请问 xxx后台 是您负责的吗？
我：您好，是我负责的，有什么问题吗？
她：我发现 xxx 页面点查询后，一直是 加载中... ，数据一直出不来，能帮忙看看吗？
我：是不是您的姿势不对？
她：我就 xxx，然后点查询
我：骚等下，我试试，确实有点慢，很长时间才能出来
她：是的，太慢了，出不来，都急死我了，能快点吗？
我：肯定能、必须能！您觉得什么速度让您觉得最舒服？
她：越快越好吧
我：呃...，是吗，我先看看是什么问题，处理好了告诉您，保证让您觉得舒服！
她：好的，谢谢！
公司没有专门的搜索服务，都是直接从MySQL查询，做简单的数据处理后返回给页面，慢的原因肯定就是SQL查询了。找到对应的查询SQL，就是两个表的联表查询，连接键也有索引，WHERE条件也能走索引，怎么会慢了？然后我用EXPLAIN看了下这条SQL的执行计划，找到了慢的原因，具体原因后面揭晓（谁让你不是猪脚！）

EXPLAIN 是什么

它是MySQL的一个命令，用来查看SQL的执行计划（SQL如何执行），根据其输出结果，我们能够知道以下信息：表的读取顺序，数据读取类型，哪些索引可以使用，哪些索引实际使用了，表之间的连接类型，每张表有多少行被优化器查询等信息，根据这些信息，我们可以找出SQL慢的原因，并做针对性的优化
MySQL 5.6之前的版本，EXPLAIN只能用于查看SELECT的执行计划，而从MySQL 5.6开始，可以查看SELECT、DELETE、INSERT、REPLACE和UPDATE的执行计划，这可不是我瞎掰，不信的可以去MySQL的官网查看：Understanding the Query Execution Plan
EXPLAIN使用方式非常简单，简单的你都不敢相信，就是在我们常写的SELECT、DELETE、INSERT、REPLACE和UPDATE语句之前加上EXPLAIN即可

EXPLAIN SELECT * FROM mysql.`user`;
EXPLAIN DELETE FROM t_user WHERE user_name = '123';

莫看 EXPLAIN 短，但它胖呀

环境和数据准备

MySQL版本是5.7.2，存储引擎是InnoDB

-- 查看 MySQL 版本
SELECT VERSION();

-- MySQL 提供什么存储引擎
SHOW ENGINES;

-- 查看默认存储引擎
SHOW VARIABLES LIKE '%storage_engine%';

准备两张表：用户表tbl_user和用户登录记录表tbl_user_login_log，并初始化部分部分数据

-- 表创建与数据初始化
DROP TABLE IF EXISTS tbl_user;
CREATE TABLE tbl_user (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(50) NOT NULL COMMENT '用户名',
  sex TINYINT(1) NOT NULL COMMENT '性别, 1:男，0:女',
  create_time datetime NOT NULL COMMENT '创建时间',
  update_time datetime NOT NULL COMMENT '更新时间',
    remark VARCHAR(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (id)
) COMMENT='用户表';

DROP TABLE IF EXISTS tbl_user_login_log;
CREATE TABLE tbl_user_login_log (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '自增主键',
  user_name VARCHAR(50) NOT NULL COMMENT '用户名',
  ip VARCHAR(15) NOT NULL COMMENT '登录IP',
  client TINYINT(1) NOT NULL COMMENT '登录端, 1:android, 2:ios, 3:PC, 4:H5',
  create_time datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (id)
) COMMENT='登录日志';
INSERT INTO tbl_user(user_name,sex,create_time,update_time,remark) VALUES
('何天香',1,NOW(), NOW(),'朗眉星目，一表人材'),
('薛沉香',0,NOW(), NOW(),'天星楼的总楼主薛摇红的女儿，也是天星楼的少总楼主，体态丰盈，乌发飘逸，指若春葱，袖臂如玉，风姿卓然，高贵典雅，人称“天星绝香”的武林第一大美女'),
('慕容兰娟',0,NOW(), NOW(),'武林东南西北四大世家之北世家慕容长明的独生女儿，生得玲珑剔透，粉雕玉琢，脾气却是刚烈无比，又喜着火红，所以人送绰号“火凤凰”，是除天星楼薛沉香之外的武林第二大美女'),
('苌婷',0,NOW(), NOW(),'当今皇上最宠爱的侄女，北王府的郡主，腰肢纤细，遍体罗绮，眉若墨画，唇点樱红；虽无沉香之雅重，兰娟之热烈，却别现出一种空灵'),
('柳含姻',0,NOW(), NOW(),'武林四绝之一的添愁仙子董婉婉的徒弟，体态窈窕，姿容秀丽，真个是秋水为神玉为骨，芙蓉如面柳如腰，眉若墨画，唇若点樱，不弱西子半分，更胜玉环一筹; 摇红楼、听雨轩，琵琶一曲值千金!'),
('李凝雪',0,NOW(), NOW(),'李相国的女儿，神采奕奕，英姿飒爽，爱憎分明'),
('周遗梦',0,NOW(), NOW(),'音神传人，湘妃竹琴的拥有者，云髻高盘，穿了一身黑色蝉翼纱衫，愈觉得冰肌玉骨，粉面樱唇，格外娇艳动人'),
('叶留痕',0,NOW(), NOW(),'圣域圣女，肤白如雪，白衣飘飘，宛如仙女一般，微笑中带着说不出的柔和之美'),
('郭疏影',0,NOW(), NOW(),'扬灰右使的徒弟，秀发细眉，玉肌丰滑，娇润脱俗'),
('钟钧天',0,NOW(), NOW(),'天界，玄天九部 - 钧天部的部主，超凡脱俗，仙气逼人'),
('王雁云',0,NOW(), NOW(),'尘缘山庄二小姐，***蛮任性'),
('许侍霜',0,NOW(), NOW(),'药王谷谷主女儿，医术高明'),
('冯黯凝',0,NOW(), NOW(),'桃花门门主，娇艳如火，千娇百媚');
INSERT INTO tbl_user_login_log(user_name, ip, client, create_time) VALUES
('薛沉香', '10.53.56.78',2, '2019-10-12 12:23:45'),
('苌婷', '10.53.56.78',2, '2019-10-12 22:23:45'),
('慕容兰娟', '10.53.56.12',1, '2018-08-12 22:23:45'),
('何天香', '10.53.56.12',1, '2019-10-19 10:23:45'),
('柳含姻', '198.11.132.198',2, '2018-05-12 22:23:45'),
('冯黯凝', '198.11.132.198',2, '2018-11-11 22:23:45'),
('周遗梦', '198.11.132.198',2, '2019-06-18 22:23:45'),
('郭疏影', '220.181.38.148',3, '2019-10-21 09:45:56'),
('薛沉香', '220.181.38.148',3, '2019-10-26 22:23:45'),
('苌婷', '104.69.160.60',4, '2019-10-12 10:23:45'),
('王雁云', '104.69.160.61',4, '2019-10-16 20:23:45'),
('李凝雪', '104.69.160.62',4, '2019-10-17 20:23:45'),
('许侍霜', '104.69.160.63',4, '2019-10-18 20:23:45'),
('叶留痕', '104.69.160.64',4, '2019-10-19 20:23:45'),
('王雁云', '104.69.160.65',4, '2019-10-20 20:23:45'),
('叶留痕', '104.69.160.66',4, '2019-10-21 20:23:45');

SELECT * FROM tbl_user;
SELECT * FROM tbl_user_login_log;

EXPLAIN 输出格式概览

楼主再不讲重点，估计有些看官老爷找他的2米长的大砍刀去了

这么滴，我们先来看看EXPLAIN输出结果的大概，是不是长得满脸麻子，让我们望而生畏?

白白净净的，挺好，关键长啊！ 官方解释如下

EXPLAIN 输出格式详解

EXPLAIN的输出字段虽然有点多，但常关注的就那么几个，但楼主秉着负责的态度，都给大家讲一下，需要重点关注的字段，楼主也会标明滴
EXPLAIN支持的SQL语句有好几种，但工作中用的最多的还是SELECT，所以楼主就偷个懒，以SELECT来讲解 EXPLAIN，有兴趣的小伙伴去试试其他的

id

输出的是整数，用来标识整个SQL的执行顺序
值如果相同，从上往下依次执行；值不同，值越大，执行优先级越高，越先被执行
如果行引用其他行的并集结果，则该值可以为NULL

不重要，有所了解就好（其实非常简单，看一遍基本就能记住了）

select_type

查询的类型，表明是普通查询、联合查询还是子查询；官方说明如下

我简单翻译一下（有能力的去读官网，毕竟那是原配，最具权威性）
SIMPLE：简单的SELECT查询，没有UNION或者子查询，包括单表查询或者多表JOIN查询

PRIMARY： 最外层的select查询，常见于子查询或UNION查询 ，最外层的查询被标识为PRIMARY

UNION：UNION操作的第二个或之后的SELECT，不依赖于外部查询的结果集（外部查询指的就是PRIMARY对应的SELECT）

DEPENDENT UNION：UNION操作的第二个或之后的SELECT，依赖于外部查询的结果集

UNION RESULT：UNION的结果（如果是UNION ALL则无此结果）

SUBQUERY：子查询中的第一个SELECT查询，不依赖于外部查询的结果集

DEPENDENT SUBQUERY：子查询中的第一个select查询，依赖于外部查询的结果集

DERIVED：派生表（临时表），常见于FROM子句中有子查询的情况

注意：MySQL5.7中对Derived table做了一个新特性，该特性允许将符合条件的Derived table中的子表与父查询的表合并进行直接JOIN，从而简化了执行计划，同时也提高了执行效率；默认情况下，MySQL5.7中这个特性是开启的，所以默认情况下，上面的SQL的执行计划应该是这样的

可通过SET SESSION optimizer_switch='derived_merge=on|off' 来开启或关闭当前SESSION的该特性
貌似扯的有点远了（楼主你是不是在随性发挥？），更多详情可以去查阅官网

MATERIALIZED：被物化的子查询，MySQL5.6引入的一种新的select_type，主要是优化FROM或IN子句中的子查询，更多详情请查看：Optimizing Subqueries with Materialization

UNCACHEABLE SUBQUERY：对于外层的主表，子查询不可被缓存，每次都需要计算

UNCACHEABLE UNION：类似于UNCACHEABLE SUBQUERY，只是出现在UNION操作中

SIMPLLE、PRIMARY、SUBQUERY、DERIVED 这 4 个在实际工作中碰到的会比较多，看得懂这 4 个就行了
至于其他的，碰到了再去查资料就好了（我也想全部记住，但用的少，太容易忘记了，我也很无奈呀）

table

显示了对应行正在访问哪个表(有别名就显示别名)，还会有<union2,3>、<subquery2>、<derived2>（这里的 2,3、2、2 指的是 id 列的值）类似的值，具体可以往上看，这里就不演示了（再演示就太长了，你们都看不下去了，那我不是白忙乎了 ?）

partitions

查询进行匹配的分区，对于非分区表，该值为NULL
大多数情况下用不到分区，所以这一列我们无需关注

type

关联类型或者访问类型，它指明了MySQL决定如何查找表中符合条件的行
是我们判断查询是否高效的重要依据（type 之于 EXPLAIN，就好比三围之于女人！）
完整介绍请看：explain-join-types
其值有多种，我们以性能好到性能差的顺序一个一个来看
system：该表只有一行（系统表），是const类型的特例
const：确定只有一行匹配的时候，mysql优化器会在查询前读取它并且只读取一次，速度非常快。用于primary key或unique索引中有常量值比较的情形

eq_ref：对于每个来自于前面的表的行，从该表最多只返回一条符合条件的记录。当连接使用的索引是PRIMARY KEY或UNIQUE NOT NULL时出现，非常高效

ref：索引访问，也称索引查找，它返回所有匹配某个单个值的行。此类型通常出现在多表的JOIN查询, 针对于非UNIQUE或非PRIMARY KEY, 或者是使用了最左前缀规则索引的查询，换句话说，如果JOIN基于关键字不能选择单个行的话，则使用ref

fulltext：当使用全文索引时会用到，这种索引一般用不到，会用专门的搜索服务(solr、elasticsearch等)来替代
ref_or_null：类似ref，但是添加了可以专门搜索NULL的行

这个是有前提条件的，前提为`weapon`列有索引，且`weapon`列存在`NULL`

index_merge：该访问类型使用了索引合并优化方法

这个同样也是有条件的， id列和weapon列都有单列索引。如果出现index_merge，并且这类SQL后期使用较频繁，可以考虑把单列索引换为组合索引，这样效率更高

unique_subquery：类似于两表连接中被驱动表的eq_ref访问方式，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键或者唯一索引进行等值匹配时，则会使用unique_subquery

index_subquery：index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通索引

range：使用索引来检索给定范围的行，当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，则会使用rang

前提是必须基于索引，也就是id上必须有索引

index：当我们可以使用索引覆盖，但需要扫描全部的索引记录时，则会使用index；进行统计时非常常见

ALL：我们熟悉的全表扫描

possible_keys

展示在这个SQL中，可能用到的索引有哪些，但不一定在查询时使用
若为空则表示没有可以使用的索引，此时可以通过检查WHERE语句看是否可以引用某些列或者新建索引来提高性能

key

展示这个SQL实际使用的索引，如果没有选择索引，则此列为null
要想强制MySQL使用或忽视 possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX

key_len

展示MySQL决定使用的索引长度（字节数）
如果key是NULL，则长度为NULL

ref

展示的是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列
它显示的列的名字（或const），此列多数时候为Null

rows

展示的是mysql解析器认为执行此SQL时需要扫描的行数，是预估值不是具体值，通常比实际值小

filtered

展示的是被条件过滤的行数所占需要读到的行（rows的值）的比例（省略了百分号，显示的是百分号前的值）
我们平时关注的不多，有兴趣的可以去仔细琢磨下，你会有更多发现

extra

表示不在其他列但也很重要的额外信息
取值有很多，我们挑一些比较常见的过一下
using index：表示SQL使用了使用覆盖索引，而不用回表去查询数据，性能非常不错

using where：表示存储引擎搜到记录后进行了后过滤(POST-FILTER)，如果查询未能使用索引，using where的作用只是提醒我们mysql要用where条件过滤结果集

using temporary：表示mysql需要使用临时表来存储结果集，常见于排序和分组查询

using filesort：表示mysql无法利用索引直接完成排序（排序的字段不是索引字段），此时会用到缓冲空间（内存或者磁盘）来进行排序；一般出现该值，则表示SQL要进行优化了，它对CPU的消耗是比较大的

impossible where：查询语句的WHERE子句永远为FALSE时将会提示该额外信息

当然还有其他的，不常见，等碰到了大家再去查吧

总结

背景疑问

还记得客服小姐姐的问题吗，她嫌我们太慢，具体原因后面再详细介绍
这里简单提一下：联表查询的连接键类型不一致，一个INT类型，一个VARCHAR类型，类型转换导致索引失效
这谁设计的呀？，坑死人呀！ 肯定不是我！

explain 精华

EXPLAIN的输出内容很多，我们没必要全部掌握，重点我已经帮大家划好
type，就像RMB一样重要
key，也像RMB一样重要
extra，还像RMB一样重要
说白了还是RMB最重要，不是，我的意思是type、key、extra都很重要，其他的用到了再去买吧

示例 SQL

-- select_type - SIMPLE
EXPLAIN SELECT * FROM tbl_user;
EXPLAIN SELECT tl.*, tu.sex,tu.remark FROM tbl_user_login_log tl LEFT JOIN tbl_user tu ON tl.user_name = tu.user_name; 

-- select_type - PRIMARY、DEPENDENT SUBQUERY
EXPLAIN SELECT * FROM tbl_user tu
WHERE EXISTS (
	SELECT * FROM tbl_user_login_log tull
	WHERE tull.user_name = tu.user_name
);

-- select_type - PRIMARY、UNION、UNION RESULT
EXPLAIN SELECT * FROM tbl_user WHERE user_name = '薛沉香'
UNION ALL
SELECT * FROM tbl_user WHERE user_name = '慕容兰娟';

-- select_type - SUBQUERY
EXPLAIN SELECT * FROM tbl_user_login_log WHERE user_name = (
	SELECT user_name FROM tbl_user WHERE id = 2
);

-- select_type - DEPENDENT SUBQUERY、DEPENDENT UNION
EXPLAIN SELECT * FROM tbl_user_login_log WHERE user_name IN(
	SELECT user_name FROM tbl_user WHERE id = 2
	UNION ALL
	SELECT user_name FROM tbl_user WHERE id = 3
);

-- 开启
SET SESSION optimizer_switch='derived_merge=on';
-- 关闭
SET SESSION optimizer_switch='derived_merge=off';
-- 会进行合并优化 select_type - DERIVED
EXPLAIN SELECT * FROM (
	SELECT * FROM tbl_user WHERE user_name = '薛沉香'
) t;

EXPLAIN SELECT * FROM tbl_user_login_log WHERE user_name IN (
	SELECT user_name FROM tbl_user WHERE id <= 3
);

-- select_type - MATERIALIZED
EXPLAIN SELECT * FROM tbl_user WHERE user_name IN (
	SELECT user_name FROM tbl_user_login_log
);

-- select_type - UNCACHEABLE SUBQUERY
EXPLAIN SELECT * FROM tbl_user WHERE user_name = ( 
	SELECT user_name FROM tbl_user_login_log WHERE id=@@sql_log_bin
);

-- select_type - UNCACHEABLE UNION
EXPLAIN SELECT * FROM tbl_user WHERE user_name IN ( 
	SELECT user_name FROM tbl_user_login_log WHERE id=@@sql_log_bin
	UNION ALL
	SELECT user_name FROM tbl_user WHERE id=@@sql_log_bin
);

-- type - system、const
SET SESSION optimizer_switch='derived_merge=off';
EXPLAIN SELECT * FROM (SELECT * FROM tbl_user WHERE id = 2) a;

-- type - eq_ref
ALTER TABLE tbl_user ADD UNIQUE (user_name);
EXPLAIN SELECT * FROM tbl_user_login_log tl 
LEFT JOIN tbl_user tu ON tl.user_name = tu.user_name;

-- type - ref
ALTER TABLE tbl_user_login_log ADD INDEX idx_user_name (user_name);
EXPLAIN SELECT * FROM tbl_user_login_log tl 
RIGHT JOIN tbl_user tu ON tl.user_name = tu.user_name;

-- type - ref_or_null
ALTER TABLE tbl_user ADD COLUMN weapon VARCHAR(50) COMMENT '武器';
UPDATE tbl_user SET weapon = '碧玉笛' WHERE user_name = '何天香';
UPDATE tbl_user SET weapon = '天问剑' WHERE user_name = '薛沉香';
UPDATE tbl_user SET weapon = '流星剑' WHERE user_name = '慕容兰娟';
UPDATE tbl_user SET weapon = '相思刀' WHERE user_name = '郭疏影';
UPDATE tbl_user SET weapon = '湘妃竹琴' WHERE user_name = '周遗梦';
UPDATE tbl_user SET weapon = '玉琵琶' WHERE user_name = '柳含姻';
ALTER TABLE tbl_user ADD INDEX idx_weapon (weapon);
EXPLAIN SELECT * FROM tbl_user WHERE weapon = '湘妃竹琴' OR weapon IS NULL;

-- type - index_merge
EXPLAIN SELECT * FROM tbl_user WHERE id = 1 OR weapon = '流星剑';

-- type - unique_subquery
EXPLAIN SELECT * FROM tbl_user t1 WHERE user_name IN (
	SELECT user_name FROM tbl_user t2 WHERE t1.weapon = t2.weapon
) OR weapon = '相思刀';

-- type - index_subquery
EXPLAIN SELECT * FROM tbl_user t1 WHERE weapon IN (
	SELECT weapon FROM tbl_user t2 WHERE t1.weapon = '玉琵琶'
) OR weapon = '相思刀';

-- type - RANGE
EXPLAIN SELECT * FROM tbl_user WHERE id < 6;

-- type - INDEX
EXPLAIN SELECT COUNT(*) FROM tbl_user;

-- type - ALL
EXPLAIN SELECT * FROM tbl_user;

-- extra - using index
EXPLAIN SELECT user_name FROM tbl_user;

-- extra - using where
EXPLAIN SELECT * FROM tbl_user WHERE sex = 0;

-- extra - using temporary
EXPLAIN SELECT sex, count(*) FROM tbl_user GROUP BY sex;

-- extra - using filesort
EXPLAIN SELECT * FROM tbl_user ORDER BY sex DESC;

-- extra - impossible where
EXPLAIN SELECT * FROM tbl_user WHERE id IS NULL;