提升数据库性能的关键所在！Mysql执行计划解析

目录

?编辑

简介

使用

执行计划信息

id

select_type

table

partitions

type

possible_keys

key

key_len

ref

rows

filtered

Extra

---

简介

在MySQL中，执行计划是指数据库系统根据查询语句和相关数据表的统计信息，预先计算出最优的执行方式。执行计划通常包括选择哪个索引来检索数据、按照什么顺序访问表和索引、使用哪些操作符来连接和过滤数据等。

执行计划是数据库提供给我们的一套对sql语句进行解析、分析、优化功能的工具，他具有以下的作用：

• 展示表的读取顺序；

• 数据读取操作的类型；

• 哪些索引是可以使用的；

• 哪些索引是实际上使用的；

• 表之间的引用关系；

• 每张表被查询的行数。

注意：执行计划只是数据库针对sql给出最佳的优化参考方案，并不一定是最优解，即不要过度相信执行计划

使用

在MySQL中，执行计划是一个对象，可以通过查询EXPLAIN语句的结果来获取。EXPLAIN语句用于分析查询语句的执行计划，以便更好地了解查询是如何执行的。

下面是一个示例查询，演示如何使用EXPLAIN语句获取执行计划：

EXPLAIN SELECT * FROM your_table WHERE column1 = 'value1' AND column2 = 'value2';

在上面的示例中，将your_table替换为实际的表名，column1和column2替换为实际的列名，value1和value2替换为实际的值。执行该查询后，MySQL将返回一张表格，其中包含执行计划的详细信息。

通过分析执行计划的结果，可以了解到查询语句的性能瓶颈，优化查询语句的执行效率。例如，如果发现查询使用了全表扫描，则可以考虑创建适当的索引来提高查询性能。

执行计划信息

执行计划的结果通常包括以下几列：

• id：表示查询中每个操作的序号。
• select_type：表示查询的类型，例如SIMPLE、PRIMARY、SUBQUERY等。
• table：表示相关的表名。
• type：表示访问表的方式，例如ALL、INDEX、RANGE、REF等。
• possible_keys：表示可能使用的索引。
• key：表示实际使用的索引。
• key_len：表示使用的索引长度。
• ref：表示与索引比较的列或常量。
• rows：表示MySQL估计需要扫描的行数。
• Extra：表示其他附加信息，例如是否使用了临时表、是否使用了文件排序等。

id

select查询序列号，有几个select就有几个id，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

select_type

select_type：表示select语句的类型，可以有以下取值;

SIMPLE：表示简单查询，其中不包含子查询、连接、分组或排序操作。
PRIMARY： 表示主查询，当查询中包含任何复杂的子部分，最外层的查询被标记成 primary
UNION：在 union、union all 和子查询中的第二个和后面的 select 会被标记为 union
DEPENDENT UNION：在包含 union 或者 union all 的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的 select_type 的值就是 dependent union
UNION RESULT： 从 union 表获取结果的 select 会被标记为 union result
SEBQUERY：在 select 或者 where 列表中包含子查询（不在from子句中）
DEPENDENT SEBQUERY：子查询中的第一个 select（不在 from 子句中），并且取决于外层查询
DERIVED：在 form 列表中包含的子查询被标记为 derived，也叫做派生类
UNCACHEABLE SEBQUERY：一个子查询的结果不能被缓存
UNCACHEABLE UNION：表示 union 的查询结果不能被缓存
SUBQUERY：子查询，嵌套在外部查询中的查询。
DEPENDENT SUBQUERY：表示子查询依赖于外部查询的结果
MULTIPLE_SUBQUERY：包含多个子查询的查询，每个子查询都返回一个结果集。
EXISTS_SUBQUERY：用于检查子查询是否返回任何结果的查询。
IN_SUBQUERY：子查询作为IN条件的一部分。
LEFT_JOIN_SUBQUERY：子查询作为左连接条件的一部分。
RIGHT_JOIN_SUBQUERY：子查询作为右连接条件的一部分。
FULL_JOIN_SUBQUERY：子查询作为全连接条件的一部分。

table

table：表示查询的表名，可以有以下几种情况：

• 显示表名，如果起了别名，则显示别名；
• <derivenN>：表示查询的条件是一个子查询；
• <union1,2>：表示表1和表2使用union。

partitions

partitions：匹配的分区。

type

type：访问类型，表示以何种方式去访问数据库。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > all

system：表中只有一行记录，相当于系统表，这是const类型的特列，平时不会出现，可以忽略不计；
const：通过索引一次命中，匹配一行数据，所以很快，常?于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的；
eq_ref：唯一性索引扫描，当进行等值联表查询使用主键索引或者唯一性非空索引进行数据查找
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，用于=、<或>操作符带索引的列；
    ref_or_null：字段既需要关联条件，也需要 null 值
    index_merge：在查询过程中需要多个索引组合使用
    unique_subquery：利用唯一索引来关联子查询，不再扫描全表
    index_subquery：利用索引来关联子查询，不再扫描全表
range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>、in等查询，这种范围查询要比index要好，因为他只需要扫描索引的一个点，结束于另外一个点；
index：全索引扫描,需要遍历索引树；
all：即全表扫描，意味着数据库需要从头到尾去查找所需要的?。通常情况下这需要增加索引来进?优化了。

一般来说在进行sql优化的时候至少要优化到range，推荐优化到ref，最好是const。

possible_keys

显示查询可能使用哪些索引来查找，可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提升查询性能，然后 explain 查看效果。

key

实际使用的索引，如果为 null ，则表示没有使用索引，key的值是NULL。可以强制使用索引或者忽略索引。

key_len

key_len：MYSQL使用的索引长度,?字节数，通过这个值可以算出具体使用了索引中的哪些列，数值计算如下：

字符串类型
char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2

数值类型
tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节　　

时间类型　
date：3字节
timestamp：4字节
datetime：8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

注意：索引最大长度是768字节，当字符串过长时，数据库会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref

ref列显示使用哪个列或常数与key一起从表中查询，常见的有：const（常量），func，null，字段名（例：film.id）

rows

这列是数据库估计要读取并扫描的行数，注意这个不是结果集的行数，因此这个值越小越好。

filtered

百分比值, 表示存储引擎返回的数据经过滤后, 剩下多少满足查询条件记录数量的比例.

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

Extra

extra：这一列显示的是额外的信息，包含MySQL解决查询的详细信息。具体值如下：

using filesort：说明 mysql 无法利用索引进行排序，只能利用排序算法进行排序,数据库会对结果使用?个外部索引排序，而不是按索引次序从表里读取行
using index：表示当前的查询是覆盖索引的，直接从索引中读取数据, 即表示select使用了覆盖索引而不必去回表查询；
using where：使用 where 进行条件过滤, 先读取整行数据，再按where 条件进行检查，符合就留下，不符合就丢弃；
using index condition：与Using where类似，查询的列不完全被索引覆盖，where条件中是?个前导列的范围；
using temporary ：建立临时表来保存中间结果，查询完成之后把临时表删除, 这种情况常见于order by和group by。出现这种情况一般是要进行索引优化的
using join buffer：使用连接缓存
impossible where：where 语句的结果总是 false
null：查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现
using sort_union(...), Using union(...), Using intersect(...)：这些函数说明如何为index_merge联接类型合并索引扫描；
distinct：数据库发现第1个匹配行后,停止为当前的行组合搜索更多的行；
not exists：数据库能够对查询进行left join优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行；
range checked for each record (index map: #)：数据库没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用；
using index for group-by：类似于访问表的Using index方式,Using index for group-by表示数据库发现了?个索引,可以用来查 询group by或distinct查询的所有列,?不要额外搜索硬盘访问实际的表；