XuJiacheng
ad270bd936
- 在心跳事件表中新增 version 字段，用于存储版本号信息 - 将 bright_g 字段的 -1 值映射为数据库中的 NULL，避免语义混淆 - 更新相关文档、数据库迁移脚本和测试用例
114 lines
5.9 KiB
Markdown
114 lines
5.9 KiB
Markdown
# Heartbeat 数据库与表结构（v2 草案）
|
||
|
||
本文档描述 PostgreSQL 中 `heartbeat` 数据库的心跳明细表设计，用于高吞吐写入与按酒店/时间范围检索。
|
||
|
||
## 1. 数据库与命名空间
|
||
- 数据库：使用既有业务库（默认 `log_platform`，以 `src/config/config.js` 为准）
|
||
- Schema：`heartbeat`
|
||
- 编码：数据库需为 UTF-8（执行器会输出并提示）
|
||
- 排序规则/字符类型：若数据库不是中文 locale，可通过 ICU collation 在列级/表达式级实现中文排序（如确有严格要求）。
|
||
|
||
## 2. 主表
|
||
- 表名：`heartbeat.heartbeat_events`
|
||
- 分区：按 `ts_ms`（epoch 毫秒）**按天 RANGE 分区**
|
||
|
||
对应脚本：
|
||
- `scripts/db/010_heartbeat_schema.sql`
|
||
- `scripts/db/020_partitioning_auto_daily.sql`
|
||
|
||
### 2.1 字段列表
|
||
| 字段 | 类型 | 必填 | 说明 |
|
||
|---|---|---:|---|
|
||
| guid | varchar(32) | 是（自动生成） | GUID（32 位无连字符 HEX，小写；自动生成） |
|
||
| ts_ms | bigint | 是 | 毫秒级时间戳（epoch ms） |
|
||
| hotel_id | int2 | 是 | 酒店编号 |
|
||
| room_id | varchar(50) | 是 | 房间编号（或房间唯一标识，按字符串存储） |
|
||
| device_id | varchar(64) | 是 | 设备 ID（序列号/MAC/混合编码）；如明确为纯数字可改 bigint |
|
||
| ip | varchar(21) | 是 | `IP:PORT`（IPv4）或纯 IP 字符串 |
|
||
| power_state | int2 | 是 | 取电状态（枚举值待标准化） |
|
||
| guest_type | int2 | 是 | 住客身份（住客/空房/保洁/维修等，枚举值待标准化） |
|
||
| cardless_state | int2 | 是 | 无卡取电/无卡策略状态（枚举待定） |
|
||
| service_mask | bigint | 是 | 服务位图/场景位图（需求指定 BRIN 索引） |
|
||
| pms_state | int2 | 是 | PMS 状态（枚举待定） |
|
||
| carbon_state | int2 | 是 | 碳控状态（枚举待定） |
|
||
| device_count | int2 | 是 | 设备数量/上报设备数量（语义待确认） |
|
||
| comm_seq | int4 | 是 | 通讯序号（语义待确认） |
|
||
| insert_card | int2 | 否 | 是否插卡（整数；可为空；不建索引） |
|
||
| bright_g | int2 | 否 | 全局亮度值（整数；可为空；若值为 -1 则存 NULL；不建索引） |
|
||
| version | int2 | 否 | 版本号（int2；可为空；不建索引） |
|
||
| elec_address | text[] | 否 | 电力设备地址数组（与 voltage[] 等按下标对齐） |
|
||
| voltage | double precision[] | 否 | 电压数组 |
|
||
| ampere | double precision[] | 否 | 电流数组 |
|
||
| power | double precision[] | 否 | 功率数组 |
|
||
| phase | text[] | 否 | 相位数组 |
|
||
| energy | double precision[] | 否 | 能耗数组 |
|
||
| sum_energy | double precision[] | 否 | 总能耗数组 |
|
||
| air_address | text[] | 否 | 空调设备地址数组（与 state[] 等按下标对齐） |
|
||
| state | int2[] | 否 | 开关状态数组 |
|
||
| model | int2[] | 否 | 运行模式数组 |
|
||
| speed | int2[] | 否 | 风速设置数组 |
|
||
| set_temp | int2[] | 否 | 设定温度数组 |
|
||
| now_temp | int2[] | 否 | 当前温度数组 |
|
||
| solenoid_valve | int2[] | 否 | 电磁阀门状态数组 |
|
||
| extra | jsonb | 否 | 可扩展字段：电参/空调状态/版本/来源等 |
|
||
|
||
### 2.2 约束
|
||
- 所有必填字段：`NOT NULL`
|
||
- `ip`：使用 `varchar(21)`，用于存储 `IP:PORT`（IPv4）
|
||
- 各 `int2/int4`：当前脚本采用“非负 + 上界”CHECK（避免枚举未来扩展造成写入失败）
|
||
- 如需更强的枚举约束，建议在确认枚举标准后改为 `IN (...)` 或 `BETWEEN` 更小范围。
|
||
|
||
### 2.3 主键（重要说明）
|
||
需求写“主键：id(bigserial)”，但 **PostgreSQL 分区表的主键/唯一约束通常必须包含分区键**。
|
||
|
||
脚本采用：
|
||
- `PRIMARY KEY (ts_ms, guid)`
|
||
|
||
原因：保证分区表可创建、约束可落地。
|
||
|
||
## 3. 分区策略与自动分区
|
||
- 分区键：`ts_ms`
|
||
- 粒度：按天（Asia/Shanghai，自然日）
|
||
- 自动分区：通过“预创建分区”的方式实现（安装时预建昨天~未来 7 天），并提供函数供服务启动/定时任务调用
|
||
|
||
调用方式：
|
||
- SQL：`SELECT heartbeat.ensure_partitions(current_date, current_date + 30);`
|
||
- Node：执行 `npm run db:apply`（会应用脚本并预创建分区）
|
||
|
||
风险与建议：
|
||
- PostgreSQL 在单条 INSERT 执行过程中对父分区表执行 `CREATE TABLE .. PARTITION OF` 会触发锁/使用中限制，导致写入失败；因此不建议“插入时动态建分区”。
|
||
- 推荐每日提前创建未来 N 天分区（例如外部调度/运维脚本或服务启动时调用 `heartbeat.ensure_partitions`）。
|
||
|
||
## 4. 索引设计
|
||
需求指定：
|
||
- B-tree：`hotel_id`, `power_state`, `guest_type`, `device_id`
|
||
- BRIN：`service_mask`
|
||
|
||
新增（数组元素查询）：
|
||
- GIN：`elec_address`, `air_address`, `state`, `model`
|
||
|
||
常用查询示例：
|
||
- `SELECT * FROM heartbeat.heartbeat_events WHERE elec_address @> ARRAY['add11'];`
|
||
- `SELECT * FROM heartbeat.heartbeat_events WHERE air_address @> ARRAY['ac1'];`
|
||
- `SELECT * FROM heartbeat.heartbeat_events WHERE state @> ARRAY[1::int2];`
|
||
- `SELECT * FROM heartbeat.heartbeat_events WHERE model @> ARRAY[2::int2];`
|
||
|
||
额外建议（脚本默认包含，可按需移除）：
|
||
- `btree (hotel_id, ts_ms)`：覆盖最常见过滤（酒店 + 时间范围），显著提升检索与分区内扫描效率。
|
||
|
||
## 5. 查询性能影响分析（分区）
|
||
- 优点：
|
||
- 时间范围查询触发分区裁剪（只扫命中的日分区）
|
||
- 冷热数据按分区自然分层，便于归档/清理
|
||
- 代价：
|
||
- 跨大量分区的查询会增加计划时间与元数据开销
|
||
- 需要运维策略（预建分区、定期维护索引、vacuum/analyze）
|
||
|
||
## 6. 性能优化建议（高吞吐）
|
||
- 写入：使用批量写入（COPY 或 multi-row INSERT）并控制批大小（例如 500~5000，按网络与锁争用调优）
|
||
- 分区：建议预创建未来 7~30 天分区；触发器只做兜底
|
||
- 统计：对 Grafana 读取的 1m/5m/1h 聚合建议做物化视图或汇总表（避免每次扫明细）
|
||
- 维护：
|
||
- 定期 `VACUUM (ANALYZE)` 各分区
|
||
- 监控 bloat 与 autovacuum 参数
|