MySQL: 如何为“OR”子句创建索引

Question

我正在执行以下查询

SELECT COUNT(*)
FROM table
WHERE field1='value' AND (field2 >= 1000 OR field3 >= 2000)

有一个索引是基于field1的，另一个索引则是由field2&field3组合而成。

我发现MySQL总是选择使用field1索引，然后再使用其他两个字段进行连接，这很糟糕，因为它需要连接146,000行。

有什么建议可以改进吗？谢谢。

(尝试方案后编辑)

根据所提供的解决方案，我在使用MySQL时看到了这个情况。

SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) AS unionTable;

运行速度比execute慢很多：

SELECT COUNT(*)
FROM table
WHERE (columnA = value1 AND columnB = value2)
      OR (columnA = value1 AND columnC = value3)

拥有两个复合索引：

index1 (columnA,columnB)
index2 (columnA,columnC)

有趣的是，请求Mysql "explain" 查询时，它总是在两种情况下都使用 index1 ，而不使用 index2。

如果我将索引更改为：

index1 (columnB,columnA)
index2 (columnC,columnA)

并且查询到：

SELECT COUNT(*)
FROM table
WHERE (columnB = value2 AND columnA = value1)
      OR (columnC = value3 AND columnA = value1)

那么这是我发现Mysql工作最快的方法。

- JoséMi

Answer 1

传统的拆分OR谓词的方法是使用UNION。

请注意，您的示例与索引不匹配。即使您从谓词中省略了field1，您仍将得到field2 >= 1000 OR field3 >= 2000，这无法使用索引。如果您在(field1, field2)和(field1,field3)或field2或field3上有索引，则可以获得相对较快的查询。

SELECT COUNT(*) FROM
(SELECT * FROM table WHERE field1 = 'value' AND field2 >= 1000
UNION
SELECT * FROM table WHERE field1 = 'value' AND field3 >= 2000) T

请注意，您必须为派生表提供别名，这就是为什么子查询被命名为T的原因。

一个真实世界的例子。列和表名已被匿名化！

mysql> SELECT COUNT(*) FROM table;
+----------+
| COUNT(*) |
+----------+
|  3059139 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnA = value1;
+----------+
| COUNT(*) |
+----------+
|     1068 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnB = value2;
+----------+
| COUNT(*) |
+----------+
|      947 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM table WHERE columnA = value1 OR columnB = value2;
+----------+
| COUNT(*) |
+----------+
|     1616 |
+----------+
1 row in set (9.92 sec)

mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
|     1616 |
+----------+
1 row in set (0.17 sec)

mysql> SELECT COUNT(*) FROM (SELECT * FROM table WHERE columnA = value1
UNION ALL SELECT * FROM table WHERE columnB = value2) T;
+----------+
| COUNT(*) |
+----------+
|     2015 |
+----------+
1 row in set (0.12 sec)

- David M

---

你有一个 bug：你正在重复计算同时满足两个条件（field2 >= 1000 和 field3 >= 2000）的元素。 （提示：使用临时表 ;)) - soulmerge

UNION默认为UNION DISTINCT。重复的行将在UNION结构的一部分中被删除。如果真的想要计算两次，则可以使用'UNION ALL'。您是否尝试在自己的类似表上尝试我建议的语句？ - David M

@DavidM 不错的 Pwnage。 - The Onin

Answer 2

我是新来的，所以不能评论其他人的帖子，但这与David M.和soulmerge的帖子有关。

临时表不是必需的。David M.建议的UNION没有重复计数，因为UNION意味着去重（即如果一行存在于联合的一半中，则在另一半中忽略它）。如果使用UNION ALL，则会得到两个记录。

UNION的默认行为是从结果中删除重复行。可选的DISTINCT关键字除了默认值之外没有任何效果，因为它还指定了去重。使用可选的ALL关键字，不会发生去重，并且结果包括所有匹配的行从所有SELECT语句。

http://dev.mysql.com/doc/refman/5.0/en/union.html

- Brandongk