mysql自我优化-优化select语句（二）

最新推荐文章于 2023-07-31 23:13:38 发布

追风少年201

最新推荐文章于 2023-07-31 23:13:38 发布

阅读量170

点赞数

分类专栏：数据库mysql 文章标签： mysql

原文链接：https://dev.mysql.com/doc/refman/5.7/en/nested-join-optimization.html

版权

数据库mysql 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

mysql自我优化

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。

1、嵌套循环连接算法

MySQL 使用嵌套循环算法或它的变体来执行表之间的连接。

1.嵌套循环连接算法

简单的嵌套循环连接(NLJ)算法每次从循环中的第一个表中读取一行，将每一行传递给嵌套循环，该循环处理连接中的下一个表。只要还有表需要连接，这个过程就会重复多次。

假设要使用以下连接类型执行三个表t1、t2和之间 t3的连接：

Table   Join Type
t1      range
t2      ref
t3      ALL

如果使用简单的 NLJ 算法，则连接的处理方式如下：

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

因为NLJ算法每次从外部循环传递一行到内部循环，所以它通常会多次读取内部循环中处理的表。

2.块嵌套循环连接算法

块嵌套循环(Block Nested-Loop, BNL)连接算法使用缓冲在外部循环中读取的行，以减少必须读取内部循环中的表的次数。例如，如果将10行读入缓冲区，并将缓冲区传递给下一个内部循环，则内部循环中读取的每一行都可以与缓冲区中的所有10行进行比较。这减少了一个数量级的必须读取内部表的次数。

MySQL 连接缓冲具有以下特点：

当连接类型为ALL或index(换句话说，当没有可能的键使用时，并且分别对数据行或索引行进行完整扫描)或range时，可以使用连接缓冲。缓存的使用也适用于外部连接，如8.2.1.11节“块嵌套循环和批密钥访问连接”所述。
永远不会为第一个非常量表分配连接缓冲区，即使它是ALL或类型 index。
联接缓冲区中只存储联接感兴趣的列，而不是整个行。
join_buffer_size系统变量决定用于处理查询的每个连接缓冲区的大小。。
为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区处理给定的查询。
在执行连接之前分配一个连接缓冲区，并在查询完成后释放。

对于前面描述的NLJ算法(没有缓冲)的连接示例，使用连接缓冲完成如下操作:

for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}

如果S为联接缓冲区中存储的每个t1, t2组合的大小，C为联接缓冲区中存储的组合数，则扫描表t3的次数为:

(S * C)/join_buffer_size + 1

t3扫描的数量随着join_buffer_size值的增加而减少，直到join_buffer_size足够大，可以容纳所有之前的行组合。超过那个点，使它变大并不能获得速度。

2、嵌套连接优化

表示连接的语法允许嵌套连接。下面的讨论参照“join子句”中描述的join语法。（这个必须看，才能看懂下面的内容）

与标准SQL相比，table_factor的语法得到了扩展。后者只接受table_reference，而不是一对括号内的它们的列表。如果我们将table_reference项列表中的每个逗号视为一个内连接，则这是一个保守的扩展。例如:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

相当于：

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)

在MySQL中，CROSS JOIN在语法上等同于INNER JOIN;它们可以互相替代。在标准SQL中，它们是不相等的。INNER JOIN与ON子句一起使用;否则使用CROSS JOIN。

通常，在只包含内连接操作的连接表达式中可以忽略括号。考虑这个连接表达式:

t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
   ON t1.a=t2.a

删除括号和向左分组操作后，该连接表达式转换为以下表达式：

(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
    ON t2.b=t3.b OR t2.b IS NULL

然而，这两个表达式并不等价。看到这一点，假设表t1， t2以及t3具有以下状态：

表t1包含行 (1)，(2)
表t2包含行 (1,101)
表t3包含行 (101)

在这种情况下，第一个表达式返回包含行(1,1,101,101)，(2,NULL,NULL,NULL)的结果集，而第二个表达式返回行(1,1,101,101)，(2,NULL,NULL,101):

mysql> SELECT *
       FROM t1
            LEFT JOIN
            (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
            LEFT JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

在这种情况下，第一个表达式返回包含行(1,1,101,101)，(2,NULL,NULL,NULL)的结果集，而第二个表达式返回行(1,1,101,101)，(2,NULL,NULL,101):

t1 LEFT JOIN (t2, t3) ON t1.a=t2.a

该表达式不能转换为以下表达式：

t1 LEFT JOIN t2 ON t1.a=t2.a, t3

对于给定的表状态，两个表达式返回不同的行集:

mysql> SELECT *
       FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL | NULL |
+------+------+------+------+

mysql> SELECT *
       FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;
+------+------+------+------+
| a    | a    | b    | b    |
+------+------+------+------+
|    1 |    1 |  101 |  101 |
|    2 | NULL | NULL |  101 |
+------+------+------+------+

因此，如果在具有外部连接操作符的连接表达式中省略括号，则可能会更改原始表达式的结果集。

更确切地说，不能忽略左外连接操作的右操作数和右连接操作的左操作数中的括号。换句话说，不能忽略外部连接操作的内部表表达式的括号。另一个操作数(外部表的操作数)的圆括号可以忽略。

以下表达式:

(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)

对于任意表t1 t2 t3和任意条件P除以属性t2等价于这个表达式。b和t3.b:

t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)

当连接表达式(joined_table)中连接操作的执行顺序不是从左到右时，我们就讨论嵌套连接。考虑以下查询:

SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
  WHERE t1.a > 1

SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
  WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1

这些查询被认为包含这些嵌套连接：

t2 LEFT JOIN t3 ON t2.b=t3.b
t2, t3

在第一个查询中，嵌套连接是通过左连接操作形成的。在第二个查询中，它由一个内连接操作构成。

在第一个查询中，可以省略括号:连接表达式的语法结构规定了相同的连接操作的执行顺序。对于第二个查询，不能省略圆括号，尽管这里的连接表达式可以在没有它们的情况下被明确地解释。在我们的扩展语法,括号中(t2、t3)需要第二个查询,虽然理论上可以解析查询没有它们:我们仍然会有明确的语法结构查询因为左连接和扮演的角色左右分隔符的表达式(t2、t3)。

前面的例子说明了这些要点:

对于只涉及内连接(而不是外连接)的连接表达式，可以删除圆括号，并从左到右计算连接。事实上，表可以以任何顺序计算。
一般而言，对于外连接或混合了内连接的外连接，情况并非如此。删除圆括号可能会改变结果。

具有嵌套外部连接的查询与具有内部连接的查询以相同的管道方式执行。更准确地说，它利用了嵌套循环连接算法的一种变体。回想一下嵌套循环联接执行查询时使用的算法(见“嵌套循环联接算法”)。假设对3个表T1,T2,T3的连接查询具有如下形式:

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
                 INNER JOIN T3 ON P2(T2,T3)
  WHERE P(T1,T2,T3)

这里，P1(T1,T2)和P2(T3,T3)是一些连接条件(在表达式上)，而P(T1,T2,T3)是表T1,T2,T3列上的条件。

嵌套循环连接算法将以以下方式执行此查询:

FOR each row t1 in T1 {
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

表示法t1||t2||t3表示通过连接行t1、t2和t3的列构造的行。在下面的一些例子中，表名中出现的NULL表示对该表的每一列使用NULL。例如，t1||t2||NULL表示通过连接行t1和行t2的列构造的行，而NULL表示行t3的每个列。这样的行称为空补行。

现在考虑一个带有嵌套外连接的查询:

SELECT * FROM T1 LEFT JOIN
              (T2 LEFT JOIN T3 ON P2(T2,T3))
              ON P1(T1,T2)
  WHERE P(T1,T2,T3)

对于此查询，修改嵌套循环模式以获得：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3 such that P2(t2,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF P(t1,t2,NULL) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

通常，对于外部连接操作中第一个内部表的任何嵌套循环，都会引入一个标志，该标志在循环之前关闭，在循环之后检查。当外部表的当前行与表示内部操作数的表匹配时，该标志将打开。如果在循环结束时标志仍然是关闭的，则没有为外部表的当前行找到匹配项。在这种情况下，行由内部表列的NULL值补充。结果行被传递给输出的最终检查或传递给下一个嵌套循环，但仅当该行满足所有嵌入外部连接的连接条件时。

在本例中，由以下表达式表示的外部连接表是嵌入式的:

(T2 LEFT JOIN T3 ON P2(T2,T3))

对于带有内部连接的查询，优化器可以选择不同顺序的嵌套循环，例如:

FOR each row t3 in T3 {
  FOR each row t2 in T2 such that P2(t2,t3) {
    FOR each row t1 in T1 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

对于具有外部连接的查询，优化器只能选择这样的顺序，即外部表的循环先于内部表的循环。因此，对于带有外部连接的查询，只可能有一个嵌套顺序。对于下面的查询，优化器计算两种不同的嵌套。在两个嵌套中，T1都必须在外部循环中处理，因为它是在外连接中使用的。T2和T3用于内连接，因此联接必须在内部循环中处理。但是，因为连接是内连接，所以可以按任意顺序处理T2和T3。

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

一个嵌套评估T2，然后 T3：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t2 in T2 such that P1(t1,t2) {
    FOR each row t3 in T3 such that P2(t1,t3) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

另一个嵌套评估T3，然后 T2：

FOR each row t1 in T1 {
  BOOL f1:=FALSE;
  FOR each row t3 in T3 such that P2(t1,t3) {
    FOR each row t2 in T2 such that P1(t1,t2) {
      IF P(t1,t2,t3) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f1:=TRUE
    }
  }
  IF (!f1) {
    IF P(t1,NULL,NULL) {
      t:=t1||NULL||NULL; OUTPUT t;
    }
  }
}

在讨论内连接的嵌套循环算法时，我们省略了一些对查询执行性能影响可能很大的细节。我们没有提到所谓的 “下推”条件。假设我们的 WHERE条件 P(T1,T2,T3)可以用一个联合公式表示：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).

这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带有内连接的查询：

FOR each row t1 in T1 such that C1(t1) {
  FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2)  {
    FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) {
      IF P(t1,t2,t3) {
         t:=t1||t2||t3; OUTPUT t;
      }
    }
  }
}

可以看到每个合取的C1(T1)， C2(T2)，C3(T3)是最内环的推到最外环的地方进行评估。如果C1(T1)是一个非常严格的条件，这个条件下推可能会大大减少从表T1 传递到内部循环的行数。因此，查询的执行时间可能会大大缩短。

对于带有外部连接的查询，WHERE 只有在发现外部表中的当前行与内部表中的匹配项后才检查条件。因此，将条件推出内部嵌套循环的优化不能直接应用于具有外部连接的查询。在这里，我们必须引入条件下推谓词，这些谓词由遇到匹配时打开的标志保护。

回想一下这个带有外连接的例子：

可以看到每个合数C1(T1)， C2(T2)， C3(T3)都从最内环推到最外环，在那里它可以被计算。如果C1(T1)是一个非常严格的条件，那么下推条件可能会大大减少从表T1传递到内部循环的行数。因此，查询的执行时间可能会大大缩短。

对于带有外部连接的查询，只有在发现外部表的当前行在内部表中有匹配后才检查WHERE条件。因此，将条件推出内部嵌套循环的优化不能直接应用于具有外部连接的查询。这里，我们必须引入条件下的下推谓词，这些谓词由遇到匹配时打开的标志保护。

回想一下外部连接的例子:

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

对于该示例，使用受保护下推条件的嵌套循环算法如下所示：

FOR each row t1 in T1 such that C1(t1) {
  BOOL f1:=FALSE;
  FOR each row t2 in T2
      such that P1(t1,t2) AND (f1?C2(t2):TRUE) {
    BOOL f2:=FALSE;
    FOR each row t3 in T3
        such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) {
      IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) {
        t:=t1||t2||t3; OUTPUT t;
      }
      f2=TRUE;
      f1=TRUE;
    }
    IF (!f2) {
      IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) {
        t:=t1||t2||NULL; OUTPUT t;
      }
      f1=TRUE;
    }
  }
  IF (!f1 && P(t1,NULL,NULL)) {
      t:=t1||NULL||NULL; OUTPUT t;
  }
}

通常，可以从连接条件（例如P1(T1,T2)和）中提取下推谓词P(T2,T3)。在这种情况下，下推谓词也由一个标志保护，该标志防止检查谓词以查找NULL由相应外连接操作生成的-complemented 行。

如果由WHERE条件中的谓词引发，则禁止通过键从一个内部表访问同一嵌套连接中的另一个内部表。

追风少年201

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql自我优化-优化select语句（二）

mysql自我优化1、嵌套循环连接算法1.嵌套循环连接算法2.块嵌套循环连接算法2、嵌套连接优化首先这里要简要说明，该文章翻译自mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高，建议大家仔细阅读，必有收获。1、嵌套循环连接算法MySQL 使用嵌套循环算法或它的变体来执行表之间的连接。1.嵌套循环连接算法简单的嵌套循环连接(NLJ)算法每次从循环中的第一个表中读取一行，将每一行传递给嵌套循环，该循环处理连接中的下一个表。只要还有表需要连接，这个过程就会重复多次。假设要使用以下
复制链接

扫一扫