SQLite 的索引机制如何影响查询性能？

SQLite 的索引机制对查询性能有重要影响：

1. 索引类型

   • B-Tree 索引：默认索引类型，适合范围查询和排序
   • 哈希索引：仅在内存数据库中可用，适合等值查询
   • R-Tree 索引：用于空间数据，支持地理信息查询
   • 全文索引（FTS）：用于全文搜索功能

2. 索引创建

   sql
   -- 创建单列索引
   CREATE INDEX idx_name ON table_name(column_name);
   
   -- 创建复合索引
   CREATE INDEX idx_composite ON table_name(col1, col2);
   
   -- 创建唯一索引
   CREATE UNIQUE INDEX idx_unique ON table_name(column_name);

3. 索引使用原则

   • 在 WHERE 子句、JOIN 条件、ORDER BY、GROUP BY 中频繁使用的列上创建索引
   • 选择性高的列（唯一值多）更适合建索引
   • 避免在小表上创建索引
   • 复合索引遵循最左前缀原则

4. 索引优化技巧

   • 使用 EXPLAIN QUERY PLAN 分析查询执行计划
   • 避免在索引列上使用函数或表达式
   • 使用 LIKE 'prefix%' 可以利用索引，LIKE '%suffix' 不能
   • 合理使用覆盖索引（Covering Index）避免回表

5. 索引维护

   • 索引会增加 INSERT、UPDATE、DELETE 操作的开销
   • 定期使用 ANALYZE 命令更新统计信息
   • 使用 REINDEX 重建碎片化的索引
   • 删除不再使用的索引以节省空间和提高写入性能

6. 主键和自动索引

   • 创建表时声明 PRIMARY KEY 会自动创建唯一索引
   • UNIQUE 约束也会自动创建索引
   • WITHOUT ROWID 表可以避免创建隐藏的 rowid 索引

合理使用索引可以显著提高查询性能，但需要在查询性能和写入性能之间找到平衡。