数据库性能：使用一个具有最大值的实体/表。可能的属性或拆分到不同的实体/表？

im need to design some database tables but im not sure about the performance impact. In my case its more about the read performance than for saving the data.

我需要设计一些数据库表，但我不确定性能影响。在我的情况下，它更多地关于读取性能而不是保存数据。

The situation

情况

With the help of pattern recognition im finding out how many values of a certain object needs to be saved in my postgresql database. Amount other lets say fixed properties the only difference is if 1, 2 or 3 values of the same type needs to be saved.

在模式识别的帮助下，我发现需要在postgresql数据库中保存多少个特定对象的值。其他数量让我们说固定属性唯一的区别是需要保存相同类型的1,2或3个值。

Currently im having 3 entities/tables which differ only in having having 1, 2 or 3 not nullable properties of the same type.

目前，我有3个实体/表，它们的区别仅在于具有相同类型的1,2或3个不可空的属性。

For example:

例如：

EntityTestOne/TableOne {
    ... other (same) properties
    String optionOne;
}

EntityTestTwo/TableTwo {
    ... other (same) properties
    String optionOne;
    String optionTwo;

}

EntityTestThree/TableThree {
    ... other (same) properties
    String optionOne;
    String optionTwo;
    String optionThree;
}

I expect to have several million records in production and im thinking what could be the performance impact of this variant and what could be alternatives.

我希望在生产中有数百万条记录，并且我正在考虑这种变体的性能影响以及可能的替代方案。

Alternatives

备择方案

Other options which come into my mind:

我想到的其他选择：

Use only one entity class or table with 3 options (optionTwo and optionThree will be nullable then). If to talk of millions of expected records plus caching im asking myself isn't it a kind of 'waste' to save millions of null values in at least two (caching) layers (database itself and hibernate). In a another answer i read yesterday saving a null value in postgresql need only 1 bit what i think isnt that much if we talk about several millions of records which can contain some nullable properties (link).
仅使用一个具有3个选项的实体类或表（optionTwo和optionThree将可为空）。如果要谈论数以百万计的预期记录加上缓存我问自己，在至少两个（缓存）层（数据库本身和休眠）中保存数百万个空值并不是一种“浪费”。在我昨天读到的另一个答案中，在postgresql中保存一个空值只需要1位，我认为如果我们谈论可以包含一些可以为空的属性（链接）的数百万条记录那么多。
Create another entity/table and use a collection (list or set) relationship instead
创建另一个实体/表并使用集合（列表或集）关系

For example:

例如：

EntityOption {
    String value;
}

EntityTest {
    ... other (same) properties
    List<EntityOption> options;
}

If to use this relationship: What would give a better performance in case of creating new records: Creating for every new EntityTest new EntityOption's or doing a lookup before and reference a existing EntityOption if exists? What about the read performance while fetching them later and the joins which will be needed then? Compared to the variant with one plain Entity with three options i can imagine it could be slightly slower...
如果要使用这种关系：在创建新记录的情况下，什么会提供更好的性能：为每个新的EntityTest创建新的EntityOption或在之前进行查找并引用现有的EntityOption（如果存在）？稍后获取它们时的读取性能以及当时需要的连接怎么样？与具有三个选项的一个普通实体的变体相比，我可以想象它可能会稍慢......

As im not that strong in database design and working with hibernate im interested of the pros and cons of these approaches and if there are even more alternatives. I even would like to ask the question if postgresql is the right choice for this or if should think about using another (free) database.

因为我不是那么强大的数据库设计和使用hibernate我对这些方法的优点和缺点感兴趣，如果有更多的选择。我甚至想问一个问题，如果postgresql是正确的选择，或者是否应该考虑使用另一个（免费）数据库。

Thanks!

谢谢！

2 个解决方案

#1

The case is pretty clear in my opinion: If you have an upper limit of three properties per object, use a single table with nullable attributes.

我认为这个案例很清楚：如果每个对象有三个属性的上限，请使用一个具有可空属性的表。

A NULL value does not take up any space in the database. For every row, PostgreSQL stores a bitmap that contains which attributes are NULL. This bitmap is always stored, except when all attributes are not nullable. See the documentation for details.
So don't worry about storage space in this case.

NULL值不占用数据库中的任何空间。对于每一行，PostgreSQL都存储一个包含哪些属性为NULL的位图。始终存储此位图，除非所有属性都不可为空。有关详细信息，请参阅文档所以在这种情况下不要担心存储空间。

Using three different tables or storing the attributes in a separate table will probably lead to UNIONs or JOINs in your queries, which will make the queries more complicated and slow.

使用三个不同的表或将属性存储在单独的表中可能会导致查询中出现UNION或JOIN，从而使查询更加复杂和缓慢。

#2

There are many inheritance strategy for creating entity class, I think you should go with single table strategy, where there will be a discriminator column (managed by hibernate itself), and all common filed will be used by each entity and some specific fields will be use by specific entity and remain null for other entity. This will get improved read performance. For your ref. : http://www.thejavageek.com/2014/05/14/jpa-single-table-inheritance-example/

有很多用于创建实体类的继承策略，我认为你应该采用单表策略，其中将有一个鉴别器列（由hibernate本身管理），并且所有公共字段将由每个实体使用，并且一些特定字段将是由特定实体使用，对其他实体保持为null。这将提高读取性能。为你的参考。：http：//www.thejavageek.com/2014/05/14/jpa-single-table-inheritance-example/

#1