mysql - 在MySQL中，在不同表中的两个字段之间使用或条件时如何充分利用索引？

Question

select
    tableA.field1,
    tableB.field2
from
    tableA
    inner join tableB on table1.someField = table2.someField
where
    tableA.field1 = 'value1'
    or tableB.field2 = 'value2'

我在 tableA.field1 和 tableB.field2 上都有很好的索引。如果我将 where 子句从“or”更改为“and”，那么查询速度会很快。然而，当使用 or 时，它似乎只能使用两个索引之一。

两个表都包含数 100,000 条记录。

当 where 子句中有 or 时，是否可以重新设计查询以使用两个索引？

Answer 1

我会重写查询以组合两个单独的集合，避免 WHERE 子句中的 OR。

类似这样的事情:

( SELECT a1.field1
       , b1.field2
    FROM tableA a1
    JOIN tableB b1
      ON b1.somefield = a1.somefield
   WHERE a1.field1 = 'value1'
)   
UNION ALL
( SELECT a2.field1
       , b2.field2
    FROM tableA a2
    JOIN tableB b2
      ON b2.somefield = a2.somefield
   WHERE b2.field2 = 'value2'
  HAVING NOT (a2.field1 <=> 'value1')
)

该查询应该能够有效地利用这些索引，用于 SELECT

 ... ON tableA (field1,somefield,...)
 ... ON tableB (somefield,field2,...)

第二个 SELECT

 ... ON tableA (somefield,field1,...)
 ... ON tableB (field2,somefield,...)

使用EXPLAIN查看执行计划

<小时/>

查看返回的集合的另一种方法是作为三个集合的组合...

• 在 a.field1 和 b.field2 上都匹配的行
• 在 a.field1 上匹配但在 b.field2 上不匹配的行
• 在 b.field2 上匹配但在 a.field1 上不匹配的行

第一个子集是原始查询，将 OR 替换为 AND。

可以返回最后两个子集中的每一个

SELECT ...
  FROM a
  JOIN b
    ON ...
 WHERE a.field1 = 'value1'
   AND NOT ( b.field2 <=> 'value2' )

和

SELECT ...
  FROM a
  JOIN b
    ON ...
 WHERE b.field2 = 'value2'
   AND NOT ( a.field1 <=> 'value1' )

为每个SELECT提供合适的索引，并使用UNION ALL集合运算符组合集合。