面试题：Flutter SQLite数据库索引优化之复杂场景处理

Question

在一个Flutter开发的电商应用里，SQLite数据库中有商品表，包含商品ID、名称、价格、库存、分类等字段。用户经常进行多条件查询，比如按分类和价格范围查找商品。请阐述如何设计索引以优化这类查询的性能，并说明设计的原理及可能存在的风险。

Answer 1

索引设计

1. 复合索引：针对按分类和价格范围查找商品的需求，设计一个复合索引，例如 CREATE INDEX idx_category_price ON products (category, price); 以 category 字段在前，price 字段在后。

设计原理

1. 前缀匹配：复合索引遵循“最左前缀原则”。查询时如果首先按分类过滤，索引可以快速定位到符合该分类的记录范围。
2. 有序查找：在确定分类范围后，对于价格范围的查找，由于索引中价格字段是有序的，能够使用二分查找等高效算法来快速定位符合价格范围的记录，从而减少全表扫描，提升查询性能。

可能存在的风险

1. 索引维护成本：每次插入、更新或删除数据时，数据库不仅要更新数据本身，还要更新相关的索引。这会增加写操作的开销，降低写性能。
2. 索引空间占用：索引本身需要占用额外的磁盘空间。大量的索引会导致数据库文件体积增大，增加存储成本。
3. 查询优化器误判：如果查询优化器对索引的使用判断错误，可能会选择全表扫描而不是使用精心设计的索引，导致性能未得到优化甚至下降。这可能发生在数据分布不均匀或者查询条件复杂的情况下。