神奇的 SQL 之 MySQL 性能分析神器 → EXPLAIN，SQL 起飞的基石！，

神奇的 SQL 之 MySQL 性能分析神器 → EXPLAIN，SQL 起飞的基石！，

前言

　　开心一刻

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

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 短，但它胖呀

虽然有点婴儿肥，但也掩不住我逼人的帅气！

　　虽然 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; View Code

EXPLAIN 输出格式概览

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

官方解释如下

SELECT ，所以楼主就偷个懒，以 SELECT 来讲解 EXPLAIN，有兴趣的老爷去试试其他的

　　id

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

官方说明如下

外部查询指的就是 PRIMARY 对应的 SELECT）

　　　　　　注意：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

<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 索引中有常亮值比较的情形

weapon 列有索引，且 weapon 列存在  NULL 

　　　　index_merge

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

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

　　　　unique_subquery

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

id 上必须有索引

　　　　index

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