面试题：SQLite创建数据库与表的性能优化及索引策略

Question

有一个SQLite数据库用于存储大量的传感器数据，包含传感器表sensors（传感器编号、类型等）和数据记录表data_records（记录id、传感器编号、时间戳、数据值）。在创建数据库和表时，如何设计表结构、选择合适的数据类型以及建立索引来优化查询性能，特别是针对按传感器类型和时间范围查询数据的场景。请详细说明设计思路和具体的SQL语句。

Answer 1

设计思路

1. 表结构设计：
   • sensors 表存储传感器的基本信息，每个传感器有唯一的编号，不同类型的传感器也有相应记录。
   • data_records 表记录传感器产生的数据，通过传感器编号关联到 sensors 表，时间戳用于记录数据产生的时间，数据值则是传感器采集到的实际数据。
2. 数据类型选择：
   • 传感器编号可使用整数类型，因为它通常是自增长且数值型，占用空间小且查询效率高。
   • 传感器类型可使用文本类型，用于描述传感器的种类。
   • 记录id可使用整数类型，且通常设置为自增长主键。
   • 时间戳使用日期时间类型，方便按时间范围查询。
   • 数据值根据实际数据类型选择，例如整数、浮点数等。
3. 索引建立：
   • 在 sensors 表的传感器类型列上建立索引，方便按传感器类型查询。
   • 在 data_records 表的传感器编号和时间戳列上建立联合索引，以优化按传感器类型和时间范围的查询，因为查询时经常会同时用到这两个条件。

具体SQL语句

1. 创建sensors表：

CREATE TABLE sensors (
    sensor_id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_type TEXT NOT NULL
);

2. 创建data_records表：

CREATE TABLE data_records (
    record_id INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id INTEGER NOT NULL,
    timestamp DATETIME NOT NULL,
    data_value REAL,
    FOREIGN KEY (sensor_id) REFERENCES sensors(sensor_id)
);

3. 在sensors表的sensor_type列上创建索引：

CREATE INDEX idx_sensor_type ON sensors(sensor_type);

4. 在data_records表的sensor_id和timestamp列上创建联合索引：

CREATE INDEX idx_sensor_time ON data_records(sensor_id, timestamp);