mysql自我优化-优化子查询、派生表和视图引用

本文主要是介绍mysql自我优化-优化子查询、派生表和视图引用，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

mysql自我优化

- 1、使用半连接转换优化子查询、派生表和视图引用
- 2、通过物化优化子查询
- 3、使用 EXISTS 策略优化子查询
- 4、使用合并或物化优化派生表和视图引用

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。
MySQL 查询优化器有不同的策略可用于评估子查询：

对于IN（或=ANY）子查询，优化器有以下选择：
- 半连接
- 物化
- EXISTS 战略
对于NOT IN（或 <>ALL）子查询，优化器有以下选择：
- 物化
- EXISTS 战略

对于派生表，优化器有以下选择（也适用于视图引用）：

将派生表合并到外部查询块中
将派生表具体化为内部临时表

使用子查询修改单个表的UPDATE和DELETE语句的一个限制是，优化器不使用半连接或物化子查询优化。作为一种解决方案，尝试将它们重写为使用连接而不是子查询的多表UPDATE和DELETE语句。

1、使用半连接转换优化子查询、派生表和视图引用

半连接是一种准备时间转换，它支持多种执行策略，如表拉出、副本剔除、第一次匹配、松散扫描和物化。优化器使用半连接策略来改进子查询的执行，如本节所述。

对于两个表之间的内连接，连接从一个表返回一行的次数与另一个表中匹配的次数相等。但对于某些问题，唯一重要的信息是是否匹配，而不是匹配的数量。假设有一个名为class和roster的表，分别列出了课程、课程和班级名册(在每个班级登记的学生)。要列出实际有学生注册的课程，可以使用这个join:

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

然而，结果为每个注册的学生列出每门课一次。对于所提出的问题，这是不必要的重复信息。

假设class_num是类表中的主键，那么可以使用SELECT DISTINCT来抑制重复，但是先生成所有匹配的行，然后再消除重复的行是低效的。

使用子查询可以获得相同的无重复结果:

SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);

这里，优化器可以识别IN子句要求子查询只返回roster表中每个class_num的一个实例。在这种情况下，查询可以使用半连接;也就是说，该操作只返回与roster中的行相匹配的类中的每一行的一个实例。

外部查询规范允许外部连接和内部连接语法，表引用可以是基表、派生表或视图引用。

在MySQL中，子查询必须满足以下条件才能被处理为半连接:

它必须是出现在WHERE或ON子句顶层的IN(或=ANY)子查询，可能作为AND表达式中的一个项。例如:
SELECT ... FROM ot1, ... WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
这里，ot_i和it_i表示查询外部和内部部分中的表，而oe_i和ie_i表示引用外部和内部表中的列的表达式。
它必须是一个没有UNION结构的SELECT。
它不能包含GROUP BY或HAVING子句。
它不能隐式分组(它必须不包含聚合函数)。
它必须没有带着limit的ORDER BY。
该语句不能在外部查询中使用STRAIGHT_JOIN连接类型。
STRAIGHT_JOIN修饰符不能出现。
外部表和内部表的数量必须小于联接中允许的最大表数。

子查询可以是相关的，也可以是不相关的。DISTINCT是允许的，LIMIT也是允许的，除非同时使用ORDER BY。

如果子查询满足上述条件，MySQL将其转换为半连接，并从以下策略中做出基于成本的选择:

将子查询转换为连接，或者使用表拉出并将查询作为子查询表和外部表之间的内部连接运行。表取出将表从子查询中取出到外部查询。
重复清除:像运行连接一样运行semijoin，并使用临时表删除重复记录。
FirstMatch:当扫描内部表以查找行组合时，如果给定值组有多个实例，则选择一个而不是返回所有实例。这种“快捷方式”扫描可以消除产生不必要的行。
LooseScan:使用索引扫描子查询表，该索引允许从每个子查询的值组中选择单个值。
将子查询具体化到一个索引临时表中，该临时表用于执行连接，其中索引用于删除重复项。索引还可以在以后连接临时表和外部表时用于查找;如果不是，则扫描表。有关物化的更多信息，请参见“用物化优化子查询”。

这些策略都可以使用下面的optimizer_switch系统变量标志来启用或禁用:

semijoin标志控制是否使用半连接。
如果启用了semijoin, firstmatch、loosescan、duplicateweedout和materialization标志将对允许的semijoin策略进行更精细的控制。
如果duplicateweedout半连接策略被禁用，它不会被使用，除非所有其他适用的策略也被禁用。
如果禁用了duplicateweedout，有时优化器可能会生成一个远非最优的查询计划。这是由于贪婪搜索期间的启发式剪枝，可以通过设置optimizer_prune_level=0来避免。

这些标志在默认情况下是启用的。请参见8.9.2节，“可切换优化”。

优化器最小化了视图和派生表处理方面的差异。这将影响使用了STRAIGHT_JOIN修饰符的查询和具有可以转换为半连接的IN子查询的视图。下面的查询说明了这一点，因为处理中的更改会导致转换中的更改，从而产生不同的执行策略:

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;

优化器首先查看视图并将IN子查询转换为半连接，然后检查是否有可能将视图合并到外部查询中。因为外部查询中的STRAIGHT_JOIN修饰符阻止半连接，所以优化器拒绝合并，导致使用物化表计算派生表。

EXPLAIN输出表示使用了如下的半连接策略:

半连接表显示在外查询中。对于扩展的EXPLAIN输出，下面的SHOW WARNINGS显示的文本将显示重写过的查询，该查询将显示半连接结构。(参见“扩展的EXPLAIN输出格式”。)由此可以了解哪些表已从半连接中取出。如果将子查询转换为半连接，则可以看到子查询谓词消失了，其表和WHERE子句被合并到外查询连接列表和WHERE子句中。
“重复清除”的临时表使用由“Extra”列中的“Start temporary”和“End temporary”表示。在Start temporary和End temporary覆盖的EXPLAIN输出行范围内没有被取出的表的行在临时表中。
Extra列中的FirstMatch(tbl_name)表示连接捷径。
Extra列中的LooseScan(m…n)表示使用了LooseScan策略。M和n是关键零件号。
MATERIALIZED的临时表使用由select_type值为MATERIALIZED的行和表值为的行表示。

2、通过物化优化子查询

优化器使用物化来支持更有效的子查询处理。物化通过生成一个子查询结果作为临时表(通常在内存中)来加快查询执行。MySQL第一次需要子查询结果时，它将结果具体化到一个临时表中。任何后续需要结果的时候，MySQL都会再次引用临时表。优化器可以使用散列索引对表建立索引，以使查找速度更快、成本更低。索引包含唯一值，以消除重复，使表更小。

子查询物化在可能的情况下使用内存中的临时表，如果表变得太大，则返回到磁盘存储。参见“MySQL内部临时表的使用”。

如果不使用物化，优化器有时会将不相关的子查询重写为相关的子查询。例如，下面的IN子查询是不相关的(where_condition只涉及t2的列而不是t1的列):

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

优化器可能会将其重写为EXISTS相关的子查询:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

使用临时表的子查询物化避免了这种重写，并且使子查询可以只执行一次，而不是每行执行一次外部查询。

要在MySQL中使用子查询物化，必须启用optimizer_switch系统变量materialization标志。(参考“可切换优化”。)启用了materialization标志后，物化适用于出现在任何地方(在选择列表中，WHERE, ON, GROUP BY, HAVING，或ORDER BY)的子查询谓词，适用于这些用例中的任何谓词:

当外部表达式oe_i或内部表达式ie_i不能为空时，谓词具有这种形式。N等于或大于1。

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

有一个外在表达oe和内在表达ie时，谓语就有这种形式。表达式可以为空。

oe [NOT] IN (SELECT ie ...)

谓词是IN或NOT IN, UNKNOWN (NULL)的结果与FALSE的结果具有相同的含义。
下面的例子说明了对UNKNOWN和FALSE谓词计算等价性的要求如何影响是否可以使用子查询物化。假设where_condition只涉及t2的列而不是t1的列，因此子查询是不相关的。

该查询需要物化:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

这里，IN谓词返回UNKNOWN还是FALSE并不重要。无论哪种方式，t1中的行都不包括在查询结果中。

没有使用子查询物化的示例是下面的查询，其中t2.B是一个空列:

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

以下限制适用于子查询物化的使用:

内部和外部表达式的类型必须匹配。例如，如果两个表达式都是整数或都是小数，优化器可能能够使用物化，但如果一个表达式是整数而另一个是小数，则不能使用物化。
内部表达式不能是 BLOB。

使用带有查询的EXPLAIN提供了优化器是否使用子查询物化的一些指示：

与不使用物化的查询执行相比，select_type可能会从DEPENDENT SUBQUERY变为 SUBQUERY。这表明，对于每个外部行执行一次的子查询，物化使子查询仅执行一次。
对于扩展EXPLAIN 输出，以下显示的文本 SHOW WARNINGS包括 materialize和 materialized-subquery。

3、使用 EXISTS 策略优化子查询

某些优化适用于使用IN(或=ANY)运算符测试子查询结果的比较。本节将讨论这些优化，特别是关于NULL值所带来的挑战。讨论的最后一部分建议如何帮助优化器。

考虑下面的子查询比较:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL计算查询“从外部到内部”。也就是说，它首先获取外部表达式outer_expr的值，然后运行子查询并捕获它生成的行。

一个非常有用的优化是“通知”子查询，只关心内部表达式inner_expr等于outer_expr的行。这是通过将适当的等式下推到子查询的WHERE子句中来实现的，以使其更具限制性。转换后的比较看起来像这样:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

转换之后，MySQL可以使用下推等式来限制它必须检查的行数来计算子查询。

更一般地说，将N个值与返回N个值行的子查询进行比较，也需要进行相同的转换。如果oe_i和ie_i表示对应的外部表达式和内部表达式值，则子查询比较:

(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

就变成:

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

为简单起见，下面的讨论假设只有一对外部和内部表达式值。

刚才描述的转换有其局限性。只有当我们忽略可能的NULL值时，它才有效。也就是说，只要这两个条件都成立，“下推”策略就有效:

outer_expr和inner_expr不能为NULL。
您不需要区分NULL和FALSE子查询结果。如果子查询是WHERE子句中OR或AND表达式的一部分，MySQL假定您不关心。优化器注意到NULL和FALSE子查询结果不需要区分的另一个实例是:

... WHERE outer_expr IN (subquery)

在这种情况下，无论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的行。粗略地说，子查询可以被转换成这样的内容:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

需要评估额外的IS NULL条件是为什么MySQL有ref_or_null访问方法:

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
...

unique_subquery和index_subquery特定于子查询的访问方法也有“or NULL”变体。

附加的OR…IS NULL条件使查询执行稍微复杂一些(并且子查询中的一些优化变得不适用)，但通常这是可以容忍的。

当outer_expr可以为NULL时，情况会更糟。根据SQL将NULL解释为“未知值”，NULL IN (SELECT inner_expr…)应该计算为:

NULL，如果SELECT产生任何行
FALSE，如果SELECT没有产生行
为了进行正确的计算，必须能够检查SELECT是否产生了任何行，因此outer_expr = inner_expr不能下推到子查询中。这是一个问题，因为许多真实世界的子查询会变得非常慢，除非相等可以下推。

实际上，必须有不同的方法来执行子查询，具体取决于outer_expr的值。

优化器选择SQL遵从合规而不是速度，因此它考虑了outer_expr可能为NULL的可能性:

如果outer_expr是NULL，为了计算以下表达式，需要执行SELECT来确定它是否产生任何行:

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

这里需要执行原始的SELECT，而不需要前面提到的那种下推等式。

另一方面，当outer_expr不是NULL时，这是绝对必要的:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

转换为使用下推条件的表达式:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

如果没有这种转换，子查询会很慢。

为了解决是否将条件下推到子查询的困境，条件被包装在“触发器”函数中。因此，以下形式的表达式:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

被转化为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(outer_expr=inner_expr))

更一般地说，如果子查询比较基于几对外部和内部表达式，则转换采用这种比较:

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

被转化为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND trigcond(oe_1=ie_1)
                          AND ...
                          AND trigcond(oe_N=ie_N)
       )

每个trigcond(X)都是一个特殊函数，其计算结果如下:

当“链接的”外部表达式oe_i不是NULL时为X
当“链接的”外部表达式oe_i为NULL时为TRUE

触发器函数不是你用create Trigger创建的那种触发器。

封装在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列中的NULL键进行Full扫描:

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

如果你运行EXPLAIN和SHOW WARNINGS，你会看到触发条件:

*************************** 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`

触发条件的使用对性能有一些影响。NULL IN (SELECT…)表达式现在可能会导致全表扫描(这是慢的)，而以前不会。这是为正确的结果所付出的代价(触发器-条件策略的目标是提高遵从性，而不是速度)。

对于多表子查询，NULL IN (SELECT…)的执行特别慢，因为连接优化器没有针对外部表达式为NULL的情况进行优化。它假设在左侧使用NULL的子查询计算非常罕见，即使有统计数据表明并非如此。另一方面，如果外部表达式可能是NULL，但实际上从来没有，则不会有性能损失。

为了帮助查询优化器更好地执行查询，请使用以下建议:

如果列真的为NOT NULL，则声明该列为NOT NULL。通过简化列的条件测试，这也有助于优化器的其他方面。
如果您不需要区分NULL和FALSE子查询结果，您可以很容易地避免缓慢的执行路径。替换一个像这样的比较:

outer_expr IN (SELECT inner_expr FROM ...)

这个表达式:

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

那么NULL IN (SELECT…)永远不会被计算，因为MySQL停止计算AND部分，只要表达式结果是清楚的。

另一个可能的修改:

EXISTS (SELECT inner_expr FROM ...
        WHERE inner_expr=outer_expr)

当您不需要区分NULL和FALSE子查询结果时，这将适用，在这种情况下您可能实际需要EXISTS。
optimizer_switch系统变量的subquery_materialization_cost_based标志允许在子查询物化和IN-to-EXISTS子查询转换之间进行选择。请参见“可切换优化”。

4、使用合并或物化优化派生表和视图引用

优化器可以使用两种策略处理派生表引用(也适用于视图引用):

将派生表合并到外部查询块中
将派生表物化为内部临时表

例1:

 SELECT * FROM (SELECT * FROM t1) AS derived_t1;

合并派生表derived_t1后，查询的执行方式类似于:

SELECT * FROM t1;

例2:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

合并派生表derived_t2后，查询的执行方式类似于:

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

使用物化，derived_t1和derived_t2分别作为各自查询中的单独表处理。

优化器以相同的方式处理派生表和视图引用:尽可能避免不必要的物化，从而使条件从外部查询下推到派生表，并产生更有效的执行计划。(请参见】“用物化优化子查询”。)

如果合并将导致一个引用超过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 or UNION ALL
- 查询列表中的子查询
- 给用户变量赋值
- 仅对文字值的引用(在本例中，没有基础表)

derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果使用与子查询等价的表达式的视图引用发生ER_UPDATE_TABLE_USED错误，那么在视图定义中添加ALGORITHM=TEMPTABLE可以防止合并，并优先于当前的derived_merge值。

如果优化器选择了物化策略而不是对派生表进行合并，它将按照如下方式处理查询:

优化器推迟派生表的物化，直到查询执行期间需要它的内容。这可以提高性能，因为延迟实现可能导致根本不需要实现。考虑一个将派生表的结果连接到另一个表的查询:如果优化器首先处理其他表，发现它没有返回任何行，则不需要进一步执行连接，优化器可以完全跳过派生表的实体化。
在查询执行期间，优化器可以向派生表添加索引，以加快从派生表中检索行的速度。

考虑下面的EXPLAIN语句，用于包含派生表的SELECT查询:

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化器通过将派生表延迟到SELECT执行期间需要结果时才物化来避免派生表。在这种情况下，不会执行查询(因为它出现在EXPLAIN语句中)，因此不需要结果。

即使对于执行的查询，派生表物化的延迟也可能使优化器完全避免物化。当发生这种情况时，查询执行速度会比执行物化所需的时间更快。考虑下面的查询，它将一个派生表的结果连接到另一个表:

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

如果优化首先处理t1，并且WHERE子句产生一个空结果，那么联接肯定是空的，派生表不需要物化。

对于派生表需要物化的情况，优化器可以向物化表添加索引，以加速对它的访问。如果这样的索引允许对表的ref访问，则可以大大减少查询执行期间的数据读取量。考虑以下查询:

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

优化器从derived_t2在列f1上构造一个索引，如果这样做将允许对成本最低的执行计划使用ref访问。添加索引之后，优化器可以将物化的派生表视为具有索引的常规表，它也可以从生成的索引中获得类似的好处。与没有索引的查询执行成本相比，创建索引的开销可以忽略不计。如果引用访问的成本高于其他访问方法，那么优化器不会创建索引，也不会丢失任何数据。

对于优化器跟踪输出，合并的派生表或视图引用不会显示为节点。只有其基础表出现在顶级查询的计划中。

这篇关于mysql自我优化-优化子查询、派生表和视图引用的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

MySql教程