目录

  • Mysql优化(出自官方文档) - 第二篇
  • 1 关于Nested Loop Join的相关知识


Mysql优化(出自官方文档) - 第二篇

1 关于Nested Loop Join的相关知识

1.1 相关概念和算法

Mysql在实现join的时候,采用的Nested Loop Join技术,join的方式还有其他两种:Hash JoinSortMergeJoin ,Mysql处于逻辑实现统一的角度,只实现了Nested Loop Join,假设有t1, t2, t3三张表,EXPLAIN优化的结果为:

Table   Join Type
t1      range
t2      ref
t3      ALL
Table   Join Type
t1      range
t2      ref
t3      ALL

那么其Join的方式可以用下面的为代表来表示:

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

为了减少内表的IO,Mysql又引入了NestedLoopJoin的一个变种,叫做:Block Nested-Loop Join,简称为BNL,简单理解就是NestedLoopJoin With Buffer,为每一个join维护一个Join Buffer,对于外层循环,每次将扫描的行放入到Buffer中,内表直接对Buffer中的行进行匹配操作,对应的伪代码如下:

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
    }
  }
}
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
    }
  }
}
1.2 一些优化
  • 在Join语句中,除了on 语句后面的条件外,如果还存在where语句,假设有三张表t1, t2, t3,如下面的格式:
select * from (t1,t2) on P1(t1,t2) inner join t3 on P2(t2,t3) where C1(t1) and C2(t2) and C3(t3);
select * from (t1,t2) on P1(t1,t2) inner join t3 on P2(t2,t3) where C1(t1) and C2(t2) and C3(t3);

inner join的伪代码如下所示:

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;
      }
    }
  }
}
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;
      }
    }
  }
}

Mysql会采用条件下推的优化方式,提前将where的条件限制在inner join的循环里面,这样子,如果C1的条件非常严苛,那么可以避免大量对t2, t3表的IO操作。需要注意的是:这种优化方式可能并不适用于outer joinouter joinwhere提前会导致不同的结果。

  • outer join优化
  • 对于left join,如果对于generated NULL row, 后面的where条件始终为false, 那么,这个left join可以被安全的转换为inner join,如下面的语句所示,假设t1.column1始终为NULL
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

那么这条语句可被转换为inner join

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

解释:之所以会有这样的转换, 是因为这样做t2便不需要进行全表扫描,只需要扫描出column2 = 5的行。

  • 有时候,在prepare阶段,一些琐碎的条件可以直接被移除掉(特指Mysql8.0.14+的版本),而不是在优化器里面在进行移除,提前移除这些条件,可以让优化器尽可能的把left join转换为inner join,比如以下语句:
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

0 = 1始终为false,因此可以直接移除掉,移除后的结果:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2
SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2

此时,优化器便可以根据实际情况将该语句优化为inner join(假设按照上一个规则优化):

SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2
SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2
  • outer join一些简化措施(重写或者转换)
  • 大多数情况下,right join会在解析阶段直接被转换为left join,如下所示:
(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)
(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

转换为的结果为:

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)
(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)
  • 有时候,当join带有where条件时,并且where条件里面首先对inner table进行访问,那么这个时候,优化器将很难对其进行优化,比如下面的例子:
SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)
SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

此时,Mysql会对where条件进行null-rejected判断,如果判断成功,那么outer join就可以安全的被转换为inner join,需要注意的是,进行null-rejected判断的对象必须在内表上,原理很简单,因为对于outer join,如果内表没有匹配到的行,会进行null-complemented(对应的内表所有行被设置为NULL),所以,如果此时where条件对于内表的判断始终为false,那么null-complemented的行将会被过滤掉,此时``outer join的结果将等价于inner join的结果,对于下面的例子:

T1 LEFT JOIN T2 ON T1.A=T2.A
T1 LEFT JOIN T2 ON T1.A=T2.A

null-rejected的条件包括(t2的列会被设置为NULL):

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1
T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

下面的条件不属于null-rejected,因为下面的条件对于null-complemented行可能为真:

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3
T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

举例说明,比如下面的语句:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

可以判断出,T3.C > 0是一个典型的null-rejected条件,因此该语句可以被直接的转换为:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

再来看一个更加复杂的例子

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

与第一个例子唯一的趋避额就是T2T3 join的条件不同,此时join的对象变成了T3.B = T2.B,类似于第一个例子,上述语句可以直接转换为:

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 INNER JOIN T3 ON T3.B=T2.B
  WHERE T3.C > 0

在Mysql中,inner joincross join带on的版本,上面这条语句和下面是等价的:

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

对于最外层的T1T2,可以看到where条件里面的T3.B = T2.B又是一个null-rejected条件,因此,外层的join也可以被直接优化为:

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

经过上面的优化过程,最终的语句已经没有了outer join,可以大幅度提高join的效率,因此根据该优化规则,我们写join的时候,也可以有效的利用这种优化方式。