## 介绍 MySQL 提供了一个**EXPALIN**命令，可以用于对**SELECT 语句**的执行计划进行分析，并详细的输出分析结果，供开发人员进行针对性的优化。 我们想要查询一条sql有没有用上索引，有没有全表查询，这些都可以通过explain这个命令来查看。 通过explain命令，我们可以深入了解到MySQL的基于开销的优化器，还可以获得很多被优化器考虑到的访问策略的细节以及运行sql语句时哪种策略预计会被优化器采用。 explain的使用十分简单，通过在查询语句前面加一个explain关键字即可。  ## 参数说明 explain 命令一共返回12列信息，分别是： ~~~text id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra ~~~ 以下是本文的案例表： ~~~mysql # 用户表 CREATE TABLE user( id int primary key auto_increment, nickname varchar(100), name varchar(100), age int, sex tinyint(1), dep int, addr int, description varchar(100) )ENGINE INNODB; # 部门表 CREATE TABLE dep( id int primary key auto_increment, name varchar(100) )ENGINE INNODB; # 地址表 CREATE TABLE addr( id int primary key auto_increment, address varchar(100) )ENGINE INNODB; # 普通索引 ALTER TABLE user ADD INDEX idx_dep(dep); # 唯一索引 ALTER TABLE user ADD unique INDEX uniq_name(name); # 复合索引 ALTER TABLE user ADD INDEX c_nickname_age_sex(nickname,age,sex); # 全文索引 ALTER TABLE user ADD FULLTEXT f_description(description); # 普通索引 ALTER TABLE dep ADD INDEX idx_name(name); ~~~ ## id 列 * 每个select语句都会自动分配的一个唯一标识符 * 表示查询中，操作表的顺序，有三种情况 * id相同，执行顺序从上到下 * id不同，如果是子查询，id号会自增，id越大，**优先级越高** * id相同的不相同的同时存在 * id列为null表示为结果集，不需要使用这个语句来查询 ## select\_type 列（很重要） 查询类型，主要用于区别**普通查询、联合查询（union、union all）、子查询等复杂查询。** **simple** 表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个。  **primary** 一个需要使用union的操作或者含有子查询的select，位于最外层的单位查询的select\_type即为primary。且只有一个  **subquery** 除了from子句中包含的子查询外，其它地方出现的子查询都可能时subquery  **dependent subquery** 子查询的结果受到外层的影响：  **union、union result** union 连接的多表查询，第一个查询是primary，后面的是union, 结果集是 union result  **dependent union** 和union一样，出现在union或者union all中，但是这个查询要受到外部查询的影响  **derived** 在from子句后面的子查询，也叫派生表,**注意，在MySql5.6 对于此查询没有优化，所以查询类型是derived.在mysql 5.7 使用了 Merge Derived table 优化，查询类型变为SIMPLE。通过控制参数: optimizer\_switch='derived=on|off' 决定开始还是优化。默认开启。**  mysql 5.7  mysql 5.6 ## table 列 * 显示的查询表名，如果查询使用了别名，那么这里显示的就是别名 * 如果不涉及对数据表的操作，那么这里就是null * 如果显示为尖括号括起来的就表示这是一个临时表，N就是执行计划的id，表示结果来自这个查询 * 如果显示为尖括号括起来的也表示一个临时表，表示来自union查询id为n、m的结果集 ## partitions 列 分区信息 ## type 列 重要 * 依次从好到差： ~~~mysql system、const、eq_ref、ref、full_text、ref_or_null、unique_subquery、 index_subquery、range、index_merge、index、all ~~~ 除了 All 以外，其它的类型都可以用到索引，除了index\_merge可以使用多个索引之外，其它的类型最多只能使用到一个索引。 注意！！最少也应该要使用索引到range级别！ **system** 表中只有一行数据或者是空表。  **const** 使用唯一**索引或者主键**，返回记录一定是一条的等值where条件时，通常type是const。  **eq\_ref** 连接字段为主键或者唯一索引，此类型通常出现于多表的join查询，表示对于前表的每一个结果，都对应后表的唯一一条结果。并且查询的比较是=操作，查询效率比较高。  上面未使用覆盖索引，下面使用覆盖索引，减少回表 **ref** ref有三种情况： 1. 非主键或者唯一键的等值查询 2. join连接字段是非主键或者唯一键 3. 最左前缀索引匹配  三个查询分别对应上面三种情况 **fulltext** 全文检索索引。  并不是优先使用全文索引 **ref\_or*\_*null** 和ref类似，增加了null值判断 **unique\_subquery、 index\_subquery** 都是子查询，前者返回唯一值，后者返回可能有重复。 **range 重要** 索引范围扫描，常用于 ><,is null,between,in,like等  **index\_merge(索引合并)** 表示查询使用了两个或者以上的索引数量，常见于and或者or查询匹配上了多个不同索引的字段 **index(辅助索引)** 减少回表次数,因为要查询的索引都在一颗索引树上  **all 全表扫描** ## possible\_keys 列 此次查询中，可能选用的索引 ## key 列 查询实际使用的索引，select\_type为index\_merge时，key列可能有多个索引，其它时候这里只会有一个 ## key\_len 列 * 用于处理查询的索引长度，如果是单列索引，那么整个索引长度都会计算进去，如果是多列索引，那么查询不一定能使用到所有的列，具体使用了多少个列的索引，这里就会计算进去，没有使用到的索引，这里不会计算进去。 * 留意一下这个长度，计算一下就知道这个索引使用了多少列 * 另外，key\_len 只计算 where 条件使用到索引长度，而排序和分组就算用到了索引也不会计算key\_len ## ref * 如果是使用的常数等值查询，这里会显示const * 如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段 * 如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能会显示func ## rows 执行计划估算的扫描行数，不是精确值（innodb不是精确值，myisam是精确值，主要是因为innodb使用了mvcc）。 ## extra 这个列包含很多不适合在其它列显示的重要信息，有很多种，常用的有： * **using temporary** * 表示使用了临时表存储中间结果 * MySQL在对**order by和group by**时使用临时表 * 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量：used\_tmp\_table、used\_tmp\_disk\_table才可以看出来  * no table used 不带from字句的查询或者from dual查询（explain select 1;） **使用 not in() 形式的子查询查询或者not exists运算符的连接查询，这种叫做反链接** **即：**一般连接先查询内表再查询外表，反链接就是先查询外表再查询内表 * **using filesort** * 排序时无法使用到所以就会出现这个，常见于order by和group by * 说明MySQL会使用一个外部的索引进行排序，而不是按照索引顺序进行读取 * MySQL中无法利用索引完成的排序就叫“文件排序”  * **using index 查询时候不需要回表** * 表示相应的select查询中使用到了**覆盖索引(Covering index)**，避免访问表的数据行 * 如果同时出现了using where，说明索引被用来执行查询键值 * 如果没有using where，表示读取数据而不是执行查找操作  * **using where** * 表示存储引擎返回的记录并不都是符合条件的，需要在server层进行筛选过滤，性能很低  * **using index condition** * 索引下推，不需要再在server层进行过滤,5.6.x开始支持 * **first match** * 5.6.x 开始出现的优化子查询的新特性之一，常见于where字句含有in()类型的子查询，如果内表数据量过大，可能出现 * **loosescan** * 5.6.x 开始出现的优化子查询的新特性之一，常见于where字句含有in()类型的子查询，如果内表返回有重复值，可能出现 ## filtered 列 5.7之后的版本默认就有这个字段，不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数。 转载：https://zhuanlan.zhihu.com/p/149807046