MySQL购物车查询OR条件索引失效 如何用UNION等方案优化提速

报错原因说明 #

你之前写的UNION嵌套查询触发1054错误，是因为MySQL的关联子查询仅支持向上引用1层外部查询的字段，你的UNION逻辑被包裹在两层子查询内，最内层的u1、u2查询和外层c表间隔了两层，无法识别c.email、c.user字段。

最优改写方案 #

利用集合运算公式：满足A OR B条件的不重复ID数量 = 满足A的数量 + 满足B的数量 - 同时满足A和B的数量，完全规避OR条件，每个子查询都可以命中现有索引：

SELECT 
    c.number,
    (
        -- 统计同邮箱的订单数
        (SELECT COUNT(DISTINCT id) FROM c AS c2 WHERE c2.email = c.email)
        +
        -- 统计同用户ID的订单数
        (SELECT COUNT(DISTINCT id) FROM c AS c3 WHERE c3.user = c.user)
        -
        -- 减去同时满足同邮箱、同用户ID的重复统计部分
        (SELECT COUNT(DISTINCT id) FROM c AS c4 WHERE c4.email = c.email AND c4.user = c.user)
    ) AS ordercount
FROM c;

结果验证 #

该查询返回的ordercount和你原有语句的结果完全一致，测试数据返回如下：

+--------+------------+
| number | ordercount |
+--------+------------+
| 00001  |          3 |
| 00002  |          1 |
| 00003  |          1 |
| 00004  |          3 |
| 00005  |          3 |
+--------+------------+

性能说明 #

改写后的三个子查询分别命中email、user索引，第三个子查询的AND条件也可以命中两个索引中的任意一个，执行计划全部为ref级别的索引查询，50万数据量下查询50条的耗时和单条件查询接近，可从原有30秒降低至1秒左右。
该方案兼容MySQL 5.7及以上所有版本，无需修改表结构和索引配置。

内容的提问来源于stack exchange，提问作者Werner