MySQL：如何索引“ OR”子句

一尘不染

MySQL：如何索引“ OR”子句

mysql

我正在执行以下查询

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;

比执行慢很多：

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。

如果我将索引更改为：

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

和查询到：

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

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

阅读 689

2020-05-17

共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)

2020-05-17