MYSQL 执行计划(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里的记录集,我们用同样的左外联合,然后用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唯一的记录集,我们用同样的全外联合,然后用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