1. 从数据库调优说起
从事数据库开发的同学都知道，好的索引设计能让查询性能产生质的飞跃。但在实际项目中，我们经常遇到这样的情况：明明建了索引，执行速度却仍然像蜗牛爬行。就像给汽车装上了喷气发动机，结果发现油门踩到底还是跑不起来——问题很可能出在索引失效上。
在SQLite这个轻量级数据库的实际应用场景中，索引失效的情况尤其容易被忽视。本文将基于SQLite 3.36版本，深入分析导致索引失效的各种"坑"，并通过详实的示例演示如何规避这些问题。
2. SQLite索引工作原理速览
在正式讲解失效案例前，咱们先简单回顾下SQLite索引的运作机制。SQLite采用B-tree结构存储索引数据，这种结构能快速定位到特定范围的值。但优化器是否选择索引，取决于多种因素：
- 数据分布统计信息
- 查询条件的排列组合
- 字段类型的匹配情况
- 表达式与函数的使用方式
现在，让我们进入实战环节，看看哪些具体场景会让你的索引"名存实亡"。
3. 索引失效的经典场景
3.1 表达式左值运算
直接在索引字段上使用运算或函数，相当于给索引字段戴上面具，导致索引失效：
-- 建立索引
CREATE INDEX idx_products_price ON products(price);
-- 正确查询（使用索引）
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price = 1999;
-- 失效查询
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price * 1.1 = 2198.9;
/*
原因：左侧的price字段经过计算后，
数据库无法反向推导原始值，导致全表扫描
*/
3.2 类型匹配不一致
SQLite是动态类型数据库，但这不等于可以随意混用类型：
CREATE TABLE users (
id INTEGER PRIMARY KEY,
phone TEXT UNIQUE
);
CREATE INDEX idx_users_phone ON users(phone);
-- 正确方式（使用索引）
SELECT * FROM users WHERE phone = '13800138000';
-- 失效查询（隐式类型转换）
SELECT * FROM users WHERE phone = 13800138000;
/*
注意：数字类型无法与TEXT类型直接匹配，
导致索引失效并触发全表扫描
*/
3.3 索引列顺序错位
组合索引的顺序就像密码锁的转轮，必须按特定顺序才能打开：
CREATE INDEX idx_order_time_user ON orders(create_time, user_id);
-- 正确顺序（使用索引）
SELECT * FROM orders
WHERE create_time > '2023-01-01' AND user_id = 10086;
-- 错误顺序（部分使用索引）
SELECT * FROM orders
WHERE user_id = 10086 AND create_time > '2023-01-01';
/*
虽然条件字段都包含，但顺序改变导致只能部分使用索引，
这种情况需要根据查询频率调整索引列顺序
*/
（由于篇幅限制，以下章节继续保持详细展开模式）
4. 关键关联技术：EXPLAIN的使用
要验证索引是否生效，必须掌握EXPLAIN QUERY PLAN工具：
-- 示例：分析查询执行计划
EXPLAIN QUERY PLAN
SELECT * FROM products
WHERE category_id = 5
AND status = 1
ORDER BY create_time DESC;
/* 输出结果解读：
0|0|0|SCAN TABLE products USING INDEX idx_category_status
0|0|0|USE TEMP B-TREE FOR ORDER BY
*/
输出中SCAN表示全表扫描，SEARCH表示索引查询。当看到SCAN时就要提高警惕了。
5. 应用场景分析
索引优化最常见于以下场景：
- 电商平台商品筛选（多条件组合查询）
- 日志系统的时间范围检索
- 社交媒体的用户关系网络
- 物联网设备的时序数据存取
在这些场景中，查询响应速度直接影响用户体验。比如当用户同时选择价格区间、商品分类、评分星级时，合理的组合索引能大幅提升响应速度。
6. 技术优缺点对比
优点：
- 将查询时间复杂度从O(n)降到O(log n)
- 显著减少磁盘I/O操作
- 提高排序和分组操作效率
缺点：
- 增加写操作时间（需要维护索引）
- 占用额外存储空间
- 不合理的索引设计反而降低性能
7. 注意事项清单
- 定期使用
ANALYZE命令更新统计信息 - 避免在WHERE子句中频繁使用函数运算
- 组合索引的列顺序要匹配高频查询条件
- 注意数据类型的严格匹配
- 使用
COVERING INDEX减少回表次数 - 监控索引使用率，及时清理冗余索引
8. 实战经验总结
经过对各种失效场景的分析，我们可以得出以下核心结论：
- 左值保护原则：保证索引字段在表达式的"纯洁性"
- 最左前缀原则：组合索引的列顺序决定可用性
- 类型严格原则：消除隐式类型转换
- 覆盖索引优先：尽可能通过索引直接获取数据
- 动态调整策略：根据数据分布变化调整索引策略