MySQL 文档翻译：深入解析查询优化策略

更新:11-12 现代故事我要投稿纠错投诉

大家好，MySQL 文档翻译：深入解析查询优化策略相信很多的网友都不是很明白，包括也是一样，不过没有关系，接下来就来为大家分享关于MySQL 文档翻译：深入解析查询优化策略和的一些知识点，大家可以关注收藏，免得下次来找不到哦，下面我们开始吧！

官方文档

MySQL官方文档地址：8.8 理解查询执行计划

引言

MySQL 优化器使用多种技术根据SQL 语句的WHERE 子句中的表、列、索引和条件的详细信息来高效执行SQL 查询。无需读取所有行即可优化SQL 查询。针对大表执行查询；可以执行涉及多个表的联接，而无需比较每个行组合。优化器选择执行最有效查询的操作集称为查询执行计划，也称为EXPLAIN 计划。您的目标是认识到EXPLAIN 计划表明查询已被优化。如果发现一些低效的操作，可以通过学习SQL语法和索引技术来改进查询计划。

使用 EXPLAIN 优化查询

EXPLAIN 语句提供有关MySQL 如何执行目标语句的信息：

EXPLAIN 可与SELECT、DELETE、INSERT、REPLACE 和UPDATE 语句一起使用。当EXPLAIN 与可解释语句（explainable statements）一起使用时，MySQL 显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释指定它将如何处理该语句，包括有关表如何连接以及连接顺序的信息。有关使用EXPLAIN 获取执行计划信息的信息，请参阅第8.8.2 节EXPLAIN 输出格式。当EXPLAIN 与FOR CONNECTION 一起使用时connection_id 当与不可解释的语句一起使用时，它显示在命名连接中执行的语句的执行计划。有关命名连接的执行计划信息，请参阅第8.8.4 节。对于SELECT 语句，使用SHOW WARNINGS 来生成EXPLAIN 并显示其他执行计划信息。请参见第8.8.3 节扩展EXPLAIN 输出格式。 EXPLAIN 对于检查涉及分区表的查询很有用。有关分区的信息，请参阅第24.3.5 节。 FORMAT 选项可用于选择输出格式。传统以表格格式显示输出。如果没有FORMAT 选项，这是默认设置。当FORMAT选项值为JSON时，可以显示JSON格式的信息。借助EXPLAIN，您可以看到应该在表的何处添加索引，以便通过使用索引查找行来更快地执行语句。您还可以使用EXPLAIN 检查优化器是否以最佳顺序连接表。要提示优化器使用与语句中表命名顺序相对应的连接顺序，请从SELECT STRAIGHT_JOIN 语句（而不是SELECT）开始。（请参见第13.2.10 节SELECT 语句。）但是，STRAIGHT_JOIN 可能会阻止使用索引，因为它禁用半连接转换。请参见第8.2.2.1 节使用半连接转换优化IN 和EXISTS 子查询谓词。

优化器跟踪有时可能提供比EXPLAIN 更详细的信息。但是，优化器跟踪格式和内容可能因版本而异。有关详细信息，请参阅MySQL 内部：跟踪优化器。

如果您认为应该使用的索引未在查询计划中使用，请运行ANALYZE TABLE 来更新表统计信息，例如键基数，这可能会影响优化器所做的选择。请参见第13.7.3.1 节ANALYZE TABLE 语句。

Explain 的输出格式

EXPLAIN 为SELECT 语句中使用的每个表返回一行信息。它是按照MySQL在处理语句时读取它们的顺序排列的。这意味着MySQL从第一个表中读取一行，然后从第二个表中读取它。在一个表中找到匹配的行，然后在第三个表中找到，依此类推。处理完所有表后，MySQL 输出选定的列并返回表列表，直到找到具有更多匹配行的表。从此表中读取表中的下一行并继续处理下一个表。

Explain 输出的列

本节描述EXPLAIN 生成的输出列。后面的部分提供有关类型和额外列的附加信息。

EXPLAIN 的每个输出行都提供有关表的信息。每行包含表8.1 的输出列中汇总的值，并在表后进行了更详细的描述。列名称出现在表的第一列中；第二列提供使用FORMAT=JSON 时输出中显示的等效属性名称。

列JSON 名称含义idselect_idSELECT 标识符select_typeNoneSELECT 类型tabletable_name 输出行的表分区匹配分区的分区typeaccess_type Join (join) 类型possible_keyspossible_keys 替代索引keykey 实际选定的索引key_lenkey_length 选定键的长度refref 估计要与索引进行比较的列rows 行检查的行数Filteredfiltered 按表条件过滤的行的百分比（过滤的越多效果越好） ExtraNone 附加信息JSON 格式输出中的NULL 不会显示在JSON 格式的EXPLAIN 信息中。

选择标识符。这是查询中SELECT 的序列号。如果该值为NULL，则表示该行引用其他行的并集结果。在这种情况下，表列显示，表明该行是在id.select_type 上联合的M 行和N 行

SELECT 的类型可以是下表中显示的任何类型。 select_type 值JSON 名称含义SIMPLE 没有任何简单SELECT（不使用UNION 或子查询） PRIMARY 没有任何最外层SELECTUNION 没有任何第二个或从属UNION DEPENDENT UNIONdependent(true 后的SELECT 语句中的第二个或后续SELECT 语句)UNION 取决于外部查询UNION RESULTunion_resultUNION 的结果。 SUBQUERY 在第一个SELECTDEPENDENT SUBQUERYdependent(true) 子查询中没有任何子查询。第一个SELECT，依赖于外部查询DERIVED 没有任何派生表DEPENDENT DERIVEDdependent (true) 派生表依赖于另一个表MATERIALIZEDmaterialized_from_subquery 物化子查询UNCACHEABLE SUBQUERYcacheable (false) 子查询，其结果不能被缓存，必须为每个外部查询的一行重新计算UNCACHEABLE UNIONcacheable(false)UNION中的第二个或后面的SELECT，即不可缓存的子查询（请参阅参考资料UNCACHEABLE SUBQUERY）。 DEPENDENT一般是指使用相关子查询。有关详细信息，请参见第13.2.11.7 节相关子查询

DEPENDENT SUBQUERY 的计算与UNCACHEABLE SUBQUERY 的计算不同。 DEPENDENT SUBQUERY中的子查询只计算外部上下文中不同集合的值一次。但是，UNCACHEABLE SUBQUERY 将重新计算外部上下文中的每一行。

桌子

输出行引用的表的名称。这也可以是以下值之一：此行引用具有M 和N 的派生表结果和. 此行引用具有值N 的派生表结果。例如，派生表可能来自FROM . 中的子查询该行引用N 的物化子查询的结果。请参见第8.2.2.2 节使用Materialization.partitions 优化子查询

查询将匹配记录的分区。如果值为NULL，则表示非分区表。类型

连接类型。有关不同类型的说明，请参阅解释连接类型。联接类型还挺重要的.possible_keys（重要）

possible_keys列表示MySQL可以选择在该表中查找行的索引。请注意，该列完全独立于输出EXPLAIN 中显示的表顺序。这意味着possible_keys 中的某些键实际上可能与生成的表顺序不匹配。一起使用。

如果此列为NULL（或在JSON 格式的输出中未定义），则没有关联的索引。在这种情况下，可以通过检查WHERE子句来检查是否引用了某些或某些适合建立索引的列，从而提高查询的性能。如果是这样，请创建适当的索引并再次EXPLAIN 检查查询。

要查看表有哪些索引，请使用：SHOW INDEX FROM tbl_namekey （重要）

键列表示MySQL实际决定使用的键（索引）。如果MySQL 决定使用possible_keys 索引之一来查找该行，则该索引将被列为键值。

possible_keys 索引中不存在possible key 中的值。如果possible_keys 没有适合查询的索引，但查询选择的所有列都是其他索引的列，则可能会发生这种情况。这也可能发生。也就是说，命名索引覆盖了选定的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更高效。 (感觉就是查询的时候，所有的索引都不太可用，然后就走全表扫描，但是所选的列又是被二级索引覆盖了，所以会优先去扫描二级索引，我理解的是这样的)

对于InnoDB来说，即使查询也选择了主键，二级索引也可能覆盖所选列，因为InnoDB存储了每个二级索引的主键值。如果key为NULL，MySQL找不到可以用来更有效执行的值。查询的索引。

要强制MySQL 使用或忽略possible_keys 列中列出的索引，请在查询中使用FORCE INDEX、USE INDEX 或IGNORE INDEX。

对于MyISAM表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，myisamchk --analyze.key_len也是如此

key_len 列指示MySQL 决定使用的密钥的长度。 key_len 的值使您能够确定MySQL 实际使用多部分密钥的多少部分。如果key 为NULL，则key_len 也为NULL。

由于密钥存储格式的原因，可以为空的密钥的长度比不能为空的密钥的长度多1。

(这个应该是联合索引可以用到前几个的情况)参考

ref 列显示将哪些列或常量与键列中指定的索引进行比较以从表中选择行。

如果该值为func，则使用的值是某个函数的结果。要查看哪个函数，请在EXPLAIN 之后使用SHOW WARNINGS 来展开输出。该函数实际上可能是一个运算符，例如算术运算符.rows

rows 列指示MySQL 认为必须检查才能执行查询的行数。

对于InnoDB 表，此数字是估计值，可能并不总是准确的。已过滤

筛选列指示按表条件筛选的表行的估计百分比。最大值为100，这意味着不过滤任何行。从100开始递减的值代表过滤量的增加。 rows 显示了估计检查的行数，显示了与下表连接的行数。例如，如果行数为1000 并且筛选为50.00 (50%)，则与下表连接的行数为。

此列包含有关MySQL 如何解析查询的附加信息。有关不同值的说明，请参阅解释额外信息。

解释连接类型

EXPLAIN 输出中的类型列描述了表的连接方式。在JSON 格式的输出中，放入access_type 属性。以下列表描述了连接类型，按从最佳到最差的顺序排列：

系统

该表只有一行（=系统表）。这是const 连接类型的特例。const

该表最多有一个匹配行，该行在查询开始时读取。因为只有一行，所以这一行中的列的值可以被优化器的其余部分视为常量。 const 表非常快，因为它们只被读取一次。

当您将PRIMARY KEY 或UNIQUE 索引的所有部分与常量值进行比较时，将使用Const。在以下查询中，tbl_name 可以用作const 表：SELECT * FROM tbl_name WHERE Primary_key=1;

从表名中选择*

WHERE Primary_key_part1=1 AND Primary_key_part2=2;eq_ref（直接查询主键或非空索引）

对于上表中的每个行组合，从该表中读取一行。除了system 和const 类型之外，这是最好的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY 或UNIQUE NOT NULL 索引时就是这种类型。

eq_ref 可用于使用运算符进行比较的索引列。比较值可以是常量，也可以是使用在此表之前读取的表中的列的表达式。在下面的示例中，MySQL可以使用eq_ref连接来处理ref_table:SELECT * FROM ref_table, other_table

WHERE ref_table.key_column=other_table.column;

从ref_table, other_table 中选择*

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1;ref（最左边的前缀或键不是主键或唯一索引）

对于上一个表中的每个行组合，将从该表中读取具有匹配索引值的所有行。如果联接仅使用键的最左边前缀或者键不是PRIMARY KEY 或UNIQUE 索引（换句话说，如果联接不能根据键值选择单个行），则ref。如果使用的键仅匹配几行，那么这是一个很好的联接类型。

ref 可用于使用or=运算符进行比较的索引列。在下面的示例中，MySQL可以使用ref join来处理ref_table:SELECT * FROM ref_table WHERE key_column=expr;

从ref_table,other_table 中选择*

WHERE ref_table.key_column=other_table.column;

从ref_table,other_table 中选择*

WHERE ref_table.key_column_part1=other_table.column

AND ref_table.key_column_part2=1;全文

使用FULLTEXT 索引执行连接。 ref_or_null（与ref相比，加了1，可以为null）

这种连接类型与ref 类似，但此外MySQL 还会搜索包含NULL 值的行。这种连接类型优化最常用于解析子查询。在以下示例中，MySQL 可以使用ref_or_null 连接来处理ref_table:SELECT * FROM ref_table

WHERE key_column=expr 或key_column IS NULL；请参见第8.2.1.15 节IS NULL 优化。

索引合并

这种连接类型表明使用了索引合并优化。在这种情况下，输出行中的键列包含所使用的索引的列表，key_len包含所使用的索引的最长键部分的列表。有关详细信息，请参阅第8.2 节.第1.3 节索引合并优化.unique_subquery

这种类型可以用下面的IN subquery eq_ref:value IN (SELECT Primary_key FROM single_table WHERE some_expr)unique_subquery来代替，只是一个索引查找功能，完全代替子查询来提高效率。

索引子查询

此连接类型类似于unique_subquery。它取代了IN 子查询，但它适用于：value IN (SELECT key_column FROM single_table WHERE some_expr)range 形式的子查询中的非唯一索引

使用索引选择行，仅检索给定范围内的行。输出行中的键列指示使用了哪个索引。 key_len 包含使用的最长密钥部分。对于该类型，ref 列为NULL。

可以使用任何运算符将范围与常量进行比较：SELECT * FROM tbl_name

其中key_column=10;

从表名中选择*

WHERE key_column 介于10 和20 之间；

从表名中选择*

WHERE key_column IN (10,20,30);

从表名中选择*

WHERE key_part1=10 AND key_part2 IN (10,20,30);index（完全扫描索引）

连接类型与索引完全相同，只是扫描索引树。这是双向的。如果索引是查询的覆盖索引并且可以用来满足表中所需的所有数据，则仅扫描索引树。在本例中，Extra 列显示使用索引。仅索引扫描通常比仅索引扫描ALL 更快，因为索引的大小通常小于表数据。使用从索引读取来按索引顺序查找数据行来执行全表扫描。使用索引不会出现在Extra 列中。

当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。 ALL（全表扫描）

对上表中的每个行组合进行全表扫描。如果表是第一个未标记为常量的表，这通常很糟糕，并且在所有其他情况下通常非常糟糕。通常，您可以通过ALL 添加索引来避免根据上表中的常量值或列值从表中检索行。哇，这太棒了。我觉得我需要掌握这个。

解释额外信息

输出列Extra 包含有关MySQL 如何解析查询的EXPLAIN 附加信息。以下列表解释了可以出现在该列中的值。每个项目还向JSON 格式的输出指示哪个属性显示该Extra 值。对于其中一些，具有特定的属性。其他显示为消息属性的文本。

如果想尽可能快的查询，请注意查看Extra值是否包含Using filesortUsing或Usingtemporary。

Extra提供更多信息，例如是否使用临时表、是否进行文件排序等。

向后索引扫描

优化器能够在InnoDB 表上使用降序索引。显示为使用索引。有关更多信息，请参见第8.3.13 节降序索引。

“table”的子级推送了join@1 （不重要）

该表在连接中被引用为表的子级，可以下推到NDB 内核。仅当启用下推联接时才适用于NDB Cluster.ndb_join_pushdown 。

未找到常量行

对于像SELECT . FROM tbl_name 这样的查询，表是空的。

删除所有行（删除时是否走捷径？）

对于DELETE，某些存储引擎（例如MyISAM）支持处理程序方法，该方法可以简单快速地删除所有表行。如果引擎使用此优化，Extra 将显示此值。

不同（有不同吗？）

MySQL 正在寻找不同的值，因此在找到第一个匹配行后，它会停止为当前行组合搜索更多行。

第一场比赛（表名）

半连接FirstMatch 连接快捷方式策略用于tble_name 表。

对NULL 键进行全面扫描

当优化器无法使用索引查找访问方法时，子查询优化作为后备策略发生。

不可能拥有

HAVING 子句始终为false，并且无法选择任何行。

不可能哪里

WHERE 子句始终为false，并且无法选择任何行。

不可能在阅读const 表后注意到WHERE

MySQL 已读取所有const（和系统）表并注意到WHERE 子句始终为false。

松散扫描(m.n)

使用半连接的LooseScan策略。 m 和n 是关键零件号。

没有匹配的最小/最大行

没有行满足查询条件，例如：SELECT MIN(.) FROM . WHERE 条件

const 表中没有匹配行

对于使用联接的查询，存在空表或没有满足唯一索引条件的行的表。

分区修剪后没有匹配的行

对于DELETE 或UPDATE，优化器在分区修剪后找不到任何可删除或更新的内容。它与SELECT 语句Impossible WHERE 的含义类似。

没有使用表

该查询没有FROM 子句，或有FROM DUAL 子句。

对于INSERT 或REPLACE 语句，EXPLAIN 在没有SELECT 部分时显示此值。执行EXPLAIN INSERT INTO t SELECT 10 FROM DUAL 将显示此内容。

不存在

MySQL 能够优化LEFT JOIN 查询，并且在找到与条件匹配的行后，不会检查表中与前一行相结合的其他行。下面是可以通过这种方式优化的查询类型的示例：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id

其中t2.id 为NULL；假设t2.id 定义为NOT NULL。在这种情况下，MySQL 扫描t1 并查找行t2 中等于t1.id 值的行。如果MySQL在t2中找到匹配的行，它就知道t2.id永远不能为NULL，并且具有相同值的其余行将不会被扫描。换句话说，对于t1中的每一行，MySQL只需要在t2中进行一次查找，而不管实际匹配了多少行。

在MySQL 8.0.17及更高版本中，这也可以表明WHERE中的NOT IN（子查询）或NOT EXIST（子查询）已在内部转换为反连接（antijoin）。这将删除子查询并将其表到最顶层查询的计划中，从而提供改进的成本计划。通过合并半连接和反连接，优化器可以更自由地对执行计划中的表进行重新排序，在某些情况下生成更快的计划。

(牛逼了) 您可以通过 EXPLAIN 后执行 SHOW WARNINGS 后的 Message 列或在EXPLAIN FORMAT=TREE的输出中查看对给定查询是否执行反连接转换. 笔记反连接是半连接 table_a 和 table_b 在 condition 上的补充. 反连接返回 table_a 中所有没有在 condition 上匹配 table_b 的所有行. Plan isn"t ready yet EXPLAIN FOR CONNECTION 当优化器尚未完成为在命名连接中执行的语句创建执行计划时, 会出现此值. 如果执行计划输出包含多行, 则任何或所有行都可能具有此 Extra 值, 具体取决于优化器在确定完整执行计划时的进度. Range checked for each record (index map: N) MySQL 没有找到可以使用的好的索引, 但发现某些索引可能会在之前表中的列值已知后使用. 对于前面表格中的每个行组合, MySQL 检查是否可以使用 range 或者 index_merge 访问方法来检索行. 这不是很快, 但比执行完全没有索引的连接要快. 应用的准则在第 8.2.1.2 节范围优化和第 8.2.1.3 节索引合并优化中所述, 但上表的所有列值都是已知的并被视为常量. 索引从 1 开始编号, 顺序与表中所示的相同 SHOW INDEX. 索引映射值 N 是指示哪些索引是候选索引的位掩码值. 例如, 值 0x19(二进制 11001) 表示考虑索引 1,4 和 5. Recursive 这表明该行适用于 SELECT 递归公用表表达式的递归部分. 请参见第 13.2.15 节 WITH(公用表表达式). Rematerialize Rematerialize (X,...)显示在EXPLAIN T中, 其中是在读取 X 新行时触发重新实现的任何横向派生表. 例如: SELECT ... FROM t, LATERAL (derived table that refers to t) AS dt ...每次 t 顶部查询处理新行时, 派生表的内容都会重新实现以使其保持最新状态. Scanned N databases 这表示在处理表查询时服务器执行了多少目录扫描 INFORMATION_SCHEMA, 如第 8.2.3 节优化 INFORMATION_SCHEMA 查询中所述. N 的值可以是 0, 1 或 all.Select tables optimized away 优化器确定 1) 最多应该返回一行, 以及 2) 要生成这一行, 必须读取一组确定性的行. 当在优化阶段可以读取要读取的行时 (例如, 通过读取索引行), 在查询执行期间不需要读取任何表. 当查询被隐式分组 (包含聚合函数但没有 GROUP BY 子句) 时, 第一个条件得到满足. 当每个使用的索引执行一次行查找时, 满足第二个条件. 读取的索引数决定了要读取的行数. 考虑以下隐式分组查询:SELECT MIN(c1), MIN(c2) FROM t1;假设 MIN(c1) 可以通过读取一个索引行 MIN(c2) 来检索它, 并且可以通过从不同的索引读取一行来检索它. 也就是说, 对于每一列 c1 和 c2, 都存在一个索引, 其中该列是索引的第一列. 在这种情况下, 通过读取两个确定性行来返回一行. 如果要读取的行不确定, 则 Extra 不会出现此值. 考虑这个查询: sql SELECT MIN(c2) FROM t1 WHERE c1< = 10; 假设这 (c1, c2) 是一个覆盖索引. 使用此索引, c1<= 10 必须扫描所有行以找到最小值 c2. 相比之下, 考虑这个查询: sql SELECT MIN(c2) FROM t1 WHERE c1 = 10; 在这种情况下, 第一个索引行 c1 = 10 包含最小值 c2 . 只需读取一行即可生成返回的行. 对于维护每个表的精确行数的存储引擎 (例如 MyISAM, 但不是 InnoDB), 对于缺少WHERE子句的COUNT(*)或始终为真且没有GROUP BY子句的查询, Extra 可能会出现此值 .(这是一个隐式分组查询的实例, 其中存储引擎影响是否可以读取确定的行数.) Skip_open_table, Open_frm_only, Open_full_table 这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化.Skip_open_table: 表格文件不需要打开. 该信息已从数据字典中获得.Open_frm_only: 表信息只需要读取数据字典.Open_full_table: 未优化的信息查找. 表信息必须从数据字典中读取并通过读取表文件.Start temporary, End temporary 这表明临时表用于 semijoin Duplicate Weedout 策略.unique row not found 对于诸如SELECT ... FROM tbl_name的查询, 没有行满足 UNIQUE 索引或 PRIMARY KEY.Using filesort(重要) MySQL 必须做一个额外的过程来找出如何按排序顺序检索行. 排序是通过根据连接类型遍历所有行并存储排序键和指向与 WHERE 子句匹配的所有行的行的指针来完成的. 然后对键进行排序, 并按排序顺序检索行. 请参见第 8.2.1.16 节排序优化.Using index 仅使用索引树中的信息从表中检索列信息, 而无需执行额外的查找来读取实际行. 当查询仅使用属于单个索引的列时, 可以使用此策略. 对于 InnoDB 具有用户定义的聚集索引的表, 即使 Extra 这一列中不存在 Using index 也可以使用该索引 Extra. 如果 type 是 index 而 key PRIMARY 就是这种情况.Using index condition 通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表. 这样, 除非有必要, 否则索引信息用于延迟 ("下推") 读取全表行. 请参阅第 8.2.1.6 节索引条件下推优化.Using index for group-by 与 Using index 表访问方法类似, Using index for group-by 表明 MySQL 找到了一个索引, 该索引可用于检索 GROUP BY 或 DISTINCT 查询的所有列, 而无需对实际表进行任何额外的磁盘访问. 此外, 索引以最有效的方式使用, 因此对于每个组, 只读取几个索引条目. 有关详细信息, 请参阅第 8.2.1.17 节 GROUP BY 优化.Using index for skip scan 表示使用了跳过扫描访问 (Skip Scan)方法. 请参阅跳过扫描范围访问方法.Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join) 来自早期连接的表被部分读入连接缓冲区, 然后从缓冲区中使用它们的行来执行与当前表的连接. (Block Nested Loop) 指示使用块嵌套循环算法,(Batched Key Access) 指示使用批量 key 访问算法, (hash join) 表示使用散列连接. 也就是说, EXPLAIN 输出前一行的表中的键被缓冲, 匹配的行从出现的行所代表的表中批量提取, 显示Using join buffer. 在 JSON 格式的输出中, 的值 using_join_buffer 始终是 Block Nested Loop,Batched Key Access 或之一 hash join. 哈希连接从 MySQL 8.0.18 开始可用; 在 MySQL 8.0.20 或更高版本的 MySQL 中不使用 Block Nested-Loop 算法. 有关这些优化的更多信息, 请参阅第 8.2.1.4 节,"哈希连接优化"和块嵌套循环连接算法. 有关批量密钥访问算法的信息, 请参阅批量密钥访问连接.Using MRR 使用多范围读取优化策略读取表. 请参见第 8.2.1.11 节多范围读取优化.Using sort_union(...), Using union(...), Using intersect(...) 这些指示显示了如何为 index_merge 连接类型索引扫描的特定算法. 请参阅第 8.2.1.3 节索引合并优化.Using temporary 为了解析查询, MySQL 需要创建一个临时表来保存结果. 如果查询包含以不同方式列出列的GROUP BY和ORDER BY子句, 通常会发生这种情况.Using where WHERE 子句用于限制与下一个表匹配或发送到客户端的行. 除非您特别打算从表中获取或检查所有行, 否则如果 Extra 值不是 Using where 并且表连接类型是ALL或index, 则您的查询可能有问题.Using where with pushed condition 此项仅适用于 NDB 表.Zero limit 该查询有一个 LIMIT 0 子句, 不能选择任何行.

EXPLAIN 输出解释

通过获取 EXPLAIN 输出 rows 列中值的乘积, 您可以很好地了解连接的好坏. 这应该大致告诉您 MySQL 必须检查多少行才能执行查询. 如果您使用max_join_size系统变量限制查询, 则此行积还用于确定 SELECT 要执行哪些多表语句以及要中止哪些语句. 请参见第 5.1.1 节配置服务器. 以下示例显示了如何根据 EXPLAIN 提供的信息逐步优化多表连接. 假设您有如下的 SELECT 语句, 并且您计划使用 EXPLAIN 命令对其进行检查 : EXPLAIN SELECT tt.TicketNumber, tt.TimeIn, tt.ProjectReference, tt.EstimatedShipDate, tt.ActualShipDate, tt.ClientID, tt.ServiceCodes, tt.RepetitiveID, tt.CurrentProcess, tt.CurrentDPPerson, tt.RecordVolume, tt.DPPrinted, et.COUNTRY, et_1.COUNTRY, do.CUSTNAME FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;对于此示例, 作出以下假设: 被比较的列已声明如下.表列数据类型ttActualPCCHAR(10)ttAssignedPCCHAR(10)ttClientIDCHAR(10)etEMPLOYIDCHAR(15)doCUSTNMBRCHAR(15)这些表具有以下索引.表索引ttActualPCttAssignedPCttClientIDetEMPLOYID(主键)doCUSTNMBR(主键)这些 tt.ActualPC 值不是均匀分布的.最初, 在执行任何优化之前, 该 EXPLAIN 语句会生成以下信息: table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 do ALL PRIMARY NULL NULL NULL 2135 et_1 ALL PRIMARY NULL NULL NULL 74 tt ALL AssignedPC, NULL NULL NULL 3872 ClientID, ActualPC Range checked for each record (index map: 0x23)因为对于每个表type都是 ALL, 这个输出表明 MySQL 正在生成所有表的笛卡尔积; 也就是说所有行的组合. 这需要相当长的时间, 因为必须检查每个表中行数的乘积. 对于当前的情况, 这个乘积是行. 如果表更大, 您只能想象需要多长时间. 这里的一个问题是, 如果将列声明为相同的类型和大小, MySQL 可以更有效地使用列上的索引. 在这种情况下, 如果 VARCHAR 和 CHAR 被声明为相同的大小, 则认为它们是相同的.tt.ActualPC 被声明为 CHAR(10) , 而 et.EMPLOYID 被声明为 CHAR(15), 因此存在长度不匹配. 要修复列长度之间的这种差异, 请使用ALTER TABLE将 ActualPC 从 10 个字符延长到 15 个字符:

mysql>ALTER TABLE tt MODIFY ActualPC VARCHAR(15);现在tt.ActualPC和et.EMPLOYID都是 VARCHAR(15). 再次执行该 EXPLAIN 语句会产生以下结果: table type possible_keys key key_len ref rows Extra tt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPC do ALL PRIMARY NULL NULL NULL 2135 Range checked for each record (index map: 0x1) et_1 ALL PRIMARY NULL NULL NULL 74 Range checked for each record (index map: 0x1) et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1这并不完美, 但要好得多: 这些 rows 值的乘积小了 74 倍. 这个版本在几秒钟内执行. 可以进行第二次更改以消除tt.AssignedPC = et_1.EMPLOYID和tt.ClientID = do.CUSTNMBR比较的列长度不匹配: mysql>ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), MODIFY ClientID VARCHAR(15);修改后, EXPLAIN 产生如下所示的输出: table type possible_keys key key_len ref rows Extra et ALL PRIMARY NULL NULL NULL 74 tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPC et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1在这一点上, 查询几乎被尽可能地优化了. 剩下的问题是, 默认情况下, MySQL 假定tt.ActualPC列中的值是均匀分布的, 而表 tt 并非如此. 幸运的是, 告诉 MySQL 分析 key 分布很容易: mysql>ANALYZE TABLE tt;使用附加的索引信息, 连接是完美的并 EXPLAIN 产生以下结果: table type possible_keys key key_len ref rows Extra tt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPC et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1 et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1 do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1EXPLAIN 输出中的 rows 列是来自 MySQL 连接优化器的有根据的猜测. rows 通过将产品与查询返回的实际行数进行比较, 检查这些数字是否更接近事实. 如果数字完全不同, 您可能会通过STRAIGHT_JOIN在SELECT语句中使用并尝试在 FROM 子句中以不同的顺序列出表来获得更好的性能.(但是, STRAIGHT_JOIN 可能会阻止使用索引, 因为它禁用了半连接转换. 请参阅第 8.2.2.1 节使用半连接转换优化 IN 和 EXISTS 子查询谓词. 在某些情况下, 当 EXPLAIN SELECT 与子查询一起使用时, 可以执行修改数据的语句; 有关更多信息,请参阅第 13.2.11.8 节派生表.

扩展 EXPLAIN 输出格式

该 EXPLAIN 语句产生额外的信息, 这些信息不是 EXPLAIN 输出的一部分, 但可以通过在 EXPLAIN 后接着SHOW WARNINGS语句来查看. 从 MySQL 8.0.12 开始, 扩展信息可用于 SELECT, DELETE, INSERT, REPLACE 和 UPDATE 语句. 在 8.0.12 之前, 扩展信息仅适用于 SELECT 语句. SHOW WARNINGS 输出的 Message 显示优化器在 SELECT 语句如何限定表名和列名, SELECT 应用重写和优化规则后的样子, 以及可能有关优化过程的其他注释. EXPLAIN 后的 SHOW WARNINGS 仅针对 SELECT 语句生成扩展信息. 其他可解释语句 (DELETE, INSERT, REPLACE 和 UPDATE) 则显示的空结果. 这是扩展 EXPLAIN 输出的示例: mysql>EXPLAIN SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t1 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 filtered: 100.00 Extra: Using index *************************** 2. row *************************** id: 2 select_type: SUBQUERY table: t2 type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 3 filtered: 100.00 Extra: Using index 2 rows in set, 1 warning (0.00 sec) mysql>SHOW WARNINGSG *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select ` test` .` t1` .` a` AS ` a` ,< in_optimizer>(` test` .` t1` .` a` ,` test` .` t1` .` a` in (< materialize>(/* select#2 */ select ` test` .` t2` .` a` from ` test` .` t2` where 1 having 1 ),< primary_index_lookup>(` test` .` t1` .` a` in< temporary table>on< auto_key>where ((` test` .` t1` .` a` = ` materialized-subquery` .` a` ))))) AS ` t1.a IN (SELECT t2.a FROM t2)` from ` test` .` t1` 1 row in set (0.00 sec)因为显示的语句 SHOW WARNINGS 可能包含特殊标记以提供有关查询重写或优化器操作的信息, 所以该语句不一定是有效的 SQL, 并且不打算执行. 输出还可能包含带有 Message 值的行, 这些值提供有关优化器所采取的操作的附加非 SQL 解释性说明. 以下列表描述了可以出现在由 SHOW WARNINGS 显示的扩展输出中的特殊标记: 为临时表自动生成的键.(expr) 表达式 (例如标量子查询) 执行一次, 结果值保存在内存中供以后使用. 对于由多个值组成的结果, 可以创建一个临时表并改为显示.(query fragment) 将子查询谓词转换为 EXISTS 谓词, 并对子查询进行转换, 以便它可以与 EXISTS 谓词一起使用.(query fragment) 这是一个没有用户意义的内部优化器对象.(query fragment) 使用索引查找来处理查询片段以查找符合条件的行.(condition, expr1, expr2) 如果条件为真, 则计算为 expr1, 否则为 expr2.(expr) 验证表达式不为 NULL.(query fragment) 使用了子查询的物化.materialized-subquery.col_name A reference to the columncol_namein an internal temporary table materialized to hold the result from evaluating a subquery.(query fragment) 使用主键查找来处理查询片段以查找符合条件的行.(expr) 这是一个没有用户意义的内部优化器对象./* select#N */ select_stmt SELECT 与非扩展 EXPLAIN 输出中 N 的 id 值相关联.outer_tables semi join (inner_tables) 半连接操作. inner_tables 显示未拉出的表. 请参阅第 8.2.2.1 节,"使用半连接转换优化 IN 和 EXISTS 子查询谓词". 这表示为缓存中间结果而创建的内部临时表.当某些表属于const或system类型时, 涉及这些表中的列的表达式由优化器提前求值, 而不是显示语句的一部分. 但是, 使用 FORMAT=JSON 时, 某些 const 表访问会显示为使用 const 值的 ref 访问.

获取命名连接的执行计划信息

暂时用不上, 这部分地址:获取命名连接的执行计划信息

估计查询性能

在大多数情况下, 您可以通过计算磁盘寻道 (disk seeks) 次数来估计查询性能. 对于小型表, 通常可以在一次磁盘查找中找到一行 (因为索引可能已缓存). 对于更大的表, 您可以估计, 使用 B-tree 索引, 您需要这么多次查找才能找到一行:在 MySQL 中, 索引块通常为 1024 字节, 数据指针通常为 4 字节. 对于一个 500000 行的表, 键值长度为 3 个字节 (大小为 MEDIUMINT), 公式表示seeks. 该索引需要大约的存储空间 (假设典型的索引缓冲区填充率为 2/3), 因此您可能在内存中有很多索引, 因此只需要一两次调用读取数据以查找行. 但是, 对于写入, 您需要四个查找请求来查找放置新索引值的位置, 通常需要两次查找来更新索引并写入行. 前面的讨论并不意味着您的应用程序性能会以的速度缓慢下降. 只要一切都被操作系统或 MySQL 服务器缓存, 随着表变大, 事情只会稍微变慢. 在数据变得太大而无法缓存后, 事情开始变得慢得多, 直到您的应用程序仅受磁盘搜索 (增加 log N) 的约束. 为避免这种情况, 请随着数据的增长而增加 key 的缓存大小. 对于 MyISAM 表, 键缓存大小由 key_buffer_size 系统变量控制. 请参见第 5.1.1 节配置服务器.