我有一个具有属性the_table.id
、the_table.firstVal
和the_table.secondVal
的表the_table
(当然,主键是the_table.id
(。
在第一个非键属性上定义索引后,如下所示:
CREATE INDEX idx_firstval
ON the_table (firstVal);
以下析取(OR
(查询的EXPLAIN
结果
SELECT * FROM the_table WHERE the_table.firstVal = 'A' OR the_table.secondVal = 'B';
是
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| 1 | SIMPLE | the_table | ALL | idx_firstval | NULL | NULL | NULL | 3436 | Using where
这表明没有使用索引CCD_ 8。现在,下面的连接(AND
(查询的EXPLAIN
结果
SELECT * FROM the_table WHERE the_table.firstVal = 'A' AND the_table.secondVal = 'B';
是
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
| 1 | SIMPLE | the_table | ref | idx_firstval | idx_firstval | 767 | const | 124 | Using index condition; Using where
这一次显示了正在使用的索引。
为什么MySQL选择不将索引用于析取查询,而将索引用于合取查询?
我已经搜索过SO,正如本线程中的答案所建议的,"在查询中使用OR
通常会导致查询优化器放弃使用索引查找并恢复到扫描"。然而,这并不能回答为什么会发生,只是确实发生了。
另一个线程试图回答为什么析取查询不使用索引,但我认为它做不到——它只是得出结论,OP使用的是一个小数据库。我想知道析取格和合取格之间的区别。
因为MySQL执行计划只为一个表使用一个索引。
如果MySQL在idx_firstval
上使用范围扫描来满足firstVal
列上的相等谓词,那么MySQL仍然需要检查secondVal
列上的条件。
对于AND
,MySQL只需要检查从索引的范围扫描返回的行。需要检查的行集受条件约束。
使用OR
,MySQL需要检查索引范围扫描未返回的行,以及表中的所有其他行。如果没有索引,这意味着要对表进行完全扫描。如果我们对表进行全扫描以检查secondVal
,那么在扫描中检查这两个条件的成本将更低(即,包括索引访问和全扫描的计划将更昂贵(
(如果同时包含firstVal和secondVal的复合索引可用,那么对于OR
查询,优化器可能会认为通过进行完整索引扫描,然后查找数据页来检查表中的所有行成本较低。(
当我们了解优化器可以使用哪些操作时,这将导致我们避免OR
并重写查询,返回等效的结果集,使用更明确地定义两个集组合的查询模式
SELECT a.*
FROM the_table a
WHERE a.firstVal = 'A'
UNION ALL
SELECT b.*
FROM the_table b
WHERE b.secondVal = 'B'
AND NOT ( b.firstVal <=> 'A' )
(如果我们希望按特定顺序返回行,请添加ORDER BY(
我很惊讶MySQL对这两个查询都使用了索引。这里要使用的正确索引是一个复合索引,它覆盖了WHERE
子句中的两列:
CREATE INDEX idx ON the_table (firstVal, secondVal);
至于MySQL为什么在第二种情况下使用索引,一种可能是the_table
中的大多数记录都有firstVal
值,这些值是而不是A
。在这种情况下,仅仅知道等式the_table.firstVal = 'A'
为假就意味着WHERE
子句的整个结果将是已知的(为假(。因此,使用索引的原因可能与确切数据的基数有关。但在任何情况下,都可以考虑使用综合指数来覆盖所有基数。