SQL 性能联合与或

我刚刚读了一篇优化文章的一部分和 被切断了关于以下陈述的内容:

当使用带有 UNION的 OR替换 SQL 语句时:

select username from users where company = ‘bbc’ or company = ‘itv’;

致:

select username from users where company = ‘bbc’ union
select username from users where company = ‘itv’;

从一个简短的 EXPLAIN:

使用 OR:

enter image description here

使用 UNION:

enter image description here

这是不是意味着 UNION在 加倍工作量做？

虽然我认为 UNION对于某些 RDBMS 和某些表模式来说可能性能更好，但是这不是作者建议的 绝对正确。

提问

我说错了吗？

57169 次浏览
小开

这不是同一个问题。

我对 MySQL 没有太多的经验，所以我不确定查询优化器能做什么或不能做什么，但这里是我的一般背景(主要是 ms sql 服务器)的想法。

通常，查询分析器可以获取上述两个查询，并对它们制定完全相同的计划(如果它们是相同的) ，所以这并不重要。我怀疑这些查询之间没有性能差异(它们是等价的)

select distinct username from users where company = ‘bbc’ or company = ‘itv’;

还有

select username from users where company = ‘bbc’
union
select username from users where company = ‘itv’;

现在的问题是，下列查询之间是否有区别，我实际上不知道，但我怀疑优化器会让它更像第一个查询

select username from users where company = ‘bbc’ or company = ‘itv’;

还有

select username from users where company = ‘bbc’
union all
select username from users where company = ‘itv’;
小开

这取决于优化器最终根据数据的大小、索引、软件版本等做什么。

我认为使用 OR 会给优化器更好的机会来找到一些效率，因为所有的东西都在一个单一的逻辑语句中。

另外，UNION 有一些开销，因为它创建了一个重置 准备好了(没有重复)。 如果对 公司进行了索引，UNION 中的每个语句都应该执行得很快... ... 不确定它是否真的在做 双倍的工作。

总之

除非你真的迫切需要从你的查询中挤出每一点速度，最好还是用最能传达你的意图的形式——手术室

更新

我还想提到 IN。我相信下面的查询会比 OR (这也是我更喜欢的形式)提供更好的性能:

select username from users where company in ('bbc', 'itv');

小开

在几乎所有情况下，union或 union all版本都会对用户表进行两次完整的表扫描。

or版本在实践中要好得多，因为它只扫描表一次。如果可用，它还将只使用一次索引。

对于任何数据库和任何情况，最初的语句似乎都是错误的。

小开
最佳答案

要么你读的文章举了一个糟糕的例子，要么你误解了他们的观点。

select username from users where company = 'bbc' or company = 'itv';

这相当于:

select username from users where company IN ('bbc', 'itv');

MySQL 可以使用 company上的索引来完成这个查询，不需要执行任何 UNION 操作。

更棘手的情况是，您有一个包含两个 与众不同列的 OR条件。

select username from users where company = 'bbc' or city = 'London';

假设在 company上有一个索引，在 city上有一个单独的索引。考虑到 MySQL 通常在给定的查询中每个表只使用一个索引，它应该使用哪个索引？如果使用 company上的索引，它仍然需要进行表扫描，以找到 city位于伦敦的行。如果它使用 city上的索引，则必须对 company为 bbc 的行进行表扫描。

UNION解决方案适用于这种情况。

select username from users where company = 'bbc'
union
select username from users where city = 'London';

现在，每个子查询都可以使用索引进行搜索，子查询的结果由 UNION组合在一起。

一个匿名用户建议对我上面的答案进行编辑，但是一个版主拒绝了这个编辑。应该是评论，而不是编辑。提议编辑的要求是 UNION 必须对结果集进行排序，以消除重复行。这使得查询运行得更慢，因此索引优化是一种清洗。

我的回答是，索引有助于在 UNION 发生之前将结果集减少到少量行。UNION 实际上确实消除了重复，但要做到这一点，它只需对较小的结果集进行排序。在某些情况下，WHERE 子句可能匹配表的一个重要部分，在 UNION 期间进行排序的代价与简单地进行表扫描一样高昂。但是，通过索引搜索减少结果集的情况更为常见，因此排序的成本比表扫描低得多。

差异取决于表中的数据和正在搜索的术语。确定给定查询的最佳解决方案的唯一方法是尝试 MySQL 查询分析器中的两种方法并比较它们的性能。

小开

比尔 · 卡尔文的回答相当正确。当 OR 语句的两部分都有自己的索引时，最好进行联合，因为一旦有了结果的一个小子集，就可以更容易地对它们进行排序并消除重复。总成本几乎比只使用一个索引(对于一个列)和对另一个列进行表扫描(因为 mysql 只对一个列使用一个索引)要低。

它取决于表的结构和一般需求，但在大型表联合给我更好的结果。

小开

这是我的基准测试结果

使用 UNION 时，查询需要花费 13.8699秒
行检查主要选择类型 -247685

当使用 OR-Query 时占用 0.0126秒并且行检查为主 选择类型 -495371

MySQL 对一个查询使用一个索引，因此当我们使用 or时，MySQL 使用一个列索引并扫描另一个列的整个表

另一部分工会同样的工作可以2倍

这就是为什么或者说比工会更快


提交答案