SQLite LIKE 查询为什么走不了索引

一张用户表，name 列上建了索引，查 WHERE name LIKE '张%' 很快，改成 WHERE name LIKE '%张%' 就突然变慢，数据量一上来能差出几十倍。SQL 只改了一个百分号的位置，索引却像不存在了一样。这篇笔记用本地 :memory: 数据库把这个现象复现出来，定位到 LIKE 能不能走索引的三条硬条件，再给出每种写法的修法，全部命令都可以直接复制验证。

现象：前缀查走索引，中间查全表扫描

建一张最小的表，name 列上建索引，插几千行模拟数据：

CREATE TABLE t_user(id INTEGER PRIMARY KEY, name TEXT);
CREATE INDEX idx_user_name ON t_user(name);
WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x < 5000)
INSERT INTO t_user(name) SELECT 'user_' || printf('%05d', x) FROM c;
INSERT INTO t_user(name) VALUES ('张伟'), ('张小明'), ('李张明'), ('王小张'), ('张三丰');

跑两条 LIKE 查询，对比执行计划：

EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%张%';

两条的执行计划都是 SCAN t_user。SCAN 是从头到尾逐行比对，SEARCH 是按索引定位到一个范围再取行。表只有几千行时差距还不明显，表到几十万行时，SCAN 要把每一行的 name 都拿出来做一次模式匹配，耗时跟着数据量线性涨，SEARCH 只扫命中范围附近的行，耗时基本不随总量涨。第一条是前缀查，照理说应该走索引，结果也是 SCAN，这个反直觉的点就是本篇要拆开的地方：前缀查本身有走索引的条件，默认建的 BINARY 索引和默认不区分大小写的 LIKE 对不上，条件没对齐，前缀查同样退化成 SCAN。给 name 列补一个 NOCASE 索引再跑：

CREATE INDEX idx_user_name_nocase ON t_user(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%张%';

这时第一条变成 SEARCH t_user USING COVERING INDEX idx_user_name_nocase (name>? AND name<?)，第二条仍是 SCAN。前缀查走索引的条件对齐了，中间查还是走不了，因为模式串开头是通配符，算不出范围。两条查询的差距，要在索引规则对齐之后才看得出来，默认状态下两条都是 SCAN，单看“前缀快、中间慢”会误判现场。

再看一个容易混淆的现象：即使 NOCASE 索引对齐了，前缀查也不是永远走索引。把查询改成下面这样：

EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%伟';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '_张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张_';

%伟 是后缀查，_张% 是首字符通配，张_ 是前缀加单字符通配。这三条里只有 张_ 能走索引，前两条都是 SCAN。通配符出现在模式串开头，索引就失效，这个规律比“LIKE 走不了索引”这个笼统说法准确得多。张_ 的模式串开头是固定字符 张，优化器能把它转成 name >= '张' AND name < '张' + 下一个字符 这样的范围条件，索引范围定位才能成立。

还有一个现场很常见的版本：查询本身是前缀查，执行计划却仍是 SCAN。典型写法是这样的：

EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE LOWER(name) LIKE 'zhang%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE 'ZHANG%';

第一条在列上套了函数，索引列是 name，查询条件是 LOWER(name)，两者对不上，优化器没法用索引定位。第二条涉及大小写问题，默认的 LIKE 是不区分 ASCII 大小写的，优化器要保证大小写不敏感的匹配语义，普通 BINARY 索引的顺序和 LIKE 的匹配顺序对不上，也可能放弃走索引。这两条的 SCAN 不是通配符位置造成的，是条件写法和索引定义不一致造成的，要分开排查。

判据：用 EXPLAIN QUERY PLAN 区分三种 SCAN

碰到 LIKE 变慢，先不要加索引，也不要改业务逻辑。跑下面这组语句，把 SCAN 的原因分出来：

SELECT name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 't_user';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%张%';
PRAGMA case_sensitive_like;

第一条确认索引是不是真的建在 LIKE 查询的列上。索引建在 id 上、查询条件是 name，再怎么调 LIKE 写法都走不了这个索引。第二条和第三条是同一张表、同一个列的前缀查和中间查对照：补 NOCASE 索引后前缀查 SEARCH、中间查 SCAN，说明索引本身没问题，是模式串开头通配符挡住了范围定位。补 NOCASE 索引前两条都是 SCAN，说明问题不在通配符位置，要往下查大小写规则或者函数包装。

第四条 PRAGMA case_sensitive_like 是关键判据，默认值是 OFF，也就是 LIKE 默认不区分大小写。这个默认值直接影响优化器能不能用索引。SQLite 文档里写明的 LIKE 优化条件是：列上有索引，模式串不以通配符开头，并且满足大小写规则二选一——要么列索引是 COLLATE NOCASE，要么 PRAGMA case_sensitive_like 是 ON。默认建的索引是 BINARY，LIKE 默认不区分大小写，两者对不上，前缀查在某些版本和数据分布下也会退化成 SCAN。判据做到这一步，就能区分“通配符开头”和“大小写规则不一致”这两种不同的 SCAN 原因。

第三个判据针对表达式和类型。跑下面两条：

EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%' ESCAPE '\';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE CAST(name AS TEXT) LIKE '张%';

ESCAPE 本身不破坏前缀定位，带 ESCAPE 的前缀查仍能走索引。CAST、LOWER、TRIM 这类函数包装会破坏列和索引的对应关系，执行计划变回 SCAN。判据的完整链条是：索引在不在这列上，模式串开头是不是固定字符，大小写规则和索引 collation 是不是一对，条件里有没有函数包装。四条对完，SCAN 的原因就锁定了，不用猜。

根因：索引是按顺序定位，LIKE 优化是把前缀翻译成范围

索引的本质是一份按列值排好序的目录。查询能走索引，前提是能从查询条件里算出一个连续的取值范围，比如 name >= '张' AND name < '张' 的下一个边界。优化器拿到 name LIKE '张%'，会把固定前缀 张 抽出来，翻译成这样一个范围条件，再到索引里定位到范围起点，顺序往后读到范围终点就停。范围能算出来，SEARCH 就成立。

模式串以 % 或 _ 开头时，固定前缀是空的。%张% 的意思是“任意位置出现张”，第一字符可以是任何值，取值范围就是整张表，没法算出一个连续区间。索引目录再有序，也不知道从哪一行开始读、到哪一行停，只能退回 SCAN，把每一行都拿出来做模式匹配。这不是索引坏了，是这类查询本身没有可用的范围条件，索引无从下手。

大小写问题是另一条独立的根因。BINARY 索引按字符码位排序，大写字母排在小写字母前面。LIKE 默认不区分大小写，匹配时 zhang 和 ZHANG 算同一个结果，匹配顺序和 BINARY 索引顺序是两套规则。优化器不能拿 BINARY 索引的顺序去保证不区分大小写的匹配结果完整，宁可 SCAN 也不敢走一个可能漏行的索引。COLLATE NOCASE 索引按忽略大小写后的顺序排，匹配顺序和索引顺序对上了，优化器才敢用它。PRAGMA case_sensitive_like = ON 是另一条路：LIKE 改成区分大小写，匹配顺序和 BINARY 索引顺序对上了，前缀查也能走索引，代价是查询语义变了，zhang% 再也匹配不到 Zhang 开头的行。

函数包装的根因也一样：索引存的是 name 的原始值，查询条件是 LOWER(name)，优化器手里没有 LOWER(name) 的索引目录，只能逐行算函数再比较。表达式索引能解决这个问题，但那是另外建一个索引，不是让原索引去适配函数结果。根因归结起来就三条：没有可用的范围条件，大小写规则和索引顺序对不上，条件里的表达式和索引列对不上。LIKE 变慢的现场，基本都是这三条里的一条，或者几条叠加。

修法：按查询语义选写法，不要只给 LIKE 加索引

修法按查询语义分四种，选错方法会白修。

语义是前缀查，比如按姓氏、按编号前缀、按目录前缀筛选。写法保持 LIKE '张%'，同时确认两点：name 列上有索引，大小写规则和索引是一对。业务上不区分大小写，建索引时写上 NOCASE：

CREATE INDEX idx_user_name_nocase ON t_user(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE 'zhang%';

查询里不用改，优化器看到 NOCASE 索引和默认不区分大小写的 LIKE 是一对，就会走 SEARCH。业务上区分大小写，把 PRAGMA 打开再查：

PRAGMA case_sensitive_like = ON;
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE 'Zhang%';

PRAGMA 是连接级的，同一个连接里后续查询都按区分大小写走，换一个连接要重新设置。把它写进应用启动时的连接初始化里，不要只在调试用的命令行里开一次就以为修好了。

语义是中间查或者后缀查，比如搜索框里输入任意关键词。LIKE 本身走不了索引，修法不是调 LIKE 写法，是换查询手段。数据量不大、查询频率不高，接受 SCAN 就行，几千行全表匹配一次的开销有限。数据量大、搜索是核心功能，用 SQLite 的 FTS5 全文索引：

CREATE VIRTUAL TABLE t_user_fts USING fts5(name);
INSERT INTO t_user_fts(name) SELECT name FROM t_user;
SELECT name FROM t_user_fts WHERE t_user_fts MATCH '张';

FTS5 是倒排索引，按词切分再定位，比 LIKE 的逐行匹配快得多，代价是多维护一张虚拟表，写入时要同步更新。中文搜索要留意分词器，默认分词器按 Unicode 字符切分，张伟 会被切成单字，MATCH 张伟 的语义和 LIKE %张伟% 不完全一样，上线前要用真实数据对照验证。

语义是前缀查但条件里套了函数，比如 LOWER(name) LIKE 'zhang%'。修法是去掉函数包装，或者给表达式建索引：

CREATE INDEX idx_user_lower_name ON t_user(LOWER(name));
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE LOWER(name) LIKE 'zhang%';

表达式索引的表达式要跟查询里的写法一字不差，查询写 LOWER(name)，索引也要写 LOWER(name)，写成 UPPER(name) 就对不上。更省事的修法是业务层统一入库格式，写入时就把 name 存成统一大小写，查询时直接用原始列做前缀查，函数包装从源头上消失。

还有一个修法误区是给 LIKE 查询的列反复加索引。模式串开头是 %，索引建得再多，优化器也算不出范围，SCAN 照旧。加索引之前先跑 EXPLAIN QUERY PLAN，看 SCAN 是不是通配符开头造成的，是的话加索引没有用，换 FTS 或者接受 SCAN 才是正解。索引不是越多越好，每个索引都要在写入时维护，写多了反而拖慢插入和更新。

验证：四组对照，确认每种写法走什么计划

用现象一节的同一张表跑四组查询，对照执行计划和结果：

-- A：默认 BINARY 索引 + 默认 LIKE，前缀查预期 SCAN
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
-- B：中间查，预期 SCAN
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%张%';
-- C：补 NOCASE 索引后，前缀查预期 SEARCH
CREATE INDEX idx_user_name_nocase ON t_user(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
-- D：函数包装，预期 SCAN
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE LOWER(name) LIKE 'zhang%';

预期结果：A 组 SCAN，B 组 SCAN，C 组在 NOCASE 索引建好后 SEARCH，D 组 SCAN。A 组 SCAN 不是写错了，是默认 BINARY 索引加默认不区分大小写的 LIKE 本来就对不上，现场的真实状态就是这样。如果 C 组仍是 SCAN，检查索引表达式里的 COLLATE 是不是写在列后面，查询里的 LIKE 模式串开头是不是固定字符。

本地复现的完整命令，用 sqlite3 命令行一次跑完：

sqlite3 :memory: "CREATE TABLE t_user(id INTEGER PRIMARY KEY, name TEXT);
CREATE INDEX idx_user_name ON t_user(name);
WITH RECURSIVE c(x) AS (SELECT 1 UNION ALL SELECT x+1 FROM c WHERE x < 5000)
INSERT INTO t_user(name) SELECT 'user_' || printf('%05d', x) FROM c;
INSERT INTO t_user(name) VALUES ('张伟'), ('张小明'), ('李张明'), ('王小张'), ('张三丰');
SELECT '前缀查', name FROM t_user WHERE name LIKE '张%';
SELECT '中间查', name FROM t_user WHERE name LIKE '%张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
CREATE INDEX idx_user_name_nocase ON t_user(name COLLATE NOCASE);
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '张%';
EXPLAIN QUERY PLAN SELECT * FROM t_user WHERE name LIKE '%张%';
PRAGMA case_sensitive_like;
SELECT '索引', name, sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 't_user';"

输出里能同时看到这样几样东西：前缀查命中张伟、张小明、张三丰三行，中间查多命中李张明、王小张两行，默认索引下前缀查的计划是 SCAN，补 NOCASE 索引后前缀查的计划变成 SEARCH，中间查的计划始终是 SCAN。结果和计划都对上，说明规则理解对了，修法也生效了，问题闭环。

碰到 SQLite LIKE 查询变慢，先跑 EXPLAIN QUERY PLAN 看是 SEARCH 还是 SCAN，再看模式串开头是不是通配符，查 PRAGMA case_sensitive_like 和索引的 collation 是否一对，最后看条件里有没有函数包装。通配符开头的中间查换 FTS 或者接受 SCAN，前缀查把大小写规则和索引对齐，函数包装去掉或者建表达式索引。LIKE 能不能走索引，从来不是 LIKE 这个关键字的问题，是范围条件、大小写规则、索引定义三处能不能对上。