专栏首页技术那些事MySQL性能优化(七):MySQL执行计划,真的很重要,来一起学习吧

MySQL性能优化(七):MySQL执行计划,真的很重要,来一起学习吧

前期回顾:

MySQL性能优化(一):MySQL架构与核心问题

MySQL性能优化(二):选择优化的数据类型

MySQL性能优化(三):深入理解索引的这点事

MySQL性能优化(四):如何高效正确的使用索引

MySQL性能优化(五):为什么查询速度这么慢

MySQL性能优化(六):常见优化SQL的技巧

一条SQL被一个懵懂的少年,一阵蹂躏,扔向了MySQL服务器的尽头,少年苦苦等待,却迟迟等不来那满载而归的硕果。于是少年气愤,费尽苦心想从度娘那边寻求帮助,面对执行计划EXPLAIN,却等来的是无尽的折磨与抓狂。

通过explain命令,根据执行计划找到存在性能问题的SQL语句,以帮助我们优化SQL提供方向和依据。

如果面对执行计划,你也是一脸疑惑,甚至抓狂,那么你真的需要认真的来了解它了。在数据库性能优化中,执行计划,真的很重要,通过执行计划能够帮助我们更加明确的来进行SQL优化。本文将从执行计划开始说起,讲解执行计划该如何用,其中各个列的含义究竟是什么。

一、执行计划?

执行计划,就是一条SQL语句,在数据库中实际执行的时候,一步步的分别都做了什么。也就是我们用EXPLAIN分析一条SQL语句时展示出来的那些信息。

EXPLAIN命令是查看查询优化器是如何决定执行查询的主要方法,从它的查询结果中可以知道一个SQL语句每一步是如何执行的,都经历了些什么,分为哪几步,有没有用到索引,哪些字段用到了什么样的索引,是否有一些可优化的地方等,这些信息都是我们SQL优化的依据。

要使用·EXPLAIN,只需在查询中的SELECT关键字之前增加EXPLAIN。语法如下:

EXPLAIN + SELECT查询语句;

当执行执行计划时,只会返回执行计划中每一步的信息,它会返回一行或多行信息,显示出执行计划中的每一部分和执行的次序。

如:

如果查询的是多个关联表,执行计划结果可能是多行。

在接下来涉及到的示例表,均来自于MySQL官方的示例数据库sakila,脚本下载:https://downloads.mysql.com/docs/sakila-db.zip

二、执行计划中的列

EXPLAIN的结果总是有相同的列,每一列代表着不同的含义,可变的只是行数和内容。从上面的例子中,我们看到返回的有很多列,为了更加清楚的了解每一列的含义,便于我们更好的完成优化SQL。

涉及到的列有:

列名

含义

id

id列,表示查询中执行select子句或操作表的顺序。

select_type

查询类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询。

table

表明对应行正在访问的是哪个表。

partitions

查询涉及到的分区。

type

访问类型,决定如何查找表中的行。

possible_keys

查询可以使用哪些索引。

key

实际使用的索引,如果为NULL,则没有使用索引。

key_len

索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。

ref

显示索引的那一列被使用。

rows

估算出找到所需行而要读取的行数。

filtered

返回结果的行数占读取行数的百分比,值越大越好。

Extra

额外信息,但又十分重要。

1. id列

id列是一个编号,用于标识SELECT查询的序列号,表示执行SQL查询过程中SELECT子句或操作表的顺序。

如果在SQL中没有子查询或关联查询,那么id列都将显示一个1。否则,内层的SELECT语句一般会顺序编号。

id列分为三种情况:

1)id相同

如下普通查询,没有子查询。

explain select f.* from film f,film_actor fa,actor a where f.film_id = fa.film_id and fa.actor_id = a.actor_id and a.first_name = 'NICK';

2)id不同

如果存在子查询,id的序号会递增,id值越大优先级越高,越先被执行

explain select * from film where film_id = (select film_id from film_actor where actor_id = 2 limit 1);

3)id相同又不同

1)、2)两种情况同时存在。id如果相同,认为是一组,从从上往下执行。在所有组中,id值越大,优先级越高,越先执行。

2. select_type列

select_type列表示对应行的查询类型,是简单查询还是复杂查询,主要用于区分普通查询、联合查询、子查询等复杂的查询。

select_type列有如下值:

select_type值

说明

SIMPLE

简单查询,意味着不包括子查询或UNION。

PRIMARY

查询中包含任何复杂的子部分,最外层查询则被标记为PRIMARY

SUBQUERY

在select 或where列表中包含了子查询

DERIVED

表示包含在from子句的子查询中的select,MySQL会递归执行并将结果放到一个临时表中,称其为“派生表”,因为该临时表是从子查询中派生而来的。

UNION

第二个select出现在UNION之后,则被标记为UNION。

UNION RESULT

从UNION表获取结果的select。

3. table列

table列表示对应行正在执行的哪张表,指代对应表名,或者该表的别名(如果SQL中定义了别名)。

4. partitions列

查询涉及到的分区。

5. type列

type列指代访问类型,是MySQL决定如何查找表中的行。

是SQL查询优化中一个很重要的指标,拥有很多值,依次从最差到最优:

ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system

1)ALL

众所周知的全表扫描,表示通过扫描整张表来找到匹配的行,很显然这样的方式查询速度很慢。

这种情况,性能最差,在写SQL时尽量避免此种情况的出现。

举例如下:

explain select * from film;

在平时写SQL时,避免使用select *,就不难理解了。换言之,是为了避免全表扫描,因为全面扫描是性能最差的。

2)index

全索引扫描,和全表扫描ALL类似,扫描表时按索引次序进行,而不是按行扫描,即:只遍历索引树。

indexALL虽然都是读全表,但index是从索引中读取,而ALL是从硬盘读取。显然,index性能上优于ALL合理的添加索引将有助于性能的提升

举例如下:

explain select title from film;
explain select description from film;

通过explain结果来看,只查询表film中字段title时,是按照索引扫描的(type列为index),倘若查询字段description,却是按照全表扫描的(type列为ALL)。这是为何呢?

接下来,我们不妨看看表film的结构:

desc film结果来看,字段title创建的有索引,而字段description没有,所以select title from film是按索引扫描,而select description from film按全表扫描。

从上面的举例对比中,也充分印证了索引的重要性。

3)range

只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在where语句中出现了bettween、<、>、in等的查询。这种索引列上的范围扫描比全索引扫描index要好。

举例如下:

explain select * from film where film_id between 1 and 10;

4)ref

非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以它属于查找和扫描的混合体。

此类型只有当使用非唯一索引或者唯一索引的非唯一性前缀时,才会发生。

举例如下:

show index from film;
explain select * from film where title = 'ACADEMY DINOSAUR';

5)eq_ref

唯一索引扫描。常见于主键或唯一索引扫描。

6)const

通过索引一次就能找到,const用于比较primary key 或者unique索引。因为只需匹配一行数据,所有很快。如果将主键置于where列表中,mysql就能将该查询转换为一个const

举例如下:

show index from film;
explain select * from film where film_id = 1;

7)system

表只有一行记录,这是const类型的特例,比较少见,如:系统表。

6. possible_keys列

显示在查询中使用了哪些索引。

7. key列

实际使用的索引,如果为NULL,则没有使用索引。查询中如果使用了覆盖索引,则该索引仅出现在key列中。

possible_keys列表明哪一个索引有助于更高效的查询,而key列表明实际优化采用了哪一个索引可以更加高效。

举例如下:

show index from film_actor;
explain select actor_id,film_id from film_actor;

8. key_len列

表示索引中使用的字节数,查询中使用的索的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。key_len是根据表定义计算而得的,不是通过表内检索出的。

9. ref列

表示在key列记录的索引中查找值,所用的列或常量const

10. rows列

估算出找到所需行而要读取的行数。

这个数字是内嵌循环关联计划里的循环数,它并不是最终从表中读取出来的行数,而是MySQL为了找到符合查询的那些行而必须读取行的平均数,只能作为一个相对数来进行衡量。

11. filtered列

返回结果的行数占读取行数的百分比,值越大越好。

举例如下:

表film_actor中actor_id为1的记录有19条,而SQL查询时扫描了19行(rows:19),19条符合条件(filtered: 100 19/19)

12. Extra列

额外信息,但又十分重要。

常见的值如下:

1)Using index

表示SQL中使用了覆盖索引。

举例如下:

2)Using where

许多where条件里是涉及索引中的列,当它读取索引时,就能被存储引擎检验,因此不是所有带·where子句的查询都会显示“Using where”

3)Using temporary

对查询结果排序时,使用了一个临时表,常见于order bygroup by

4)Using filesort

对数据使用了一个外部的索引排序,而不是按照表内的索引进行排序读取。也就是说MySQL无法利用索引完成的排序操作成为“文件排序”。

三、总结

通过上述对执行计划的了解,我们能够从中得到什么?

  • SQL如何使用索引
  • 复杂SQL的执行顺序
  • 查询扫描的数据函数
  • ……

当面临不够优的SQL时,我们首先要查看其执行计划,根据执行计划结果来分析可能存在哪些问题,从而帮助、指导我们是否添加索引、是否调整SQL顺序、是否避免不应该的书写方式等等。

执行计划,真的很重要,尤其是SQL调优时,很香!

本文分享自微信公众号 - 程序猿技术大咖(cxyjsdk),作者:xcbey0nd

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-10

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL性能优化(六):常见优化SQL的技巧

    在面对不够优化、或者性能极差的SQL语句时,我们通常的想法是将重构这个SQL语句,让其查询的结果集和原来保持一样,并且希望SQL性能得以提升。而在重构SQL时,...

    xcbeyond
  • 面试官问:MySQL的自增ID用完了,怎么办?

    可以发现 AUTO_INCREMENT 已经自动变成2,这离用完还有很远,我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 intunsigned...

    xcbeyond
  • MySQL性能优化(四):如何高效正确的使用索引

    实践是检验真理的唯一途径,本篇只是站在索引使用的全局来定位的,你只需要通读全篇并结合具体的例子,或回忆以往使用过的地方,对整体有个全面认识,并理解索引是如何工作...

    xcbeyond
  • Leetcode 215. Kth Largest Element in an Array

    Find the kth largest element in an unsorted array. Note that it is the kth larg...

    triplebee
  • 响铃:母婴社区进入竞争深水区,富媒体会是下一个风向标?

    围绕母婴概念相关的互联网创业已经持续多年,其势头不但不减,竞争反而趋向激烈。而在母婴行业的内部,也存在着一些风口性质的业务。例如母婴富媒体化,3月29日,知名母...

    曾响铃
  • LWC 73: 789. Escape The Ghosts

    思路: 最优策略:所有ghost都守候在target处,等你来。所以只要计算ghost的曼哈顿距离中最小的,与你到target处的距离进行比较即可。可参考证...

    用户1147447
  • LAMP平台基于NFS实现web服务器负载均衡

    前言 随着访问量的逐渐增大,一台web服务器可能已经无法满足需求,这就需要增加web服务器的数量了,于是问题就来了:如何保证两台服务器数据同步呢?本文将讲解...

    小小科
  • iOS系统菜单控制器UIMenuController使用简介

       在许多iOS应用中,当用户进行某文字或图片区域的长按操作时,都会弹出一个系统菜单控件,用户可以通过操作菜单控件上的按钮进行数据的复制、剪切、粘贴等操作。系...

    珲少
  • 案例分享:关闭 Oracle 审计时遇到的 Bug 排查与解决

    一重要的生产库长期以来就有各种问题,前段时间刚进行完 PSU190716 的更新,这两天查到发现审计功能对其性能有较大的影响,故客户要求关闭审计功能。我们便申请...

    JiekeXu之路
  • ASP.NET MVC5+EF6+EasyUI 后台管理系统--任务调度系统解析

    任务调度系统是什么:他是一个类似Sql Server的代理,他可以帮助你定时处理一些SQL事务

    用户1149182

扫码关注云+社区

领取腾讯云代金券