为什么在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 aUNION 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屋!