为什么在MySQL中UNION查询这么慢? [英] Why are UNION queries so slow in MySQL?

问题描述

当我优化我的2个单个查询以在0.02秒内运行，然后UNION优化它们时，结果查询将花费1秒钟以上的时间来运行.同样，UNION ALL花费的时间比UNION DISTINCT长.

When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run. Also, a UNION ALL takes longer than a UNION DISTINCT.

我假设允许重复将使查询运行更快而不是更慢. 我真的最好单独运行两个查询吗? 我更喜欢使用UNION.

I would assume allowing duplicates would make the query run faster and not slower. Am I really just better off running the 2 queries separately? I would prefer to use the UNION.

举一个简单的例子

SELECT name FROM t1 WHERE field1 = true

花费.001秒

如果可以的话

SELECT name FROM t1 WHERE field1 = false

需要0.1秒钟.

如果我再运行

SELECT name FROM t1 WHERE field1 = true 
UNION ALL 
SELECT name FROM t1 WHERE field1 = false

需要1秒钟以上.

推荐答案

当我优化我的2个单个查询以在0.02秒内运行，然后对它们进行UNION时，结果查询将花费1秒钟以上的时间来运行.

When I optimize my 2 single queries to run in less than 0.02 seconds and then UNION them the resulting query takes over 1 second to run.

您的查询中是否包含ORDER BY … LIMIT子句?

Do your queries include ORDER BY … LIMIT clauses?

如果将ORDER BY … LIMIT放在UNION之后，则会将其应用于整个UNION，并且在这种情况下不能使用索引.

If you put an ORDER BY … LIMIT after a UNION, it gets applied to the whole UNION, and indexes cannot be used in this case.

如果id是主键，则该查询将是即时的:

If id is a primary key, this query will be instant:

SELECT  *
FROM    table
ORDER BY id
LIMIT 1

，但是这一点不会:

SELECT  *
FROM    table
UNION ALL
SELECT  *
FROM    table
ORDER BY id
LIMIT 1

此外，UNION ALL比UNION DISTINCT需要更长的时间.我认为允许重复将使查询运行更快而不是更慢.

Also, a UNION ALL takes longer than a UNION DISTINCT. I would assume allowing duplicates would make the query run faster and not slower.

这似乎也应归因于ORDER BY.对较小的集合进行排序要比对较大的集合进行排序.

This also seems to be due to ORDER BY. Sorting a smaller set is faster than a larger one.

我真的最好单独运行两个查询吗?我更喜欢使用UNION

是否需要对结果集进行排序?

Do you need the resulting set to be sorted?

如果没有，则删除最后的ORDER BY.

If not, just get rid of the final ORDER BY.

这篇关于为什么在MySQL中UNION查询这么慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！