如何提高UNION ALL的MYSQL性能？

I am optimizing a query which involves a UNION ALL of two queries. Both Queries have been more than optimized and run at less than one second separately. However, when I perform the union of both, it takes around 30 seconds to calculate everything.

我正在优化一个涉及UNION ALL两个查询的查询。两种查询都经过优化,并且分别在不到一秒的时间内运行。但是,当我执行两者的并集时,计算所有内容大约需要30秒。

I won't bother you with the specific query, since they are optimized as they get, So let's call them Optimized_Query_1 and Optimized_Query_2

我不打扰你使用特定的查询,因为它们在获得时进行了优化,所以让我们称之为Optimized_Query_1和Optimized_Query_2

Number of rows from Optimized_Query_1 is roughly 100K

Optimized_Query_1的行数大约为100K

Number of rows from Optimized_Query_2 is roughly 200K

Optimized_Query_2的行数大约为200K

SELECT * FROM (
   Optimized_Query_1
UNION ALL
   Optimized_Query_2
) U 
ORDER BY START_TIME ASC

I do require for teh results to be in order, but I find that with or without the ORDER BY at the end the query takes as much time so shouldn't make no difference.

我确实要求结果有序,但我发现无论有没有ORDER BY,查询都需要花费尽可能多的时间,所以不应该有任何区别。

Apparently the UNION ALL creates a temporary table in memory, from where the final results are then given, is there any way to work around this?

显然UNION ALL在内存中创建了一个临时表,然后从那里得到最终结果,有什么方法可以解决这个问题吗?

Thanks

1 个解决方案

#1

You can't optimize UNION ALL. It simply stacks the two results sets on top of each other. Compared to UNION where an extra step is required to remove duplicates, UNION ALL is a straight stacking of the two result sets. The ORDER BY is likely taking additional time.

您无法优化UNION ALL。它只是将两个结果集叠加在一起。与需要额外步骤来删除重复项的UNION相比,UNION ALL是两个结果集的直接堆叠。 ORDER BY可能需要额外的时间。

You can try creating a VIEW out of this query.

您可以尝试使用此查询创建VIEW。