MySql EXPLAIN用法和结果学习总结

1.EXPLAIN的作用：
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
通过EXPLAIN 我们可以得到：表的读取顺序（id），数据读取操作的操作类型（select_type）,哪些索引可以被使用（possible_keys），哪些索引实际被使用（key）,表之间的引用（type）,每张表有多少行被优化器查询（rows）
EXPLAIN使用方式，如图所示

2.执行结果分析

2.1 id
id指的是查询中执行select子句或操作表的顺序，当id一致时，执行顺序由上到下，id不一致则是由大到小，即id越大优先级越高

2.2 select_type
常用的值如下所示：

simple : 指的是简单的select查询，且查询中不包含子查询和UNION （注意：where是可以的，where是查询条件）
primary : 指的是若存在子查询则最外层查询就是primary
subquery : 指的是处于select和where中的子查询语句，注意 不包含from后的
derived : 指的是衍生，在from中的存在子查询时，子查询查询出的结果会存放在一张临时表中，故被称为衍生
union : 若第二个select出现在union 之后，则被标记为union ：若union 包含在from子句的子查询中，外层select将被标记为：derived
union result : 从union 表获取结果的select查询

2.3 table
指的是当前执行的表，若为derived表时，后面会加上id，指明是由该id的select生成的衍生表；若为union时，会加上两个id,指明union的是哪两个id对应的select语句

2.4 type
ype所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：
这里写图片描述
从最好到最差依次是：system > const > eq_ref > ref > range > index > all
system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
const : 表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref : 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref : 非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range : 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
index : Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
all : Full Table Scan 将遍历全表以找到匹配的行 ,也就是没使用到索引

2.5 possible_keys和key
possible_keys:指的是可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key : 实际使用的索引，如果为NULL，则没有使用索引。但查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中，不会出现在possible_keys列表中

2.6 key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

2.7 ref
显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

2.8 rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

3.实例分析

执行顺序1：select_type为UNION，说明第四个select是UNION里的第二个select，最先执行【select name,id from t2】
执行顺序2：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为DERIVED【select id,name from t1 where other_column=”】
执行顺序3：select列表中的子查询select_type为subquery,为整个查询中的第二个select【select id from t3】
执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name ……】
执行顺序5：代表从UNION的临时表中读取行的阶段，table列的< union1,4 >表示用第一个和第四个select的结果进行UNION操作。【两个结果union操作】

4.除此之外，再简单提一下索引使用时的最左匹配原则
首先索引存在多种类型：普通索引、唯一索引（允许列值为空）、主键索引（不允许列值为空）、组合索引、全文索引
此处讲下组件索引创建规则

在执行上述语句时，实际上mysql为表建立了3个索引（user_id）,（user_id,vote_id），（user_id,vote_id,group_id）如图所示

当我们执行如下sql语句时，


通过对比，可以看出此时并第一条语句未走索引进行查询，而后一条查询条件由于有了user_id这个组合索引中最左的字段，在查询时则引用到了索引，这就是最左匹配原则。
到这，有个疑问时，上述查询条件中将条件位置互换一下是否，还会走索引呢？


查看执行结果可以得出，查询条件顺序并不影响使用最左原则，具体原因此处就不去分析了，个人猜测可能是在and连接的情况下mysql内部对盖条语句进行了优化，使其仍匹配最左匹配原则