数据库表设计

目录

宽表设计（Wide Table Design）

窄表设计（Narrow Table Design）

垂直分区（Vertical Partitioning）

水平分区（Horizontal Partitioning）

总结

---

这里展示的是工业互联网的设备数据采集时，表结构的简单设计方式；

有时候这些表设计方式可以采用混合设计方式，根据自己的业务情况来执行；

宽表设计（Wide Table Design）

所有参数在同一行中，每台设备每个时间点的数据占据一行。

示例：

CREATE TABLE device_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50),
    timestamp TIMESTAMP,
    param1 FLOAT,
    param2 FLOAT,
    ...
    param150 FLOAT
);

优点：

1. 简单查询：可以一次查询出一个设备所有参数。
2. 插入效率高：一次插入所有参数，减少了插入操作的频率。

缺点：

1. 存储冗余：如果某些参数经常为空，会浪费存储空间。
2. 结构固定：添加或删除参数需要修改表结构，不灵活。

窄表设计（Narrow Table Design）

每个参数作为单独的一条记录存储，通常称为EAV（Entity-Attribute-Value）模型。

示例：

CREATE TABLE device_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50),
    timestamp TIMESTAMP,
    param_name VARCHAR(50),
    param_value FLOAT
);

-- 示例插入
INSERT INTO device_data (device_id, timestamp, param_name, param_value)
VALUES ('device_1', '2024-05-17T10:00:00Z', 'param1', value1),
       ('device_1', '2024-05-17T10:00:00Z', 'param2', value2),
       ...
       ('device_1', '2024-05-17T10:00:00Z', 'param150', value150);

优点：

1. 灵活性高：添加或删除参数不需要修改表结构，只需增加或减少记录。
2. 节省空间：只存储有数据的参数，不会有多余的空字段。

缺点：

1. 查询复杂：要获取一个设备的所有参数需要多次查询或JOIN操作。
2. 插入效率低：每个参数需要单独插入一条记录，插入操作频繁。

垂直分区（Vertical Partitioning）

垂直分区是指根据列的特性将表中的列进行分割，将不同的列存储在不同的物理表或分区中。每个分区只包含表的一部分列。垂直分区通常根据列的访问频率、数据类型、大小等因素进行划分。

优点：

1. 性能提升：经常访问的列可以被垂直分区到单独的表中，减少了查询时的IO开销。
2. 管理简化：相关性较低的列被分离，减少了数据表的复杂性，使得维护和管理更加容易。

示例：

考虑一个设备信息表（device_info），包含设备ID、设备名称、制造商、型号、生产日期、安装日期等信息。如果设备ID和设备名称经常被查询，而生产日期和安装日期很少被查询，可以进行垂直分区：

• 主表（device_main）：包含设备ID和设备名称等经常查询的字段。
• 附加表（device_additional）：包含生产日期和安装日期等不经常查询的字段。

水平分区（Horizontal Partitioning）

水平分区是指根据行的特性将表中的数据进行划分，将不同行存储在不同的物理表或分区中。通常根据某个条件（如设备ID、时间戳等）进行分区。水平分区将表中的数据按照某种规则水平划分成多个较小的子集。

优点：

1. 性能提升：减少了单个表中的数据量，提高了查询性能和索引效率。
2. 容易管理：可以将数据分散存储在不同的物理位置，减少了单个表的维护成本。

示例：

考虑一个设备参数表（device_parameters），包含设备ID、时间戳、参数名和参数值等信息。如果数据量巨大，可以根据时间戳进行水平分区，每个分区存储某个时间段的数据：

• 设备参数表_2024_05_17：存储2024年5月17日的设备参数数据。
• 设备参数表_2024_05_18：存储2024年5月18日的设备参数数据。
• ...

总结

垂直分区和水平分区都是常见的数据库分区策略，可以根据具体的业务需求和数据特点进行选择。垂直分区适用于优化列的访问性能和简化管理，而水平分区适用于管理大量数据和提高查询性能。在设计数据库时，可以根据实际情况结合使用这两种分区策略，以达到最佳的性能和管理效果。

• 垂直分区和水平分区，前者就是将列存放到不同的分区中，按分区处理，后者就是按照不同的数据，以行的方式存放在不同的分区中，按分区处理；
• 不过在水平分区中，分割后的每个表或分区通常称为“分区”而不是“表”。这是因为水平分区通常是为了管理大量的数据而不是为了逻辑上的分离。每个分区仍然属于同一个逻辑表，但是物理上可能存储在不同的位置或独立的表中。