MySQL数据库执行计划cost mysql执行计划参数

文章目录

• 简介
• 常用参数详细解析

• id
• select_type
• table
• type
• possible_keys和key
• key_len
• ref
• rows
• filtered
• Extra

• No tables used
• Impossible WHERE
• No matching min/max row：
• Using index
• Using index condition
• Using where：
• Zero limit
• Using filesort
• Using temporary

简介

一条查询语句在经过MySQL查询优化器的各种基于成本和规则的优化会后生成一个所谓的执行计划。

EXPLAIN 语句 就可以看到某个查询数据的执行计划，以下是执行计划的各个参数：

列名	描述
id	在一个查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SELECT 关键字对应的那个查询类型
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际使用的索引
key_len	实际使用的索引长度
ref	当使用索引为等值查询的时候，与索引列进行等值查询的对象信息
rows	预估的需要读取的记录条数
filter	某个表经过搜索条件过滤后剩余条数的百分比
Extra	一些额外的信息

常用参数详细解析

测试表创建

CREATE TABLE `order_record` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `user_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
    `order_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '订单ID',
    `good_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID',
    `mobile` varchar(32) NOT NULL DEFAULT '' COMMENT '手机号',
    PRIMARY KEY (`id`),
    KEY `idx_user_id` (`user_id`),
    UNIQUE KEY `uniq_order_id` (`order_id`)
) Engine=InnoDB CHARSET=utf8mb4 COMMENT='订单表';

CREATE TABLE `goods` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
    `good_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID',
    `nums` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品数量',
    `good_name` varchar(255) NOT NULL DEFAULT '' COMMENT '商品名称',
    PRIMARY KEY (`id`),
        UNIQUE KEY `uniq_good_id` (`good_id`)
) Engine=InnoDB CHARSET=utf8mb4 COMMENT='订单表';

id

id规则：

• 查询语句中每出现一个SELECT关键字，MySQL就会为它分配一个唯一的id值(可能会相同)
• id值越大，优先级越高，越先执行

不同的查询方式下的id值：

• 对于连接查询来说，一个SELECT关键字后边的FROM子句中可以跟随多个表，连接查询的执行计划中，每个表都会对应一条记录，但是这些记录的id值都是相同的，出现在前边的表表示驱动表，出现在后边的表表示被驱动表

explain select * from goods join order_record;

• 对于包含子查询的查询语句来说，可能涉及多个SELECT关键字，在包含子查询的查询语句的执行计划中，每个SELECT关键字都会对应一个唯一的id值。需要注意查询优化器可能对涉及子查询的查询语句进行重写，从而转换为连接查询。

explain SELECT * FROM order_record WHERE id IN (SELECT good_id FROM goods) OR mobile="122";

//优化器重写
   explain SELECT * FROM order_record WHERE id IN (SELECT good_id FROM goods);

• 对于包含UNION子句的查询语句来说，每个SELECT关键字对应一个id值也是没错的，但是最后一行是NULL的，因为使用了临时表，它会把多个查询的结果集合并起来并对结果集中的记录进行去重，id为NULL表明这个临时表是为了合并两个查询的结果集而创建的。

//去重，用到临时表
	explain SELECT * FROM order_record UNION SELECT * FROM order_record;

UNION ALL就不需要为最终的结果集进行去重，所以没有id为NULL的这一行。

//不去重，不用临时表
	explain SELECT * FROM order_record UNION ALL SELECT * FROM order_record;

select_type

select_type来代表查询属性，就知道了小查询在整个大查询中扮演了个什么角色。

• SIMPLE ：查询语句不包含UNION或者子查询都算作SIMPLE类型
• PRIMARY：对于包含UNION、UNION ALL或者子查询的大查询来说，最左边的查询的select_type就是PRIMARY。
• UNION：对于UNION、UNION ALL来说，除了最左边的查询，其余的都是UNION
• UNION RESULT：MySQL使用临时表来完成UNION的去重查询工作，对该临时表的查询就是UNION RESULT
• SUBQUERY: 如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY

explain SELECT * FROM order_record WHERE id IN (SELECT good_id FROM goods) OR mobile="122";

• DEPENDENT SUBQUERY：如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY

explain SELECT * FROM order_record WHERE id IN (SELECT good_id FROM goods WHERE goods.good_id=order_record.good_id) OR mobile="122";

table

EXPLAIN语句输出的每条记录都对应着某个单表的访问方法。
table列代表表名。

type

• system：当表中仅有一条记录且该表使用的存储引擎统计数据是精确的，比如MyISAM,Memory，那么我们访问方法就是system.
• const: 根据主键或唯一索引和常数进行等值匹配时，对单表的访问是const

explain SELECT * FROM order_record WHERE id = 10;

• eq_ref：连接查询时，被驱动表是通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果是联合索引，必须所有的索引列都是等值比较），被驱动表访问方式为eq_ref

explain SELECT * FROM goods join order_record ON goods.id=order_record.id;

• ref:当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是ref

explain SELECT * FROM order_record  where user_id=10;

• ref_or_null：当对普通二级索引进行等值匹配查询，该索引列的值也可以是NULL值时，那么对该表的访问方法就可能是ref_or_null
• index_merge：一般情况下对于某个表的查询只能使用到一个索引，但我们唠叨单表访问方法时特意强调了在某些场景下可以使用Intersection、Union、Sort-Union这三种索引合并的方式来执行查询.
• unique_subquery:类似于两表连接中被驱动表的eq_ref访问方法，unique_subquery是针对在一些包含IN子查询的查询语句中，如果查询优化器决定将IN子查询转换为EXISTS子查询，而且子查询可以使用到主键进行等值匹配的话，那么该子查询执行计划的type列的值就是unique_subquery
• index_subquery:index_subquery与unique_subquery类似，只不过访问子查询中的表时使用的是普通的索引，
• range：如果使用索引获取某些范围区间的记录，那么就可能使用到range访问方法
• index:当我们可以使用索引覆盖，但需要扫描全部的索引记录时，该表的访问方法就是index。
• ALL：全表扫描

注：对于使用InnoDB存储引擎的表来说，二级索引的记录只包含索引列和主键列的值，而聚簇索引中包含用户定义的全部列以及一些隐藏列，所以扫描二级索引的代价比直接全表扫描，也就是扫描聚簇索引的代价更低一些。

possible_keys和key

possible_keys 对某个表进行单表查询时可能用到的索引有哪些，key表示实际用到的索引有哪些，

使用index访问方法来查询某个表时，possible_keys列是空的

possible_keys列中的值并不是越多越好，可能使用的索引越多，查询优化器计算查询成本时就得花费更长时间，所以如果可以的话，尽量删除那些用不到的索引。

key_len

表示当优化器决定使用某个索引执行查询时，该索引记录的最大长度。

• 固定长度类型的索引是实际存储空间的固定值，指定字符集的变长类型的的索引例如varchar(100) 为100*3（utf8）字节。
• 索引列可以存储为NULL值比不可以存储NULL多1个字节
• 对于变长字段，都有2个字节的空间来存储该变长字段的实际长度。

key_len列主要是为了让我们区分某个使用联合索引的查询具体用了几个索引列

ref

当使用索引列等值匹配的条件去执行查询时，也就是在访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列。

rows

查询优化器全表扫描的时候，代表的是预计需要扫描的行数
如果使用索引来查询是，代表的是预计扫描的索引记录的行数。

filtered

Extra

No tables used

查询语句的没有FROM子句时

Impossible WHERE

查询语句的WHERE子句永远为FALSE

No matching min/max row：

当查询列表处有MIN或者MAX聚集函数，但是并没有符合WHERE子句中的搜索条件的记录

Using index

使用覆盖索引的情况

explain select id,user_id from order_record where user_id=4;

Using index condition

查询过程中索引条件下推的情况。例如：

有些搜索条件中虽然出现了索引列，但却不能使用到索引，比如下边这个查询：

SELECT * FROM order_record WHERE user_id > 1761041 AND user_id LIKE '%104104';

虽然user_id LIKE ‘%104104’ 不能组成范围区间参与range访问方法的执行，但这个条件毕竟只涉及到了user_id 列，所以设计MySQL的设计如下：

• 先根据key1 > 'z’这个条件，定位到二级索引idx_key1中对应的二级索引记录。
• 对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足key1 LIKE '%a’这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
• 对于满足key1 LIKE '%a’这个条件的二级索引记录执行回表操作。

Using where：

• 使用全表扫描来执行对某个表的查询，并且该语句的WHERE子句中有针对该表的搜索条件
• 当使用索引访问来执行对某个表的查询，并且该语句的WHERE子句中有除了该索引包含的列之外的其他搜索条件时

explain select * from order_record where good_id=10;

explain select * from order_record where user_id=4 and good_id=10

Zero limit

当我们的LIMIT子句的参数为0时，表示压根儿不打算从表中读出任何记录，将会提示该额外信息

Using filesort

如果某个查询需要用不到索引之后，需要排序等复杂操作的时候，在内存中或者磁盘上进行排序的方式。

Using temporary

许多包含DISTINCT、GROUP BY、UNION等子句的查询过程中，如果用不到索引，就有可能使用到内部的临时表。