MySQL 优化：了解查询执行计划
关于
根据 表，列，索引的详细信息以及“WHERE”子句中的条件，MySQL 优化器考虑了许多技术来有效执行 SQL 查询中涉及的查找。无需读取所有行即可执行对巨大表的查询；可以在不比较行的每个组合的情况下执行涉及多个表的联接。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为EXPLAIN计划。
您的目标是认识到“EXPLAIN”计划的各个方面，这些方面表明查询已得到优化，并且如果发现操作效率低下，则需要学习 SQL 语法和索引技术来改进计划。
使用 EXPLAIN 优化查询
EXPLAIN 语句提供有关 MySQL 如何执行语句的信息：
- EXPLAIN 与 SELECT，DELETE，INSERT，REPLACE 和 UPDATE 语句一起使用。
- 当 EXPLAIN 与可解释的语句一起使用时，MySQL 将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL 解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。
- 当 EXPLAIN 与“FOR CONNECTION connection_id”而不是可解释的语句一起使用时，它将显示在命名连接执行的语句的执行计划。【？】
- 对于 SELECT 语句，EXPLAIN 生成可以使用“SHOW WARNINGS”显示的其他执行计划信息。
- EXPLAIN 对于检查涉及分区表的查询很有用。【？】
- “FORMAT”选项可用于选择输出格式。“TRADITIONAL”以表格格式显示输出（如果不提供 FORMAT 选项，则为默认设置）。 JSON 格式以 JSON 格式显示信息。
借助 EXPLAIN，您可以看到应该向表中添加索引的位置，以便通过使用索引查找行来使语句更快地执行。您还可以使用 EXPLAIN 来检查优化器是否以最佳顺序连接表。为了提示优化器使用与 SELECT 语句中表的命名顺序相对应的连接顺序，请以“SELECT STRAIGHT_JOIN”而不是“SELECT”开头该语句。但是，“STRAIGHT_JOIN”可能会禁止使用索引，因为它禁用了半联接转换。
优化器跟踪有时可能提供与 EXPLAIN 互补的信息。但是，优化程序的跟踪格式和内容在版本之间可能会发生变化。
如果您在认为应该使用索引时遇到问题，请运行“ANALYZE TABLE”以更新表统计信息（例如，键的基数），这可能会影响优化器的选择。
EXPLAIN 也可用于获取有关表中列的信息。 “EXPLAIN tbl_name”与“DESCRIBE tbl_name”和“SHOW COLUMNS FROM tbl_name”同义。【！！！】
EXPLAIN 输出格式
EXPLAIN 为 SELECT 语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取它们的顺序列出了输出中的表。 MySQL 使用嵌套循环连接方法解析所有连接。这意味着 MySQL 从第一个表中读取一行，然后在第二个表，第三个表中找到匹配的行，依此类推。处理完所有表后，MySQL 通过表列表输出选定的列和回溯，直到找到一个表，其中存在更多匹配的行。从该表中读取下一行，然后继续下一个表。
- EXPLAIN 语句提供有关 MySQL 如何执行语句的信息。 EXPLAIN 与“SELECT”，“DELETE”，“INSERT”，“REPLACE”和“UPDATE”语句一起使用。
- EXPLAIN 输出包括分区信息。同样，对于 SELECT 语句，EXPLAIN 生成扩展信息，该信息可以在 EXPLAIN 之后用“SHOW WARNINGS”显示。
- 在较早的 MySQL 版本中，分区和扩展信息是使用“EXPLAIN PARTITIONS”和“EXPLAIN EXTENDED”生成的。
- MySQL Workbench 具有视觉解释功能，该视觉解释提供了 EXPLAIN 输出的视觉表示。【！还有这好玩意？】
EXPLAIN 输出列
EXPLAIN 的每个输出行均提供有关一个 table 的信息。每行包含一下列：
| Column | JSON Name | Meaning |
|---|---|---|
| id | select_id | SELECT 标识符 |
| select_type | None | SELECT 类型 |
| table | table_name | 输出行表 |
| partitions | partitions | 匹配的分区 |
| type | access_type | 联接类型 |
| possible_keys | possible_keys | 可能的索引选择 |
| key | key | 实际选择的索引 |
| key_len | key_length | 所选键的长度 |
| ref | ref | 与索引比较的列 |
| rows | rows | 估计要检查的行 |
| filtered | filtered | 按表条件过滤的行百分比 |
| Extra | None | 附加信息 |
- 上表中“JSON Name”：使用“FORMAT=JSON”时在输出中显示的等效属性名称。【NULL 的 JSON 属性不会显示在 JSON 格式的 EXPLAIN 输出中】
EXPLAIN 查询类型（“select_type”）
其中“select_type”的说明，如下：
| select_type值 | JSON Name | Meaning |
|---|---|---|
| SIMPLE | None | 简单的“SELECT”（不使用“UNION”或 子查询） |
| PRIMARY | None | 最外层的“SELECT” |
| UNION | None | “UNION”中的第二个或之后的“SELECT”语句 |
| DEPENDENT UNION | dependent ( true ) | “UNION”中的第二个或之后的“SELECT”语句，具体取决于外部查询 |
| UNION RESULT | union_result | “UNION”的结果 |
| SUBQUERY | None | 子查询中的第一个“SELECT” |
| DEPENDENT SUBQUERY | dependent ( true ) | 子查询中的第一个“SELECT”，取决于外部查询 |
| DERIVED | None | 派生表 |
| MATERIALIZED | materialized_from_subquery | 实体化子查询 |
| UNCACHEABLE SUBQUERY | cacheable ( false ) | 不可缓存子查询：其结果无法缓存，必须针对外部查询的每一行重新进行评估 |
| UNCACHEABLE UNION | cacheable ( false ) | 不可缓存子查询的“UNION”中的第二个或之后的“SELECT” |
- 非“SELECT”语句的“select_type”值显示受影响表的语句类型。例如，对于“DELETE”条语句的“select_type”是“DELETE”。
EXPLAIN 联接类型（“type”）
EXPLAIN 输出的“type”列描述如何联接表。
- 在 JSON 格式的输出中，这些是“access_type”属性的值。
以下列表描述了连接类型，从最佳类型到最差类型：
| type | Description |
|---|---|
| system | 该表只有一行【即，系统表】。这是“const”连接类型的特例。 |
| const | 该表最多具有一个匹配行，该行在查询开始时读取。因为只有一行，所以优化器的其余部分可以将这一行中列的值视为常量。
在以下查询中，tbl_name 可用作 const 表： SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
|
| eq_ref | 对于前一个表中的每一行组合，从该表中读取一行。
在以下示例中，MySQL 可以使用 eq_ref 连接来处理 ref_table ： SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
|
| ref | 对于前一个表中的行的每种组合，将从该表中读取具有匹配索引值的所有行。
在以下示例中，MySQL 可以使用 ref 连接来处理 ref_table ： SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
|
| ref_or_null | 这种连接类型类似于 ref，但是 MySQL 会额外搜索包含 NULL 值的行。
在以下示例中，MySQL 可以使用 ref_or_null 连接来处理 ref_table ： SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
|
| fulltext | 使用 FULLTEXT 索引执行连接。 |
| index_merge | 此联接类型指示使用索引合并优化。
在这种情况下，输出行中的“key”列包含使用的索引列表，而“key_len”包含使用的索引的最长键部分的列表。 |
| unique_subquery | 此类型将 eq_ref 替换为以下形式的某些“IN”子查询：
value IN (SELECT primary_key FROM single_table WHERE some_expr)
|
| index_subquery | 此连接类型类似于 unique_subquery。它代替了“IN”子查询，但适用于以下形式的子查询中的非唯一索引：
value IN (SELECT key_column FROM single_table WHERE some_expr)
|
| range | 使用索引选择行，仅检索给定范围内的行。
输出行中的“key”列指示使用哪个索引。 “key_len”列包含已使用的最长键部分。此类型的“ref”列是 NULL。
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
|
| index | index 联接类型与 ALL 相同，只是扫描了索引树。这发生两种方式：
|
| ALL | 对来自前一个表的行的每个组合进行全表扫描。
|
EXPLAIN 额外信息（“Extra”）【？？？】
EXPLAIN 输出的“Extra”列包含有关 MySQL 如何解析查询的其他信息。以下列表说明了可以在此列中显示的值：
- 对于其中一些，有一个特定的属性。其他显示为message属性的文本。
- 如果要使查询尽可能快，请注意“Using filesort”和“Using temporary”的 Extra 列值。【！！！】
- 或者以 JSON 格式的 EXPLAIN 输出查找“using_filesort”和“using_temporary_table”属性等于 true 的。
| Extra | JSON 格式输出 | Description |
|---|---|---|
| Child of 'table' pushed join@1 | message | 该表在可以下推到 NDB 内核的连接被称为 'table' 的子级。启用下推联接时，仅适用于 NDB 群集。 |
| const row not found | const_row_not_found | 对于诸如“SELECT ... FROM tbl_name”的查询，该表为空。 |
| Deleting all rows | message | 对于“DELETE”，某些存储引擎（例如 MyISAM）支持处理程序方法，该方法以一种简单而快速的方式删除所有表行。如果引擎使用此优化，则会显示此 Extra 值。 |
| Distinct | distinct | MySQL 正在寻找不同的值，因此在找到第一个匹配的行后，它将停止为当前行组合搜索更多行。 |
| FirstMatch(tbl_name) | first_match | 半连接 FirstMatch 连接快捷方式用于 tbl_name。 |
| Full scan on NULL key | message | 当优化器无法使用索引查找访问方法时，这会作为子查询优化的后备策略发生。 |
| Impossible HAVING | message | “HAVING”子句始终为 false，无法选择任何行。 |
| Impossible WHERE | message | “WHERE”子句始终为 false，无法选择任何行。 |
| Impossible WHERE noticed after reading const tables | message | MySQL 已读取所有 const（和system）表，并注意“WHERE”子句始终为 false。 |
| LooseScan(m..n) | message | 使用半连接的 LooseScan 策略。 m 和 n 是关键 Component 号。 |
| No matching min/max row | message | 没有行满足诸如“SELECT MIN(...) FROM ... WHERE condition”之类的查询的条件。 |
| no matching row in const table | message | 对于具有联接的查询，存在一个空表或没有满足唯一索引条件的行的表。 |
| No matching rows after partition pruning | message | 对于“DELETE”或“UPDATE”，优化器在分区修剪后找不到要删除或更新的内容。 “SELECT”语句的含义与“Impossible WHERE”相似。 |
| No tables used | message | 该查询没有“FROM”子句，或者没有“FROM DUAL”子句。
对于“INSERT”或“REPLACE”语句，如果没有“SELECT”部分，则 EXPLAIN 将显示此值。例如，它对“EXPLAIN INSERT INTO t VALUES(10)”出现，因为它等效于“EXPLAIN INSERT INTO t SELECT 10 FROM DUAL”。 |
| Not exists | message | MySQL 能够对查询进行“LEFT JOIN”优化，并且在找到与“LEFT JOIN”条件匹配的一行后，不检查该表中的更多行以进行前一行的组合。这是可以通过这种方式优化的查询类型的示例：
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
假设 t2.id 被定义为 NOT NULL。在这种情况下，MySQL 扫描 t1 并使用 t1.id 的值查找 t2 中的行。如果 MySQL 在 t2 中找到匹配的行，它将知道 t2.id 永远不会是 NULL，并且不会扫描 t2 中具有相同 id 值的其余行。换句话说，对于 t1 中的每一行，MySQL 只需在 t2 中进行一次查找，而不管 t2 中实际匹配多少行。【？？？】 |
| Plan isn't ready yet | 无 | 当优化程序尚未完成为在命名 Connecting 执行的语句的执行计划的创建时，此值出现在连接说明处。如果执行计划输出包含多行，则它们中的任何一个或全部都可以具有 Extra 值，这取决于优化程序确定完整执行计划的进度。 |
| Range checked for each record (index map: N) | message | MySQL 找不到很好的索引来使用，但是发现在知道先前表中的列值之后可能会使用某些索引。对于上表中的每个行组合，MySQL 检查是否可以使用 range 或 index_merge 访问方法来检索行。这不是很快，但是比根本没有索引的连接要快。
索引从 1 开始编号，其顺序与表“SHOW INDEX”所示的顺序相同。索引 Map 值 N 是一个位掩码值，指示哪些索引是候选索引。例如，值 0x19(二进制 11001) 表示将考虑索引 1、4 和 5。 |
| Scanned N databases | message | 这表示在处理 INFORMATION_SCHEMA 表的查询时服务器执行的目录扫描次数。N 的值可以为 0、1 或 all。 |
| Select tables optimized away | message | 优化器确定：1、最多应返回一行，以及 2、要生成该行，必须读取确定的一组行。当在优化阶段可以读取要读取的行时（例如，通过读取索引行），则在查询执行期间无需读取任何表。【？？？？？？】
考虑以下隐式分组查询： SELECT MIN(c1), MIN(c2) FROM t1;
假设可以通过读取一个索引行来检索MIN(c1)，并且可以通过从另一索引中读取一行来检索MIN(c2)。也就是说，对于每个列c1和c2，都有一个索引，其中该列是索引的第一列。在这种情况下，将通过读取两个确定性行来返回一行。 如果要读取的行不确定，则不会出现此 Extra 值。考虑以下查询： SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
假设(c1, c2)是覆盖索引。使用该索引，必须扫描所有带有c1 <= 10的行以找到最小的c2值。相比之下，请考虑以下查询： SELECT MIN(c2) FROM t1 WHERE c1 = 10;
在这种情况下，具有c1 = 10的第一索引行包含最小c2值。只读取一行就可以产生返回的行。
|
| Skip_open_table，Open_frm_only，Open_full_table | message | 这些值表示适用于 INFORMATION_SCHEMA 表查询的文件打开优化。
|
| Start temporary，End temporary | message | 这表明临时表用于半联接重复淘汰策略。 |
| unique row not found | message | 对于诸如“SELECT ... FROM tbl_name”的查询，没有任何行满足表上“UNIQUE”索引或“PRIMARY KEY”的条件。 |
| Using filesort | using_filesort | MySQL 必须额外进行一遍，以找出如何按排序顺序检索行。排序是通过根据联接类型遍历所有行并存储与“WHERE”子句匹配的所有行的排序键和指向该行的指针来完成的。然后对键进行排序，并按排序顺序检索行。 |
| Using index | using_index | 仅使用索引树中的信息从表中检索列信息，而不必进行其他查找以读取实际行【即，覆盖索引？】。当查询仅使用属于单个索引的列时，可以使用此策略。
对于具有用户定义的聚集索引的 InnoDB 表，即使 Extra 列中不存在 Using index，也可以使用该索引。如果 type 是 index 且 key 是 PRIMARY 就是这种情况。【？？？】 |
| Using index condition | using_index_condition | 通过访问索引元组并首先对其进行测试以确定是否读取完整的表行来读取表。以此方式，除非必要，否则索引信息用于推迟（“下推”）读取整个表行。【索引下推】 |
| Using index for group-by | using_index_for_group_by | 与“Using index”表访问方法类似，“Using index for group-by”表示 MySQL 找到了一个索引，该索引可用于检索“GROUP BY”或“DISTINCT”查询的所有列，而无需对实际表进行任何额外的磁盘访问。此外，以最有效的方式使用索引，因此对于每个组，仅读取少数索引条目。 |
| Using join buffer (Block Nested Loop)，
Using join buffer (Batched Key Access) |
using_join_buffer | 来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来执行与当前表的联接。 (Block Nested Loop)表示使用块嵌套循环算法，(Batched Key Access)表示使用批处理密钥访问算法。也就是说，对 EXPLAIN 输出的前一行中的表中的键进行缓冲，并从出现“Using join buffer”的行所代表的表中批量提取匹配的行。
在 JSON 格式的输出中，using_join_buffer 的值始终是 Block Nested Loop 或 Batched Key Access 之一。 |
| Using MRR | message | 使用多范围读取优化策略读取表。 |
| Using sort_union(...)，
Using union(...)， Using intersect(...) |
message | 这些指示了特定算法，该算法显示了如何针对 index_merge 连接类型合并索引扫描。 |
| Using temporary | using_temporary_table | 为了解决该查询，MySQL 需要创建一个临时表来保存结果。如果查询包含“GROUP BY”和“ORDER BY”子句以不同的方式列出列，通常会发生这种情况。 |
| Using where | attached_condition | “WHERE”子句用于限制要与下一张表匹配或发送到 Client 端的行。除非您专门打算从表中获取或检查所有行，否则如果 Extra 的值不是“Using where”且表连接类型为 ALL 或 index，则查询中可能会出错。
Using where在 JSON 格式的输出中没有直接对应的内容； attached_condition 属性包含使用的任何 WHERE 条件。 |
| Using where with pushed condition | message | 此项仅适用于 NDB 表格。这意味着 NDB Cluster 正在使用条件下推优化来提高在非索引列和常量之间进行直接比较的效率。在这种情况下，条件被“压入”群集的数据节点，并同时在所有数据节点上进行评估。这样就无需通过网络发送不匹配的行，并且在可以但不使用条件下推的情况下，可以将此类查询的速度提高 5 到 10 倍。【引擎条件“下推”】 |
| Zero limit | message | 该查询具有“LIMIT 0”子句，无法选择任何行。 |
理解：EXPLAIN 输出（“rows”）
通过获取 EXPLAIN 输出的“rows”列中值的乘积，可以很好地表明联接的良好程度。这应该大致告诉您 MySQL 必须检查多少行才能执行查询。如果使用“max_join_size”系统变量限制查询，则此行乘积还用于确定要执行和终止哪些多表“SELECT”语句。
示例：
假设您在此处显示了 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;
对于此示例，假设：
- 表的索引即被比较列的定义如下：
Table Column Data Type
---------------------------------------
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
Table Index
---------------------------------------
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID(主键)
do CUSTNMBR(主键)
- 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 正在为所有表生成笛卡尔乘积。也就是说，每行的组合。这需要相当长的时间，因为必须检查每个表中的行数的乘积。对于手头的情况，此乘积为“74×2135×74×3872 = 45,268,558,720”行。如果表更大，时间将会更长。
这里的一个问题是，如果将索引声明为相同的类型和大小，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 分析密钥分布：
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 1
EXPLAIN 输出中的rows列是来自 MySQL 联接优化器的有根据的猜测。通过将 rows 乘积与查询返回的实际行数进行比较，检查数字是否接近真实值。
- 如果数字完全不同，则可以通过在“SELECT”语句中使用“STRAIGHT_JOIN”并尝试在“FROM”子句中以不同顺序列出表来获得更好的性能。（但是 STRAIGHT_JOIN 可能会禁止使用索引，因为它禁用了半联接转换）
- 在某些情况下，当“EXPLAIN SELECT”与子查询一起使用时，可能会执行修改数据的语句。
扩展 EXPLAIN 输出格式【？】
对于“SELECT”语句，EXPLAIN 语句会产生额外的（“扩展”）信息，这些信息不是 EXPLAIN 输出的一部分，但可以通过在 EXPLAIN 之后发出“SHOW WARNINGS”语句来查看。 SHOW WARNINGS 输出中的 Message 值显示优化器如何限定 SELECT 语句中的表名和列名，应用重写和优化规则后 SELECT 的外观以及有关优化过程的其他 Comments。
- 只能在 SELECT 语句中生成可在 EXPLAIN 之后用 SHOW WARNINGS 语句显示的扩展信息。 SHOW WARNINGS 显示其他可解释语句（DELETE，INSERT，REPLACE和UPDATE）的结果为空。
这是扩展的 EXPLAIN 输出的示例：
mysql> EXPLAIN
SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 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 WARNINGS\G
*************************** 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”显示的扩展输出中出现的特殊标记：
| markers | Description |
|---|---|
| <auto_key> | 自动生成的临时表密钥。 |
| <cache>(expr) | 表达式（例如标量子查询）执行一次，结果值保存在内存中以备后用。对于包含多个值的结果，可能会创建一个临时表，您将看到<temporary table>。 |
| <exists>(query fragment) | 子查询谓词将转换为 EXISTS 谓词，子查询将进行转换，以便可以与 EXISTS 谓词一起使用。 |
| <in_optimizer>(query fragment) | 这是一个内部优化器对象，对用户没有任何意义。 |
| <index_lookup>(query fragment) | 使用索引查找来处理查询片段以查找合格的行。 |
| <if>(condition, expr1, expr2) | 如果条件为 true ，则求值为 expr1 ，否则为 expr2。 |
| <is_not_null_test>(expr) | 检验表达式不等于 NULL 的测试。 |
| <materialize>(query fragment) | 使用子查询实现。 |
| ```materialized-subquery`.col_name`` | 实现对内部临时表中的 col_name 列的引用，以保存评估子查询的结果。 |
| <primary_index_lookup>(query fragment) | 使用主键查找来处理查询片段以查找合格的行。 |
| <ref_null_helper>(expr) | 这是一个内部优化器对象，对用户没有任何意义。 |
| /* select#N */ select_stmt | SELECT 与未扩展的 EXPLAIN 输出中具有 id 值 N 的行关联。 |
| outer_tables semi join (inner_tables) | 半联接操作。inner_tables 显示未拉出的表。 |
| <temporary table> | 这表示为缓存中间结果而创建的内部临时表。 |
当某些表为 const 或 system 类型时，涉及这些表中列的表达式将由优化器尽早评估，并且不属于所显示语句的一部分。但是，对于“FORMAT=JSON”，某些 const 表访问将显示为使用 const 值的 ref 访问。【？？？】
获取命名连接的执行计划信息【？】
要获得在命名 Connecting 执行的可解释语句的执行计划，请使用以下语句：
EXPLAIN [options] FOR CONNECTION connection_id;
连接说明返回当前用于在给定 Connecting 执行查询的 EXPLAIN 信息。由于数据的变化(和支持的统计数据)，与在等效查询文本上运行 EXPLAIN 可能会产生不同的结果。这种行为上的差异对于诊断更多瞬时性能问题很有用。例如，如果您在一个会话中运行需要很长时间才能完成的语句，则在另一个会话中使用连接说明可能会产生有关延迟原因的有用信息。
- connection_id 是从 INFORMATION_SCHEMA “PROCESSLIST” 表或“SHOW PROCESSLIST”语句获得的连接标识符。如果您具有 PROCESS 特权，则可以指定任何连接的标识符。否则，您只能为自己的连接指定标识符。
- 如果命名连接未执行语句，则结果为空。否则，“EXPLAIN FOR CONNECTION”仅在可解释在命名 Connecting 执行的语句时适用。这包括“SELECT”，“DELETE”，“INSERT”，“REPLACE”和“UPDATE”。 (但是，“EXPLAIN FOR CONNECTION”不适用于预备语句，甚至不适用于刚刚提到的那些类型的预备语句.)
- 如果命名连接正在执行一个可解释的语句，则输出将是您在语句本身上使用 EXPLAIN 所获得的结果。
- 如果命名连接正在执行不可解释的语句，则会发生错误。例如，您无法命名当前会话的连接标识符，因为 EXPLAIN 无法解释：
mysql> SELECT CONNECTION_ID(); +-----------------+ | CONNECTION_ID() | +-----------------+ | 373 | +-----------------+ 1 row in set (0.00 sec) mysql> EXPLAIN FOR CONNECTION 373; ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported only for SELECT/UPDATE/INSERT/DELETE/REPLACE
“Com_explain_other”状态变量指示已执行的连接说明条语句的数量。
估算查询性能【？】
在大多数情况下，您可以通过计算磁盘搜索次数来估计查询性能：
- 对于小型表，通常可以在一个磁盘搜索中找到一行（因为索引可能已缓存）。
- 对于较大的表，您可以估计，使用 B 树索引，您需要进行许多查找才能找到行：“log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1”。
在 MySQL 中，索引块通常为 1,024 字节，数据指针通常为四个字节。对于键值长度为三个字节（大小为 MEDIUMINT）的 500,000 行表，该公式表示“log(500,000)/log(1024/3*2/(3+4)) + 1 = 4”搜索。
该索引将需要大约 “500,000 * 7 * 3/2 = 5.2MB” 的存储空间（假设典型的索引缓冲区填充率为 2/3），因此您可能在内存中拥有很多索引，因此只需要一个或两个调用即可读取数据以查找行。
但是，对于写操作，您需要四个搜索请求来查找在何处放置新索引值，通常需要两个搜索来更新索引并写入行。【？？？】
前面的讨论并不意味着您的应用程序性能会因 “logN” 缓慢退化。只要所有内容都由 OS 或 MySQL 服务器缓存，随着表的增加，事情只会变得稍微慢一些。在数据变得太大而无法缓存之后，事情开始变得缓慢得多，直到您的应用程序仅受磁盘搜索约束为止（磁盘日志增加了“logN”）。为避免这种情况，请随着数据的增长而增加密钥缓存的大小。
- 对于 MyISAM 表，键缓存大小由“key_buffer_size”系统变量控制。