使用UNION ALL从多个表中获取行或在生产中使用一个表？ [英] Fetching rows from multiple tables with UNION ALL or using one table in production?

问题描述

我知道，对于像Postgresql这样的关系数据库，使用分离的表会更有效率，但是我关心的是性能问题，因为最执行的查询将使用 UNION ALL 。

我必须选择处理这个问题。第一个是：

  table1  - > column1，column2 
 table2  - > column1，column2 
 table3  - > column1，column2，column3 
  

在此解决方案中，我必须使用3个不同的查询与 UNION ALL 在生产中，此查询将执行登录系统的用户（系统中最执行的查询）

另一个是：

 表 - > column1，column2，typeColumn，extraColumnForTable3 
  

在此解决方案中，我必须创建一个额外的列 typeColumn 来区分行的类型。而且我还必须为 table3 类型创建一个列 extraColumnForTable3 ，并为 table2 和 table1 类型。在此解决方案中，最执行的查询将只包含一个 SELECT 语句。

生产中将有数百万行所以我关心的是性能。 NULL 值可能在数据库中占用额外的空间，但我认为它可以忽略不计。我将使用部分索引消除NULL值，所以我不认为它会影响其他查询特定类型的查询。你认为哪一个在生产中更有效率？

解决方案

一般来说，我发现广泛使用 UNION 表示糟糕的数据库设计。有些情况下， UNION 和 UNION ALL 有意义，但在递归公用表表达式之外，它们应该相对较少。 / p>

PostgreSQL提供了相当多的选项，用于在单个表上保持性能可管理，并且您指出部分索引是管理此问题的非常好的方法。 p>

分解表格的主要问题是，这样的 UNION 语句是常见的，它使得主键和外键管理相当有问题的一般来说，首先要确保您的数据结构清晰可控，然后担心优化，而不是担心优化，然后尝试使优化的解决方案可管理，总是更好。

I know that for relational database like Postgresql using separated tables would be more efficient but I'm concerning for performance issues because the most executed query will fetch rows from multiple tables using UNION ALL.

I have to option to handle this problem. First one is:

table1 -> column1, column2
table2 -> column1, column2
table3 -> column1, column2, column3

In this solution I have to use 3 different query merged with UNION ALL in production and this query will be performed a user logged in the system (the most executed query in the system)

The other is:

table -> column1, column2, typeColumn, extraColumnForTable3

In this solution I have to create an extra column typeColumn to distinguish which type the row is. And I also have to create a column extraColumnForTable3 for the type table3 and it will be NULL for table2 and table1 type. In this solution the most executed query will include only one SELECT statement.

There will be million of rows in production so I'm concerning about performance. NULL values may occupy an extra space in database but I think it can be negligible. I will use partial index that eliminates NULL values so I don't think it will affect the other queries that fetch specific types. Which one do you think more efficient in production?

解决方案

In general I find that extensive use of UNION suggests bad database design. There are cases where UNION and UNION ALL make sense but they should be relatively rare outside of recursive common table expressions.

PostgreSQL provides a fairly large number of options for keeping performance on a single table manageable, and as you point out partial indexes are a very good way to manage this problem.

The major problem with breaking up tables such that such UNION statements are common is that it makes primary and foreign key management quite problematic. In general it is almost always far better to make sure your data structure is clear and manageable first, and then worry about optimization than it is to worry about optimization and then try to make the optimized solution manageable.

这篇关于使用UNION ALL从多个表中获取行或在生产中使用一个表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！