条件之间存在OR时，MySQL可以使用索引吗? [英] Can MySQL use Indexes when there is OR between conditions?

问题描述

我有两个查询以及它自己的EXPLAIN的结果:

I have two queries plus its own EXPLAIN's results:

一个:

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表上有这两个索引:

The result of two queries above is identical. Also I have these two indexes on notifications table:

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

如您所知，OR通常会阻止索引的有效使用，这就是为什么我编写第二个查询(通过UNION的原因)的原因.但是经过一些测试，我发现仍然使用OR的速度要比使用UNION的速度快得多.因此，我很困惑，我真的无法选择适合自己情况的最佳选择.

As you know, OR usually prevents effective use of indexes, That's why I wrote second query (by UNION). But after some tests I figured it out which still using OR is much faster that using UNION. So I'm confused and I really cannot choose the best option in my case.

基于一些合理合理的解释，使用union更好，但是基准测试结果表明使用OR更好.我可以使用哪种方法，您能帮我吗?

Based on some logical and reasonable explanations, using union is better, but the result of benchmark says using OR is better. May you please help me should I use which approach?

推荐答案

针对OR案例的查询计划似乎表明MySQL确实在使用索引，因此显然可以，至少在此方面它可以这样做.案子.这似乎是完全合理的，因为seen上有一个索引，而id是PK.

The query plan for the OR case appears to indicate that MySQL is indeed using indexes, so evidently yes, it can do, at least in this case. That seems entirely reasonable, because there is an index on seen, and id is the PK.

根据一些合理合理的解释，使用联合会更好，但是基准测试结果表明使用OR会更好.

Based on some logical and reasonable explanations, using union is better, but the result of benchmark says using OR is better.

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

If "logical and reasonable explanations" are contradicted by reality, then it is safe to assume that the logic is flawed or the explanations are wrong or inapplicable. Performance is notoriously difficult to predict; performance testing is essential where speed is important.

请问我可以使用哪种方法吗?

May you please help me should I use which approach?

您应该在输入上使用测试更快的输入，以充分模拟程序在实际使用中会看到的内容.

You should use the one that tests faster on input that adequately models that which the program will see in real use.

但是，请注意，您的两个查询在语义上并不等效:如果带有id = 5204的行也具有seen = 3，则OR查询将返回一次，但是UNION ALL查询将返回两次.除了正确的代码之外，在任何正确的代码和不正确的代码之间进行选择都是毫无意义的.

Note also, however, that your two queries are not semantically equivalent: if the row with id = 5204 also has seen = 3 then the OR query will return it once, but the UNION ALL query will return it twice. It is pointless to choose between correct code and incorrect code on any basis other than which one is correct.

这篇关于条件之间存在OR时，MySQL可以使用索引吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！