面试题：SQLite的性能优化与索引策略

Question

有一个包含100万条记录的表 'big_table'，包含字段 'id'（主键），'category'（文本类型，有5种不同类别），'date'（日期类型），'value'（数值类型）。业务需求是频繁查询特定类别且特定日期范围内的记录。请详细阐述如何通过合理的索引策略对该表进行性能优化，并解释不同索引策略的优缺点。

Answer 1

索引策略

1. 单列索引：

   • 策略：对 category 字段创建单列索引，即 CREATE INDEX idx_category ON big_table(category);。
   • 优点：在仅查询特定类别记录时能显著提高查询性能，比如 SELECT * FROM big_table WHERE category = '某类别';，因为索引能快速定位到相关记录。
   • 缺点：当需要同时查询特定类别且特定日期范围时，如 SELECT * FROM big_table WHERE category = '某类别' AND date BETWEEN '开始日期' AND '结束日期';，此单列索引无法充分利用，可能仍需全表扫描日期范围部分，性能提升有限。

2. 复合索引：

   • 策略：创建复合索引 CREATE INDEX idx_category_date ON big_table(category, date);，复合索引顺序按业务查询条件中字段出现顺序，通常将选择性高（类别种类少但区分度大）的 category 放在前面。
   • 优点：对于查询特定类别且特定日期范围内的记录，如 SELECT * FROM big_table WHERE category = '某类别' AND date BETWEEN '开始日期' AND '结束日期';，复合索引能快速定位到对应类别，再在类别内按日期范围快速查找，大大提高查询性能。
   • 缺点：索引维护成本较高，插入、更新、删除操作时，不仅要更新数据，还要更新索引，会增加数据库操作开销。同时，复合索引中字段顺序很关键，如果查询条件中字段顺序与索引不一致（如先查询日期范围再查询类别），索引利用率可能降低。

3. 覆盖索引：

   • 策略：创建覆盖索引 CREATE INDEX idx_category_date_value ON big_table(category, date, value);，包含查询中常涉及的所有字段（除主键 id 外，因为主键本身也有索引，且覆盖索引默认包含主键列）。
   • 优点：在查询特定类别、特定日期范围并需要返回 value 字段值时，如 SELECT value FROM big_table WHERE category = '某类别' AND date BETWEEN '开始日期' AND '结束日期';，数据库可直接从索引中获取所需数据，无需回表操作，大大提高查询性能。
   • 缺点：占用更多磁盘空间，因为索引包含了更多字段的数据。同时，索引维护成本进一步提高，对插入、更新、删除操作性能影响更大。

综合业务需求频繁查询特定类别且特定日期范围内的记录，复合索引 idx_category_date 是较为合适的选择，在保证查询性能的同时，相对覆盖索引能减少空间占用和维护成本。