处理时序数据（如设备传感器数据），如何设计数据库表结构，以及如何优化查询性能？请说明表结构设计、索引策略、分页查询优化方法。

1) 【一句话结论】时序数据表结构设计需以时间维度为核心，通过复合主键（设备ID+时间戳）和覆盖索引优化查询，分页时采用时间倒序+分页键策略提升性能。

2) 【原理/概念讲解】时序数据（如设备传感器数据）的核心特性是时间连续性和查询模式偏向时间范围（如“最近7天数据”“每小时趋势”）。传统关系型数据库处理时序数据时，需设计能高效支持时间范围查询的表结构。

• 表结构设计：通常将时间作为核心维度，采用“设备ID + 时间戳”作为复合主键（时间戳需为唯一且有序），并按时间分区（如按天、小时分区）以减少单表数据量。例如，设备传感器数据表可设计为：
  sensor_data (device_id INT, timestamp DATETIME, temperature FLOAT, humidity FLOAT, PRIMARY KEY (device_id, timestamp))
• 索引策略：主键是时间+设备ID的复合索引，这是最基础的索引。此外，针对时间范围查询（如“查询2024-01-01到2024-01-07的数据”），可创建覆盖索引（包含时间范围列+设备ID+查询所需字段），避免回表查询。
• 分页查询优化：时序数据查询常按时间倒序（如“最近100条数据”），此时若使用LIMIT offset, count（offset-based分页），当offset较大时会导致全表扫描（因为B+树索引的顺序扫描效率低）。因此，推荐使用时间范围+分页键（如“取2024-01-07 23:59:59到2024-01-06 23:59:59的数据，且分页键为上一条记录的时间戳”），或使用数据库的游标功能（如MySQL的FETCH FIRST ...）。

3) 【对比与适用场景】

索引策略/分页方法	定义/特性	使用场景	注意点
复合主键（设备ID+时间戳）	时间+设备ID作为主键，保证唯一性和有序性	所有时序数据表的基础设计	主键需保证时间戳唯一（如带毫秒级精度）
覆盖索引（时间范围+设备ID+数据字段）	索引包含查询所需的所有字段，无需回表	时间范围查询（如“查询最近7天数据”）	索引列需覆盖查询条件，避免回表
offset-based分页	通过LIMIT offset, count实现分页	小范围分页（如offset较小）	当offset大时，性能急剧下降（全表扫描）
key-based分页（时间范围+分页键）	使用时间范围+上一条记录的时间戳作为分页键	大范围分页（如查询历史数据）	需维护分页键（如上一条记录的时间戳）

4) 【示例】：

• 表结构设计：

  CREATE TABLE sensor_data (
    device_id INT NOT NULL,
    timestamp DATETIME NOT NULL,
    temperature FLOAT,
    humidity FLOAT,
    PRIMARY KEY (device_id, timestamp)
  );
  

• 索引策略：

  -- 创建覆盖索引（时间范围+设备ID+数据字段）
  CREATE INDEX idx_sensor_time_range ON sensor_data (timestamp, device_id, temperature, humidity);
  

• 分页查询优化（时间倒序取最近100条数据）：

  -- 方法1：时间范围+分页键（推荐）
  SELECT * FROM sensor_data 
  WHERE timestamp >= '2024-01-07 23:59:59' 
    AND timestamp < '2024-01-08 00:00:00' 
    AND device_id = 1 
  ORDER BY timestamp DESC 
  LIMIT 100;
  
  -- 方法2：游标（如MySQL的FETCH）
  FETCH FIRST 100 ROWS ONLY FROM (SELECT * FROM sensor_data WHERE device_id=1 ORDER BY timestamp DESC);
  

5) 【面试口播版答案】
“针对时序数据（如设备传感器数据），表结构设计上我会以时间戳为核心，采用设备ID + 时间戳作为复合主键，保证数据按时间有序存储。索引策略上，除了主键索引外，会创建覆盖索引（包含时间范围列+设备ID+查询所需字段），避免回表查询。分页优化时，由于时序数据查询常按时间倒序（如最近数据），我会优先使用时间范围+分页键的分页策略（或游标），避免offset-based分页导致的性能问题。这样既能高效支持时间范围查询，又能优化分页性能。”

6) 【追问清单】

• 问题1：如何处理数据量增长带来的索引维护成本？
  回答要点：可通过时间分区（如按天、小时分区）减少单表数据量，降低索引维护成本；或使用时间序列数据库（如InfluxDB、Timestream）的内置分区功能。
• 问题2：如何处理实时数据写入的性能？
  回答要点：可采用批量写入（如每秒批量插入100条数据）减少单次写入开销；或使用数据库的事务批处理功能（如MySQL的批量插入）；对于超高频写入，可考虑使用消息队列（如Kafka）缓冲写入请求。
• 问题3：如何处理跨时间段的聚合查询（如计算每小时平均温度）？
  回答要点：可使用物化视图（如MySQL的物化视图）预先计算聚合结果，或使用数据库的窗口函数（如AVG() OVER (PARTITION BY HOUR(timestamp))）实时计算。
• 问题4：若传感器数据有更新（如修正温度值），如何保证查询结果的准确性？
  回答要点：可使用时间戳版本控制（如添加update_time字段），或使用数据库的乐观锁（如版本号字段）确保更新操作的原子性。

7) 【常见坑/雷区】

• 坑1：使用普通索引而非时间范围索引：若仅对时间列创建普通索引，时间范围查询（如“>2024-01-01”）会导致全表扫描，性能极差。
• 坑2：分页时使用offset-based分页：当查询历史数据（offset较大）时，offset-based分页会导致全表扫描，性能急剧下降。
• 坑3：未按时间倒序分页：时序数据查询常按时间倒序（如“最近数据”），若未按时间倒序排序，结果不符合业务预期。
• 坑4：表结构未按时间分区：单表数据量过大时，索引维护成本高，查询性能下降。
• 坑5：未考虑数据量增长：未提前规划时间分区或使用时间序列数据库，导致后续扩展困难。