7.2.6. 索引合并优化
7.2.6.1. 索引合并交集访问算法 7.2.6.2. 索引合并并集访问算法 7.2.6.3. 索引合并排序并集访问算法
索引合并方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。
在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。 例如：
SELECT * FROM tbl_name WHERE key_part1 = 10 OR key_part2 = 20;
SELECT * FROM tbl_name
WHERE (key_part1 = 10 OR key_part2 = 20) AND non_key_part=30;
SELECT * FROM t1, t2
WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1=t1.some_col;
SELECT * FROM t1, t2 WHERE t1.key1=1
AND (t2.key1=t1.some_col OR t2.key2=t1.some_col2);
索引合并方法有几种访问算法 (参见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访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。
7.2.6.1. 索引合并交集访问算法
该访问算法可以用于当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表的主键的一个条件，不用于记录查询，但用于过滤使用其它条件搜索的记录。 7.2.6.2. 索引合并并集访问算法
该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当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;
7.2.6.3. 索引合并排序并集访问算法
该访问算法可以用于当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，然后在返回记录前对它们进行排序。
7.2.7. 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。
下面是一些优化的查询例子，假定表t2的列a和b有一个索引：
SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;
SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;
SELECT * FROM t1, t2
WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;
SELECT * FROM t1, t2
WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) OR (t1.a=t2.a AND t2.a IS NULL AND ...);
ref_or_null首先读取参考关键字，然后单独搜索NULL关键字的行。
请注意该优化只可以处理一个IS NULL。在后面的查询中，MySQL只对表达式(t1.a=t2.a AND t2.a IS NULL)使用关键字查询，不能使用b的关键元素：
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);
7.2.8. MySQL如何优化DISTINCT
在许多情况下结合ORDER BY的DISTINCT需要一个临时表。
请注意因为DISTINCT可能使用GROUP BY，必须清楚MySQL如何使用所选定列的一部分的ORDER BY或HAVING子句中的列。参见12.10.3节，“具有隐含字段的GROUP BY”。
在大多数情况下，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查询的优化的更详细的情况，参见7.2.13节，“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;
7.2.9. 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;