关于MYSQL的优化全面详解

Categories: 数据库, 网站相关
Comments: No Comments
Published on: 2011 年 12 月 21 日
SELECT查询的速度

总的来说,要想使一个较慢速SELECT ... WHERE更快,应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。

下面是一些加速对MyISAM表的查询的一般建议:
· 为了帮助MySQL更好地优化查询,在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值(当然,如果只有一个索引,这总是1。)MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。

· 要想根据一个索引排序一个索引和数据,使用myisamchk --sort-index --sort-records=1(如果你想要在索引1上排序)。如果只有一个索引,想要根据该索引的次序读取所有的记录,这是使查询更快的一个好方法。但是请注意,第一次对一个大表按照这种方法排序时将花很长时间!

MySQL对WHERE子句的优化

这部分貌似mysql优化器做了,但是了解一下:
去除不必要的括号:
· ((a AND b) AND c OR (((a AND b) AND (c AND d))))
· -> (a AND b AND c) OR (a AND b AND c AND d)
· 常量重叠:
· (a b>5 AND b=c AND a=5
· 去除常量条件(由于常量重叠需要):
· (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
· -> B=5 OR B=6
· 索引使用的常数表达式仅计算一次。

对于MyISAM和HEAP表,在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时,对NOT NULL表达式也这样做。
无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
如果不使用GROUP BY或分组函数(COUNT()、MIN()……),HAVING与WHERE合并。
对于联接内的每个表,构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。
所有常数的表在查询中比其它表先读出。常数表为:
空表或只有1行的表。
与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表,这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。

范围优化

mysql分单元素索引的范围访问方法和多元素索引的范围访问方法
简单来讲区别就是:单元素索检条件只有一个字段,多元素的是多个字段。
MySQL尝试为每个可能的索引从WHERE子句提取范围条件。在提取过程中,不能用于构成范围条件的条件被放弃,产生重叠范围的条件组合到一起,并且产生空范围的条件被删除。索检出信息后,MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。
范围条件提取算法可以处理嵌套的任意深度的AND/OR结构,并且其输出不依赖条件在WHERE子句中出现的顺序。

索引合并优化

索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中,该方法表现为type列内的index_merge。在这种情况下,key列包含一列使用的索引,key_len包含这些索引的最长的关键元素。

索引合并方法有几种访问算法 (参见EXPLAIN输出的Extra字段):
· 交集
· 联合
· 排序并集

索引合并优化算法具有以下几个已知缺陷:
· 如果可以对某些关键字进行范围扫描,则不考虑索引合并。例如,下面的查询:
· SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; 对于该查询,可以有两个方案: 1. 使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。 2. 使用badkey < 30条件进行范围扫描。 然而,优化器只考虑第2个方案。如果这不是你想要的,你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行: SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; SELECT * FROM t1 IGNORE INDEX(badkey) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; · 如果查询有一个复杂的WHERE子句,有较深的AND/OR嵌套关系,MySQL不选择该优选方案,通过下面的识别法则尝试分布各条件: · (x AND y) OR z = (x OR z) AND (y OR z) · (x OR y) AND z = (x AND z) OR (y AND z) index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。 索引合并交集访问算法: 该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件,每个条件为下面之一: · 以这种形式,即索引有确切的N部分(即包括了所有索引部分): · key_part1=const1 AND key_part2=const2 ... AND key_partN=constN · 任何InnoDB或BDB表的主键的范围条件。 下面是一些例子: SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20; SELECT * FROM tbl_name WHERE (key1_part1=1 AND key1_part2=2) AND key2=2; 索引合并交集算法同时对所有使用的索引进行扫描,并产生从合并的索引扫描接收的行序列的交集。 如果使用的索引包括查询中使用的所有列,所有表记录均不搜索,并且在这种情况下EXPLAIN的输出包含Extra字段中的Using index。下面是一个此类查询的例子: SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1; 如果使用的索引未包括查询中使用的所有列,只有满足所有使用的关键字的范围条件才搜索所有记录。 如果某个合并条件是InnoDB或BDB表的主键的一个条件,不用于记录查询,但用于过滤使用其它条件搜索的记录。 索引合并并集访问算法: 该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候,每个条件为下面之一: · 以这种形式,即索引有确切的N部分(即包括了所有索引部分): · key_part1=const1 AND key_part2=const2 ... AND key_partN=constN · 任何InnoDB或BDB表的主键的范围条件。 · 索引合并方法交集算法适用的一个条件。 下面是一些例子: SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3; SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3='foo' AND key4='bar') AND key5=5; 索引合并排序并集访问算法: 该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件,但索引合并方法联合算法并不适用的时候。 下面是一些例子: SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30;
排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID,然后在返回记录前对它们进行排序。

MySQL如何优化IS NULL

MySQL可以对可以结合col_name = constant_value使用的col_name IS NULL进行相同的优化。例如,MySQL可以使用索引和范围用IS NULL搜索NULL。

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
如果WHERE子句包括声明为NOT NULL的列的col_name IS NULL条件,表达式则优化。当列会产生NULL时,不会进行优化;例如,如果来自LEFT JOIN右侧的表。

MySQL也可以优化组合col_name = expr AND col_name IS NULL,这是解决子查询的一种常用形式。当使用优化时EXPLAIN显示ref_or_null。该优化可以为任何关键元素处理IS NULL。

MySQL如何优化DISTINCT

在许多情况下结合ORDER BY的DISTINCT需要一个临时表。请注意因为DISTINCT可能使用GROUP BY,必须清楚MySQL如何使用所选定列的一部分的ORDER BY或HAVING子句中的列。

在大多数情况下,DISTINCT子句可以视为GROUP BY的特殊情况。例如,下面的两个查询是等效的:
SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const;

SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3;
由于这个等效性,适用于GROUP BY查询的优化也适用于有DISTINCT子句的查询。这样,关于DISTINCT查询的优化的更详细的情况,参见,“MySQL如何优化GROUP BY”。
结合LIMIT row_count和DISTINCT后,MySQL发现唯一的row_count行后立即停止。

如果不使用查询中命名的所有表的列,MySQL发现第1个匹配后立即停止扫描未使用的表。在下面的情况中,假定t1在t2之前使用(可以用EXPLAIN检查),发现t2中的第1行后,MySQL不再(为t1中的任何行)读t2:

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

MySQL如何优化LEFT JOIN和RIGHT JOIN

在MySQL中,A LEFT JOIN B join_condition执行过程如下:
· 根据表A和A依赖的所有表设置表B。
· 根据LEFT JOIN条件中使用的所有表(除了B)设置表A。
· LEFT JOIN条件用于确定如何从表B搜索行。(换句话说,不使用WHERE子句中的任何条件)。
· 可以对所有标准联接进行优化,只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系,MySQL提示出现一个错误。
· 进行所有标准WHERE优化。
· 如果A中有一行匹配WHERE子句,但B中没有一行匹配ON条件,则生成另一个B行,其中所有列设置为NULL。
· 如果使用LEFT JOIN找出在某些表中不存在的行,并且进行了下面的测试:WHERE部分的col_name IS NULL,其中col_name是一个声明为 NOT NULL的列,MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。

RIGHT JOIN的执行类似LEFT JOIN,只是表的角色反过来。
联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作,因为检查的表交换更少。请注意这说明如果执行下面类型的查询,MySQL进行全扫描b,因为LEFT JOIN强制它在d之前读取:
SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
在这种情况下修复时用a的相反顺序,b列于FROM子句中:
SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key;
MySQL可以进行下面的LEFT JOIN优化:如果对于产生的NULL行,WHERE条件总为假,LEFT JOIN变为普通联接。
例如,在下面的查询中如果t2.column1为NULL,WHERE 子句将为false:
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
因此,可以安全地将查询转换为普通联接:
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
这样可以更快,因为如果可以使查询更佳,MySQL可以在表t1之前使用表t2。为了强制使用表顺序,使用STRAIGHT_JOIN。

MySQL如何优化嵌套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,1,101,101}、{2,NULL,NULL,NULL}的结果,第2个表达式返回行{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 |
+------+------+------+------+

在下面的例子中,外面的联接操作结合内部联接操作使用:
t1 LEFT JOIN (t2,t3) ON t1.a=t2.a
该表达式不能转换为下面的表达式:
t1 LEFT JOIN t2 ON t1.a=t2.a,t3.
对于给定的表状态,第1个表达式返回行{1,1,101,101}、{2,NULL,NULL,NULL},第2个表达式返回行{1,1,101,101}、{2,NULL,NULL,101}:

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、t3和属性t2.b和t3.b的任何条件P,下面的表达式:
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
等价于表达式:
t1,t2 LEFT JOIN t3 ON P(t2.b,t3.b)
如果联接表达式(join_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
认为是嵌套的。第1个查询结合左联接操作则形成嵌套的联接,而在第二个查询中结合内联接操作形成嵌套联接。

在第1个查询中,括号可以忽略:联接表达式的语法结构与联接操作的执行顺序相同。但对于第2个查询,括号不能省略,尽管如果没有括号,这里的联接表达式解释不清楚。(在外部扩展语法中,需要第2个查询的(t2,t3)的括号,尽管从理论上对查询分析时不需要括号:这些查询的语法结构将仍然不清楚,因为LEFT JOIN和ON将充当表达式(t2,t3)的左、右界定符的角色)。

前面的例子说明了这些点:
· 对于只包含内联接(而非外联接)的联接表达式,可以删除括号。你可以移除括号并从左到右评估(或实际上,你可以按任何顺序评估表)。
· 总的来说,对外联接却不是这样。去除括号可能会更改结果。
· 总的来说,对外联接和内联接的结合,也不是这样。去除括号可能会更改结果。
含嵌套外联接的查询按含内联接的查询的相同的管道方式执行。更确切地说,利用了嵌套环联接算法。让我们回忆嵌套环联接执行查询时采用什么算法。

假定我们有一个如下形式的表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的列以及t3的每个列的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;
}
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;
}
}
}
}

对于有外联接的查询,优化器可以只选择这样的顺序:外表的环优先于内表的环。这样,对于有外联接的查询,只可能有一种嵌套顺序。在下面的查询中,优化器将评估两个不同的嵌套:

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
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) {
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;
}
}
}

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

在两个嵌套中,必须在外环中处理T1,因为它用于外联接中。T2和T3用于内联接中,因此联接必须在内环中处理。但是,因为该联接是一个内联接,T2和T3可以以任何顺序处理。

当讨论内联接嵌套环的算法时,我们忽略了部分详情,可能对查询执行的性能的影响会很大。我们没有提及所谓的“下推”条件。假定可以用连接公式表示我们的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条件。这样,对内嵌套环下推的条件不能直接用于带外联接的查询。这里我们必须引入有条件下推前提,由遇到匹配后打开的标志保护。

对于带下面的外联接的例子
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-补充的行的断言。

请注意如果从判断式的WHERE条件推导出,根据从一个内表到相同嵌套联接的另一个表的关键字进行的访问被禁止。(在这种情况下,我们可以使用有条件关键字访问,但是该技术还未用于MySQL 5.1中)。

MySQL如何简化外部联合

在许多情况下,一个查询的FROM子句的表的表达式可以简化。

在分析阶段,带右外联接操作的查询被转换为只包含左联接操作的等效查询。总的来说,根据以下原则进行转换:

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
所有T1 INNER JOIN T2 ON P(T1,T2)形式的内联接表达式被替换为T1,T2、P(T1,T2),并根据WHERE条件(或嵌入连接的联接条件,如果有)联接为一个连接。

当优化器为用外联接操作的联接查询评估方案时,它只考虑在访问内表之前访问外表的操作的方案。优化器选项受到限制,因为只有这样的方案允许我们用嵌套环机制执行带外联接操作的查询。

假定我们有一个下列形式的查询:
SELECT * T1 LEFT JOIN T2 ON P1(T1,T2)
WHERE P(T1,T2) AND R(T2)

R(T2)大大减少了表T2中匹配的行数。如果我们这样执行查询,优化器将不会有其它选择,只能在访问表T2之前访问表T1,从而导致执行方案非常低。

幸运的是,如果WHERE条件拒绝null,MySQL可以将此类查询转换为没有外联接操作的查询。如果为该操作构建的NULL补充的行评估为FALSE或UNKNOWN,则该条件称为对于某个外联接操作拒绝null。

因此,对于该外联接:
T1 LEFT JOIN T2 ON T1.A=T2.A
类似下面的条件为拒绝null:

T2.B IS NOT NULL,
T2.B > 3,
T2.C <= T1.C, T2.B < 2 OR T2.C > 1
类似下面的条件不为拒绝null:

T2.B IS NULL,
T1.B < 3 OR T2.B IS NOT NULL, T1.B < 3 OR T2.B > 3
检查一个外联接操作的条件是否拒绝null的总原则很简单。以下情况下为拒绝null的条件:
· 形式为A IS NOT NULL,其中A是任何内表的一个属性
· 包含内表引用的判断式,当某个参量为NULL时评估为UNKNOWN
· 包含用于连接的拒绝null的条件的联合
· 拒绝null的条件的逻辑和

一个条件可以对于一个查询中的一个外联接操作为拒绝null的而对于另一个不为拒绝null的。在下面的查询中:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0

WHERE条件对于第2个外联接操作为拒绝null的但对于第1个不为拒绝null的。

如果WHERE条件对于一个查询中的一个外联接操作为拒绝null的,外联接操作被一个内联接操作代替。

例如,前面的查询被下面的查询代替:
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T1.B
WHERE T3.C > 0

对于原来的查询,优化器将评估只与一个访问顺序T1、T2、T3兼容的方案。在替换的查询中,还考虑了访问顺序T3、T1、T2。

一个外联接操作的转化可以触发另一个的转化。这样,查询:
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

INNER JOIN T3 ON T3.B=T2.B

WHERE T3.C > 0

该查询等效于查询:
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B

现在剩余的外联接操作也可以被一个内联接替换,因为条件T3.B=T2.B为拒绝null的,我们可以得到一个根本没有外联接的查询:
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B

有时我们可以成功替换嵌入的外联接操作,但不能转换嵌入的外联接。下面的查询:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0

被转换为:
SELECT * FROM T1 LEFT JOIN
(T2 INNER JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A
WHERE T3.C > 0,

只能重新写为仍然包含嵌入式外联接操作的形式:
SELECT * FROM T1 LEFT JOIN
(T2,T3)
ON (T2.A=T1.A AND T3.B=T2.B)
WHERE T3.C > 0。

如果试图转换一个查询中的嵌入式外联接操作,我们必须考虑嵌入式外联接的联接条件和WHERE条件。在下面的查询中:
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B)
ON T2.A=T1.A AND T3.C=T1.C
WHERE T3.D > 0 OR T1.D > 0
WHERE条件对于嵌入式外联接不为拒绝null的,但嵌入式外联接T2.A=T1.A AND T3.C=T1.C的联接条件为拒绝null的。因此该查询可以转换为:
SELECT * FROM T1 LEFT JOIN
(T2, T3)
ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B
WHERE T3.D > 0 OR T1.D > 0

我猜你可能也喜欢:

Pages: 1 2 3 4 5
No Comments - Leave a comment

Leave a comment

电子邮件地址不会被公开。 必填项已用*标注

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Welcome , today is 星期三, 2018 年 09 月 26 日