当条件之间有OR时，MySQL可以使用索引吗？-解网

问：

我有两个查询加上它自己的结果：EXPLAIN

一：

SELECT * 
FROM notifications 
WHERE id = 5204 OR seen = 3

基准（10,000 行）：0.861

二：

SELECT h.* FROM ((SELECT n.* from notifications n WHERE id = 5204) 
                    UNION ALL
                 (SELECT n.* from notifications n WHERE seen = 3)) h

基准（10,000 行）：2.064

上述两个查询的结果是相同的。此外，我还有以下两个索引：notifications

notifications(id) -- this is PK
notification(seen)

如您所知，通常会阻止索引的有效使用，这就是我编写第二个查询（由）。但是经过一些测试，我发现它仍然使用比使用快得多。所以我很困惑，我真的无法选择最适合我的选择。ORUNIONORUNION

根据一些合乎逻辑的合理解释，使用更好，但基准测试的结果说使用更好。如果我使用哪种方法，请您帮助我吗？unionOR

MySQL 数据库

虽然我不知道答案 - 我也发现OR语句可以杀死MySQL的性能。通常，我会更改诸如select a.*，从帐户a中加入帐户b，a.member_number = b.member_number或a.last_name = b.last_name。--> 从账户 A 中选择 a.*，在左加入账户 B a.member_number左加入账户 C，在 a.last_name = c.last_name 中，其中 b.account_id 不为 null 或 c.account_id 不为 null;为了绕过手术室条件的糟糕表现。我很好奇看到真正的答案......

1赞 Roland Starke 6/9/2016

如果您可以在第二个示例中删除它，看看它是否有所作为，那就太好了SELECT h.* FROM () h

0赞 Quassnoi 6/10/2016

@RolandStarke说了什么。来自文档：在 MySQL 5.7.6 之前，派生表总是具体化，而等效的视图引用有时会具体化，有时会合并。这种对等效查询的不一致处理可能会导致性能问题：不必要的派生表具体化会花费时间，并阻止优化程序将条件向下推送到派生表。

1赞 Quassnoi 6/10/2016

@Stack：或者？必须确保结果是唯一的，这就是为什么它可能会抛出另一个临时表。尝试添加到查询中，看看效果如何。UNIONUNION ALLUNIONid <> 5204seenUNION ALL

答：

11赞 John Bollinger 6/9/2016 #1

该案例的查询计划似乎表明它确实使用了索引，因此显然是可以的，至少在这种情况下是这样。这似乎是完全合理的，因为上有一个索引，并且是 PK。ORMySQLseenid

根据一些合乎逻辑和合理的解释，使用联合更好，但基准测试的结果说使用 OR 更好。

如果“合乎逻辑和合理的解释”与现实相矛盾，那么可以安全地假设逻辑是有缺陷的，或者解释是错误的或不适用的。众所周知，性能很难预测;在速度很重要的情况下，性能测试是必不可少的。

如果我使用哪种方法，请您帮助我吗？

您应该使用对输入进行更快测试的那个，该输入可以充分模拟程序在实际使用中看到的内容。

但是，还要注意，您的两个查询在语义上并不等效：如果 also has 的行，则查询将返回一次，但查询将返回两次。在正确的代码和不正确的代码之间进行选择是没有意义的，除非哪一个是正确的。id = 5204seen = 3ORUNION ALL

如果您提供的查询是产生正确结果的查询，则该查询是错误的。这不是一个真正的替代品。令人高兴的是，该查询似乎有一个非常有效的查询计划，并且在测试中也表现得更好。我不明白为什么选择哪个会有任何不确定性。ORUNION ALLOR

0赞 Martin AJ 6/9/2016

你知道吗？！存在不确定性，因为 Gordon（数据库主题的完美人选）建议我使用而不是 ..！你仍然认为我应该使用吗？UNIONOROR:-)

0赞 John Bollinger 6/9/2016

@Stack，Gordon 知识渊博，但在你链接的答案中，他建议你尝试一个子查询，看看它是否更快。他没有断言会更快。实际上，他说的和我做的完全一样：依靠性能测试。UNIONUNION

4赞 jkavalik 6/10/2016 #2

答案包含在您的问题中。OR 的 EXPLAIN 输出表示 - 这意味着正在使用优化，并且查询实际上是通过合并两个索引的结果来执行的。Using union(PRIMARY, seen)index_merge

因此，MySQL在某些情况下可以使用索引，并且在这种情况下可以使用索引。但是并不总是可用或不使用，因为索引的统计数据表明它不值得。在这些情况下，OR 可能比 UNION 慢得多（或者不是，如果您不确定，您需要始终检查两个版本）。index_merge

在您的测试中，您“很幸运”，MySQL自动为您进行了正确的优化。情况并非总是如此。

5赞 Quassnoi 6/10/2016 #3

index_merge顾名思义，使用 or for 和条件适当地组合两个索引的主键，然后通过 PK 查找表中的其余值。Sort Merge JoinSort Merge UnionANDOR

为此，两个索引上的条件应使每个索引按顺序生成主键（您的条件是）。

您可以在文档中找到条件的严格定义，但简而言之，您应该使用相等条件按索引的所有部分进行过滤，可能加上、或 PK。<=>

如果您有索引，这应该是（方括号中的部分不是必需的）。(col1, col2, col3)col1 = :val1 AND col2 = :val2 AND col3 = :val3 [ AND id > :id ]

以下情况将不起作用：

col1 = :val1 -- you omit col2 and col3

col1 = :val1 AND col2 = :val2 AND col3 > :val3 -- you can only use equality on key parts

作为免费的副作用，您的输出按排序。id

您可以使用以下方法获得类似的结果：

SELECT  *
FROM    (
        SELECT  5204 id
        UNION ALL
        SELECT  id
        FROM    mytable
        WHERE   seen = 3
                AND id <> 5204
        ) q
JOIN    mytable m
ON      m.id = q.id

，除了在早期版本的 MySQL 中，派生表必须具体化，这肯定会使查询性能变差，并且您的结果将不再按排序。id

简而言之，如果您的查询允许，那就去做吧。index_merge(union)

@Shafizadeh：不，这不行。您只能在 PK 上使用范围条件，以使条件符合条件。您提供的链接不包括 .该索引确实可用于范围访问，只是范围访问不会按 PK 顺序返回元组。我的回答中有一个文档链接，请随时阅读。index_mergeindex_merge

0赞 Rick James 10/11/2023 #4

（这个问题已经很老了，但我觉得有必要指出一些缺陷——无论是当时还是现在。

这两个查询结果不一定相同。将避免任何重复;将 [可能] 显示一行两次（一次表示，一次表示 .ORUNION ALLid=5204seen=3
较新版本的MySQL将更好地优化，避免使用临时表来收集每个子查询的结果。（此优化有局限性。继续需要临时表。（注意“使用临时”）UNION ALLUNION DISTINCT
“索引合并并集”很少发生，很难预测何时使用。“索引合并相交”几乎总是表示复合索引会使查询速度更快。
SELECT *是放缓的一部分。执行以下操作可能会更好。它更有效地获取 id，返回表以获取可能笨重的“*”。如果有大列，这也许可以解释为什么这么慢。UNION
```
  SELECT *
      FROM ( fetch just `id` from the OR or UNION or whatever ) AS x
      JOIN tbl USING (id);
```

上一个：如何删除所有本地提交并转到分支主节点（在远程存储库上）上的最后一次提交？[复制]

下一个：GROUP_BYs的两个 LEFT JOIN GROUP_CONCAT出现奇怪的重复行为

当条件之间有OR时，MySQL可以使用索引吗？

Can MySQL use Indexes when there is OR between conditions?

评论

评论

评论