在处理工业安全日志时，若日志数据量每天约10亿条（包含时间戳、设备ID、事件类型、参数等），如何设计数据库表结构以优化查询性能？请说明索引策略（如主键、复合索引）和分片策略（如按时间分片），并举例说明如何优化特定查询（如按设备ID查询过去24小时的事件）。
答案
1) 【一句话结论】：针对每天10亿条工业安全日志，采用按时间分片存储，主键为时间戳+设备ID，并建立(device_id, timestamp)复合索引，可有效优化时间范围查询（如按设备ID查询过去24小时事件）的查询性能。
2) 【原理/概念讲解】：工业安全日志属于海量时间序列数据，数据量巨大时，单表查询效率低。分片（Sharding）是将数据水平拆分到多个分片表，减少单表压力；索引（Index）是数据库的“目录”，加速数据查找。主键需唯一且能覆盖查询条件，复合索引可减少回表。类比：日志表像一个大仓库，分片是把仓库按时间分成小库（如每天一个库），索引是库里的货架标签（如按设备ID+时间找商品），快速找到日志条目。
3) 【对比与适用场景】：分片策略对比（按时间分片 vs 按设备ID分片）
| 分片策略 | 定义 | 特性 | 使用场景 | 注意点 |
|---|---|---|---|---|
| 按时间分片 | 按时间维度（如天、周）拆分表 | 数据按时间顺序存储，时间范围查询只需扫描当前及相邻分片 | 适合时间范围查询（如按设备ID查询过去24小时事件） | 需处理跨分片查询，数据迁移复杂 |
| 按设备ID分片 | 按设备ID拆分表 | 数据按设备分组存储，设备内查询快，跨设备查询慢 | 适合设备内聚合查询（如某设备事件总数） | 时间范围查询可能跨多个分片，性能下降 |
4) 【示例】：表结构设计（伪代码）
-- 分片表（按天分片，表名：log_table_20240101）
CREATE TABLE log_table_20240101 (
timestamp BIGINT PRIMARY KEY, -- 时间戳（毫秒级，自增或存储时间）
device_id VARCHAR(50) NOT NULL,
event_type VARCHAR(50),
params JSON, -- 事件参数，JSON存储
INDEX idx_device_time (device_id, timestamp) -- 复合索引
);
查询优化示例（按设备ID查询过去24小时事件）：
-- 查询语句
SELECT * FROM log_table_20240101
WHERE device_id = 'dev_001'
AND timestamp >= NOW() - INTERVAL 24 HOUR;
由于索引idx_device_time包含device_id和timestamp，数据库可利用索引直接定位满足条件的行，避免全表扫描。
5) 【面试口播版答案】：面试官您好，针对每天10亿条工业安全日志，我会设计按时间分片的数据库表，并配合主键和复合索引优化查询。首先，表结构上，主键设为时间戳和设备ID的组合（如timestamp, device_id），因为时间+设备ID能唯一标识一条日志，且时间戳自增可保证主键有序。分片策略采用按天分片，比如每天一个表（如log_20240101），这样每个表的数据量约1000万条，查询时只需扫描当前天或相邻几天，避免全表扫描。索引方面，除了主键，还会建一个复合索引（device_id, timestamp），这样查询“按设备ID过去24小时的事件”时，可以直接用这个索引覆盖查询条件，避免回表到表数据。具体来说，查询语句类似：SELECT * FROM log_table WHERE device_id = 'dev_001' AND timestamp >= NOW() - INTERVAL 24 HOUR，由于索引包含设备ID和时间范围，数据库能快速定位到相关行，性能提升显著。这样设计既能应对海量数据，又能高效处理时间范围查询。
6) 【追问清单】：
- 问题1：如果按设备ID分片，如何处理时间范围查询？
回答要点：按设备ID分片时，时间范围查询可能跨多个分片，需合并结果，性能下降；而按时间分片更适合时间范围查询。 - 问题2：主键选择自增ID vs 时间戳+设备ID，哪个更好？
回答要点：时间戳+设备ID作为主键，能直接支持时间范围查询（索引包含时间），自增ID需额外存储时间，且时间范围查询需扫描整个表。 - 问题3：复合索引的维护成本？
回答要点：插入数据时需更新索引，但查询性能提升，适合高频查询场景；需评估索引大小和更新开销。 - 问题4：分片键的选择依据？
回答要点：分片键应选择查询频率高、数据量大的维度（如时间），并考虑数据分布均匀性，避免数据倾斜。 - 问题5：如何处理数据迁移或扩容？
回答要点：按时间分片时，扩容可通过增加新分片表（如未来表名按周或月分片）；数据迁移需按时间范围逐步迁移，避免影响在线查询。
7) 【常见坑/雷区】：
- 坑1：主键使用自增ID，导致时间范围查询需扫描整个表，性能差。
- 坑2：分片键选择设备ID，时间范围查询跨多个分片，导致查询慢。
- 坑3：复合索引未覆盖查询条件，导致回表。
- 坑4：分片策略未考虑数据倾斜。
- 坑5：未考虑日志的更新或删除操作。