mysql关于or前后条件的索引问题

        不是说，一条sql语句只能用一个索引么？但如下这样一条sql语句

SELECT * FROM `comment` WHERE `toconuid` = '10' or `tocomuid` = '10'

        其中toconuid列和tocomuid列分别为单列索引，explain后显示两个索引都用了，extra为 Using union(toconuid,tocomuid); Using where

        在这里，可以理解MYSQL把这个语句拆成了两条语句

SELECT * FROM `comment` WHERE `toconuid` = '10'
union
SELECT * FROM `comment` WHERE `tocomuid` = '10' 

一.索引合并优化

        索引合并方法用于通过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字段，有交集、联合、排序并集三种索引合并访问算法。

        注意：索引合并优化算法具有以下几个已知缺陷：

1).如果可以对某些关键字进行范围扫描，则不考虑索引合并。例如，下面的查询：

SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30;

        对于该查询，可以有两个方案：

        a.使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。

        b.使用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;

2).如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案，通过下面的识别法则尝试分布各条件：

        a.(x AND y) OR z = (x OR z) AND (y OR z)

        b.(x OR y) AND z = (x AND z) OR (y AND z)

        index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。

1.索引合并交集访问算法

        该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件，每个条件为下面之一：

        以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

        a.key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

        b.任何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表的主键的一个条件，不用于记录查询，但用于过滤使用其它条件搜索的记录。

 

2.索引合并并集访问算法

        该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候，每个条件为下面之一：

        以这种形式，即索引有确切的N部分(即包括了所有索引部分)：

        1).key_part1=const1 AND key_part2=const2 ... AND key_partN=constN

        2).任何InnoDB或BDB表的主键的范围条件。

        3).索引合并方法交集算法适用的一个条件。

        下面是一些例子：

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;

 

3.索引合并排序并集访问算法

        该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。

        下面是一些例子：

SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20;

 

文章来源：http://www.educity.cn/wenda/590849.html