8、explain
2021-10-16 16:38:53 2 举报AI智能生成
Explain是一个英文单词，意为“解释”或“说明”。当我们遇到一个概念、问题或者现象时，我们可能需要对其进行解释，以便让别人更好地理解。解释可以通过文字、语言、图表等多种方式进行。在学术、教育、工作等领域，解释能力是非常重要的，因为它可以帮助我们更有效地传达信息、解决问题和达成共识。同时，具备良好的解释能力也意味着我们对所要解释的事物有深入的理解和掌握。因此，学会如何清晰、简洁地解释复杂的概念和问题是我们在学习和工作中需要不断努力提高的能力。
作者其他创作
大纲/内容
explain
id
每个SELECT都会对应一个id，就是一个复杂的SQL里可能会有很多个SELECT，也可能会包含多条执行计划，每一条执行计划都会有一个唯一的id
select_type
一条执行计划对应的查询是个什么查询类型
SIMPLE
一般单表查询或者是多表连接查询
不包括UNION操作或子查询操作
explain select * from user where uid=1;
PRIMARY
查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
explain select * from (select * from user where uid=1)b
UNION
UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句没有依赖关系）
explain select * from user where uid=1 union select * from user where uid=2
DEPENDENT UNION
UNION操作中，查询中处于内层的SELECT（内层的SELECT语句与外层的SELECT语句有依赖关系）
explain select * from people where id in (select id from people where zipcode = 100000 union select id from people where zipcode = 200000 )
select id from people where zipcode = 200000的select_type为DEPENDENT UNION也许很奇怪这条语句并没有依赖外部的查询
MySQL优化器对IN操作符的优化
SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);
上面类似这样的语句会被重写成下面这样
SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);
所以查询实际上被重写成这样
explain select * from people o where exists (select id from people where zipcode = 100000 and id = o.id union select id from people where zipcode = 200000 and id = o.id);
UNION RESULT
UNION操作的结果，id值通常为NULL，针对两个查询的结果依托一个临时表进行去重
参考UNION和DEPENDENT UNION的例子
SUBQUERY
子查询中首个SELECT（如果有多个子查询存在）
explain select * from groups where gid =(select gid from user where uid=1)
DEPENDENT SUBQUERY
子查询中首个SELECT，但依赖于外层的表（如果有多个子查询存在）
参考DEPENDENT UNION的例子
DERIVED
被驱动的SELECT子查询（子查询位于FROM子句）
FROM子句后跟了一个子查询，在子查询里是根据x1字段进行分组然后进行count聚合操作，也就是统计出来x1这个字段每个值的个数，然后在外层则是针对这个内层查询的结果集进行查询通过where条件来进行过滤
第二条执行计划，select_type是derived，意思是，针对子查询执行后的结果集会物化为一个内部临时表，然后外层查询是针对这个临时的物化表执行的
外层查询是第一个执行计划，select_type是PRIMARY，针对的table是<derived2>，就是一个子查询结果集物化形成的临时表，他是直接针对这个物化临时表进行了全表扫描根据where条件进行筛选的
MATERIALIZED
被物化的子查询
UNCACHEABLE SUBQUERY
对于外层的主表，子查询不可被物化，每次都需要计算（耗时操作）
UNCACHEABLE UNION
UNION操作中，内层的不可被物化的子查询（类似于UNCACHEABLE SUBQUERY）
table
表名，意思是要查询哪个表
partitions
表分区
type
当前这个表的访问方法
const
直接通过索引定位到数据
速度极快
二级索引必须是唯一索引，才是属于const方式的，也就是说必须建立unique key唯一索引，保证一个二级索引的每一个值都是唯一的
eq_ref
索引是唯一索引，对于每个索引键值，表中只有一条记录匹配；简单来说，就是多表连接中使用primary key或者unique index作为关联条件
ref
普通的二级索引
查询速度也是很快的
如果是包含多个列的普通索引的话，那么必须是从索引最左侧开始连续多个列都是等值比较才可以是属于ref方式
ref_or_null
在二级索引里搜值以及是NULL的值
name IS NULL这种语法的话，name是主键或者唯一索引
range
SQL里有范围查询
类似select * from table where age>=x and age <=Y，age是一个普通索引，此时就必然利用索引来进行范围筛选
index
只要遍历二级索引就可以拿到想要的数据，而不需要回源到聚簇索引的访问方式
all
全表扫描
possible_keys
跟type结合起来的，意思就是type确定访问方式
针对一个表进行查询的时候有哪些潜在可以使用的索引
key
在possible_keys里实际选择的那个索引
通过成本优化方法，去估算possible_keys里的所有索引进行查询的成本，看使用哪个索引的成本更低，那么就选择用那个索引，最终选择的索引，就是执行计划里的key这个字段的值了
key_len
索引的长度
使用某个字段的索引进行等值匹配搜索的时候，跟索引列进行等值匹配的那个目标值的一些信息
当查询方式是索引等值匹配的时候，比如const、ref、eq_ref、ref_or_null这些方式时此时执行计划的ref字段说明：跟索引列等值匹配的是什么？是等值匹配一个常量值？还是等值匹配另外一个字段的值？
EXPLAIN SELECT * FROM t1 WHERE x1 = 'xxx'
针对t1表的查询，type是ref方式的，基于普通的二级索引进行等值匹配
possible_keys只有一个，就是index_x1，针对x1字段建立的一个索引，而实际使用的索引也是index_x1
key_len是589，意思就是说index_x1这个索引里的x1字段最大值的长度也就是589个字节
ref字段，意思是：既然是针对某个二级索引进行等值匹配的，那么跟index_x1索引进行等值匹配的是什么？是一个常量或者是别的字段？这里的ref的值是const，意思就是说，是使用一个常量值跟index_x1索引里的值进行等值匹配的
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
t1表作为驱动表执行一个全表扫描，接着针对t1表里每条数据都会去t2表根据t2表的主键执行等值匹配，所以第二个执行计划的type是eq_ref，意思就是被驱动表基于主键进行等值匹配，而且使用的索引是PRIMARY就是使用了t2表的主键
ref是test_db这个库下的t1表的id字段，这里跟t2表的主键进行等值匹配的是t1表的主键id字段
rows
预估通过索引或者别的方式访问这个表的时候，大概可能会读取多少条数据
EXPLAIN SELECT * FROM t1 WHERE x1 > 'xxx' AND x2 = 'xxx'（x1字段建了索引，x2字段是没有索引的）
针对t1表的查询方式是range，是基于索引进行范围查询，用的索引是index_x1，也就是x1字段的索引基于x1>'xxx'这个条件通过index_x1索引查询出来的数据大概是1987条，接着会针对这1987条数据再基于where条件里的其他条件，也就是x2='xxx'进行过滤
filtered
经过搜索条件过滤之后的数据的百分比
参考rows字段的例子
filtered是13.00，意思是估算基于x2='xxx'条件过滤后的数据大概是13%，也就是说最终查出来的数据大概是1987 * 13% = 258条左右
extra
一些额外的信息
EXPLAIN SELECT x1 FROM t1 WHERE x1 = 'xxx'
extra的信息，是Using index，就是说明这次查询，仅仅涉及到了一个二级索引，不需要回表，因为仅仅是查出来了x1这个字段，直接从index_x1索引里查就行了
SELECT * FROM t1 WHERE x1 > 'xxx' AND x1 LIKE '%xxx'
此时会先在二级索引index_x1里查找，查找出来的结果还会额外的跟x1 LIKE '%xxx'条件做比对，如果满足条件的才会被筛选出来，这种情况下，extra显示的是Using index condition。
EXPLAIN SELECT * FROM t1 WHERE x2 = 'xxx'（x2没有建立索引）
针对t1表进行查询，用的是全表扫描方式，没有使用任何索引，然后全表扫描，扫出来的是4578条数据
extra中的Using where的意思是，对每条数据都用了WHERE x2 = 'xxx'去进行筛选
filtered说明过滤出来了15%的数据，大概就是说，从这个表里筛选出来了686条数据
EXPLAIN SELECT * FROM t1 WHERE x1 = 'xxx' AND x2 = 'xxx'（x1字段建了索引，x2字段是没有索引的）
where条件里有一个条件是针对索引列查询的，有一个列是普通列的筛选
针对t1表去查询，先通过ref方式直接在index_x1索引里查找，是跟const代表的常量值去查找，然后查出来250条数据，接着再用Using where代表的方式，去使用AND x2 = 'xxx'条件进行筛选，筛选后的数据比例是18%，最终所以查出来的数据大概应该是45条
EXPLAIN SELECT * FROM t1 INNER JOIN t2 ON t1.x2 = t2.x2（x2没有建立索引）
在多表关联的时候，有的时候关联条件并不是索引，此时就会用一种叫做join buffer的内存技术来提升关联的性能
要执行join，那么先查询t1表的数据，此时是对t1表直接全表查询，查出来4578条数据，然后对每条数据的x2字段的值，跑到t2表里去查对应的数据，进行关联
此时因为 t2 表也没法根据索引来查，也是属于全表扫描，所以每次都得对t2表全表扫描一下，根据extra提示的Using where，就是根据t1表每条数据的x2字段的值去t2表查找对应的数据了，然后此时会用join buffer技术，在内存里做一些特殊优化，减少t2表的全表扫描次数
EXPLAIN SELECT * FROM t1 ORDER BY x2 LIMIT 10（x2没有建立索引）
基于x2字段来排序，是没法直接根据有序的索引去找数据的，只能把所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里按照x2字段的值完成排序，然后再按照LIMIT 10的要求取出来头10条数据
所以这种把表全数据放磁盘文件排序的做法真的是相当的糟糕，性能其实会极差的。
Using filesort表示没有使用索引排序
对全表数据放到临时表里做大量的磁盘文件操作，然后才能完成对x2字段的不同的值去分组，分组完了以后对不同x2值的分组去做聚合操作，这个过程也是相当的耗时的，性能是极低的
用group by、union、distinct之类的语法的时候，万一没法直接利用索引来进行分组聚合，那么会直接基于临时表来完成，也会有大量的磁盘操作，性能其实也是极低的
Using temporary表示创建临时表进行处理
0 条评论
回复 删除
下一页
