MySQL：如何索引“OR”条款 [英] MySQL: how to index an "OR" clause

问题描述

我正在执行以下查询

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

有field1上的一个索引和field2& field3上的另一个索引。

There is one index over field1 and another composited over field2&field3.

我看到MySQL总是选择field1索引，然后使用另外两个字段进行连接，这非常糟糕，因为它需要加入146.000行。

I see MySQL always selects the field1 index and then makes a join using the other two fields which is quite bad because it needs to join 146.000 rows.

关于如何改进这一点的建议？谢谢

Suggestions on how to improve this? Thanks

（建议尝试解决方案后编辑）

(EDIT AFTER TRYING SOLUTION PROPOSED)

基于提出的解决方案我在Mysql上看过这个玩这个时。

Based in the solution proposed I've seen this on Mysql when playing with this.

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

比执行慢很多：

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

有两个合成索引：

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

有趣的是要求Mysql解释它在两种情况下总是使用index1并且不使用index2的查询。

Interesting enough is that asking Mysql to "explain" the query it's taking always index1 on both cases and index2 is not used.

如果我将索引更改为：

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

查询到：

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

然后这是我发现Mysql工作的最快方式。

Then it's the fastest way I've found Mysql works.

推荐答案

分解 OR 谓词的典型方法是 UNION 。

The typical way to break up OR predicates is with UNION.

请注意，您的示例不适合您的索引。即使你从谓词中省略了 field1 ，你也有 field2> = 1000 OR field3> = 2000 ，不能使用索引。如果你有（field1，field2）和（field1，field3）或 field2的索引或 field3 另外，你会得到一个相当快的查询。

Note that your example doesn't fit well with your indexes. Even if you omitted field1 from the predicate, you'd have field2 >= 1000 OR field3 >= 2000, which can't use an index. If you had indexes on (field1, field2) and (field1,field3) or field2 or field3 separately, you would get a reasonably fast query.

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

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

Note that you have to provide an alias for the derived table, which is why the subquery is aliased as T.

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

A real-world example. Column and table names have been anonymized!

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)

这篇关于MySQL：如何索引“OR”条款的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！