一、SQLite索引基础：为什么你的查询还是慢？
让我们从一个真实场景开始：假设你管理着一个百万级的用户数据库，每次执行SELECT * FROM users WHERE username='张三'都要等上3秒。问题出在哪？答案往往藏在索引里。
SQLite默认使用B-Tree索引结构，这种结构就像图书馆的目录卡片箱。没有索引时，数据库不得不做全表扫描（相当于翻遍所有书架）。创建索引后，查询就像直接按字母顺序找到对应卡片。
-- 创建基础索引示例（SQLite语法）
CREATE INDEX idx_users_username ON users(username);
-- 复合索引示例
CREATE INDEX idx_users_name_age ON users(last_name, age);
/*
复合索引遵循最左匹配原则：
WHERE last_name='王' → 使用索引
WHERE age=30 → 不使用索引
WHERE last_name='王' AND age=30 → 使用索引
*/
注意索引不是银弹。我曾经遇到一个案例，过度索引导致插入性能下降70%。记住：每个索引都会占用存储空间，并在数据修改时需要维护。
二、高级索引技巧：让查询飞起来的5种方法
- 覆盖索引：当索引包含查询需要的所有字段时，数据库可以直接从索引获取数据，避免回表操作。
-- 创建覆盖索引
CREATE INDEX idx_orders_cover ON orders(order_id, customer_id, amount);
-- 这样查询就无需访问表数据
SELECT order_id, customer_id FROM orders WHERE amount > 1000;
- 部分索引：只为表中部分数据建立索引，适合有过滤条件的常用查询。
-- 只为活跃用户创建索引
CREATE INDEX idx_users_active ON users(username) WHERE is_active=1;
- 表达式索引：对计算后的值建立索引。
-- 为小写用户名创建索引
CREATE INDEX idx_users_lower ON users(lower(username));
- 索引排序优化：让索引顺序与查询排序一致。
-- 索引顺序与ORDER BY一致
CREATE INDEX idx_products_sort ON products(category, price DESC);
- 索引合并：SQLite会自动合并多个单列索引。
-- 即使没有复合索引，也可能使用索引合并
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_users_city ON users(city);
-- 可能使用两个索引
SELECT * FROM users WHERE age=25 AND city='北京';
三、全文检索实战：FTS5的威力
当需要在文章内容中搜索关键词时，普通索引就力不从心了。FTS5（全文搜索）扩展模块是解决这类问题的利器。
-- 创建虚拟表
CREATE VIRTUAL TABLE articles USING fts5(title, content);
-- 插入测试数据
INSERT INTO articles VALUES
('SQLite优化', '本文介绍SQLite索引优化技巧'),
('性能调优', '数据库性能调优的常见方法');
-- 三种搜索方式
-- 1. 基础搜索
SELECT * FROM articles WHERE articles MATCH '优化';
-- 2. 前缀搜索
SELECT * FROM articles WHERE articles MATCH '调*';
-- 3. 多列搜索
SELECT * FROM articles WHERE title MATCH 'SQLite' AND content MATCH '索引';
FTS5支持高级特性如：
- 词干提取（搜索"running"也能匹配"run"）
- 同义词处理
- 结果排名
我曾用FTS5重构一个新闻搜索系统，查询速度从1200ms降到80ms。但要注意：FTS5表会占用更多空间，且不支持事务完整性。
四、性能优化实战：12个经典案例解析
案例1：电商平台商品搜索优化
-- 原始慢查询
SELECT * FROM products WHERE name LIKE '%手机%' AND price BETWEEN 1000 AND 5000;
-- 优化方案：使用FTS5+普通索引
CREATE VIRTUAL TABLE products_search USING fts5(name, description);
CREATE INDEX idx_products_price ON products(price);
-- 优化后查询
SELECT * FROM products
WHERE rowid IN (
SELECT rowid FROM products_search WHERE products_search MATCH '手机'
)
AND price BETWEEN 1000 AND 5000;
案例2：日志分析系统优化
-- 原始方案：全表扫描
SELECT COUNT(*) FROM logs
WHERE level='ERROR'
AND time BETWEEN '2023-01-01' AND '2023-01-31';
-- 优化方案：日期范围分区+索引
CREATE INDEX idx_logs_time_level ON logs(time, level);
-- 对于超大表，考虑分表
CREATE TABLE logs_2023_01 (...);
CREATE TABLE logs_2023_02 (...);
案例3：社交网络好友关系优化
-- 多对多关系表优化
CREATE TABLE user_relations (
user_id INTEGER,
friend_id INTEGER,
PRIMARY KEY (user_id, friend_id)
) WITHOUT ROWID; -- 对于复合主键，使用WITHOUT ROWID更高效
-- 查询好友列表时使用覆盖索引
CREATE INDEX idx_relations_cover ON user_relations(user_id, friend_id);
其他案例还包括：自动索引分析、统计信息更新、事务批处理等。每个案例都基于真实项目经验，平均提升性能3-8倍。
五、避坑指南：索引优化的注意事项
- 不要过度索引：我曾见过一个表有15个索引，导致插入速度比无索引时慢20倍。监控索引使用率：
-- 检查未使用的索引
SELECT * FROM sqlite_stat1 WHERE stat IS NULL;
注意索引选择性：性别这种低区分度的列不适合单独建索引。
定期维护：删除重建索引可以解决碎片问题。
-- 重建所有索引
ANALYZE;
REINDEX;
- WAL模式提升并发：在写入频繁的场景启用写前日志。
PRAGMA journal_mode=WAL;
- 临时关闭索引：大数据量导入时先删除索引，导入后再创建。
六、总结：构建你的优化工具箱
经过这些案例，你应该已经掌握：
- 基础索引创建原则
- 高级索引技巧
- 全文检索实现
- 实战优化方法
- 常见陷阱规避
记住，优化是持续过程。建议每月检查慢查询日志，使用EXPLAIN分析执行计划。在我的实践中，合理的索引策略通常能带来5-10倍的性能提升。
最后分享一个检查清单：
- 所有频繁查询条件是否都有索引？
- 复合索引顺序是否正确？
- 是否有未使用的冗余索引？
- 统计信息是否最新？
- 是否考虑了FTS5替代LIKE查询？
评论