前期回顾:
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列,表示查询中执行select子句或操作表的顺序。
|
|
|
查询类型,主要是用于区分普通查询、联合查询、子查询等复杂的查询。
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
索引中使用的字节数,查询中使用的索引的长度(最大可能长度),并非实际使用长度,理论上长度越短越好。
|
|
|
|
|
|
|
|
|
|
|
|
|
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
列有如下值:
|
|
|
|
|
|
|
|
查询中包含任何复杂的子部分,最外层查询则被标记为PRIMARY
|
|
|
|
|
|
表示包含在from子句的子查询中的select,MySQL会递归执行并将结果放到一个临时表中,称其为“派生表”,因为该临时表是从子查询中派生而来的。
|
|
|
第二个select出现在UNION之后,则被标记为UNION。
|
|
|
|
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
类似,扫描表时按索引次序进行,而不是按行扫描,即:只遍历索引树。
index
与
ALL
虽然都是读全表,但
index
是从索引中读取,而ALL是从硬盘读取。显然,
index
性能上优于
ALL
,
合理的添加索引将有助于性能的提升
。
举例如下:
explain select title from film;
explain select description from film;
通过explain结果来看,只查询表
film
中字段
title
时,是按照索引扫描的(
type
列为
index
),倘若查询字段
description
,却是按照全表扫描的(
type
列为
ALL
)。这是为何呢?