SQLite中normal index与FTS的区别是什么？普通索引是否仅支持=查询？

好问题！我来帮你理清楚这两者的差异，刚好能验证你之前的猜测：

1. 设计目标与核心能力的本质差异 #

• 普通索引：它的核心是把整个列值作为一个单一的“键”，用B树结构排序存储，目的是快速定位精确匹配的行，或者有限的前缀匹配（比如WHERE content LIKE 'sql%'这种左匹配，SQLite能利用普通索引加速）。但它完全不处理文本的分词（tokenization）——也就是说，它把一段完整的段落当成一个不可拆分的整体，根本不知道里面有哪些单词。
• FTS索引：这是专门为全文搜索打造的索引，它会先对文本做分词处理：把段落拆成单个词汇、过滤掉无意义的停用词（比如“the”“a”）、统一大小写/处理词根，然后为每个分词建立反向索引，记录每个词出现在哪些行、哪个位置。这也是它能支持MATCH查询的核心原因——它能理解“找包含某个单词的段落”这种需求。

2. 支持的查询场景差异 #

• 普通索引只能加速这些场景：
  • 精确匹配：WHERE content = 'SQLite Index'
  • 左前缀匹配：WHERE content LIKE 'SQL%'
    而如果是WHERE content LIKE '%index%'这种中间/后缀匹配，普通索引完全派不上用场，只能全表扫描；更别提匹配段落内单个单词的复杂查询了。
• FTS索引支持的场景则完全是为文本搜索量身定做的：
  • 匹配单个单词：WHERE content MATCH 'index'
  • 多单词组合：WHERE content MATCH 'index AND SQLite'
  • 短语精确匹配：WHERE content MATCH '"SQLite index"'
  • 模糊前缀匹配：WHERE content MATCH 'sql*'

3. 存储与实现方式差异 #

• 普通索引是基于常规表的B树索引，和表的数据存储相互独立，占用空间相对小，适合常规的精确查询。
• FTS索引是通过虚拟表实现的（比如FTS3/FTS4/FTS5），它有自己的存储格式，会存储分词、位置等额外信息，占用空间通常比普通索引大，但针对全文搜索的性能提升是数量级的。

关于你猜测的验证 #

你之前的猜测完全正确！普通索引确实无法支持MATCH这类针对段落内单词的复杂查询——因为它从设计之初就没有做分词处理，根本无法识别文本内部的词汇。SQLite的CREATE INDEX文档里没提tokenization，就是因为普通索引不需要这个功能，它的索引单位是整个列值，而不是文本里的单个词汇。

内容的提问来源于stack exchange，提问作者Minh Nghĩa