MySQL案例：全文索引浅析
原创MySQL案例：全文索引浅析
原创
前言
所谓全文索引，就是一种通过建立倒排索引，快速匹配文档内容的方式。和B+树索引一样，倒排索引也是一种索引结构，一个倒排索引是由文档中所有不重复的分词和其所在文档的映射组成。倒排索引一般有两种不同的结构，一种是inverted file index，另一种是full inverted index。
（1）inverted file index，里面存储的映射关系是{分词，(该分词所在的文档ID)}
Number | Text | Documents |
|---|---|---|
1 | how | (1,3) |
2 | are | (1,3) |
3 | you | (1,3) |
4 | fine | (2,4) |
5 | thanks | (2,4) |
（2）full inverted index，里面存储的映射关系是{分词，(该分词所在的文档ID:在文档中的具体位置)}
Number | Text | Documents |
|---|---|---|
1 | how | (1:1),(3:1) |
2 | are | (1:2),(3:2) |
3 | you | (1:3),(3:3) |
4 | fine | (2:1),(4:1) |
5 | thanks | (2:2),(4:2) |
实现原理
辅助表
在MySQL InnoDB中，当一个全文索引被创建时，一系列的辅助表会被同时创建，用于存储倒排索引的信息。
mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;
mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name | space |
+----------+----------------------------------------------------+-------+
| 333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 | 289 |
| 334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 | 290 |
| 335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 | 291 |
| 336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 | 292 |
| 337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 | 293 |
| 338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 | 294 |
| 330 | test/FTS_0000000000000147_BEING_DELETED | 286 |
| 331 | test/FTS_0000000000000147_BEING_DELETED_CACHE | 287 |
| 332 | test/FTS_0000000000000147_CONFIG | 288 |
| 328 | test/FTS_0000000000000147_DELETED | 284 |
| 329 | test/FTS_0000000000000147_DELETED_CACHE | 285 |
| 327 | test/opening_lines | 283 |
+----------+----------------------------------------------------+-------+（1）FTS_0000000000000147_00000000000001c9_INDEX_1-6：这6张辅助表用于存储倒排索引，存储的是分词、文档ID和位置；即InnoDB采用的是full inverted index。
（2）FTS_0000000000000147_DELETED/FTS_0000000000000147_DELETED_CACHE：FTS_0000000000000147_DELETED存储的是已经被删除的、但还未从全文索引数据中移除的文档，FTS_0000000000000147_DELETED_CACHE是其缓存表。
（3）FTS_0000000000000147_BEING_DELETED/FTS_0000000000000147_BEING_DELETED_CACHE：FTS_0000000000000147_BEING_DELETED存储的是已经被删除的、且正在从全文索引数据中移除的文档，FTS_0000000000000147_BEING_DELETED_CACHE是其缓存表。
（4）FTS_0000000000000147_CONFIG：存储全文索引的内部信息；最重要的是存储FTS_SYNCED_DOC_ID，表示已经解析且刷盘的文档；当发生crash recovery时，可以通过FTS_SYNCED_DOC_ID去判断哪些文档没有刷盘、需要重新解析并加入到全文索引缓存中。
数据插入
如果当插入一个文档时，就需要进行分词、更新辅助表等操作，那可能会造成极大的开销。为了避免这个问题，InnoDB引入了全文索引缓存，用于缓存最近插入的数据，直到缓存占满才会批量将数据刷盘写入辅助表；可以通过INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE查询最近插入的数据；可以通过innodb_ft_cache_size/innodb_ft_total_cache_size参数控制单个表/所有表的全文索引缓存大小；另外需要注意的是，全文索引缓存，只缓存了最近插入的数据，而没有缓存辅助表的数据，当返回结果时，需要将辅助表的数据和缓存中最近插入的数据进行合并后再返回。
数据删除
如果删除一个文档时，就需要更新辅助表，这也可能会造成极大的开销。为了避免这个问题，InnoDB只会将被删除的文档记录到FTS_0000000000000147_DELETED/FTS_0000000000000147_DELETED_CACHE表，而不会将其从辅助表中删除，如果要彻底清理被删除数据，需要通过optimize table重建全文索引。
mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> OPTIMIZE TABLE opening_lines;
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| test.opening_lines | optimize | status | OK |
+--------------------+----------+----------+----------+
1 row in set (0.01 sec)数据更新
对于数据更新，InnoDB的处理方式是先删除数据、然后插入数据，具体操作流程参考上文。
监控表
之前我们有提到，当全文索引被创建时，一系列的辅助表也被同时创建，用于存储全文索引的相关信息；但是，我们是没有办法直接去查询这些辅助表的，只能通过查询information_schema下封装过的临时表来监控全文索引状态，具体如下：
INNODB_FT_CONFIG
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_DELETED
INNODB_FT_BEING_DELETED基本语法
全文索引的语法，和普通索引的语法差别不大，简单如下：
（1）创建全文索引
alter table $table_name add fulltext index $index_name($column_name);
create fulltext index $index_name on $table_name($column_name);（2）删除全文索引
alter table $table_name drop index $index_name;（3）查询
select xxx from $table_name where match($column_name) against(xxx);总结
在一些特定场景下，全文索引还是非常有用的，能够大大加快查询速度；但是，MySQL的全文索引还是具有很大的局限性，比如不支持指定分词的分隔符(默认为空格)，ngram分析器可以指定固定长度分词，但实用性仍然较差。如果是对全文检索要求比较高的场景，建议还是使用ES等产品。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
