← Back to list

mysql执行计划

Published on: | Views: 91

在优化mysql执行速度的时候, 执行计划是非常重要的, 通过执行计划可以知道语句慢在哪里, 从而针对性地优化语句.

mysql中, 执行计划是通过explain 命令查看, 加到查询语句前面就可以了, 详细的官方文档见: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

explain 支持的查询命令包括:  select, insert , update, delete, replace.

语法

explain 可以查看表的定义，但这里我们只考虑查询计划：

explainable_stmt: {
    SELECT statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

# 结果查看 建立三个表来做示例 student(id,name) class(id,name) student_class(id,class_id,student_id)

试着执行一下：

explain select * from student_class sc
  join class c on sc.class_id = c.id
  join student s on sc.student_id = s.id;

image.png

一般mysql是通过NLJ(Nested-Loop Join)或者BNLJ(Block Nested-Loop Join)来处理join请求，NLJ算法是这样的，例如一个执行计划如下：

Table   Join Type
t1      range
t2      ref
t3      ALL

查询处理如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

它是在第一个表中读了一行，然后再读第二个表匹配的行。。。直到读取最后一个表， 一条查询结果就出来了， 接着再读第一个表的第二行，如此循环，直到查询出所有结果， 属于深度搜索算法。 BNLJ增加了一个join buffer的缓存，把外层循环的数据缓存成块后，再执行内部循环，加快速度。

执行计划会输出很多列，这些列定义如下： image.png

id

查询号，每个id代表一个查询。 相同id的行，属于同一个查询，从上到下依次是最外层循环，次外层。。。最里层循环。 如果行引用的是union结果时，这个行的id列是空的。

select_type

这个类型是查询类型，其取值如下： image.png - SIMPLE: 查询中不包括union和子查询 - PRIMARY: 最外层的查询 - UNION: union第二条或者后续语句 - DEPENDENT UNION: union第二条或者后续语句，并且依赖外层查询结果 - UNION RESULT：依赖union结果 - SUBQUERY：子查询 - DEPENDENT SUBQUERY: 子查询，并且依赖外层查询结果 - DERIVED： 派生查询 - MATERIALIZED：物化子查询 - UNCACHEABLE SUBQUERY： 无法缓存的子查询 - UNCACHEABLE UNION： 无法缓存的union

table

本行对应的表名，也可以是：

partitions

分区号，如果表分了区的话。

type

这个类型是访问类型， 其取值如下： - system：表中只有一行（系统表） - const: 表中最多匹配到一行， 使用主键或者唯一键与常量比较(=) - eq_ref:表中最多只匹配到一行, 使用表的主键或者非空唯一键与查询结果比较(=) - ref: 表中会匹配到多行，使用表的普通索引与查询结果比较（=,<=>) - fulltext: 使用表的fulltext索引进行比较 - ref_or_null: 和ref一样，只是多了一步使用key和null比较(is null) - index_merge: 表示使用索引合并优化 - unique_subquery：和eq_ref类似， 但是是使用 IN(子查询)的比较方式 - index_subquery： 和ref类似，但是是使用 IN(子查询)的比较方式 - range: 使用表的普通索引和常量比较（=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, IN） - index: 有两种情况，一是查询的数据全部在索引中（Extra data会有use index）， 二是以索引的顺序来进行全表扫描 - all: 全表扫描

possible_keys

可能用到的索引

key

实际使用的索引

key_len

实际用到的索引长度

ref

索引的比较对象，来自于上一层的表

rows

检查的行数

filtered

过滤百分比， 返回行数/检查行数

Extra

额外信息 using index: 数据全部在索引中，将会使用覆盖索引 using where: 使用where进行条件过滤，当条件在索引中时，存储引擎就可以过滤。 using temporary: 对结果排序时使用临时表 using filesort: 使用外部排序，而不是索引排序