SQL查询-联合-多个相同的表需要很长时间。

I have a big problem with my query to the database.

我对数据库的查询有一个大问题。

I explained my problem, I have 11 identical tables (backups). The tables are exactly the same and I need to pull data from them, but it takes a very long time.

我解释了我的问题，我有11个相同的表(备份)。这些表完全相同，我需要从它们中提取数据，但这需要很长时间。

I'm using union query like this:

我使用的union查询是这样的:

select id, name, ... from table_1 WHERE (...)
UNION
select id, name, ... from table_3 WHERE (...)
UNION 
select id, name, ... from table_2 WHERE (...)
... ORDER BY created DESC LIMIT 0,50

But only query takes about 16 seconds! The database does not have that much data that it took so long ...

但是查询只需要16秒!数据库没有那么多的数据需要这么长时间……

table_1 = 1.3k
table_2 = 17k
table_3 = 10k
table_4 = 10k 
...     = 10k
table_11= 140K

Can you help me to optimize this query? Thanks a lot!

你能帮我优化这个查询吗?谢谢!

2 个解决方案

#1

First, don't use UNION -- unless you want to incur the overhead to remove duplicates. Instead, use UNION ALL:

首先，不要使用UNION——除非您希望产生删除副本的开销。相反,使用UNION ALL:

select id, name, ... from table_1 WHERE (...)
UNION ALL
select id, name, ... from table_3 WHERE (...)
UNION ALL
select id, name, ... from table_2 WHERE (...)
...
ORDER BY created DESC
LIMIT 0, 50;

You may also be able to improve performance by using indexes appropriate for the WHERE clause. However, your question does not have enough information for that.

您还可以通过使用适合WHERE子句的索引来提高性能。但是，你的问题没有足够的信息。

Actually, another optimization is to take the first 50 rows from each subquery before the union:

实际上，另一个优化是从union之前的每个子查询中取出前50行:

(select id, name, ... from table_1 WHERE (...) ORDER BY created DESC LIMIT 50)
UNION ALL
(select id, name, ... from table_3 WHERE (...) ORDER BY created DESC LIMIT 50)
UNION ALL
(select id, name, ... from table_2 WHERE (...) ORDER BY created DESC LIMIT 50)
...
ORDER BY created DESC
LIMIT 0, 50;

This reduces the amount of data for the final ORDER BY, which should also speed things up.

这减少了最终订单的数据量，这也应该会加快速度。

#2

UNION tries to sort out all the distinct queries from each table one it has brought them all back. If you know for sure there are no duplicates then you can use UNION ALL instead.

UNION尝试从它所返回的每个表中找出所有不同的查询。如果您确定没有重复，那么您可以使用UNION ALL。

If you're worried about duplicates, use UNION ALL to select everything into a temporary table or subquery, and then GROUP BY every column you are returning to eliminate them.

如果您担心重复，请使用UNION ALL将所有内容选择到临时表或子查询中，然后按返回的每个列进行分组，以消除它们。

For a real indication, look in the toolbar for Estimated Execultion Plan. Click this and it will show you what part of the query is taking a long time, and will suggest improvements.

要获得真正的指示，请在工具栏中查找估计的执行计划。单击此按钮，它将显示查询的哪些部分需要花费很长时间，并建议进行改进。

#1