做网站什么主题好,哈尔滨企业建站服务商,要怎样建立自己的网站,个体户怎么做购物网站主博客#xff1a;
【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客
上一篇#xff1a;
【MySQL精通之路】SQL优化(1)-查询优化(7)-嵌套循环联接-CSDN博客
下一篇#xff1a;
【MySQL精通之路】SQL优化(1)-查询优化(9)-外部联接优化-CSDN博客 与SQL标准相比#xff0c…主博客
【MySQL精通之路】SQL优化(1)-查询优化-CSDN博客
上一篇
【MySQL精通之路】SQL优化(1)-查询优化(7)-嵌套循环联接-CSDN博客
下一篇
【MySQL精通之路】SQL优化(1)-查询优化(9)-外部联接优化-CSDN博客 与SQL标准相比table_factor的语法得到了扩展。后者只接受table_reference而不接受一对括号内的结果。如果我们将table_reference项列表中的每个逗号视为等效于内部联接那么这是一个保守的扩展。
例如
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)ON (t2.at1.a AND t3.bt1.b AND t4.ct1.c)
相当于
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)ON (t2.at1.a AND t3.bt1.b AND t4.ct1.c)
在MySQL中CROSS JOIN在语法上等同于INNER JOIN它们可以相互替换。
在标准SQL中它们是不等价的。INNER JOIN与ON子句一起使用否则使用CROSS JOIN。
通常在只包含内部联接操作的联接表达式中可以忽略圆括号。
例如下面联接表达式
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.bt3.b OR t2.b IS NULL)ON t1.at2.a
删除括号并向左分组操作后该联接表达式将转换为以下表达式
(t1 LEFT JOIN t2 ON t1.at2.a) LEFT JOIN t3ON t2.bt3.b OR t2.b IS NULL
然而这两种表述并不等同。
为此假设表t1、t2和t3具有以下状态 Table t1 contains rows (1), (2) Table t2 contains row (1,101) Table t3 contains row (101) 在这种情况下第一个表达式返回包括行(1,1,101,101), (2,NULL,NULL,NULL),的结果集而第二个表达式返回行(1,1,101,101), (2,NULL,NULL,101)
mysql SELECT *FROM t1LEFT JOIN(t2 LEFT JOIN t3 ON t2.bt3.b OR t2.b IS NULL)ON t1.at2.a;
------------------------
| a | a | b | b |
------------------------
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
------------------------mysql SELECT *FROM (t1 LEFT JOIN t2 ON t1.at2.a)LEFT JOIN t3ON t2.bt3.b OR t2.b IS NULL;
------------------------
| a | a | b | b |
------------------------
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
------------------------
在以下示例中外联操作与内联操作一起使用
t1 LEFT JOIN (t2, t3) ON t1.at2.a 该表达式无法转换为以下表达式
t1 LEFT JOIN t2 ON t1.at2.a, t3 对于给定的表状态这两个表达式返回不同的行集
mysql SELECT *FROM t1 LEFT JOIN (t2, t3) ON t1.at2.a;
------------------------
| a | a | b | b |
------------------------
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
------------------------mysql SELECT *FROM t1 LEFT JOIN t2 ON t1.at2.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和属性t2.b和t3.b上的任何条件P等价于该表达式
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)
每当联接表达式joined_table中联接操作的执行顺序不是从左到右时我们就讨论嵌套联接。请考虑以下查询
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.bt3.b) ON t1.at2.aWHERE t1.a 1SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.at2.aWHERE (t2.bt3.b OR t2.b IS NULL) AND t1.a 1 这些查询被认为包含这些嵌套联接
t2 LEFT JOIN t3 ON t2.bt3.b
t2, t3 第一个查询中嵌套联接是通过左联接操作形成的。 第二个查询中它是用内部联接操作形成的。 第一个查询括号可以省略联接表达式的语法结构规定了联接操作的相同执行顺序。 第二个查询不能省略括号尽管这里的联接表达式在没有括号的情况下可以被明确地解释。 在我们的扩展语法中第二个查询的t2t3中的括号是必需的尽管理论上可以在没有括号的情况下解析查询我们仍然会有明确的查询语法结构因为LEFT JOIN ON扮演着表达式t2t3的左右分隔符的角色。 前面的例子说明了以下几点 对于只涉及内部联接而不涉及外部联接的联接表达式可以删除圆括号并从左到右计算联接。事实上表可以按任何顺序进行评估。 博主ps你可以理解为内联接查询求交集的过程就是先把所有表都先求交集拼接起来先拼谁都差不多 通常对于外部联接或与内部联接混合的外部联接情况并非如此。删除括号可能会改变结果。 具有嵌套外部联接的查询以与具有内部联接的查询相同的管道方式执行。
更确切地说利用了嵌套循环连接算法的变体。调用嵌套循环联接执行查询的算法 请参阅“嵌套循环联接算法”。 假设对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)
这里P1T1T2和P2T3T3是一些连接条件关于表达式而PT1T2T3是表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表示该表的每一列的行。
例如
t1 || t2 || NULL表示通过串联行t1和t2的列而构造的行并且对于t3的每一列设为NULL。
这样的行被称为NULL补码。
现在考虑一个具有嵌套外部联接的查询
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;}f2TRUE;f1TRUE;}IF (!f2) {IF P(t1,t2,NULL) {t:t1||t2||NULL; OUTPUT t;}f1TRUE;}}IF (!f1) {IF P(t1,NULL,NULL) {t:t1||NULL||NULL; OUTPUT t;}}
}
通常对于外部联接操作中第一个内部表的任何嵌套循环都会引入一个标志该标志在循环之前为false在循环之后检查状态。
当外部表中的当前行与表示内部操作的表匹配时该标志为true。
如果在循环周期结束时标志仍然为false则未找到外部表的当前行的匹配项。在这种情况下该行由内部表的列的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条件PT1T2T3可以用一个连接公式表示
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;}}}
} 你可以看到连接词C1T1、C2T2、C3T3中的每一个都被从最内部的循环推到最外部的循环在那里它可以被评估。如果C1T1是一个非常严格的条件则此条件下推可以大大减少从表T1传递到内部循环的行数。因此查询的执行时间可能会大大提高。 博主PS 这里意思是如果可以减少外层for循环的查询次数将大大减少内层for循环的循环次数。这就是条件下推的意思。也就是将内层for循环的匹配条件推到外层判断。这样如果不匹配的话之间终结外层循环。 对于具有外部联接的查询只有在发现外部表中的当前行在内部表中匹配后才检查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 T2such that P1(t1,t2) AND (f1?C2(t2):TRUE) {BOOL f2:FALSE;FOR each row t3 in T3such that P2(t2,t3) AND (f1f2?C3(t3):TRUE) {IF (f1f2?TRUE:(C2(t2) AND C3(t3))) {t:t1||t2||t3; OUTPUT t;}f2TRUE;f1TRUE;}IF (!f2) {IF (f1?TRUE:C2(t2) P(t1,t2,NULL)) {t:t1||t2||NULL; OUTPUT t;}f1TRUE;}}IF (!f1 P(t1,NULL,NULL)) {t:t1||NULL||NULL; OUTPUT t;}
} 通常可以从诸如P1T1T2和PT2T3的联接条件中提取下推。
在这种情况下下推断言由一个标志保护该标志防止检查断言时由相应的外部联接操作生成的NULL补码行。 通过键从一个内部表访问同一嵌套联接中的另一个表是被禁止的如果它是由WHERE条件中的谓词引发的
