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

发布时间:2025-05-22 00:29:21 发布人:远客网络

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

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

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

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

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

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

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

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

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

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

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

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类型,可以为以下任何一种:

SIMPLE:简单SELECT(不使用UNION或子查询)

UNION:UNION中的第二个或后面的SELECT语句

DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询

SUBQUERY:子查询中的第一个SELECT

DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询

DERIVED:导出表的SELECT(FROM子句的子查询)

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

联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:

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。

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

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

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

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

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

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

该列包含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数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

笔者将优化分为了两大类,软优化和硬优化,软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

其中会显示索引和查询数据读取数据条数等信息.

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

类似于创建中间表,增加冗余也是为了减少连接查询.

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1.分析表:使用 ANALYZE关键字,如ANALYZE TABLE user;

2.检查表:使用 CHECK关键字,如CHECK TABLE user [option]

option只对MyISAM有效,共五个参数值:

3.优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user;

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力.

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表+读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

三、explain | 索引优化的这把绝世好剑,你真的会用吗

在互联网公司中，随着用户量和数据量的持续增长，慢查询问题难以避免。慢查询不仅导致接口响应速度减慢、接口超时，还会在高并发场景下引发数据库连接占用过多，从而直接影响服务的可用性。为了优化慢查询，主要解决方法包括但不限于索引优化，它是解决SQL慢查询问题最有效的手段之一。那么，如何查看SQL的索引执行情况呢？通过在SQL语句前添加“EXPLAIN”关键字，我们就能看到其执行计划，从而清晰地了解表和索引的执行情况，包括索引是否被使用、执行顺序以及索引的类型等。接下来，我们来详细探讨索引优化的步骤和方法。

首先，让我们全面了解“EXPLAIN”关键字。通过官方文档，我们可以看到“EXPLAIN”的语法和用途。使用一条简单的SQL语句，如“EXPLAIN SELECT* FROM test1;”，就能直观地看到执行结果。执行结果包括12列信息，每一列的具体含义对于判断索引使用情况至关重要。接下来，我们将逐一介绍这些列的含义。

在解释执行结果的列时，我们首先关注的是“id”列。这列的值代表了查询中的序号，例如1、2、3等，它决定了表的执行顺序。在查询执行计划中，常见的三种情况如下：1.当id相同，执行顺序是从上到下；2.当id不同，执行顺序是从下到上；3.当id既相同又不同，先执行序号大的，然后再按照从上到下的顺序执行。了解执行顺序对于优化查询至关重要。

接下来，我们来探讨“select_type”列，它表示SELECT的类型。该列包含了11种类型，其中常用的有：SIMPLE、DEPENDENT SUBQUERY、DERIVED、SUBQUERY和UNION等。在了解这些类型的具体实现方式后，我们可以通过一个嵌套查询的例子，如“EXPLAIN SELECT* FROM test1 t1 WHERE t1.id=(SELECT id FROM test1 t2 WHERE t2.id= 2);”，来进一步理解它们是如何在查询中出现的。通过观察执行结果，我们可以清晰地看到外部查询（t1）和内部查询（t2）的类型。

在探讨查询中的表名时，“table”列显得尤为重要。它表示输出行所引用的表的名称，如“test1”、“test2”等。此外，还有一些特定值，如PARTITIONS，表示查询将从中匹配记录的分区。通过理解这些列的含义，我们能够更深入地分析SQL执行计划。

接下来，我们关注“type”列，它表示连接类型，是查看索引执行情况的关键指标。连接类型包括：SYSTEM、CONSTANT、EQUAL REFERENCE、REFERENCE、RANGE、INDEX和ALL。执行结果从最好到最坏的顺序是从上到下。为了更好地掌握这些类型，我们需要关注下面几种类型：SYSTEM> CONSTANT> EQUAL REFERENCE> REFERENCE> RANGE> INDEX> ALL。

在演示之前，我们先构建一个简单的测试场景：假设test2表中只有一条数据，且在“code”字段上建立了一个普通索引。接下来，我们逐一探讨常见的连接类型是如何出现的。例如，“EXPLAIN SELECT* FROM test1 t1 INNER JOIN test2 t2 ON t1.id= t2.id;”将产生这样的执行结果，让我们直观地看到“SYSTEM”、“CONSTANT”、“EQUAL REFERENCE”等类型的实现方式。

在“possible_keys”列中，我们看到可能的索引选择。请注意，此列独立于表的顺序，这意味着“possible_keys”在实践中可能无法与生成的表顺序一起使用。如果此列为空，则说明没有相关的索引。此时，我们可以通过检查WHERE子句，了解它是否引用了适合索引的列，以提高查询性能。

“key”列表示实际使用的索引。有时，我们可能会遇到“possible_keys”列为空，但“key”不为空的情况。在演示之前，我们先查看test1表的结构、数据以及使用的索引。然后，执行SQL语句“EXPLAIN SELECT code FROM test1;”，我们会看到查询预计没有使用索引，但实际使用了全索引扫描方式的索引。

在“key_len”列中，我们查看使用索引的长度。通过比较“key”列和“key_len”列，我们可以更深入地理解索引的使用是否充分。关键问题是，“key_len”是如何计算的？关键因素包括字符集、长度和是否为空。例如，通过将“code”字段类型改为“char”并允许为空，执行SQL语句“EXPLAIN SELECT code FROM test1;”，我们可以直观地看到计算结果。

在“ref”列中，我们关注索引命中的列或常量。例如，在SQL语句“EXPLAIN SELECT* FROM test1 t1 INNER JOIN test1 t2 ON t1.id= t2.id WHERE t1.code='001';”中，我们看到表t1命中的索引类型为“CONSTANT”，而表t2命中的索引类型为“列”，即“t1表的id字段”。

在讨论“rows”列时，我们查看MySQL认为执行查询需要检查的行数。对于InnoDB表而言，此数值为估计值，可能并不总是准确的。在分析查询性能时，“filtered”列提供了按表条件过滤的表行的估计百分比，最大值为100，表示未过滤行；值从100减小表示过滤量增加。通过“rows”和“filtered”的乘积，我们可以计算与下表连接的行数。

最后，我们关注“extra”列，它包含有关MySQL如何解析查询的其他信息，对优化SQL查询具有重要意义。通过理解这些列的含义和常见值，我们可以更深入地分析SQL执行计划并进行优化。索引优化的过程包括定位需要优化的SQL、查看索引使用情况、关注关键列（如“key”、“key_len”、“type”和“extra”）以及优化SQL，最终回到重新分析执行计划的过程。