MySQL EXPLAIN 命令: 查看查询执行计划

2023-11-12 来源：花图问答

eq_ref: 使用 PRIMARY KEY 或 UNIQUE KEY 进行关联查询。示例: SELECT * FROM post JOIN user ON post.uid = user.id WHERE user.gender = ‘M‘;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE post ALL idx_uid 0 0 0 57796 null
1 SIMPLE user eq_ref PRIMARY PRIMARY 8 post.uid 1 Using where
ref: 使用允许重复的索引进行查询示例: SELECT * FROM user WHERE phone=‘12345678901‘;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_phone idx_phone 259 const 1 Using index condition
range: 使用索引进行范围查询:示例: SELECT * FROM user WHERE age>18;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_age idx_age 259 const 1 null
index: 在索引上进行顺序扫描。常见于在多列索引中未使用最左列进行查询。示例: SELECT * FROM user WHERE last_name=‘smith‘
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE user ref idx_full_name idx_full_name 259 const 1 Using where
all: 扫描全表，最坏的情况

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	post	ALL	idx_uid	0	0	0	57796	null
1	SIMPLE	user	eq_ref	PRIMARY	PRIMARY	8	post.uid	1	Using where

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ref	idx_phone	idx_phone	259	const	1	Using index condition

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ref	idx_age	idx_age	259	const	1	null

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	user	ref	idx_full_name	idx_full_name	259	const	1	Using where

extra

extra 列显示了查询过程中需要执行的其它操作，有些情况应尽力避免。

using filesort: 查询时执行了排序操作而无法使用索引排序。虽然名称为‘file‘但操作可能是在内存中执行的，取决是否有足够的内存进行排序。应尽量避免这种filesort出现。

using temporary: 使用临时表存储中间结果，常见于ORDER BY和GROUP BY语句中。临时表可能在内存中也可能在硬盘中，应尽量避免这种操作出现。

using index: 索引中包含查询的所有列(覆盖索引)不需要查询数据表。可以加快查询速度。

using index condition: 索引条件推送(MySQL 5.6 新特性)，服务器层将不能直接使用索引的查询条件推送给存储引擎，从而避免在服务器层进行过滤。

using where: 服务器层对存储引擎返回的数据进行了过滤

distinct: 优化distinct操作，查询到匹配的数据后停止继续搜索

MySQL EXPLAIN 命令: 查看查询执行计划

标签：相同方式简单 mysql 5.6 pos range 推送使用临时

小编还为您整理了以下内容，可能对您也有帮助：

MySql中如何使用explain查询SQL的执行计划

explain命令是查看查询优化器如何决定执行查询的主要方法。

这个功能有局限性，并不总会说出，但它的输出是可以获取的最好信息，值得花时间去了解，因为可以学习到查询是如何执行的。

1、什么是MySQL执行计划

要对执行计划有个比较好的理解，需要先对MySQL的基础结构及查询基本原理有简单的了解。

MySQL本身的功能架构分为三个部分，分别是应用层、逻辑层、物理层，不只是MySQL ，其他大多数数据库产品都是按这种架构来进行划分的。

应用层，主要负责与客户端进行交互，建立链接，记住链接状态，返回数据，响应请求，这一层是和客户端打交道的。

逻辑层，主要负责查询处理、事务管理等其他数据库功能处理，以查询为例。

首先接收到查询SQL之后，数据库会立即分配一个线程对其进行处理，第一步查询处理器会对SQL查询进行优化，优化后会生成执行计划，然后交由计划执行器来执行。

计划执行器需要访问更底层的事务管理器，存储管理器来操作数据，他们各自的分工各有不同，最终通过调用物理层的文件获取到查询结构信息，将最终结果响应给应用层。

物理层，实际物理磁盘上存储的文件，主要有分文数据文件，日志文件。

通过上面的描述，生成执行计划是执行一条SQL必不可少的步骤，一条SQL性能的好坏，可以通过查看执行计划很直观的看出来，执行计划提供了各种查询类型与级别，方面我们进行查看以及为作为性能分析的依据。

2、如何分析执行计划

MySQL为我们提供了 explain 关键字来直观的查看一条SQL的执行计划。

explain显示了MySQL如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

下面我们使用 explain 做一个查询，如下：

mysql> explain select * from payment;

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+---------+------------+------+---------------+------+---------+------+-------+----------+-------+

1 row in set, 1 warning (0.01 sec)

查询结构中有12列，理解每一列的含义，对理解执行计划至关重要，下面用一个表格的形式进行说明。列名

说明

SELECT识别符，这是SELECT的查询序列号。 select_type

SELECT类型,可以为以下任何一种: SIMPLE:简单SELECT(不使用UNION或子查询) PRIMARY:最外面的SELECT UNION:UNION中的第二个或后面的SELECT语句 DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询 UNION RESULT:UNION 的结果 SUBQUERY:子查询中的第一个SELECT DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询 DERIVED:导出表的SELECT(FROM子句的子查询)

table

输出的行所引用的表 partitions

如果查询是基于分区表的话，显示查询将访问的分区。 type

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序: system:表仅有一行(=系统表)。这是const联接类型的一个特例。 const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次! eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。 ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。 ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。 index_merge:该联接类型表示使用了索引合并优化方法。 unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。 index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr) range:只检索给定范围的行,使用一个索引来选择行。 index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。 ALL:对于每个来自于先前的表的行组合,进行完整的表扫描，说明查询就需要优化了。一般来说，得保证查询至少达到range级别，最好能达到ref。 possible_keys

指出MySQL能使用哪个索引在该表中找到行 key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。 key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。在不损失精确性的情况下，长度越短越好 ref

显示使用哪个列或常数与key一起从表中选择行。 rows

显示MySQL认为它执行查询时必须检查的行数。多行之间的数据相乘可以估算要处理的行数。 filtered

显示了通过条件过滤出的行数的百分比估计值。 Extra

该列包含MySQL解决查询的详细信息 Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。 Select tables optimized awayMySQL根本没有遍历表或索引就返回数据了，表示已经优化到不能再优化了 Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。 range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。 Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行，说明查询就需要优化了。 Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。 Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果，说明查询就需要优化了。 Using where:WHERE 子句用于哪一个行匹配下一个表或发送到客户。 Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。 Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

总结

以上所述是小编给大家介绍的MySql中如何使用 explain 查询 SQL 的执行计划，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对脚本之家网站的支持！您可能感兴趣的文章:MySQL查询优化之explain的深入解析mysql中explain用法详解mysql总结之explainMySQL性能分析及explain的使用说明Mysql中explain作用详解Mysql之EXPLAIN显示using filesort介绍MySQL中通过EXPLAIN如何分析SQL的执行计划详解MYSQL explain 执行计划详解MySQL中EXPLAIN解释命令及用法讲解MySQL性能优化神器Explain的基本使用分析怎么看,分析MYSQL的执行计划?

怎么看,分析MYSQL的执行计划?

Mysql学会查看sql的执行计划

首先在Mysql的服务中有连接器、查询缓存(Mysql8 已经删除)、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现

而一条sql怎么执行是由优化器决定的, 优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

而执行计划就是优化器优化后的sql的执行的详细方案

Mysql中查看执行计划的方式有两种 : 1. 使用desc 2.使用 explain 使用它俩的效果是一样的

接下来要通过执行计划知道sql是怎么执行的

执行计划中有几个重要的字段, 分别是

id, table, type, possible_keys, key, key_len, Extra

id : 可以通过ID来查看在多表联查中sql是先查询哪张表的 id相同的从上往下依次执行,id不同的id大的先执行

table : table当然就是查询的表名

type : 查询的类型查询类型分为 ALL, index, range, ref , eq_ref, const(system), null

ALL: 指的全盘扫描,没有走任何索引查询结果集大于25% 优化器可能会走全盘扫描字符串查询的时候一定要加"" 不然可能会全索引扫描(隐式转换) 统计信息失效或者过旧也可能走全盘扫描因为优化器会参考统计信息来制定执行计划

index: 全索引扫描就是扫描整颗索引树

range: 索引范围查询索引树的一部分范围范围索引中 > < <= >= like 的效率会比 or in 的效率高, 使用like %再前面的不走索引

ref: 辅助索引的等值查询

当查询的数据量小,优化器也有可能会走索引的全盘扫描这里我就不贴图了;

eq_ref : 多表连接查询中,被连接的表的连接条件列是主键或者唯一键

const(system): 主键或者唯一键的等值查询

null: 没有数据

他们的性能是依次递增的全盘扫描性能最差, const性能最高

possible_keys: 查询过程中可能用到的索引

key: 真正使用到的索引

key_len: 走索引的长度

这个是怎么计算的呢?

key_len 的计算方法 :

int 类型最长存储4个字节长度的数字有not null 是4字节没有的话会花1字节存储是不是null

tinyint 最大存储一个字节也会花1字节来判断是不是null

字符串类型 : 字符集 utf8mb4 1-4字节

varchar超过255会预留2个字节存储长度没超预留1个字节

key_len 永远是你设置的长度的最大的

联合索引可以通过key_len 来判断走了几个索引

使用desc format=json select * from table 可以查看详细情况

filtered: 索引扫描过滤掉数据的占比

Extra: 额外的信息

Using filesort ：MySQL 对数据在sql层进行了排序，而不是按照表内的索引进行排序读取。效率比较低

Using temporary ：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。

Using index ：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。

Using index condition ：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。

Using where ：表示 SQL 操作使用了 where 过滤条件。

Select tables optimized away ：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。

Using join buffer (Block Nested Loop) ：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算

怎么去看懂mysql的执行计划

mysql的查看执行计划的语句很简单，explain+你要执行的sql语句就OK了。

举一个例子

EXPLAIN SELECT * from employees where employees.gender='M'

返回的结果如下：

这些结果都代表什么？

id是一组数字，表示查询中执行select子句或操作表的顺序。

如果id相同，则执行顺序从上至下。

如果是子查询，id的序号会递增，id越大则优先级越高，越先会被执行。

id如果相同，则可以认为是一组，从上往下顺序执行，所有组中，id越高，优先级越高，越容易执行。

selecttype有simple，primary，subquery，derived(衍生)，union，unionresult。

simple表示查询中不包含子查询或者union。

当查询中包含任何复杂的子部分，最外层的查询被标记成primary。

在select或where列表中包含了子查询，则子查询被标记成subquery。

在from的列表中包含的子查询被标记成derived。

显示全文

全部频道

MySQL EXPLAIN 命令: 查看查询执行计划