在SQLite中使用LIKE进行模糊查询时，'abc%'这种前缀匹配形式是有机会利用索引的，而'%abc'这种左右都包含通配符的形式则只能全表扫描。很多开发者误以为LIKE永远无法使用索引，实际上SQLite的查询规划器在满足特定条件时，会把前缀LIKE查询改写为索引范围扫描（range scan），性能可以接近等值查询。但这个优化并不是无条件生效的，它受到索引定义、排序规则（collation）、大小写敏感设置等多个因素的影响。本文将详细拆解这些条件，帮助你写出既能命中索引又可靠的查询语句。
一、LIKE前缀匹配为何能够使用索引
要理解LIKE前缀匹配的索引优化，首先要明白SQLite索引的底层结构。SQLite的索引基于B-tree实现，索引条目按照键值有序存储。当我们执行WHERE name LIKE 'abc%'时，如果name列上有索引，查询规划器可以将这个条件近似转换为name >= 'abc' AND name < 'abd'这样的范围条件。由于索引本身是有序的，满足这个范围的记录在物理上是连续存放的，数据库只需要定位到起点，然后顺序读取直到越界即可，时间复杂度从全表扫描的O(n)降低到O(log n + m)，其中m是匹配的行数。
但这个转换有一个隐含前提：范围条件的比较规则必须与索引的排序规则一致，并且LIKE的匹配语义必须能被范围比较准确表达。问题恰恰出在这里——SQLite默认的LIKE是不区分ASCII大小写的，即'abc%'能匹配'ABC123'，而普通的字符串比较>=是区分大小写的。如果规划器贸然把LIKE改写为范围扫描，就可能漏掉'ABC123'这样的行，导致查询结果错误。因此SQLite只有在确认大小写语义不会造成遗漏时，才敢启用索引。
我们可以用EXPLAIN QUERY PLAN来验证一条查询是否走了索引。看下面的例子：
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
);
-- 创建默认排序规则的索引
CREATE INDEX idx_users_name ON users(name);
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE name LIKE 'abc%';
在默认配置下执行上述语句，你会看到SEARCH users USING INDEX idx_users_name (name>? AND name<?)这样的输出吗？答案是不会。默认情况下，由于LIKE不区分大小写而索引区分大小写，规划器会放弃索引，输出SCAN users表示全表扫描。这正是许多开发者踩的第一个坑：明明写了前缀匹配，建了索引，查询却依然慢。
二、让LIKE命中索引的三种配置方案
既然大小写语义不一致是主要障碍，解决思路就是让两者对齐。SQLite提供了三种常见方案，各有适用场景。
方案一：使用PRAGMA case_sensitive_like。执行PRAGMA case_sensitive_like = ON;后，LIKE运算符变为大小写敏感，与默认的BINARY排序规则一致，此时前缀LIKE可以被安全地改写为索引范围扫描。注意这个PRAGMA是连接级别的设置，必须在准备语句之前执行，对已编译好的语句无效。示例：
PRAGMA case_sensitive_like = ON; EXPLAIN QUERY PLAN SELECT * FROM users WHERE name LIKE 'abc%'; -- 输出：SEARCH users USING INDEX idx_users_name (name>? AND name<?)
方案二：为列或索引指定NO CASE排序规则。如果业务上确实需要不区分大小写的匹配，可以在建索引时声明COLLATE NOCASE：
CREATE INDEX idx_users_name_nocase
ON users(name COLLATE NOCASE);
EXPLAIN QUERY PLAN
SELECT * FROM users WHERE name LIKE 'abc%';
-- 使用NOCASE索引时同样能走范围扫描
NO CASE排序规则在比较时会把ASCII字母统一按小写处理，因此'ABC'与'abc'被视为相等，LIKE的默认不区分大小写语义与索引排序达成一致，规划器就可以放心使用索引。也可以直接在表定义中给列加上COLLATE NOCASE，效果类似。需要注意NO CASE只对ASCII字母生效，Unicode字符的大小写不受影响。
方案三：改用GLOB运算符。GLOB是SQLite特有的运算符，语法类似shell通配符，*匹配任意串，?匹配单字符，并且它天生就是大小写敏感的。因此WHERE name GLOB 'abc*'在默认索引上可以直接走范围扫描，无需任何PRAGMA设置。如果你的匹配模式本来就是大小写敏感的，GLOB是最省事的选择。
三、容易失效的场景与多列索引的使用
即使配置正确，还有一些情况会导致索引失效。第一是通配符出现在开头，例如LIKE '%abc'或LIKE '%abc%'，这种模式无法转换为有序范围，任何B-tree索引都帮不上忙。如果这类查询是高频需求，需要考虑反向存储字段（把字符串倒序存一列，对倒序列做前缀匹配）或引入FTS5全文索引。
第二是使用了ESCAPE子句或模式不是静态字符串。当LIKE的匹配模式来自宿主参数而非字面量时，SQLite仍然可以优化，前提是你在运行前用sqlite3_stmt准备语句并让规划器看到参数形态；实际上对于绑定参数的前缀匹配，较新版本的SQLite会在执行时动态判断模式是否为前缀形式并尝试索引，但最稳妥的做法仍然是用EXPLAIN QUERY PLAN实测验证。当模式中包含转义字符时，例如LIKE 'abc\%%' ESCAPE '\'，反斜杠后的%是普通字符，规划器仍可能识别出前缀'abc\'，但复杂模式下的行为需要逐个验证。
第三是多列索引中的列顺序问题。假设有索引CREATE INDEX idx ON users(dept, name COLLATE NOCASE)，查询WHERE name LIKE 'abc%'无法直接使用这个索引，因为name不是索引的最左前缀列。必须同时约束dept列，例如WHERE dept = 'sales' AND name LIKE 'abc%'，此时SQLite会先用dept做等值定位，再在name上做范围扫描，这就是所谓的最左前缀原则。验证方式同样是：
EXPLAIN QUERY PLAN SELECT * FROM users WHERE dept = 'sales' AND name LIKE 'abc%'; -- SEARCH users USING INDEX idx (dept=? AND name>? AND name<?)
此外提醒一点：即使查询计划显示走了索引，如果匹配行数占比很高（比如前缀只有一个字符'a%'），范围扫描的收益也会下降，规划器可能基于统计信息主动选择全表扫描，这是正常且合理的行为，不要机械地认为SCAN就一定比SEARCH差。
四、实践建议与验证流程
综合以上分析，给出一个可落地的操作流程。第一步，明确业务对大小写的真实需求，这决定了你选哪条路线：需要不区分大小写就用COLLATE NOCASE索引，需要区分就用GLOB或PRAGMA设置。第二步，尽量让匹配模式是字面量或简单的前缀形式，避免开头通配符。第三步，多条件查询时按等值列在前、LIKE列在后的顺序设计复合索引。第四步，也是最重要的，任何优化都要用EXPLAIN QUERY PLAN确认，输出中出现SEARCH ... USING INDEX才算真正命中。
最后再用.eqp on或.scanstats est等sqlite3命令行工具观察真实执行情况，在大数据量下做对比测试。索引优化没有银弹，理解排序规则与LIKE语义的对齐原理，比死记配置结论更重要——掌握了这一点，无论是处理中文搜索、多列组合还是迁移到其他数据库，你都能举一反三地分析类似问题。