Explain的SQL分析及优化思路

explain的定义和作用

定义： explain是模拟优化器来执行sql语句，进行分析语句或者表结构的性能，在5.6版本以后不但可以对select，亦可以对update、delete、insert等语句。
作用 ：

• 表的读取顺序
• 数据读取实际的操作类型
• 是否可以使用索引
• 那些索引被使用了
• 表有多少行被执行优化

用法： explain + sql语句

执行结果解读

EXPLAIN SELECT
	a.iid,a.uuid,a.appid,a.channel,a.starttime,p.Name AS appName,p.icon_new_name AS appIconName,
  p.key_value AS keyValue,a.accesscount AS accessCountSum,
  p.iid AS uid,
	(SELECT region FROM tj_region d WHERE d.uuid = a.uuid AND d.starttime = a.starttime LIMIT 1 ) region,
	(SELECT terminal FROM tj_terminal d WHERE d.uuid = a.uuid AND d.starttime = a.starttime LIMIT 1) terminal
FROM jmopen_app p,tj_channel a  WHERE  a.appid = p.iid
LIMIT 30000;

结果：

这里是自己写的sql进行优化，我们可以看到结果有10列结果。我们来逐个解读下

1. id: 选择标识符
2. select_type: 查询类型
3. table： 数据的结果表，可以是别名
4. type： 表的连接类型
5. possible_key: sql语句查询的时候可能使用的索引
6. key： 实际使用的索引
7. key_len： 索引的长度
8. ref： 列与索引的比较
9. rows： 估算的行数
10. Extra: 执行的情况和说明

字段解释

一、id

是select的查询序列号，子查询id值越大则优先级约高，越先被执行，id相同时执行顺序由上到下，

二、select_type

1. SIMPLE: 简单的SELECT,不使用UNION或者子查询等
2. PRIMARY: 子查询中最外层的查询，查询中包含任何复杂的子部分
3. UNION联合: union中的第二个或者后面的select语句
4. DEPENDENT UNION依赖联合：union语句中第二个select开始后面的所有select，取决于外面的select
5. UNION RESULT: union的结果，union的结果，union中的第二个select开始后面的所有select
6. SUBQUERY 子查询: 子查询的第一个select， 依赖于外部查询
7. DEPENDENT SUBQUERY ：子查询的第一个select，依赖于外部查询
8. DERIVED：派生表的select，from的子查询
9. UNCACHEABLE SUBQUERY: 一个子查询结果不能被缓存，必须重新评估

三、table

显示这一步的数据表名称

四、type

对表的访问方式，也称访问类型，是比较重要的
按照性能好坏从左到右，依次为
ALL、index、 range、ref、 eq_ref、 const、 system、Null

• ALL full table scan 全表匹配
• index: full index scan ,index与all的区别是index只遍历索引树
• range： 只检索给定范围的行，使用一个索引来选择行
• ref： 表示上述表的连接匹配条件，即那些列或者常量被用于查找索引列上的值
• eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引值，表中只有一条记录匹配，简单来说就是多表中使用primary key或者uniqe key作为关联条件
• const、system：当mysql查询其中一部分的进行优化并转化为一个常量的时，使用这些类型访问，如将主键放在where中，mysql就能将该查询作为一个常量，system是constant类型的特例，当查询只有一行的时候，使用system。
• NULL: 在优化过程中分解语句，执行时甚至不用访问表或者索引，例如一个索引列里面选取最小值可以通过单独索引查找完成。

五、 possible_key

指出优化过程中能使用到的索引，如果查询字段上存在索引，则索引将被列出，但是不一定使用，如果么有索引则会为NULL

六、 key

key中表示实际用到的索引，必然会被包含在possible_key中，如果没有选择索引，键是null，想要强制或者忽视possible_key列中的索引，在查询使用FORCE INDEX, USE INDE或者IGNORE INDEX

举例：

SELECT * FROM user u force index(idx_user_id_update_time)

七、key_len

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

八、ref

列与索引的比较，表示上述表的连接匹配条件，即那些列或者常量被用于索引列上的值

九、rows

结果出现的行数，表示mysql估计所需记录的行数，

十、Extra

该列包含包含解决查询的详细信息

• Using where ： 不用读取表中所有的信息，仅通过索引就可以获取数据发生在对表的请求都在同一个索引的部分，表示服务器将在存储引擎检索后在进行过滤
• Using temporary： 表示mysql需要使用临时表来存储结构集，常见于分组和排序
• Using filesort： 当Query中包含order by操作，而且无法利用索引完成的排序操作称为“文件排序”。
• USing join buffer： 这个强调了获取连接条件的时候没有使用索引，需要连接缓冲区来存储中间结果。
• inpossible where 这个值强调了where语句会导致没有符合条件的行数
• select tables optimized away： 这个意味着通过索引，优化器可以聚合函数结果汇总中返回一行
• no tables used： 语句中没有from子句

总结：
• EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
• EXPLAIN不考虑各种Cache
• EXPLAIN不能显示MySQL在执行查询时所作的优化工作
• 部分统计信息是估算的，并非精确值
• EXPALIN只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划。

通过收集统计信息不可能存在结果

后记

这篇文章是看的一篇博文想动动手自己编写一篇，加深印象，也为了和其他伙伴讨论
原博客：https://www.cnblogs.com/tufujie/p/9413852.html

如果让你有收获，请留下个赞鼓励一下，祝我们越来越好。