文章目录

MySQL执行计划是MySQL提供的一种查看SQL与数据库交互行为的工具。

可以很直观的帮助开发者发现问题并进行SQL优化。

最常用的是EXPLAIN命令。

使用EXPLAIN关键字可以模拟优化器的行为返回MySQL如何使用索引进行搜索和表的连接顺序JOIN)并且可以让我们知道在MySQL解析我们的查询时每个动作后的信息。

查询执行计划信息包含

表的读取顺序数据读取操作的操作类型哪些索引可以被用到哪些索引被实际使用表之间的引用每张表有多少行被优化器查询

当我们使用EXPLAIN开头对查询做分析预测后解析的结果能告诉我们

哪些部分或者说哪个环节消耗的时间多哪些索引被使用哪些没有被使用上查询与表的行数和读取方式总之就是看待查询各项操作的代价如何

要理解和解释执行计划就需要了解它包含的一些关键信息并可以对其进行适当的优化。

例如在合适的地方建立索引改变JOIN顺序重写查询等都可以改善查询的效率。

基本介绍

执行计划是MySQL数据库在执行SQL查询时的一个操作步骤集合。

它描述了数据库如何执行SQL语句以及如何从数据表中检索或更新数据。

执行计划包括了多种信息如数据读取的顺序数据过滤的方式连接表的方式等。

如何查看执行计划

在MySQL中可以通过在查询语句前面添加EXPLAIN关键字来查看该查询的执行计划。

例如

EXPLAIN

id查询的序列号表示查询的执行顺序。

select_type查询的类型例如SIMPLE简单查询PRIMARY主查询SUBQUERY子查询等。

table输出结果集的表。

type连接类型表示MySQL在表中找到所需行的方式常见的类型有ALL全表扫描index全索引扫描range范围扫描等。

possible_keys可能应用的索引。

key实际应用的索引如果为NULL则没有使用索引。

key_len表示索引字段的长度如果为NULL则表示不使用索引。

ref显示关键字的比较是常数还是字段等。

rows根据表统计信息及索引选用情况大致估算出查找所需读取的行数。

Extra包含MySQL解决查询的详细信息如Using

这是查询的标识符代表查询的组执行顺序。

相同的id说明在同一执行阶段。

select_type

这个列标明查询的类型。

常见的查询类型有SIMPLE简单查询不包含子查询或者UNION操作,

PRIMARY主查询外层查询,

这是表示MySQL如何对表的行进行遍历的类型。

各种类型从最好到最坏分别是system

const

filesort等。

这些都是MySQL优化器在生成查询执行计划时的一些策略说明

Using

where表示MySQL服务器将在存储引擎检索行后再进行过滤这说明查询中有一个WHERE子句并且存储引擎不会自动过滤这些行。

Using

temporary表示MySQL需要使用一个临时表来存储结果集这通常发生在对不同列的多次排序时。

Using

filesort表示MySQL需要进行额外的步骤来找出如何检索行因为它不能仅通过索引来完成。

这通常出现在ORDER

BY或GROUP

BY查询中当无法使用索引顺序来完成排序时会进行额外的排序步骤。

temporary和Using

Index即所有需要的数据都被直接从索引中读取而无需从数据表中读取。

使用覆盖索引可以大大提高查询性能。

Using

buffer表示使用了连接缓冲这是MySQL优化连接操作的一种方式可以提高连接多张表的效率。

Using

union表示使用排序合并算法进行OR操作该算法可以在不同的索引之间进行排序和合并操作。

Using

Pushdown即将部分WHERE子句条件在存储引擎层进行计算避免了不必要的行扫描和传输。

MySQL查询优化器常见的一些策略。

这些策略并非越多越好而是根据具体查询和数据表结构来选择最适合的策略。

优化器的目标是尽可能减少查询处理所需的磁盘I/O和CPU时间。

底层原理

MySQL的执行计划基于查询优化器它的主要任务是找到执行SQL查询的最优方式。

优化器会考虑各种可能的执行计划比如使用哪个索引以何种顺序连接表等等然后根据估算的成本选择一个最优的执行计划。

当你执行一个SQL查询时MySQL首先会解析这个查询检查语法是否正确然后生成一个对应的内部数据结构我们称之为解析树。

接着优化器会使用各种规则和启发式方法对解析树进行优化生成一个或多个可能的执行计划。

每个执行计划都对应一个可能的查询执行路径包括使用哪个索引以何种顺序连接表等等。

然后优化器会对每个执行计划进行成本估算这个成本主要基于读取数据的数量也就是IO操作的数量。

此外优化器还会考虑CPU消耗内存消耗等因素。

最后优化器会选择成本最低的执行计划来执行这个查询。

执行计划中的每个步骤都会被转换为一系列的底层操作比如读取磁盘上的数据执行计算等等这些操作最终由MySQL的存储引擎来执行。

当你使用EXPLAIN命令查看执行计划时你看到的就是优化器生成的这个最优执行计划的详细信息。

执行计划示例解读

DESC;这个查询涉及到多个表的连接并使用了聚合函数和分组操作。

查询的目标是获取每个产品的名称、所属类别、供应商名称以及总销量并按照销量进行降序排序。

JOIN语句将四个表(products,

order_details)连接起来使用ON子句指定连接的条件。

然后通过GROUP

BY子句对产品名称、类别名称和供应商名称进行分组使用SUM函数计算每个组别的总销量。

最后在HAVING子句中对总销量进行筛选只返回销量大于100的数据。

最后使用ORDER

这个复杂的查询可以用于分析产品销售情况找出销量最高的产品并了解它们所属的类别和供应商。

EXPLAIN

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra1SIMPLEpALLNULLNULLNULLNULL1000Using

temporary;

filesort1SIMPLEcrefPRIMARYPRIMARY4db.p.category_id1NULL1SIMPLEseq_refPRIMARYPRIMARY4db.p.supplier_id1NULL1SIMPLEodrefp2prod_id4db.p.product_id10Using

Where

productscategoriessuppliersorder_details

pproducts要对行进行全表扫描(类型为ALL)结果集预计的行数为1000并且需要额外的操作temporary和filesort用来处理GROUP

BY和ORDER

均是使用了ref查找方式对应索引PRIMARY意味着它们确认supplier_id

category_id是作为索引存在的并且每一个可供该基表使用的联接列都参与了索引查找在联接时每找出一行。

表

(order_details)使用了索引prod_id该操作与提供的prod_id匹配行并在对结果进行执行并过滤掉结果中未满足WHERE子句条件的记录。

products

添加以product_id作为主键的索引用于减少全表扫描的影响。

本文知识图谱

表的读取顺序数据读取操作的操作类型可用的索引与实际使用的索引表之间的引用优化器查询的行数

id查询的执行顺序select_type查询的类型table输出结果集的表typeMySQL在表中找到所需行的方式possible_keys可能应用的索引key实际应用的索引key_len索引字段的长度ref关键字的比较rows查找所需读取的行数ExtraMySQL解决查询的详细信息

底层原理

基于查询优化器优化器任务找到执行SQL查询的最优方式执行计划优化器生成的最优执行计划的详细信息