设计一个存储港口船舶动态数据（如靠离泊时间、位置、装卸状态）的数据库表结构，要求支持实时查询（如当前在港船舶列表），并保证数据准确性（误差率<0.1%）。请说明表结构、索引策略以及如何保证数据一致性。
答案
1) 【一句话结论】：采用主表（船舶基础信息）与动态状态表（实时状态）的RDBMS方案，通过复合唯一索引（ship_id+时间戳）、SERIALIZABLE事务隔离、Redis缓存实时查询结果，以及Kafka消息队列缓冲写入，确保实时查询性能与数据准确性（误差率<0.1%）。
2) 【原理/概念讲解】：港口船舶数据分为静态属性（如船舶ID、名称、类型）和实时状态（如靠离泊时间、位置、装卸状态）。静态信息存储在主表（ship_info），主键用自增ID或UUID，保证唯一。动态状态存储在状态表（ship_status），包含ship_id（外键关联主表）、精确到毫秒的时间戳（timestamp）、各状态字段。为支持实时查询，给状态表添加复合唯一索引（ship_id和timestamp），类似索引是“时间戳目录”，快速定位最新状态记录。数据一致性通过数据库事务（ACID）保证，选择SERIALIZABLE隔离级别，相当于“全局锁”，防止并发写入时出现脏读、不可重复读等问题，确保数据误差率<0.1%。为提升实时查询性能，用Redis缓存“当前在港船舶列表”（TTL 1分钟，事件驱动更新，即状态表更新时触发Redis缓存更新），减少数据库查询压力。高并发写入时，通过Kafka消息队列缓冲（批量10条，延迟100ms），避免数据库直接接收大量写入请求导致性能下降。时间戳的毫秒精度和唯一性约束（UNIQUE索引）确保状态更新及时且无重复，误差率通过监控事务回滚率（Prometheus指标）、网络延迟（<50ms）等指标实时跟踪，若指标异常则调整策略。
3) 【对比与适用场景】：
| 方案 | 定义 | 关键特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 关系型数据库（主表+动态表） | 传统RDBMS，存储主表（静态）与动态表（实时状态），复合唯一索引优化查询 | 强一致性（ACID），事务支持，支持复杂关联，复合索引提升实时查询性能 | 港口船舶基础信息存储+实时状态查询（需优化索引），支持历史数据查询（分区） | 实时查询需复合索引，避免全表扫描；历史数据需分区 |
| 时序数据库（如InfluxDB） | 专为时间序列数据设计，高效写入，时间索引，聚合查询 | 高频写入（毫秒级），时间索引优化，支持时间范围查询 | 高频实时数据流（如每秒更新），对复杂关联支持较弱 | 不支持复杂关联，需额外存储元数据；写入性能高但查询复杂关联慢 |
4) 【示例】：
-- 船舶基础信息表（主表）
CREATE TABLE ship_info (
ship_id INT PRIMARY KEY AUTO_INCREMENT,
ship_name VARCHAR(100) NOT NULL,
ship_type VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 船舶动态状态表（实时状态表）
CREATE TABLE ship_status (
id INT PRIMARY KEY AUTO_INCREMENT,
ship_id INT NOT NULL,
timestamp TIMESTAMP(6) NOT NULL, -- 精确到微秒
berth_time DATETIME,
position POINT, -- 经纬度
loading_status ENUM('loading', 'unloading', 'idle') NOT NULL,
FOREIGN KEY (ship_id) REFERENCES ship_info(ship_id),
UNIQUE KEY idx_ship_status (ship_id, timestamp) -- 复合唯一索引（时间戳唯一性）
);
-- 查询当前在港船舶（最近1分钟内更新）
SELECT
si.ship_name,
ss.berth_time,
ss.position,
ss.loading_status
FROM
ship_info si
JOIN
ship_status ss ON si.ship_id = ss.ship_id
WHERE
ss.timestamp > NOW() - INTERVAL 1 MINUTE
AND ss.berth_time IS NOT NULL;
-- Redis缓存更新逻辑（伪代码）
# 当ship_status表更新时，触发Redis缓存更新
UPDATE ship_status SET ... WHERE ...;
# Redis缓存键：`in_port_ships:{port_id}`
# 缓存内容：最近1分钟内更新的船舶列表（JSON）
# TTL：1分钟，过期后自动更新
5) 【面试口播版答案】：
面试官您好，针对港口船舶动态数据存储，我会设计一个主表（存储船舶基础信息）与动态状态表（记录实时状态）的方案。主表用自增ID或UUID主键，动态表包含ship_id（外键）、精确到毫秒的时间戳（UNIQUE约束）、各状态字段。通过复合唯一索引（ship_id+时间戳）支持实时查询。数据一致性通过数据库事务（选择SERIALIZABLE隔离级别）保证，防止并发冲突。为提升实时查询性能，用Redis缓存“当前在港船舶列表”（TTL 1分钟，事件驱动更新），减少数据库压力。高并发写入时，通过Kafka消息队列缓冲（批量10条，延迟100ms），避免数据库阻塞。时间戳唯一性约束确保状态更新无重复，误差率通过监控事务回滚率（Prometheus指标）和网络延迟（<50ms）控制，确保<0.1%的误差率。这样设计既能支持实时查询，又能保证数据准确性。
6) 【追问清单】：
- 问题1：若船舶状态每秒更新多次，如何优化写入性能？
回答要点：采用Kafka消息队列缓冲（批量10条，延迟100ms），减少数据库直接写入压力；结合批量写入（如每秒提交10条），提升写入吞吐量。 - 问题2：如何处理Redis缓存与数据库数据不一致？
回答要点：使用事件驱动更新（状态表更新时触发Redis缓存更新），或设置Redis缓存TTL（1分钟），过期后自动重新查询数据库获取最新数据。 - 问题3：若事务回滚率超过阈值，如何调整策略？
回答要点：增加事务隔离级别（如从READ COMMITTED提升为SERIALIZABLE），或优化索引（覆盖索引减少锁竞争），降低回滚率。 - 问题4：如何支持历史数据查询（如过去24小时）？
回答要点：按时间分区（如按天分区），将旧数据移至历史表（ship_status_history），减少主表数据量，提升查询性能。 - 问题5：不同港口的船舶数据如何合并查询？
回答要点：使用全局唯一ID（结合港口ID和船舶ID），分库分表（Sharding）+分布式索引（如Elasticsearch）实现跨库查询。
7) 【常见坑/雷区】：
- 坑1：时间戳非唯一，导致数据重复，影响查询准确性（如重复记录导致当前在港船舶列表错误）。
- 坑2：索引选择不当（仅单字段索引），导致实时查询全表扫描，性能下降。
- 坑3：事务隔离级别不足（如READ COMMITTED），导致并发写入时出现脏读，误差率超标。
- 坑4：缓存未考虑更新机制（如TTL过期后未重新查询），导致缓存数据与数据库不一致。
- 坑5：消息队列未缓冲（直接写入数据库），高并发时数据库性能下降，写入延迟增加。