MySQL OR 查询优化

最近在优化 SQL 语句的时候发现一个问题:MySQL 在执行 OR 查询的时候,即使有多个字段能够使用索引,也只会使用其中一个字段的索引。然后在网上找到了如下答案,贴在这里以备参考。

注意:以下内容完全拷贝自 Stack Overflow。


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

2. UNION is UNION DISTINCT by default. Duplicate rows are removed as part of the UNION construct. If one really to count them twice, one would use ‘UNION ALL’.

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)

参考:

https://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause

https://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html

 414 total views,  2 views today

Leave a Reply

Your email address will not be published. Required fields are marked *