MYSQL 执行计划(explain详解)

使用explain后给出的相关索引信息

下面是一个select语句的使用explain之后的例子:

id

select查询的序列号

select_type

a.SIMPLE:查询中不包含子查询或者UNION
b.PRIMARY:查询中若包含任何复杂的子部分,最外层查询的标记
c.SUBQUERY:在SELECT或WHERE列表中包含了子查询,该子查询的标记
d.DERIVED(衍生):在FROM列表中包含的子查询的标记
e.若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在 FROM子句的子查询中,外层SELECT将被标记为:DERIVED
f.从UNION表获取结果的SELECT被标记为:UNION RESULT

table

输出的行所引用的表

type

联合查询所使用的类型,表示MySQL在表中找到所需行的方式,又称“访问类型”。

type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref。

index: 扫描全部索引树
range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const, system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量。system是const类型的特例,当查询的表只有一行的情况下, 使用system。
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引。

possible_keys

指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引

key

显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL

key_len

显示MySQL决定使用的键长度。表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。

rows

这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的

SQL语句的一般执行顺序

(1)from
(3) join
(2) on
(4) where
(5)group by(开始使用select中的别名,后面的语句中都可以使用)
(6) avg,sum....
(7)having
(8) select
(9) distinct
(10) order by

下面来个例子帮助记忆

select 考生姓名, max(总成绩) as max总成绩   
  
from tb_Grade   
  
where 考生姓名 is not null   
  
group by 考生姓名   
  
having max(总成绩) > 600   
  
order by max总成绩   

在上面的示例中 SQL 语句的执行顺序如下:

(1). 首先执行 FROM 子句, 从 tb_Grade 表组装数据源的数据

(2). 执行 WHERE 子句, 筛选 tb_Grade 表中所有数据不为 NULL 的数据

(3). 执行 GROUP BY 子句, 把 tb_Grade 表按 "学生姓名" 列进行分组(注:这一步开始才可以使用select中的别名,他返回的是一个游标,而不是一个表,所以在where中不可以使用select中的别名,而having却可以使用,感谢网友 zyt1369 提出这个问题)

(4). 计算 max() 聚集函数, 按 "总成绩" 求出总成绩中最大的一些数值

(5). 执行 HAVING 子句, 筛选课程的总成绩大于 600 分的.

(7). 执行 ORDER BY 子句, 把最后的结果按 "Max 成绩" 进行排序.

各种Join的语句和图

我在网上看到的这个,对于理解join操作很棒。整个搬过来,以后在这里方便查看。

这个是原始表:

id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja

inner join
内联
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name
 
id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja

内联合(inner join)只生成同时匹配表A和表B的记录集

full outer join
全外联合
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
 
id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga       
null  null       3     Darth Vader

全外联合(full outer join)生成表A和表B里的记录全集,包括两边都匹配的记录。如果有一边没有匹配的,缺失的这一边为null。并且我们看到,以TableA为基准表,然后表B来匹配,当表A中没有表B中的元素时,表B中元素排列的顺序也是按照id大小。先保证基准表全部输出,然后再去看A中没有B有的。

left outer join
左外联合
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
 
id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null

左外联合(left outer join)生成表A的所有记录,包括在表B里匹配的记录。如果没有匹配的,右边将是null

那right join就和这个一样了,只是相反而已

下面看看其他几种集合的表现形式:

只取表A中有,但表B中没有的

为了生成只在表A里而不在表B里的记录集,我们用同样的左外联合,然后用where语句排除我们不想要的记录

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null
 
id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null

只取表A中和表B中各自有的

为了生成对于表A和表B唯一的记录集,我们用同样的全外联合,然后用where语句排除两边都不想要的记录


SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null 
OR TableB.id IS null
 
id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader

全家福:

image.png

参考文章:
画图解释 SQL join 语句
MySQL explain执行计划解读

推荐阅读更多精彩内容