查看“MySQL 优化：优化 SQL 语句：优化子查询，派生表和视图引用”的源代码

[[category:MySQL文档]]

== 关于 ==
MySQL 查询优化器有多种策略可用于评估子查询：
# 对于'''IN'''（或=ANY）子查询，优化器具有以下选择：
## 半连接
## 实体化
## EXISTS策略
# 对于'''NOT IN'''（或<>ALL）子查询，优化器具有以下选择：
## 实体化
## EXISTS 策略
# 对于“派生表”（“from”中的“select”），优化器具有以下选择（这也适用于视图引用）：
## 将派生表'''合并'''到外部查询块中
## 将派生表'''实体化'''为内部临时表


* 使用子查询修改单个表的“UPDATE”和“DELETE”语句的局限性在于，优化器不使用半联接或物化子查询优化。
*: 解决方法是，尝试将它们重写为使用'''联接'''而不是子查询的多表“UPDATE”和“DELETE”语句。

== 使用'''半联接'''转换优化子查询，派生表和视图引用【？？？？？？？】 ==
半联接是准备时转换，它启用多种执行策略，例如：表提取，重复删除，首次匹配，松散扫描和实体化。


对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配数：
: 假设存在名为 class 和 roster 的表，这些表分别列出了班级表中的班级和班级花名册（每个班级的学生人数）。要列出实际招收学生的班级，您可以使用以下联接：
: <syntaxhighlight lang="mysql">
SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;
</syntaxhighlight>
: 但是，结果为每个注册学生列出一次每个班级。对于所提出的问题，这是不必要的信息重复。
: 假设 class_num 是 class 表中的主键，则可以通过使用“SELECT DISTINCT”来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。
: 可以通过使用子查询获得相同的无重复结果：
: <syntaxhighlight lang="mysql">
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);
</syntaxhighlight>
: 在这里，优化器可以识别“IN”子句要求子查询仅返回 roster 表中每个班级号的一个实例。在这种情况下，查询可以使用'''半联接'''。
: 也就是说，'''该操作仅返回 class 中的每一行的一个实例，该实例与 roster 中的行匹配'''。

* 外部查询规范中允许使用外部联接和内部联接语法，并且表引用可以是基表，派生表或视图引用。


在 MySQL 中，子查询必须满足以下条件才能作为“半联接”进行处理：【！！！】
# 它必须是出现在“WHERE”或“ON”子句的顶级（可能作为“AND”表达式中的术语）的“IN”（或“=ANY”）子查询。例如：
#: <syntaxhighlight lang="mysql">
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
</syntaxhighlight>
#: 在这里，ot_i 和 it_i 表示查询的外部和内部部分中的表，oe_i 和 ie_i 表示引用外部和内部表中的列的表达式。
# 它必须是没有“UNION”构造的单个“SELECT”。
# 它不能包含“GROUP BY”或“HAVING”子句。
# 不能将其隐式分组（不能包含任何聚合函数）。
# 它不能带有“ORDER BY”和“LIMIT”。
# 该语句不得在外部查询中使用“STRAIGHT_JOIN”连接类型。
# “STRAIGHT_JOIN”修饰符不能存在。
# 外部表和内部表的总数必须小于联接中允许的最大表数。
# 子查询可以是相关的或不相关的。除非已使用“ORDER BY”，否则允许“DISTINCT”，也允许“LIMIT”。
如果子查询满足上述条件，MySQL 会将其转换为半联接并，从以下策略中进行基于成本的选择：
# 将子查询转换为联接，或使用表提取，并将查询作为子查询表与外部表之间的内部联接运行。（表提取：将表从子查询中拉出到外部查询）
# 重复删除：像运行连接一样运行半连接，并使用临时表删除重复记录。
# FirstMatch（首次匹配）：当扫描内部表中的行组合并且给定值组有多个实例时，选择一个返回而不是全部返回。（这种“快捷方式”扫描可以消除不必要行的产生）
# LooseScan（松散扫描）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。
# 将子查询“实体化”到用于执行联接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。


可以使用以下“optimizer_switch”系统变量标志来启用或禁用这些策略中的每一个：
# “semijoin”标志控制是否使用半联接。
# 如果启用了“semijoin”，则“firstmatch”，“loosescan”，“duplicateweedout”和“materialization”标志可对允许的半联接策略进行更好的控制。
# 如果禁用了“duplicateweedout”半联接策略，则除非所有其他适用的策略也都被禁用，否则将不使用它。
# 如果禁用“duplicateweedout”，则有时优化器可能会生成一个远非最佳的查询计划。这是由于贪婪搜索期间的启发式修剪而发生的，可以通过设置“optimizer_prune_level=0”来避免。
默认情况下启用这些标志。


优化器将视图和派生表的处理差异最小化。这会影响使用“STRAIGHT_JOIN”修饰符的查询以及带有“IN”子查询的视图，该视图可以转换为半联接。

以下查询说明了这一点，因为处理中的更改导致转换中的更改，从而导致不同的执行策略：
<syntaxhighlight lang="mysql">
CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN * FROM t3 JOIN v ON t3.x = v.a;
</syntaxhighlight>
优化器首先查看视图，并将“IN”子查询转换为半联接，然后检查是否有可能将视图合并到外部查询中。因为外部查询中的'''“STRAIGHT_JOIN”修饰符防止半联接'''，所以优化器拒绝合并，从而导致使用物化表进行派生表评估。


EXPLAIN 输出指示使用半连接策略，如下所示：
* 半联接表显示在外部选择中。对于扩展的 EXPLAIN 输出，以下“SHOW WARNINGS”显示的文本显示了重写的查询，该查询显示了半联接结构。由此您可以了解哪些表已从半联接中拉出。如果将子查询转换为半联接，则将看到该子查询谓词已消失，并且其表和“WHERE”子句已合并到外部查询联接列表和“WHERE”子句中。
* “Extra”列中的“Start temporary”和“End temporary”表示用于重复除草的临时表。未拉出并且在“Start temporary”和“End temporary”覆盖的 EXPLAIN 输出行范围内的表在临时表中具有 rowid。
* “Extra”列中的“FirstMatch(tbl_name)”表示加入快捷方式。
* “Extra”列中的“LooseScan(m..n)”表示使用了 LooseScan 策略。（m 和 n 是关键 Component 编号）
* 用于实现的临时表由“select_type”值为“MATERIALIZED”的行和表值为“<subqueryN>”的行指示。

== 通过'''实体化'''来优化子查询 ==
实体化通过生成子查询结果作为'''临时表'''（通常在内存中）来加快查询执行速度：
: '''MySQL 第一次需要子查询结果时，会将结果具体化为临时表。任何随后的需要结果的时间，MySQL 都会再次引用临时表'''。优化器可以使用'''哈希索引'''对表进行索引，以使查找快速，廉价。索引包含唯一值，以消除重复项并使表更小。
* 子查询实体化在可能的情况下使用'''内存'''中的临时表，如果表太大，则会退回到磁盘上的存储。
* 如果'''未使用实体化'''，则优化器有时会'''将不相关的子查询重写为相关的子查询'''。
*: 例如，以下“IN”子查询是不相关的（where_condition 仅涉及 t2 而不是 t1 的列）：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
</syntaxhighlight>
*: 优化器可能将此重写为 EXISTS 相关子查询：【！！！！！！！！！！！】
*: '''<syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);
</syntaxhighlight>'''
** 使用'''临时表的子查询实现避免了这样的重写'''，并使得只可能执行一次子查询，而不是对外部查询的每一行执行一次。


为了使子查询实现在 MySQL 中使用，必须启用“optimizer_switch”系统变量“'''materialization'''”标志。
: 启用 materialization 标志，实现适用于出现在以下任何一种情况下的谓词：在任何位置（在选择列表 WHERE，ON，GROUP BY，HAVING 或 ORDER BY 中）出现的子查询谓词：
* 当没有外部表达式 oe_i 或内部表达式 ie_i 为空时，谓词具有这种形式。（N 为 1 或更大）。
*: <syntaxhighlight lang="mysql">
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
</syntaxhighlight>
* 当存在单个外部表达式 oe 和内部表达式 ie 时，谓词具有这种形式。表达式可以为空。
*: <syntaxhighlight lang="mysql">
oe [NOT] IN (SELECT ie ...)
</syntaxhighlight>
* 谓词是“'''IN'''”或“'''NOT IN'''”，并且“UNKNOWN(NULL)”的结果与“FALSE”的结果具有相同的含义。
*: 以下示例说明了 UNKNOWN 和 FALSE 谓词评估的等价要求如何影响是否可以使用子查询实现。假定 where_condition 仅包含来自 t2 而不是 t1 的列，因此子查询是不相关的。
*: 此查询需要具体实现：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);
</syntaxhighlight>
*: 在这里，IN 谓词返回 UNKNOWN 或 FALSE 都没有关系。无论哪种方式，t1 中的行都不会包含在查询结果中。
*: 以下查询是不使用子查询实现的示例，其中 t2.b 是可为空的列：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2 WHERE where_condition);
</syntaxhighlight>


以下限制适用于子查询实现的使用：【！！！！！】
* '''内部和外部表达式的类型必须匹配'''。例如，如果两个表达式都是整数或两个都是十进制，那么优化器可能可以使用实现，但是如果一个表达式是整数而另一个表达式是十进制，则优化器不能使用实现。
* '''内部表达式不能为 BLOB'''。


在查询中使用 EXPLAIN 可以表明优化器是否使用子查询实现：
* 与不使用实现的查询执行相比，select_type 可能从“DEPENDENT SUBQUERY”更改为“SUBQUERY”。这表明，'''对于将对每个外行执行一次的子查询，实体化将使子查询仅执行一次'''。
* 对于扩展的 EXPLAIN 输出，随后的“SHOW WARNINGS”显示的文本包括“materialize”和“materialized-subquery”。

== 使用'''EXISTS策略'''优化子查询【？？？？？？？】 ==
某些优化适用于使用“IN”（或“=ANY”）运算符测试子查询结果的比较。本节讨论这些优化，尤其是针对“NULL”值所带来的挑战。


考虑以下子查询比较：
: <syntaxhighlight lang="mysql">
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
</syntaxhighlight>
: MySQL“从外到内”评估查询。也就是说，它首先获取外部表达式 outer_expr 的值，然后运行子查询并捕获其产生的行。
一个非常有用的优化是“通知”子查询仅感兴趣的行是那些内部表达式 inner_expr 等于 outer_expr 的行。这是通过'''将适当的等式推入子查询的 WHERE 子句'''以使其更具限制性来完成的。
: 转换后：
: '''<syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
</syntaxhighlight>'''
: 转换后，MySQL 可以使用下推式相等性来限制为评估子查询而必须检查的行数。
更一般而言，将 N 值与返回 N-value 行的子查询进行比较将经历相同的转换。
: 如果 oe_i 和 ie_i 表示相应的外部和内部表达式值，则此子查询比较：
: <syntaxhighlight lang="mysql">
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
</syntaxhighlight>
: 转换后：
: <syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)
</syntaxhighlight>


刚刚描述的转换有其局限性。仅当我们忽略可能的“NULL”值时才有效。也就是说，只要满足以下两个条件，就可以使用“下推”策略：
# '''outer_expr 和 inner_expr 不能为 NULL'''
# '''无需将 NULL 与 FALSE 子查询结果区分开'''。
#: 如果子查询是 WHERE 子句中 OR 或 AND 表达式的一部分，则 MySQL 假定您不在乎。
#: 优化器注意到不需要区分 NULL 和 FALSE 子查询结果的另一个实例是以下结构：
#: <syntaxhighlight lang="mysql">
... WHERE outer_expr IN (subquery)
</syntaxhighlight>
#: 这种情况下，无论“IN (subquery)”返回 NULL 还是 FALSE，WHERE 子句都拒绝该行。

当这些条件中的任何一个或两个都不成立时，优化将更加复杂。

: 假定 outer_expr 是一个非 NULL 值，但是子查询不会产生 outer_expr = inner_expr 的行。然后“outer_expr IN (SELECT ...)”评估如下：
:# NULL，如果 SELECT 产生 inner_expr 为 NULL 的任何行
:# FALSE，如果 SELECT 只产生非 NULL 值或什么都不产生
: 在这种情况下，使用“outer_expr = inner_expr”查找行的方法不再有效。有必要查找这样的行，但是如果找不到，则还要查找 inner_expr 为 NULL 的行。
: 粗略地讲，子查询可以转换为如下形式：
: <syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND (outer_expr=inner_expr OR inner_expr IS NULL))
</syntaxhighlight>
: 需要评估额外的“IS NULL”条件是 MySQL 具有 ref_or_null 访问方法的原因：
: <syntaxhighlight lang="mysql">
mysql> EXPLAIN
       SELECT outer_expr IN (SELECT t2.maybe_null_key
                             FROM t2, t3 WHERE ...)
       FROM t1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
</syntaxhighlight>
* unique_subquery 和 index_subquery 特定于子查询的访问方法也具有“OR NULL”变体。【？？？】
** 附加的“OR ... IS NULL”条件使查询的执行稍微复杂一些（并且子查询中的某些优化变得不适用），但这通常是可以容忍的。

当 outer_expr 可以是 NULL 时，情况就更糟了。
: 根据 NULL 的 SQL 解释为“UNKNOWN”，“NULL IN (SELECT inner_expr ...)”的计算结果应为：
:# NULL，如果 SELECT 产生任何行
:# FALSE，如果 SELECT 不产生任何行
: 为了进行正确的评估，必须能够检查 SELECT 是否已产生任何行，因此“outer_expr = inner_expr”无法下推到子查询中。这是一个问题，因为许多现实世界中的子查询会变得非常缓慢，除非可以降低相等性。


本质上，根据 outer_expr 的值，必须有不同的方法来执行子查询。


优化器选择 SQL 遵从性而不是速度，因此考虑了 outer_expr 可能是 NULL 的可能性：
* 如果 outer_expr 是 NULL，则要计算以下表达式，必须执行 SELECT 以确定它是否产生任何行：
*: <syntaxhighlight lang="mysql">
NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)
</syntaxhighlight>
*: 必须在此处执行原始的 SELECT，而没有前面提到的那种下推式等式。
* 另一方面，当 outer_expr 不是 NULL 时，此比较绝对必要：
*: <syntaxhighlight lang="mysql">
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
</syntaxhighlight>
*: 转换为使用下推条件的表达式：
*: <syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)
</syntaxhighlight>
*: 没有这种转换，子查询将很慢。


为了解决是否将条件下推到子查询中的难题，'''将条件包装在“触发”函数中'''。因此，以下形式的表达式：【触发器函数不是用“CREATE TRIGGER”创建的那种触发器】
<syntaxhighlight lang="mysql">
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)
</syntaxhighlight>
转换为：
'''<syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND trigcond(outer_expr=inner_expr))
</syntaxhighlight>'''
更一般而言，如果子查询比较基于几对外部和内部表达式，则转换将采用以下比较：
<syntaxhighlight lang="mysql">
(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)
</syntaxhighlight>
并将其转换为以下表达式：
'''<syntaxhighlight lang="mysql">
EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )
</syntaxhighlight>'''

每个“trigcond(X)”是一个特殊函数，其结果为以下值：
* FALSE：当“链接的”外部表达式 oe_i 不是 NULL 时
* TRUE：当“链接的”外部表达式 oe_i 为 NULL 时


“'''trigcond()'''”函数中包装的等式不是查询优化器的第一类谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定任何“trigcond(X)”为未知函数并忽略它。那些优化可以使用触发的等式：
* 参考优化：“trigcond(X=Y [OR Y IS NULL])”可用于构造 '''ref'''，'''eq_ref''' 或 '''ref_or_null''' 表访问。
* 基于索引查找的子查询执行引擎：“trigcond(X=Y)”可用于构造 unique_subquery 或 index_subquery 访问。
* 表条件生成器：如果子查询是多个表的联接，则将尽快检查触发条件。


当优化器使用触发条件创建某种基于索引查找的访问时（对于前面列表的前两项），对于条件关闭的情况，优化器必须具有后备策略。此'''后备策略'''始终相同：执行'''全表扫描'''。

在 EXPLAIN 输出中，后备广告在“Extra”列中显示为“Full scan on NULL key”：
<syntaxhighlight lang="mysql">
mysql> EXPLAIN SELECT t1.col1,
       t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
        ...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: key1
          key: key1
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Full scan on NULL key
</syntaxhighlight>
如果先运行 EXPLAIN，然后运行“SHOW WARNINGS”，则可以看到触发的条件：
<syntaxhighlight lang="mysql">
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         <in_optimizer>(`test`.`t1`.`col1`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`
</syntaxhighlight>
使用触发条件会影响性能。现在，“NULL IN (SELECT ...)”表达式以前可能没有引起全表扫描(这很慢)。这是为获得正确结果而付出的代价（'''触发条件策略的目标是提高合规性，而不是速度'''）。


对于多表子查询，“NULL IN (SELECT ...)”的执行特别慢，因为联接优化器不会针对外部表达式为 NULL 的情况进行优化。它假定左侧带有 NULL 的子查询评估非常少见，即使有统计数据表明并非如此。另一方面，如果外部表达式可能是 NULL 但实际上不是，则不会影响性能。

为了帮助查询优化器更好地执行查询，请使用以下建议：
* 如果确实是一列，则将其声明为“NOT NULL”。通过简化色谱柱的条件测试，这也有助于优化程序的其他方面。
* 如果您不需要区分“NULL”和“FALSE”子查询结果，则可以轻松避免执行路径缓慢。替换如下所示的比较：
*: <syntaxhighlight lang="mysql">
outer_expr IN (SELECT inner_expr FROM ...)
</syntaxhighlight>
*: 具有以下表达式：
*: <syntaxhighlight lang="mysql">
(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))
</syntaxhighlight>
*: 然后永远不会评估“NULL IN (SELECT ...)”，因为一旦表达式结果明确，MySQL 就会停止评估“AND”个部分。
*: 另一种可能的重写：
*: <syntaxhighlight lang="mysql">
EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr=outer_expr)
</syntaxhighlight>
** 当您不需要区分“NULL”和“FALSE”子查询结果时，这将适用，在这种情况下，您实际上可能需要“EXISTS”。


“optimizer_switch”系统变量的“'''subquery_materialization_cost_based'''”标志可控制子查询实现和“IN”到“EXISTS”子查询转换之间的选择。

== 通过'''合并'''或'''实体化'''来优化派生表和视图引用【？？？？？？？】 ==
优化器可以使用两种策略来处理派生表引用（这也适用于视图引用）：
# 将派生表'''合并'''到'''外部查询块'''中
#: <syntaxhighlight lang="mysql">
SELECT * FROM (SELECT * FROM t1) AS derived_t1;
</syntaxhighlight>
#: 通过合并派生表 derived_t1，该查询的执行类似于：
#: <syntaxhighlight lang="mysql">
SELECT * FROM t1;
</syntaxhighlight>
# 将派生表'''实体化'''为'''内部临时表'''
#: <syntaxhighlight lang="mysql">
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
#: 通过合并派生表 derived_t2，该查询的执行类似于：
#: <syntaxhighlight lang="mysql">
SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
* 优化器以相同的方式处理派生表和视图引用：尽可能避免不必要的实现，这可以将条件从外部查询下推到派生表，并产生更有效的执行计划。
* 如果合并将导致一个外部查询块引用超过 61 个基本表，则优化程序将选择实现。


如果满足以下所有条件，则优化器将派生表或视图引用中的“ORDER BY”子句传播到外部查询块：
# 外部查询未分组或聚合。
# 外部查询未指定“DISTINCT”，“HAVING”或“ORDER BY”。
# 外部查询将此派生表或视图引用作为“FROM”子句中的唯一源。
否则，优化器将忽略“ORDER BY”子句。


可以使用以下方法来影响优化器是否尝试将派生表和视图引用合并到外部查询块中：
* 假定没有其他规则阻止合并，则可以使用“optimizer_switch”系统变量的“derived_merge”标志。
** 默认情况下，该标志启用以允许合并。禁用该标志可防止合并并避免“ER_UPDATE_TABLE_USED”错误。
** derived_merge标志还适用于不包含 ALGORITHM 子句的视图。因此，如果使用与子查询等效的表达式的视图引用发生 ER_UPDATE_TABLE_USED 错误，则在视图定义中添加“ALGORITHM=TEMPTABLE”可以防止合并并优先于 derived_merge 值。【？？？】
* 可以通过在子查询中使用任何阻止合并的构造来禁用合并，尽管这些构造对实现的影响不那么明显。
*: 防止合并的构造与派生表和视图引用相同：
** 聚合函数（SUM()，MIN()，MAX()，COUNT()等）
** DISTINCT
** GROUP BY
** HAVING
** LIMIT
** UNION 或 UNION ALL
** 选择列表中的子查询
** 分配给用户变量
** 仅引用 Literals 值（在这种情况下，没有基础表）


如果优化器选择实体化策略而不是合并派生表，则它将按以下方式处理查询：
* 优化程序将派生表的'''实体化推迟'''到'''查询执行期间需要其内容之前'''。这会提高性能，因为延迟实现可能会导致根本不必这样做。考虑一个将派生表的结果连接到另一个表的查询：如果优化器首先处理该另一个表并发现它不返回任何行，则不需要进一步执行联接，并且优化器可以完全跳过实体化派生表。【延迟实体化】
* 在查询执行期间，优化器可以将'''索引'''添加到派生表中，以加快从中获取行的速度。【添加索引】


【延迟实体化】
# 考虑以下 EXPLAIN 语句，用于包含派生表的 SELECT 查询：
#: <syntaxhighlight lang="mysql">
EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;
</syntaxhighlight>
#: 优化程序通过将其推迟到 SELECT 执行期间需要结果之前来避免实体化派生表。在这种情况下，查询不会执行（因为它发生在 EXPLAIN 语句中），因此永远不需要结果。
# 即使对于已执行的查询，派生表实体化的延迟也可以使优化程序完全避免实现。发生这种情况时，查询执行将比实现实现所需的时间更快。考虑以下查询，该查询将派生表的结果连接到另一个表：
#:<syntaxhighlight lang="mysql">
SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;
</syntaxhighlight>
#:如果最先进行优化的过程 t1 和 WHERE 子句产生的结果为空，则联接必须一定为空，并且派生表不必实现。
【添加索引】
# 对于派生表需要实体化的情况，优化器可以向实现表添加索引以加快对其的访问。如果这样的索引启用对表的 ref 访问，则可以大大减少查询执行期间读取的数据量。考虑以下查询：
#:<syntaxhighlight lang="mysql">
SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;
</syntaxhighlight>
#:如果这样做可以使 ref 访问用于最低成本的执行计划，则优化器将在 derived_t2 的 f1 列上构造索引。添加索引后，优化器可以将物化派生表与具有索引的常规表相同，并且它从生成的索引中也可以得到类似的好处。与没有索引的查询执行成本相比，索引创建的开销可以忽略不计。如果 ref 访问会比其他访问方法带来更高的成本，则优化器不会创建索引，也不会丢失任何内容。


对于优化程序跟踪输出，未将合并的派生表或视图引用显示为节点。仅其基础表出现在顶部查询的计划中。【？？？】