MYSQL执行计划很难吗?

一 前言

本篇是MYSQL进阶第三篇,SQL调优的前置知识之一;学习本篇的基础是知识追寻者发布的MYSQL系列文章;

《SQL-你真的了解什么SQL么?》

《SQL-小白最佳入门sql查询一》

《SQL-小白最佳入门sql查询二》

《SQL- 多年开发人员都不懂的插入与更新删除操作注意点》

《SQL-SQL事物操作》

《SQL-Mysql数据类型》

《SQL-mysql视图的前世今生》

《SQL-mysql储存过程》

《SQL-mysql游标与触发器》

《SQL-mysql用户权限管理》

《SQL-mysql架构入门》

《SQL-mysql锁等待与死锁》

《SQL-mysql操作json》

公众号:知识追寻者

知识追寻者(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:当 selectwhere 列表中包含子查询;
  • 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 filesortUsing temporary 时需要优化;

posted @ 2020-07-14 13:53  知识追寻者  阅读(138)  评论(0编辑  收藏  举报