多表优化数据库设计

Question

我对数据库设计有点陌生。因此，这个问题给那些可能有更多经验的人。

我需要设计一个数据库，它需要存储许多系统的统计数据。这些数据每天都在收集。可能有几百个统计柜台。系统的数量也可以增加。

哪种数据库设计更有效？从长期维护的角度来看，从性能的角度来看，等等。

1. 设计1:一个巨大的表，列为计数器。然后，每个系统都会在每个日期上添加其条目数。

1. 设计2:每个系统都动态地创建自己的表。它将包含每天收集统计数据的一行。还有一个表将包含所有系统表的列表。

Answer 1

你说每天测量一次。我有两张桌子如下：

CREATE TABLE Measurement
(
  Measurement_ID INTEGER PRIMARY KEY,
  System_ID INTEGER,  -- FK into the System table
  Measurement_Date DATE (or DATETIME depending),
  Measurement_1 M1_Datatype,
  ..
  .. 100 lines
  Measurement_100 M100_Datatype
);

CREATE TABLE System
(
  System_ID INTEGER PRIMARY KEY,
  System_Location VARCHAR(3) -- maybe a code, if that suits? Zip?
  System_Description VARCHAR(50)
);

@GordonLindoff认为将所有这些数据放在一个表中是可行的--一个系统的度量就像另一个表一样--它们是相同的--具有相似属性的对象属于同一个表(除非您的存储需求变得非常庞大)。加上64位整数作为键，您实际上将不会耗尽潜在的主键。

请参阅我接受的答案这里，有充分的理由不考虑EAV系统。

Answer 2

毫无疑问，在提到的两种选择中，第一种更好。MySQL很擅长处理大型表。没有任何理由将数据分解成单独的“等效”表。事实上，具有相同布局的多个表通常表明数据库设计很差。

为了提高性能，可以在系统和日期上添加索引(或者在一个索引中同时添加这两个索引)。您还可以了解性能。

对于每个系统，有一些原因可以将数据分割成不同的表。这里有两个：

1. 如果为每个系统维护的统计数据与其他系统有很大不同，那么只存储适当的列会更有效。
2. 如果系统需求强制数据分离或允许。后者在表级别上更容易实现。

您还可以考虑一种实体属性-值(EAV)方法。这将是一个表，其中基本上有四列：

• 日期
• 系统名称
• 统计名称
• 价值

然后，对每个统计数据重复行。这通常比单表方法效率低。但这是有益的，特别是当你有很多统计数据，而且大多数系统都有不同的统计数据集时。

Answer 3

因为你的约会会发展得很快。我建议将所有“字符字段”分隔到维度中，并在事实表中使用只包含数字数据的数值链接。这种方法将非常快，并节省您的语气的空间。此外，根据分区配置的不同，可以使其更有效地进行重新测试。

    create table dim_system (
        system_id int PK
        system_name char    
    )

    create table dim_counters (
        counter_id int PK
        counter_name char
    )

    create table fact_measurements (
        id int 
        datetime datetime
        system_id FK( dim_system.system_id )
        counter_id FK( dim_counters.counter_id)
    )