mysql 执行计划分析

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就和这个一样了，只是相反而已

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

1.  

只取表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

1.  

只取表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

全家福：