MySQL 优化：优化 SQL 语句：优化子查询，派生表和视图引用

来自Wikioe
跳到导航 跳到搜索


关于

MySQL 查询优化器有多种策略可用于评估子查询：

  1. 对于IN（或=ANY）子查询，优化器具有以下选择：
    1. 半连接
    2. 实体化
    3. EXISTS策略
  2. 对于NOT IN（或<>ALL）子查询，优化器具有以下选择：
    1. 实体化
    2. EXISTS 策略
  3. 对于派生表，优化器具有以下选择（这也适用于视图引用）：
    1. 将派生表合并到外部查询块中
    2. 将派生表实体化为内部临时表


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

使用半联接转换优化子查询，派生表和视图引用

半联接是准备时转换，它启用多种执行策略，例如：表提取，重复删除，首次匹配，松散扫描和实体化。


对于两个表之间的内部联接，该联接从一个表返回一行的次数是另一表中存在匹配项的次数。但是对于某些问题，唯一重要的信息是是否存在匹配项，而不是匹配数：

假设存在名为 class 和 roster 的表，这些表分别列出了班级表中的班级和班级花名册（每个班级的学生人数）。要列出实际招收学生的班级，您可以使用以下联接：
SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;
但是，结果为每个注册学生列出一次每个班级。对于所提出的问题，这是不必要的信息重复。
假设 class_num 是 class 表中的主键，则可以通过使用“SELECT DISTINCT”来抑制重复，但是先生成所有匹配的行仅用于稍后消除重复是无效的。
可以通过使用子查询获得相同的无重复结果：
SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);

在这里，优化器可以识别“IN”子句要求子查询仅返回 roster 表中每个班级号的一个实例。在这种情况下，查询可以使用半联接。也就是说，该操作仅返回 class 中的每一行的一个实例，该实例与 roster 中的行匹配。


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


在 MySQL 中，子查询必须满足以下条件才能作为半联接进行处理：【！！！】

  1. 它必须是出现在“WHERE”或“ON”子句的顶级（可能作为“AND”表达式中的术语）的“IN”（或“=ANY”）子查询。例如： 
    SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
    在这里，ot_i 和 it_i 表示查询的外部和内部部分中的表，oe_i 和 ie_i 表示引用外部和内部表中的列的表达式。
  2. 它必须是没有“UNION”构造的单个“SELECT”。
  3. 它不能包含“GROUP BY”或“HAVING”子句。
  4. 不能将其隐式分组（不能包含任何聚合函数）。
  5. 它不能带有“ORDER BY”和“LIMIT”。
  6. 该语句不得在外部查询中使用“STRAIGHT_JOIN”连接类型。
  7. “STRAIGHT_JOIN”修饰符不能存在。
  8. 外部表和内部表的总数必须小于联接中允许的最大表数。

子查询可以是相关的或不相关的。除非已使用“ORDER BY”，否则允许“DISTINCT”，也允许“LIMIT”。


如果子查询满足上述条件，MySQL 会将其转换为半联接并从以下策略中进行基于成本的选择：

  1. 将子查询转换为联接，或使用表提取，并将查询作为子查询表与外部表之间的内部联接运行。表提取将表从子查询中拉出到外部查询。
  2. 重复删除：像运行连接一样运行半连接，并使用临时表删除重复记录。
  3. FirstMatch：当扫描内部表中的行组合并且给定值组有多个实例时，请选择一个而不是全部返回。这种“快捷方式”扫描可以消除不必要行的产生。
  4. LooseScan（松散扫描）：使用索引扫描子查询表，该索引允许从每个子查询的值组中选择一个值。
  5. 将子查询实体化到用于执行联接的索引临时表中，在该临时表中，索引用于删除重复项。当将临时表与外部表连接时，该索引以后也可能用于查找。如果不是，则扫描表。


可以使用以下“optimizer_switch”系统变量标志来启用或禁用这些策略中的每一个：

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

默认情况下启用这些标志。


优化器将视图和派生表的处理差异最小化。这会影响使用“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”显示的文本显示了重写的查询，该查询显示了半联接结构。由此您可以了解哪些表已从半联接中拉出。如果将子查询转换为半联接，则将看到该子查询谓词已消失，并且其表和“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 的列）：
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”。

使用EXISTS策略优化子查询

通过合并或实体化来优化派生表和视图引用

取自“http://wiki.eijux.com/index.php?title=MySQL_优化：优化_SQL_语句：优化子查询，派生表和视图引用&oldid=2705”