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

2012-05-13

mysql 关于or前后条件的索引问题不是说，一条sql语句只能用一个索引么我写了这样一条sql语句SELECT * FROM

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'
[解决办法]

引用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，然后在返回记录前对它们进行排序。

[解决办法]
不一定。一条语句可以用多个索引。
[解决办法]
是大多数情况一条sql语句只能用一个索引

热点排行

Mysql

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