Mysql优化（出自官方文档）

Mysql优化（出自官方文档） - 第六篇

目录

Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

对于子查询，Mysql通常使用如下的优化方式：

  • 对于IN(or =ANY)式的子查询，优化器使用如下方式：
    • semijoin
    • 物化
    • EXISTS策略
  • 对于NOT IN(OR <>ALL)式的子查询，优化器使用如下方式：
    • 物化
    • EXISTS策略

对于derived tables，优化器使用如下方式（对于view references和common table expressions也同样适用）：

  • 将derived table合并到外部查询里
  • 将derived table物化为临时表。

特别的，对于带有子查询的UPDATE 和 DELETE语句，优化方式为：

优化器不会使用semijoin和物化来优化这种情况，而是将其重写为多张表的UPDATE和DELETE操作，同时使用join来代替子查询操作。

1 Optimizing IN and EXISTS Subquery predicates with Semijoin Transformations

假设有两张表，class和roster，现在要查询有学生出勤课程的课程编号和课程名称，我们可以很简单的写出下面的语句：

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

假设class_num是class表的primary key，可以看出来，上面的查询结果中必然有重复列，因为多个学生可以出勤同一个课程，所以，为了去重，我们可以加上SELECT DISTINCT这样的限定。

除此之外，还可以将上面的join语句改为子查询的方式，如下：

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

该语句有如下特点：

  • SELECT的目标只有一张表的列
  • IN表示在第二张表中只要有第一张表相同的值就立即返回

此时，Mysql会将上面的子查询优化为semijoin，semijoin的特点就是在join中一旦查询到匹配行，就立即只返回一张表的数据，后续重复的值将没有必要继续扫描。

同时，在Mysql8.0.17之后，下面的语句也会被转换为antijoin（和semijoin相反，当第一张表在第二张表中没有匹配行时，立即返回第一张表的列。）

  • NOT IN (SELECT ... FROM ...)
  • NOT EXISTS (SELECT ... FROM ...).
  • IN (SELECT ... FROM ...) IS NOT TRUE
  • EXISTS (SELECT ... FROM ...) IS NOT TRUE.
  • IN (SELECT ... FROM ...) IS FALSE
  • EXISTS (SELECT ... FROM ...) IS FALSE.

对于semijoin，Mysql主要的处理方式如下：

  • Duplicate Weedout
  • FirstMatch
  • LooseScan
  • Materialize

这四种的实现方式网上均有介绍，这里就不赘述了。

2 Optimizing Subqueries with Materialization

Mysql经常使用物化的方式来优化subquery，通常的方式是创建一个临时表（一般来讲是全内存临时表，只有当临时表变得比较大的时候，才会进行下盘处理），并且优化器会使用hash index的方式对临时表创建索引来加快查询，index的值是唯一的，所以能够避免重复的值。

对于下面的语句，如果不适用物化的话：

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

优化器会将该语句重写为：

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

这种有所关联的子查询语句（关联指子查询语句中不仅查询t2内表的数据，还会和t1外表有关）， 这种类型的子查询的执行过程为：外表每执行一次，子查询就要执行一次，所以效率很低。

为了让Mysql使用物化来运行子查询，查询语句必须符合如下形式：

  • 外查询中oe_i和内查询ie_i不能为null，N是1或者更大的值（？？为什么？不是很理解？）

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

  • 外查询和内查询均只有一个表达式，表达式的值可以为null

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

  • 谓词必须为in或者not in，或者和FALSE具有相同语义的表达式。

举例如下，下面的语句将会使用物化技术：

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

下面的语句将无法使用物化技术，因为t2.b可能为null：

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

需要注意的是，对于列的类型信息，必须满足如下条件，才能使用物化技术：

  • 内查询和外查询的列必须匹配，比如如果一个integer另外一个是decimal，优化器将无法使用物化技术。
  • 内查询的表达式类型不能为BLOB，根据第一条的限制，外查询也同样不能为BLOB

在EXPLAIN的输出里面，如果使用了物化技术，那么输出如下：

  • select_type将由DEPENDENT SUBQUERY 变为SUBQUERY, DEPENDENT的意思是外查询每执行一次，内查询就要执行一次，使用物化技术的话，内查询只需要执行一次。
  • 在EXPLAIN的输出里面，SHOW WARNINGS会包含materialize 和materialized-subquery。

3 Optimizing Subqueries with the EXISTS Strategy

对于如下的语句，如果不采取章节2中的优化方式，那么通常的执行方式是：外查询执行一次，内查询在执行一次。

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

对于这种情况，由于只需要特定列，所以Mysql通常会使用条件下推的方式进行优化，优化后的结果为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND 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 oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

这种条件下推的方式有其局限性，如下：

  • outer_expr和inner_expr均不能为NULL

  • 如果OR或者AND语句在WHERE语句中，Mysql假设用户并不关心返回的值是NULL还是FALSE，因此，对于下面的语句：

    ... WHERE outer_expr IN (subquery)

    无论子查询返回NULL还是返回FALSE，WHERE都不会接受。

如果说，上面两条限制都不符合，那么此时的优化方式将会变的很复杂，主要分为以下两种情况：

  • 如果outer_expr不会有NULL产生，此时，outer_expr IN (SELECT ...)的结果分为以下两种情况：

    • 如果SELECT在inner_expr is NULL的条件下返回了任意行，那么结果为NULL
    • 如果SELECT只返回了非NULL行或者说没有返回任何一行，那么结果为FALSE

    对于这种情况，当查找outer_expr = inner_expr时，如果没有找到，还需要查找inner_expr is NULL这样的列，因此，这种语句会被转换为下面的形式：

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

    在EXPLAIN里面，这样的语句type列为： ref_or_null

  • 如果outer_expr有可能产生NULL列，那么情况将会变得比较复杂，对于NULL IN (SELECT inner_expr ...)这样的语句，结果分为两种情况：

    • 如果SELECT返回了任意行，那么结果为NULL
    • 如果SELECT没有返回行，那么结果为FALSE

    所以，优化器为了加快速度，需要分两种情况来处理：

    • 如果outer_expr的结果为NULL，就需要判断子查询是否返回任意行，此时无法使用条件下推的优化，这个时候的性能是最差的，外查询没查询一次，就需要执行一次子查询。

    • 如果outer_expr的结果不为NULL，那么就可以使用上面提到的条件下推这种优化方式，语句将会被重写为：

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

      综上，为了包含上面两种情况，Mysql使用一种叫做"trigger"的函数（不同于数据库里面创建的trigger），在Mysql里面体现为Item_func_trig_cond类，因此，上面的两种情况都会统一被转换为：

      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)，Mysql的处理方式如下：

      • 如果外查询的结果不是NULL，那么trigcond的结果即为x的结果
      • 如果外查询的结果为NULL，那么trigcond返回TRUE（这里不是很理解，需要详细理解下）

      Note

      这里的trigger不同于平时使用sql创建的trigger``，CREATE TRIGGER

帮助优化器进行优化的一些技巧：

  • 如果某一列永远不会产生NULL列，那么将其声明为NOT NULL，这样子可以帮助优化器进行更进一步的优化。

  • 如果不需要区分NULL和FALSE，对于下面的语句：

    outer_expr IN (SELECT inner_expr FROM ...)

    为了避免Mysql采用最糟糕的方式进行执行，可以将该语句修改为：

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

    这样子Mysql可以使用短路判断尽快返回结果，可以大量的减少AND后面语句的执行次数。

    另外，也可以写成下面这样子：

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

4 Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

在优化derived table的时候，通常采用两种策略（同样适用于view references和common table expressions）：

  • 将derived table合并到外层的查询里面
  • 物化derived table到一个临时表里面

举例如下：

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

会被优化为：

SELECT * FROM t1;

下面的语句：

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

会被优化为：

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

可以明显的看到，如果采取非物化的方式，执行效率将会大大提升。

优化器会将derived table里面的ORDER BY优化到外层查询语句中，但是，必须满足如下条件：

  • 外层查询没有使用group by或者聚合函数
  • 外层查询没有使用DISTINCT, HAVING, or ORDER BY
  • 外层查询只有在FROM中才使用到了derived table

如果优化器没办法使用MERGE，意味着只能使用物化为临时表的方式来执行，此时，为了加快效率，将采用如下的优化方式：

  • 优化器只有在需要derived table的时候才会进行物化操作，这样子有时候就可以避免进行物化操作。比如说：一个查询中子查询需要进行物化操作，条件里面有外表和dervied table的对比，此时先执行外查询，如果外查询返回了空行，这个时候，dervied table就没必要再继续执行了，可以减少没有必要的物化操作。
  • 在执行期间，优化器会根据需要给dervied table添加对应的索引，这样子可以提高dervied table的访问效率。
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/seancheer/p/11288623.html