从倒排索引原理到 MySQL / Elasticsearch / SQLite 的完整实现机制，彻底讲清楚
传统数据库用 LIKE '%keyword%' 做模糊查询，本质是全表扫描 + 字符串遍历，不走索引，性能极差。
-- 文档 → 词列表 Doc1 → [MySQL, 数据库, 索引, 查询] Doc2 → [MySQL, 全文, 搜索, InnoDB] Doc3 → [Elasticsearch, 全文, Lucene] -- 查"全文"? 要扫所有文档 ❌
-- 词 → 文档列表 MySQL → [Doc1, Doc2] 全文 → [Doc2, Doc3] 索引 → [Doc1] Lucene → [Doc3] -- 查"全文"? 直接查 ✅ O(1)
| Token（词项） | Posting List（倒排列表） | 词频 TF | 文档频率 DF |
|---|---|---|---|
| MySQL | Doc1(pos:0)Doc3(pos:0) |
1,1 | 2 |
| 全文搜索 | Doc1(pos:2)Doc2(pos:1) |
1,1 | 2 |
| Elasticsearch | Doc2(pos:0)Doc3(pos:2) |
1,1 | 2 |
| 索引 | Doc1(pos:3) |
1 | 1 |
| 对比分析 | Doc3(pos:3) |
1 | 1 |
倒排列表中每条记录（Posting）通常包含：
在分词前对原始字符做清洗：
<b>搜索</b> → 搜索& → &ＡＢＣ → ABC把文本切割成词项（Token）：
对分出的 Token 做后处理：
两个核心直觉：
BM25 是 TF-IDF 的改进版，解决了两个问题：
-- 1. 建表时创建全文索引 CREATE TABLE articles ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200), body TEXT, -- 创建全文索引（可以多列联合） FULLTEXT INDEX idx_fts (title, body) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2. 自然语言模式（默认，按相关性排序） SELECT id, title, MATCH(title, body) AGAINST ('MySQL 全文搜索') AS relevance FROM articles WHERE MATCH(title, body) AGAINST ('MySQL 全文搜索') ORDER BY relevance DESC; -- 3. 布尔模式（精确控制） SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('+MySQL +索引 -删除' IN BOOLEAN MODE); -- + 必须包含, - 必须排除, * 前缀通配, "" 短语 -- "全文搜索" 短语匹配, ~负权重, > < 调整权重 -- 4. 查询扩展模式（两轮搜索） SELECT * FROM articles WHERE MATCH(title, body) AGAINST ('数据库' WITH QUERY EXPANSION); -- 5. 事后创建索引 ALTER TABLE articles ADD FULLTEXT INDEX idx_fts2 (title, body) WITH PARSER ngram; -- 中文用 ngram 分词
-- 当你创建 FULLTEXT INDEX 后，MySQL 会在 .ibd 文件中 -- 自动创建 6 张辅助索引段，命名规则： FTS_{table_id}_BEING_DELETED -- 待删除文档ID列表 FTS_{table_id}_BEING_DELETED_CACHE -- 内存缓存的待删列表 FTS_{table_id}_CONFIG -- FTS配置（同步LSN等） FTS_{table_id}_DELETED -- 已标记删除的文档ID FTS_{table_id}_DELETED_CACHE -- 删除缓存 FTS_{table_id}_0000000000000161_INDEX_1 -- 实际倒排索引分片 -- INDEX_1 ~ INDEX_6：按首字母哈希值分6个分片 -- 辅助索引表每行结构： -- (word VARCHAR, first_doc_id BIGINT, last_doc_id BIGINT, -- doc_count INT, ilist BLOB) -- ilist：压缩存储 doc_id + word_pos 列表
ngram_token_size=2innodb_ft_min_token_size（默认3，中文要改2）// 1. 创建索引，配置 Analyzer（中文IK分词） PUT /articles { "settings": { "analysis": { "analyzer": { "ik_smart_analyzer": { "type": "custom", "tokenizer": "ik_smart", // IK粗粒度分词 "filter": ["lowercase", "stop"] // 小写 + 停用词 } } } }, "mappings": { "properties": { "title": { "type": "text", "analyzer": "ik_smart_analyzer", // 写入时分析器 "search_analyzer": "ik_smart" // 搜索时分析器 }, "body": { "type": "text", "analyzer": "ik_max_word" } } } } // 2. match 查询（全文搜索最常用） GET /articles/_search { "query": { "match": { "title": { "query": "MySQL 全文搜索", "operator": "or", // or/and "minimum_should_match": "75%" // 至少75%词匹配 } } } } // 3. multi_match（多字段搜索 + 字段权重） GET /articles/_search { "query": { "multi_match": { "query": "全文搜索原理", "fields": ["title^3", "body^1"], // title 权重3倍 "type": "best_fields" // cross_fields/most_fields } } } // 4. match_phrase（短语匹配，词序和位置） GET /articles/_search { "query": { "match_phrase": { "body": { "query": "倒排索引", "slop": 1 // 允许词间距1个词的误差 } } } } // 5. bool 复合查询（最强大的查询方式） GET /articles/_search { "query": { "bool": { "must": [{ "match": { "title": "全文搜索" }}], "should": [{ "match": { "body": "BM25" }}], "must_not":[{ "match": { "title": "广告" }}], "filter": [{ "term": { "status": "published" }}] } }, "highlight": { "fields": { "title": {}, "body": {} } // 高亮 } }
| 特性 | FTS4 | FTS5 |
|---|---|---|
| 评分算法 | TF-IDF | BM25 |
| 列过滤 | 不支持 | 支持 |
| 短语查询 | 支持 | 支持 |
| 自定义tokenizer | 有限 | 完整API |
-- 1. 创建 FTS5 虚拟表 CREATE VIRTUAL TABLE docs USING fts5( title, body, tokenize = 'unicode61', -- 默认分词器（英文友好） -- tokenize = 'porter ascii', -- 词干还原 -- tokenize = 'trigram', -- trigram模式（支持中文LIKE模糊） content='articles', -- 关联真实表（content table） content_rowid='id' ); -- 2. 同步真实表数据到 FTS 索引 INSERT INTO docs(docs) VALUES('rebuild'); -- 全量重建索引 -- 3. 基本全文搜索 SELECT rowid, title, bm25(docs) AS score -- BM25 评分（负值，越小越相关） FROM docs WHERE docs MATCH '全文搜索' ORDER BY bm25(docs); -- 升序（负值越小=得分越高） -- 4. 布尔查询语法 SELECT * FROM docs WHERE docs MATCH 'MySQL AND 索引 NOT 删除'; -- 5. 列过滤（仅搜 title 列） SELECT * FROM docs WHERE docs MATCH 'title:全文搜索'; -- 6. 短语搜索 SELECT * FROM docs WHERE docs MATCH '"倒排 索引"'; -- 精确短语 -- 7. 前缀查询 SELECT * FROM docs WHERE docs MATCH 'index*'; -- 前缀匹配 -- 8. highlight + snippet 辅助函数 SELECT highlight(docs, 0, '<b>', '</b>') AS title_hl, snippet(docs, 1, '<em>', '</em>', '...', 30) AS body_snippet FROM docs WHERE docs MATCH '索引'; -- 9. trigram 模式（SQLite 3.38+，支持中文/LIKE类搜索） CREATE VIRTUAL TABLE docs_cn USING fts5( content, tokenize='trigram' -- 3字符滑动窗口，无需外部分词 ); -- 适合中文但索引较大，存储开销约3x -- 10. bm25 权重调整（各列权重） SELECT *, bm25(docs, 10.0, 1.0) score FROM docs WHERE docs MATCH '搜索' ORDER BY score; -- title权重10, body权重1
key = (level, segment_id, page_no)| 对比维度 | 🐬 MySQL InnoDB FTS | 🟡 Elasticsearch | 🪶 SQLite FTS5 |
|---|---|---|---|
| 底层引擎 | InnoDB 辅助索引段（6张隐藏表） | Apache Lucene（Segment + FST） | B-Tree 虚拟表（%_data） |
| 索引结构 | 哈希分片倒排索引，BLOB存Posting | FST词典 + 压缩Posting List（FOR算法） | B-Tree叶页存Posting，变长整型压缩 |
| 评分算法 | TF-IDF（固定，不可调） | BM25（默认，可自定义） | BM25（内置 bm25() 函数） |
| 中文分词 | ngram（内置，精度一般） | IK/HanLP等专业分词（插件） | trigram（内置）/ 自定义tokenizer |
| 部署方式 | 内嵌 MySQL，无需额外服务 | 独立服务，需单独维护 | 无服务，单文件嵌入 |
| 分布式支持 | 不支持 | 原生支持（Shard/Replica） | 不支持 |
| 写入性能 | 中等（FTS Cache 批量落盘） | 高（内存Buffer+近实时Refresh） | 高（内存哈希+LSM风格合并） |
| 查询性能 | 中等（千万级可用） | 极高（亿级，分布式并行） | 高（百万级以内） |
| 数据一致性 | 与主表强一致（事务支持） | 近实时（默认1秒延迟） | 与SQLite事务一致 |
| 高亮/摘要 | 不支持 | highlight API，支持多种策略 | highlight() / snippet() 内置函数 |
| 聚合分析 | 不支持 | 强大的 Aggregation API | 不支持 |
| 运维复杂度 | 低（MySQL自带） | 高（独立集群，JVM调优） | 极低（嵌入式） |
| 适用数据量 | 百万~千万级 | 亿级以上 | 百万级以内 |
| 典型场景 | 已有MySQL系统，简单搜索需求 | 专业搜索引擎，日志分析，大数据 | 移动端/桌面App，小型系统 |
MySQL FTS 直接在现有 MySQL 中建 FULLTEXT 索引，无需引入新服务。适合博客、CMS、内部工具等中小规模业务，数据量千万级以内，不追求极致搜索体验。
Elasticsearch 电商搜索、知识库、日志平台、新闻推荐等搜索是核心功能的场景。支持同义词、拼音、分面过滤、相关性调优，亿级文档毫秒响应。
SQLite FTS5 iOS/Android App内置搜索、Electron桌面应用、个人工具、CLI程序。零运维，单文件，BM25评分，百万文档轻松驾驭。
从 SQLite FTS5 入门，理解倒排索引和BM25
在 MySQL 项目中实践全文索引，理解生产限制
学习 Elasticsearch，掌握 Lucene Segment 架构