一. EXPLAIN 执行计划基础解释

1. 解释: 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySql是如何处理接收的SQL语句的,然后进行指定的修改
2. 通过EXPLAIN+SQL语句,查看该语句的执行计划,执行顺序,可以获取到当前执行的SQL语句的查询耗时时间,索引是否生效,使用了几个索引等…,分析优进行优化,借用大神整理的EXPLAIN用法,EXPLAIN作用

通过EXPLAIN中的id列可以查看表的读取顺序,id越大优先级越高
通过EXPLAIN中select_type列可以查看数据读取操作的操作类型,简单查询,主查询,子查询,UNION或UNION RESULT等
通过EXPLAIN中的possible_keys列可以查看哪些索引可能被
通过EXPLAIN中的key列可以查看那个索引被实际使用
通过EXPLAIN中的type列可以查看当前执行的sql是否走的全表扫描sql级别,由高到低:system>const>eq_ref>ref>range>index>ALL
通过EXPLAIN中的rows列可以查看每张表有多少行被优化器查询

3. 几个比较重要的列: id, type, key,rows 与Extra

#where条件中snapshot_id 为主键,通过EXPLAIN分析执行计划中的type为const,possible_keys为PRIMARY 使用主键索引
EXPLAIN SELECT * FROM express_customer_snapshot where snapshot_id='123456';
#where条件中'customer_name'字段添加了普通索引,EXPLAIN查看执行计划type为 ref, possible_keys 为index_name
EXPLAIN SELECT * FROM express_customer_snapshot where customer_name='bbb';
#查看EXPLAIN执行计划,possible_keys为'PRIMARY,index_name'使用了两个索引
EXPLAIN SELECT * FROM express_customer_snapshot where snapshot_id='123456' and customer_name='bbb';
#查看EXPLAIN执行计划,where条件字段中没有添加索引,type为ALL全表扫描
EXPLAIN SELECT * FROM express_customer_snapshot where operate_user_id='ccc';

3. 示例:
   

二. EXPLAIN 字段解释

1. 我们通过EXPLAIN+执行SQL,查看MySql是如何处理接收优化SQL语句的,会显示一个table,其中包含这些字段
   

id

1. 此处的id代表select查询的序列号,表示查询中执行的select操作表的顺序,id值越大约先被执行,相同id从上往下顺序执行,有三种情况: id相同, id不同, id相同,与不同的同时存在,根据id我们可以观察到表的先后读取顺序
2. 根据下图explain+sql举例id相同情况: from t1, t2, t3三张表,explain输出的执行计划中三个id都为1,表示按照加载顺序先后执行,注意点加载顺序并不是值from的顺序,这是mysql优化后的加载顺序
   
3. 根据下图explain+带子查询sql举例id不同情况, 首先了解id越大越先执行,select_type中PRIMARY代表主查询,SUBQUERY代表子查询,table代表加载顺序,我自己理解的是越括号里面的子查询越先执行,所以先执行的t3子查询,然后t1子查询,然后t2主查询
   
4. 根据下图explain+带子查询带虚表sql举例相同不同id同时存在情况,首先观察发现table列中有< derived >叫做"衍生",可以理解为通过from t3 表中查询出的数据虚拟出来的s1表,而< derived2 > 后面的"2"对应id中的2,该表是通过id为2对应的table中的表衍生出来的,然后在看执行顺序,先执行t3,然后t2跟derived2的id相同,从上往下顺序执行
   

select_type

1. select_type 通过select_type可以了解数据读取操作类型,主要用于区别普通查询,联合查询,子查询等: SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, UNION RESULT,
2. SIMPLE: 简单的,简单的select查询,不包含子查询,或UNION
3. PRIMARY: 主查询,若执行的sql语句中包含子查询,最外层的查询标记为主查询
4. SUBQUERY: 子查询(自己理解为括号里面的)
5. DERIVED: 衍生的,在from中包含虚表,MySql会递归执行这些子查询,把结果放入临时表中
6. UNION: 若第二个select出现在UNION之后,则被标记为UNION,若UNION在from子句的自查询中,外层的from被标记为DERIVED
7. UNION RESULT: 表示执行的SQL中有在UNION表获取结果的操作(也可以理解为两个UNION操作后合并结果集)
   

table

表示该行的数据来自于哪一张表

type

1. type 包含: 从最好到最差: system>const>eq_res>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
2. 常见的是 system>const>eq_ref>ref>range>index>ALL,百万级表type为ALL需要优化,一般来说,至少要达到range级别,最好能到ref级别
   
3. system: 表只有一行记录(等于mysql的系统表),是const类型的一种特别变种,平时不会出现
4. const: 表示通过索引,一次就找到了记录,const通常出现与使用了primary key或unique索引的sql,(下图中from t1 使用id主键的子查询,显示的type为const, 由于id=1衍生表d1的type显示的是system类型)
   
5. eq_ref: 唯一性索引扫描, 对于每个索引键,表中只有一条匹配的记录数据,常见于使用主键,或唯一索引的sql
6. ref: 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,但是它可能会返回多个符合条件的数据,属于查找合扫描的混合体(简单举例: 通过普通索引查询数据,数据库匹配到有多行)
7. range: 只检索给定范围的行,使用一个索引来选择行,简单理解为通过索引字段进行范围查询,例如between,< , >,或in等,这种范围查询比全表扫描要好,因为只需要索引的开始点与结束点,不会进行全表扫
8. index: Full Index Scan遍历全表索引匹配, index与ALL区别在于index类型只遍历索引树,而ALL是从磁盘读取,index比ALL快
   
9. ALL: Full Table Scan 遍历全表匹配

possible_keys 与 key 与 key_len

1. possible_keys: 表示该sql执行是可能使用到的索引,查询涉及到的索引,都会被列出,但是不一定被实际使用
2. key: 表示该sql执行时实际使用到的索引,如果为NULL表示没有使用索引,
3. 注意点: 若查询中使用了覆盖索引,则该索引只出现在key列表中
4. key_len:表示索引中使用的字节数,可通过该列计算使用的索引的长度,在不损失精度的情况下,长度越小越好,注意点ken_len显示的值为"索引字段的最大可能长度",并非实际长度,是根据表定义得出来的,不是表内检索出的

ref

1. ref: 当执行的select sql 使用到索引时,应用到该索引上的值,没有使用到索引时为NULL
2. 根据下图举例: from t1, t2, 首先看t2表没有使用到索引,ref为NULL, t1表中使用到了idx_col1_col2索引,ref中使用到了shared库下的t2表中的col1字段的值,与const一个常量,分别对应where中的两个条件"where t1.col1 = t2.col1" 与 "t1.col2 =‘ac’ ",(当直接传递常量时例如’ac’在ref中显示的是const)
   

rows

rows: 根据表统计信息及索引使用情况,大致估算出当前sql查询找到结果需要读取的行数

Extra

1. Extra:扩展列,包含了一下不适合在其它列中显示,又十分重要的信息,包含了: Using filesort, Using temporary, USING index, Using where, using join buffer, impossible where, select tables optimized away, distinct…

Using filesort

1. Using filesort: sql执行是出现了文件内排序,说明MySql对数据会使用一个外部的索引进行排序而不是按照表内的索引顺序进行读取,简单来说,我们对一个表添加了索引字段,正常情况下应该按照该索引进行排序的,但是由于某种情况MySql并没有按照索引去排序,而是在内部又重新按照某种规则又排序了一次,出现这个东西不好,如果可以尽快优化
2. 举例:from t1表,该表中创建了"index_col1_col2_col3"的符合索引,在查询时where了col1,order by了col3字段 (下图中的select结尾为"\G",意思是输出table为竖版key value键值对,没啥实际意义,重点观察下方的Extra), 在Extra列中输出了: Using where(表示当前sql使用到了where条件), Using index(表示当前sql使用到了索引),最终后面还输出了Using filesort(表示使用到了文件内排序),自己理解: 在执行该sql时,where条件col1中创建了索引,所以Extra有输出Using index,但是后面的"order by col3"排序没有使用到我们自己创建的所有,MySql自己内部又重新排了一次,出现这种情况的原因是我们创建的符合索引为(col1_col2_col3),但实际过滤排序的条件,少了中间的col2
   
3. 添加联合索引(index_col1_col2_col3), where col1, order by col2,col3 与联合索引对应,没有出现文件内排序
   

Using temporary

1. Using temporary: sql执行时使用了临时表用来保存中间结果,常见于order by, group by,尽量不要出现
2. 举例:from t1表,该表添加了 index_col1_col2联合索引, where col1 in条件, group by col2 条件, 查看Extra,不但出现了文件内排序,也出现了临时表(虽然存在index_col1_col2联合索引,但是group by时,只根据col2一个字段进行分组的,利用不上我们的索引)
   
3. 该表添加了 index_col1_col2联合索引,group by中也是根据col1,col2两个条件进行分组的,没有出现临时表
   

USING index

1. 表示当前执行的sql使用了覆盖索引,减少数据库的io操作,可以提高效率(覆盖索引:select的列的个数,顺序,刚好对应我们创建的某个索引或联合索引,InnoDB通过非聚集索引查询时,通过索引即可返回数据,减少了通过非聚集索引叶子节点拿到聚集索引key,然后查到聚集索引,找到聚集索引的叶子节点拿到数据的步骤)
2. 注意点:当出现USING index同时也出现了 USING where,表示索引被用来执行索引键值的查找,如果没有同时出现表示索引用来读取数据,而非执行查找动作(是不是同时出现了 USING where 说明where条件中使用了索引,如果没有出现说明where没有使用到索引?)

USING index condition

索引下推

USING where

表示当前执行的 sql 中存在where条件

USING join buffer

表示当前执行的 sql 使用到了链接缓存

Using intersect

Using union

Using sotr union

Zero limit

Impossible where

1. 表示where子句的值总是false,不能用来获取任何元组
2. 举例: 假设一条sql : “SELECT * FROM table WHERE name =‘aaa’ AND name =‘bbb’ " , where的name条件等于"aaa"又等于"bbb”,让MySql错乱了

select tables optimized away

表示在没有 group by 的情况下,基于索引优化 MIN/MAX操作,或者对应没有MyISAM存储引擎优化COUNT(*)操作时不必等到执行阶段再进行计算,查询执行计划生成阶段及完成优化

distinct

表示优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作

Start temporary, End temporary

LooseScan

FirstMatch

三. show warnings

1. “show warnings” 命令是一个诊断语句，用于显示由于在当前会话中执行语句而导致的情况(error、waring和note)信息,只 针对select语句,也可以在EXPLAIN后面使用，用以显示EXPLAIN生成的扩展信息