在SQLite查询优化中，一个容易被忽视却影响巨大的习惯，就是在WHERE子句里把数据表的列放进函数中进行过滤。这种写法在业务逻辑上也许能得出正确结果，但数据库引擎往往因此放弃使用已有的索引，转而执行代价高昂的全表扫描。要写出高性能的SQLite查询，必须先弄清楚索引的工作机制以及函数调用对它的破坏方式。
SQLite索引匹配的基本规则
SQLite默认使用B树结构来组织索引。当我们对某个列建立索引后，引擎会按照该列的原始值有序存储指向行记录的指针。在执行查询时，如果WHERE条件的形式是列名 操作符 常量或参数，优化器就能通过二分查找快速定位到符合条件的索引区间，避免遍历整张表。
一旦我们把列名包裹在函数中，例如lower(name) = 'tom'或者date(created_at) = '2023-01-01'，索引中保存的是name或created_at的原始值，而函数计算后的结果并没有被预先排序和存储。优化器无法根据函数输出的有序性来缩小搜索范围，于是只能对每一行都调用一次该函数，再判断结果是否满足条件。这种行为在SQLite的查询计划中被称为“全索引扫描”或“全表扫描”，随着数据量增长，延迟会线性甚至指数级上升。
我们可以通过EXPLAIN QUERY PLAN指令直观看到差异。对于SELECT * FROM user WHERE id = 10，计划显示SEARCH user USING INDEX；而SELECT * FROM user WHERE abs(id) = 10则显示SCAN user。后者意味着引擎要读完所有行。理解这条规则，是避免写出慢查询的前提。
常见错误写法与等价改写方案
开发中最频繁的误用场景是对字符串做截取或大小写转换。比如想找出用户名前三位是“adm”的账户，有人会写：
SELECT * FROM account WHERE substr(username, 1, 3) = 'adm';
这条语句让username上的索引完全失效。更好的做法是把计算转移到常量侧，或者利用前缀匹配。如果确实需要按前缀查，可以直接用LIKE 'adm%'，SQLite对末尾带通配符的LIKE在索引列上能部分使用索引；若必须函数处理，可为该表达式建立索引（见下一节）。
另一个典型例子是时间字段的格式转换。许多表以TEXT存ISO时间，查询某天时写成WHERE date(login_time) = '2023-05-01'。正确思路是改用范围查询：WHERE login_time >= '2023-05-01' AND login_time < '2023-05-02'。这样原始列参与比较，索引可正常生效，而且不需要任何函数计算。
对于大小写不敏感匹配，与其lower(email) = lower('A@B.com')，不如建一个COLLATE NOCASE的索引，然后直接写email = 'A@B.com'。SQLite的NOCASE排序规则会在比较时忽略大小写，同时仍能走索引，既简洁又高效。
使用表达式索引与生成列从根本上解决
如果业务上必须对列使用函数才能过滤，SQLite提供了表达式索引（expression index）来打破“函数导致索引失效”的限制。语法是在建索引时把函数调用写在括号内：
CREATE INDEX idx_user_name_prefix ON account (substr(username, 1, 3)); SELECT * FROM account WHERE substr(username, 1, 3) = 'adm';
此时优化器识别到WHERE中的函数表达式与索引定义一致，就会用上这个专用索引。要注意表达式索引会占用额外空间，并且只在查询表达式完全匹配时生效，因此不能滥用，应针对高频查询定制。
从SQLite 3.31.0开始，还支持生成列（generated column）。我们可以新增一个持久化或虚拟的生成列，把函数结果存下来并单独建索引：
ALTER TABLE account ADD COLUMN name_prefix TEXT GENERATED ALWAYS AS (substr(username, 1, 3)) VIRTUAL; CREATE INDEX idx_prefix ON account (name_prefix); SELECT * FROM account WHERE name_prefix = 'adm';
生成列把运行期计算变为存储期计算，查询时直接比对列值，索引利用率与普通列无异。对于读多写少、函数逻辑固定的场景，这是兼顾语义清晰与性能的最佳实践。综合来看，避免在WHERE中直接对列使用函数，是SQLite性能调优的第一性原则，实在无法避开时就用表达式索引或生成列将计算前置。