MYSQL执行计划很难吗?
一 前言
本篇是MYSQL进阶第三篇,SQL调优的前置知识之一;学习本篇的基础是知识追寻者发布的MYSQL系列文章;
公众号:知识追寻者
知识追寻者(Inheriting the spirit of open source, Spreading technology knowledge;)
二 执行计划概念
执行计划(EXPLAIN)即表示MYSQL这条语句是如何执行,其执行顺序如何,使用到哪些索引,表之间的关联关系等;如何对一条查询语句实行执行计划?很简单,在查询语句上面加上explain
关键字即可;
示例 :
EXPLAIN SELECT * from sys_user where last_name = 'ijklmnopqrs'
输出结果如下,总共有十二个字段,我们可以根据这12个字段给出的信息对SQL语句进行评估,然后进行调整优化我们的查询语句;
三 关键字段详解
3.1id
表示查询语句中的执行顺序,其值越大,优先级越高,被优先执行的可能性久越大;
示例:
EXPLAIN SELECT * from `order` , oder_detail where `order`.id = oder_detail.oid
如上语句查询中涉及2张表,但它们id 是一致,故拥有执行的优先权一样;
示例:
EXPLAIN select * from sys_user where id = '1' UNION (select * from sys_user )
如上语句 出现了id 不同的,id 越大,越优先被执行,但也出现了情况id 为 null
3.2select_type
select_type
表示 区分查询类型,通常用来判定该查询是简单查询还是复杂查询(子查询,联合查询等);
SIMPLE
:表示不包含子查询或者UNION;PRIMARY
: 包含子查询最外层的查询;SUBQUERY
:当select
或where
列表中包含子查询;DERIVED
:表示包含在from
子句中的子查询;UNION
: 表示union
后边又出现的select
语句,则会被标记为union
;UNION RESULT
: 代表从union
的临时表中读取数据,<union 1,2>
表示从第一个查询和第二个查询的临时表中进行union操作;dependent union
:与union一样,出现在union 或union all语句中,但是这个查询要受到外部查询的影响dependent subquery
:与dependent union类似,表示这个subquery的查询要受到外部表查询的影响
3.3 table
查询行的表名,也有可能是临时表;
3.4 type
type 联合查询使用的类型;SQL优化的重要指标之一;
-
system
:仅当只有一条数据时(系统表) -
const
:表示 表中最多有一个匹配行
示例 :
EXPLAIN SELECT * from `order` where id = '1'
输出
eq_ref
:关联查询时命中主键primary key
或者unique key
索引,必须是等值操作;
示例:
EXPLAIN SELECT * from `order` , oder_detail where `order`.id = oder_detail.oid
输出
ref
:非唯一索引列;
示例:
EXPLAIN SELECT * from `order` where order_name = '小天使的订单'
输出
fulltext
:使用到全文索引检索;ref_or_null
:类似于ref,但是可以搜索包含null值的行;index_merge
:关联查询使用了两个以上的索引unique_subquery
在in中使用了子查询中某些时候会取代 ref;index_subquery
: 与unique_subquery
类似,但非作用在唯一索引上;range
:按范围来检索,比如 > , < , between....and ,in等
示例:
EXPLAIN SELECT * from `order` where id > '2'
输出
index
:从索取树中查找,也属于全表扫描;
示例:
EXPLAIN select id from `order`
输出
ALL
:全表扫描;
示例:
EXPLAIN select * from `sys_user` where last_name = 'ijklmnopqrs'
输出
3.5 partitions
partitions 查询匹配到的分区,没有分区就是null;
示例:
EXPLAIN select id from `order`
输出
3.6 possible_keys
可能会使用到的索引
示例
EXPLAIN SELECT * from `order` where order_name = '小天使的订单'
输出
3.7 key
mysql中实际使用到的索引,否则为null;
示例
EXPLAIN SELECT * from `order` where order_name = '小天使的订单'
输出
3.8 key_length
key_length :表示查询用到的索引长度(字节数),越短越好
示例:
EXPLAIN SELECT * from `order` where id ='9'
输出
3.9 ref
- const : 等值查询
- func: 关联查询,使用了函数,表达式
- null: 其它情况
3.10 rows
扫描表的行,非精确值;一般情况下 rows
越小越好。
示例:
EXPLAIN SELECT * from `order` where id ='9'
输出
3.11 extra
扩展信息
Using index
: 使用了覆盖索引;Using where
: 使用了where 条件过滤数据;Using temporary
:表示查询后结果需要使用临时表来存储;Using filesort
: 排序时未使用到索引;Using join buffer
:官联表查询的时候,表的连接条件没有用到索引;
更多扩展信息参考官网: https://dev.mysql.com/doc/refman/5.7/en/explain-output.htm
四 何时需要优化
explain 执行计划的参数非常多我们一般是记不过来,于是筛选了一些性能极差的条件用作SQL优化标准,知识追寻者的认为如下情况是必须需要优化;
-
当type 出现all 为 全表扫描时一般需要优化
-
当 row 的数值 非常大,或接近全表时需要优化
-
extra 出现
Using filesort
,Using temporary
时需要优化;