阅读 15394

这一次,彻底读懂Mysql执行计划

写在最前面

本文主要介绍mysql执行计划各个字段的作用,以及如何利用Mysql执行计划优化我们的Sql,本文主要面向后端开发程序员,以及需要和数据库打交道的同学们。

1、什么是Mysql执行计划

所谓的执行计划就是Mysql如何执行一条Sql语句,包括Sql查询的顺序、是否使用索引、以及使用的索引信息等内容。一个例子:

基本语法

explain select ...
复制代码

一些变体

explain extended select ...
复制代码

上述的语句是将表格形式的执行计划转化成 select语句,在使用 show warnings可以得到mysql优化器优化后的查询语句。

explain partitions select ...
复制代码

用于分区表的EXPLAIN

2、执行计划包含的信息

不同版本的Mysql和不同的存储引擎执行计划不完全相同,但基本信息都差不多。mysql执行计划主要包含以下信息:

2.1 id

有一组数字组成。表示一个查询中各个子查询的执行顺序;

  • id相同执行顺序由上至下。

  • id不同,id值越大优先级越高,越先被执行。

  • id为null时表示一个结果集,不需要使用它查询,常出现在包含union等查询语句中。

2.2 select_type

每个子查询的查询类型,一些常见的查询类型。

id select_type description
1 SIMPLE 不包含任何子查询或union等查询
2 PRIMARY 包含子查询最外层查询就显示为 PRIMARY
3 SUBQUERY selectwhere字句中包含的查询
4 DERIVED from字句中包含的查询
5 UNION 出现在union后的查询语句中
6 UNION RESULT 从UNION中获取结果集,例如上文的第三个例子

2.3 table

查询的数据表,当从衍生表中查数据时会显示<derivedx> x 表示对应的执行计划id

2.4 partitions

表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子:

create table tmp (
    id int unsigned not null AUTO_INCREMENT,
    name varchar(255),
    PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;
复制代码

2.5 type

访问类型

  • ALL 扫描全表数据
  • index 遍历索引
  • range 索引范围查找
  • index_subquery 在子查询中使用 ref
  • unique_subquery 在子查询中使用 eq_ref
  • ref_or_nullNull进行索引的优化的 ref
  • fulltext 使用全文索引
  • ref 使用非唯一索引查找数据
  • eq_refjoin查询中使用PRIMARY KEYorUNIQUE NOT NULL索引关联。

  • const 使用主键或者唯一索引,且匹配的结果只有一条记录。
  • system const 连接类型的特例,查询的表为系统表。

2.6 possible_keys

可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

2.7 key

显示MySQL在查询中实际使用的索引,若没有使用索引,显示为NULL。

TIPS:查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在key列表中

2.8 key_length

索引长度 char()、varchar()索引长度的计算公式:

(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
复制代码

其他类型索引长度的计算公式: ex:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL DEFAULT '',
  `age` int(11),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx` (`name`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
复制代码

name 索引长度为: 编码为utf8mb4,列长为128,不允许为NULL,字段类型为varchar(128)key_length = 128 * 4 + 0 + 2 = 514;

age 索引长度:int类型占4位,允许null,索引长度为5。

2.9 ref

表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值

2.10 rows

返回估算的结果集数目,并不是一个准确的值。

2.11 extra

extra的信息非常丰富,常见的有: 1.Using index 使用覆盖索引 2.Using where 使用了用where子句来过滤结果集 3.Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。 4.Using temporary 使用了临时表

3 参考文档

Mysql 官方文档

dev.mysql.com/doc/refman/…

文章分类
后端