世间上的相遇
都是久别重逢
数据库是程序员必备的一项基本技能，基本每次面试必问。对于刚出校门的程序员，你只要学会如何使用就行了，但越往后工作越发现，仅仅会写sql语句是万万不行的。写出的sql，如果性能不好，达不到要求，可能会阻塞整个系统，那对于整个系统来讲是致命的。
所以如何判断你的sql写的好不好呢？毕竟只有先知道sql写的好不好，才能再去考虑如何优化的问题。
MySQL官方就给我们提供了很多sql分析的工具，这里我们主要说一下EXPLAIN。
以下是基于MySQL5.7.28版本进行分析的，不同版本之间略有差异。
1.1 概念
使用EXPLAIN关键字可以模拟优化器执行sql语句，从而知道MySQL是如何处理你的语句，分析你的查询语句或者表结构的性能瓶颈。
用法：EXPLAIN sql语句
EXPLAIN执行后返回的信息如下：
各个字段的大致含义如下：
- id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符。
- select_type: SELECT 查询的类型。
- table: 查询的是哪个表。
- partitions: 匹配的分区。
- type: join 类型。
- possible_keys: 此次查询中可能选用的索引。
- key: 此次查询中确切使用到的索引。
- key_len: 查询优化器使用了索引的字节数。
- ref: 哪个字段或常数与 key 一起被使用。
- rows: 显示此查询一共扫描了多少行. 这个是一个估计值。
- filtered: 表示此查询条件所过滤的数据的百分比。
- extra: 额外的信息。
1.2 准备工作
新建一个数据库test，执行下面的sql语句
代码语言：javascript复制CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT('t1_',FLOOR(1 RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT('t2_',FLOOR(1 RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT('t3_',FLOOR(1 RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT('t4_',FLOOR(1 RAND()*1000)));下面一一解释各列的含义。
1.3 id
select查询的序列号，包含一组数字，表示查询中执行select子句的顺序或操作表的顺序。大致分为下面几种情况
（1）id相同，执行顺序由上至下
上面的查询语句，三个id都为1，具有相同的优先级，执行顺序由上而下，具体执行顺序由优化器决定，这里执行顺序为t1，t2，t3。
（2）id不同，数字越大优先级越高
如果sql中存在子查询，那么id的序号会递增，id越大越先被执行。如上图，执行顺序是t3、t1、t2，也就是说，最里面的子查询最先执行，由里往外执行。
在我测试的时候，无意中发现，下面的语句，一个使用的是IN关键字，一个使用的=运算符，但使用EXPLAIN执行后，结果天壤之别。
这说明使用IN嵌套子查询，它是按顺序来执行的，也就是说每执行一次最外层子查询，里面的子查询都会被重复执行，这好像和我的理解差很多啊（我一直以为是先执行最里面的子查询，再执行外面的）。
具体可以看看这篇文章，我觉得讲的大概算是明白了。https://segmentfault.com/a/1190000005742843。这里就不再继续赘述了。
千万别用IN，使用JOIN或者EXISTS代替它
（3）id存在相同的和不同的
在上面语句的基础上，增加一个IN的子查询，执行结果如下
执行顺序为t3、t1、t2、t4。值越大的越先执行，相同值的从上往下执行。
1.4 select_type
select_type表示查询的类型，主要是为了区分普通查询、子查询、联合查询等复杂查询。分为以下几种类型：
（1）SIMPLE
简单的select查询，查询中不包含子查询或者UNION。
（2）PRIMARY
查询中若包含任何复杂的子查询，那么最外层的查询被标记为PRIMARY。
（3）DERIVED
在from子句中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。
（4）SUBQUERY
在select或where子句中包含了子查询，该子查询被标记为SUBQUERY。
（5）UNION
若第二个select查询语句出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
（6）UNION RESULT
从UNION表获取结果的SELECT。
上面的前三种在上一小节已经出现过了，看看后面这三种
可以看到id列出现了一个NULL，这是上面没讲到的。一般来说，特殊情况下，如果某行语句引用了其他多行结果集的并集，则该值可以为 NULL。
1.5 table
这个没啥好讲的，表示这个查询是基于哪种表的。并不一定是真实存在的表，比如上面出现的DERIVED和<union1,2>，一般来说会出现下面的取值：
（1）<union a,b>：输出结果中编号为 a 的行与编号为 b 的行的结果集的并集。
（2）<derived a>：输出结果中编号为 a 的行的结果集，derived 表示这是一个派生结果集，如 FROM 子句中的查询。
（3）<subquery a>：输出结果中编号为 a 的行的结果集，subquery 表示这是一个物化子查询。
1.6 partitions
查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况。
根据官方文档，在创建表的时候，指定不同分区存放的id值范围不同。
插入测试数据，让id值分布在四个分区内。
执行查询输出结果。
1.7 type
type是查询的访问类型，是较为重要的一个指标，性能从最好到最坏依次是 system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
一般来说，得保证查询至少到达range级别，最好能达到ref。
（1）system
当表仅存在一行记录时（系统表），数据量很少，速度很快，这是一种很特殊的情况，不常见。
（2）const
当你的查询条件是一个主键或者唯一索引（UNION INDEX）并且值是常量的时候，查询速度非常快，因为只需要读一次表。
给t1表的content列增加一个唯一索引
（3）eq_ref
除了system和const，性能最好的就是eq_ref了。唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
（4）ref
非唯一性索引扫描，返回匹配某个单独值的所有行。区别于eq_ref，ref表示使用除PRIMARY KEY 和UNIQUE index 之外的索引，即非唯一索引，查询的结果可能有多个。可以使用 = 运算符或者<=> 运算符。
在t2表的content列加上普通索引
进行查询
（5）fulltext
查询时使用 fulltext 索引。
（6）ref_or_null
对于某个字段既需要关联条件，也需要null 值的情况下。查询优化器会选择用ref_or_null 连接查询。
（7）index_merge
在查询过程中需要多个索引组合使用，通常出现在有or 关键字的sql 中。
（8）unique_subquery
该联接类型类似于index_subquery。子查询中的唯一索引。在某些in子查询里，用于替换eq_ref，比如下面的查询语句
代码语言：javascript复制value IN (SELECT primary_key FROM single_table WHERE some_expr)（9）index_subquery
利用索引来关联子查询，不再全表扫描。用于非唯一索引，子查询可以返回重复值。类似于unique_subquery，但用于非唯一索引
代码语言：javascript复制value IN (SELECT key_column FROM single_table WHERE some_expr)（10）range
只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where 语句中出现了between、<、>、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
举个例子，t3表中id字段为主键，有PRIMARY索引，content字段没有建立索引，查询时使用id作为条件，结果如下
使用content作为条件，结果如下
所以，只有对设置了索引的字段，做范围检索 type 才是 range。
（11）index
sql语句使用了索引，但没有通过索引进行过滤，一般是使用了覆盖索引或者利用索引进行了排序分组。
index和ALL都是读全表，区别在于index是遍历索引树读取，ALL是从硬盘读取。index通常比ALL更快，因为索引文件通常比数据文件小。
举个例子，查询t3表主键id，结果如下
（12）ALL
全表扫描，性能最差。
1.8 possible_keys
查询时可能使用的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。注意是可能，实际查询时不一定会用到。
1.9 key
查询时实际使用的索引，没有使用索引则为NULL。查询时若使用了覆盖索引，则该索引只出现在key字段中。
举个例子，trb1表中有一个组合索引(age, name)，那么当你的查询列和索引的个数和顺序一致时，查询结果如下：
1.10 key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。
key_len显示的值是索引字段可能的最大长度，并非实际使用长度，即key_len是根据表定义计算得到，不是通过表内检索。
key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。
注意：key_len只计算where条件中用到的索引长度，而排序和分组即便是用到了索引，也不会计算到key_len中。
举个例子，有表trb1，存在以下字段，以及一个组合索引idx_age_name
下面查询语句的执行结果
key_len的值为153、158、null。如何计算：
①先看索引上字段的类型 长度。比如int=4 ; varchar(50) = 50 ; char(50) = 50。
②如果是varchar 或者char 这种字符串字段，视字符集要乘不同的值，比如utf-8 要乘3,GBK 要乘2。
③varchar 这种动态字符串要加2 个字节。
④允许为空的字段要加1 个字节。
第一条：key_len = name的字节长度 = 50 * 3 2 1 = 153
第二条：key_len = age 的字节长度 name 的字节长度= 4 1 ( 50*3 2 1)= 5 153 = 158。（使用的索引更充分，查询结果更精确，但消耗更大）
第三条：索引失效了。
1.11 ref
显示索引的哪一列被使用了，常见的取值有：const， func，null，字段名。
- 当使用常量等值查询，显示const。
- 当关联查询时，会显示相应关联表的关联字段。
- 如果查询条件使用了表达式、函数，或者条件列发生内部隐式转换，可能显示为func。
- 其他情况null。
举个例子，t3表的content字段有普通索引，下面的查询语句结果如下
1.12 rows
rows 列表示 MySQL 认为它执行查询时可能需要读取的行数，一般情况下这个值越小越好！
1.13 filtered
filtered 是一个百分比的值，表示符合条件的记录数的百分比。简单点说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。
在MySQL.5.7版本以前想要显示filtered需要使用explain extended命令。MySQL.5.7后，默认explain直接显示partitions和filtered的信息。
1.14 Extra
其他额外的信息。
（1）Using filesort
说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
举个例子，trb1表建立一个组合索引
下面的查询出现filesort ：
按照组合索引的顺序，是name、age、purchased，而上面的查询语句，没有使用中间的age，所以在order by的时候索引失效了。通常这种情况是需要进行优化的。
修改一下上面的sql语句，让索引不失效。
（2）Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by。
这条sql语句用了临时表，又用了文件排序，在数据量非常大的时候效率是很低的，需要进行优化。
所以在使用group by 和 order by的时候，列的数量和顺序尽量和索引的一样。
（3）Using index
Using index 表示相应的select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，可以提高效率。
如果同时出现using where，表明索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
还是使用上面的trb1表举例子
只出现了Using index，说明索引用来读取数据而不是执行查找。
出现了Using where，说明索引被用来执行查找。
（4）Using where
表示查询时有索引被用来进行where过滤。
（5）Using join buffer
查询时使用了连接缓存。
（6）impossible where
查询语句的where条件总是为false，举个例子
一般情况下不会出现这种。
关于Extra字段，有很多取值，这里就不一一列举了，具体可以看官方文档。
参考资料：https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
