MySQL技术内幕读书笔记（六）——索引与算法之全文索引

偏执的太偏执、 2021-12-14 08:34 293阅读 0赞

### 全文索引 ###

#### 概述 ####

通过索引字段的前缀进行查找，B+树索引是支持的，利用B+树索引就可以进行快速查询。

SELECT * FROM blog WHERE content like 'xxx%';

但是查询包含单词的情况，就无能为力了。所以需要进入全文检索技术`Full-Test Search`

select * from blog where content like '%xxx%';

全文检索是将存储于数据库中的整本书或者整片文章中的任意内容信息查找出来的技术。

#### 倒排索引 ####

全文检索通常使用倒排索引`inverted index`来实现。倒排索引同B+树索引一样，也是一种索引结构。它在辅助表`auxiliary table`中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

*  `invert file index`：\{单词，单词所在文档的ID\}
 *  `full inverted index`：\{单词，（单词所在文档的ID，在具体文档中的位置）\}

一个demo：

![657755-20180723200928145-302376836.png][]

`DocumentId`表示进行全文检索文档的ID，`Text`保湿存储的内容，需要对内容进行全文检索，得到某个单词的出现过的文档ID。

对于使用`invert file index`的关联数组，其存储内容如下，之后进行查找就简单了

![657755-20180723200936906-1027145476.png][]

对于使用`full inverted index`的关联数组，其存储内容如下

![657755-20180723200942560-1385275474.png][]

不仅存储了ID，还存储了出现的位置。空间占用更多，但是功能更强。

#### `InnoDB`全文检索 ####

`InnoDB`存储引擎采用`full inverted index`的方式，将(`DocumentId`，`Position`)视为一个`ilist`，因此在全文检索表中，有两个列，一个是word字段，另一个是ilist字段。

`InnoDB`存储引擎中，为了提高全文检索的并行性能，共有六张`Auxiliary Table`辅助表，目前每张表根据word的`Latin`编码进行分区。

`Auxiliary Table`辅助表是持久表，存放在磁盘上，使用`FTS Index Cache`全文检索缩进缓存，用来提高全文检索的性能。`FTS Index Cache`是一个红黑树结构，其根据(word, ilist)进行排序。

表数据更新后，先导入到`FTS Index Cache`中，但是还没有更新到`Auxiliary Table`中。`InnoDB`存储引擎会批量对`Auxiliary Table`进行更新，而不是每次插入后更新一次`Auxiliary Table`。

当对全文检索进行查询时，`Auxiliary Table`首先将会在`FTS Index Cache`中对应的word字段合并到`Auxiliary Table`中，然后再进行查询。

引擎允许用户查看指定倒排索引的`Auxilary Table`中分词的信息，可以通过设置参数`innodb_ft_aux_table`来观察倒排索引的`Auxiliary Table`。

set global innodb_ft_aux_table = 'test/fts_a'；

设置完成后，可以通过查询`information_scheme`架构下的表`Innodb_ft_index_table`得到表`fts_a`的分词信息。

对于InnoDB存储引擎而言，总是在事务提交时将分词写入到`FTS Index Cache`，然后再通过批量更新写入到磁盘。所以在数据库关闭时，会将`FTS Index Cache`同步到`Auxiliary Table`中。如果发生宕机的话，下次重启数据库时，当用户对表进行全文索引（查询或插入操作时）时，会自动读取未完成的文档，然后进行分词操作，再将分词的结果放入到`FTS Index Cache`中。

参数`innodb_ft_cache_size`用来控制`FTS Index Cache`的大小，默认值为32M。当缓存满时，会将其中的分词信息同步到磁盘的辅助表中。增大参数可以提高全文检索的性能。但是宕机的时候，未同步到磁盘的索引信息需要更长的时间恢复。

`FTS Document ID`是另外一个重要的概念。为了支持全文检索，必须有一个列与word进行映射，在InnoDB存储引擎重这个列被命名为`FTC_DOC_ID`，其类型必须是`BIGINT UNSIGNED NOT NULL`，并且`Innodb`存储引擎自动会在该列上加入一个名为`FTS_DOC_ID_INDEX`的`Unqiue Index`。上述操作都由引擎自己完成。

对于删除操作，在事务提交时，只删除`FTS Cache Index`中的记录，对于`Auxiliary Table`中被删除的记录，会记录下其`FTS Documont ID`，并将其保存在`DELETED auxiliary table`中。在设置参数`innodb_ft_aux_table`后，用户同样可以访问`information_scheme`架构下的表`INNODB_FT_DELETED`来观察删除的`FTS Document ID`。

因为文档的DML操作实际并不删除索引中的数据，反而还会在对应的DELETED表中插入数据，因此索引会变的非常大。从所以中清理已经删除的记录，命令是`OPTIMIZE TABLE`。但是这个命令会进行一些其他操作，如果进希望对倒排索引进行操作，通过设置参数`innodb_optimize_fulltext_only`进行设置。

set global innodb_optimize_fulltext_only = 1;
    optimize table fts_a;

若被删除的文档非常多，那么`OPTIMIZE TABLE`操可能需要占用非常多的时间，会影响程序的并发性，可以设置参数`innodb_ft_num_word_optimize`来限制每次实际删除的分词数量。默认值为2000。

一个demo：

创建表，添加全文检索

CREATE TABLE fts_a(
        FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
        body TEXT,
        PRIMARY KEY(FTS_DOC_ID);
    )
    
    INSERT INTO fts_a
        select null, 'Pease porrideg in the pot';
        
    INSERT INTO fts_a
        select null, 'Pease porrideg in the hot, pease porridege cold';
        
    INSERT INTO fts_a
        select null, 'Nine days old';
        
    CREATE FULLTEXT INDEX idx_fts ON fts_a(body);

通过设置参数查看分词对应信息：

set global innodb_ft_aux_table='test/fts_a';
    select * from information.innodb_ft_index_table;

删除`FTS_DOC_ID`为3的文档。

DELETE FROM test.fts_a where fts_doc_id = 3;

并不会直接删除索引中对应的数据，而是将删除的文档ID插入到DELETED表，因此可以进行查询

select * from innodb_ft_deleted;

如果要彻底删除倒排索引中改文档的分词信息。执行

set global innodb_optimize_fulltext_only = 1;
    optimize table test.fts_a;
    select * from innodb_ft_deleted;
    select * from innodb_ft_being_deleted;

运行`OPTIMIZE TABLE`可以将记录进行彻底的删除，并且彻底删除的文档ID会记录到表`INNODB_FT_BEING_DELETED`中。此外，被删除的文档ID，不允许再次进行插入。

`stopword`列表，表示在该列表中的`word`不需要进行索引分词操作。默认的表在`information_schema`下的`INNODB_FT_DEFAULT_STOPWORD`，默认共有36个`stopword`。此外用户也可以通过参数`innodb_ft_server_stopword_table`来自定义`stopword`列表。

create table user_stopword (
        value varchar(30)
    )ENGINE = INNODB;
    
    SET GLOBAL 
    innodb_ft_server_stopword_table = "test/user_stopword";

INNODB全文检索的限制：

*  每张表只嗯呢该有一个全文检索的索引
 *  由多列组合而成的全文索引列不必须使用相同的字符集与排序规则
 *  不支持没有单词界定符的语言：如中文、韩文、日文等。

#### 全文检索 ####

语法为：

MATCH(col1, col2, ...) AGAINST (expr (serch_modifier))
    search_modifier:
    {
        IN NATURAL LANGUAGE MODE
        | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
        | IN BOOLEAN MODE
        | WITH QUERY EXPANSIONS
    }

1.  `Natural Language`
    
    通过MACTH函数进行查询，默认采用的模式，表示查询带有指定word的文档。
    
        select * from fts_a where match(body) against('Porridge' IN NATURAL LANGUAGE MODE)
        
        select * from fts_a where match(body) against('Porridge')；
    
    在where条件中使用MATCH函数，其返回结果是根据相关性进行降序排序的，即相关性最高的结果放在第一位。相关性的值是一个非负的浮点数字。0表示咩有任何相关行。
    
    相关性的计算依据的条件：
    
     *  word是否在文档中出现
     *  word在文档中出现的次数
     *  word在索引列中的数量
     *  多少个文档包含该word
    
    通过SQL语句查看相关性：
    
        select fts_doc_id, body, 
        Match(body) against('Porridge' IN NATURAL LANGUAGE MODE) as Relevance
        from fts_a;
    
    INNODB存储引擎的全文检索，还需要考虑一下的因素：
    
     *  查询的word在stopword列中，忽略该字符串的查询
     *  查询的word的字符长度是否在区间`[innodb_ft_min_token_size， innodb_ft_max_token_size]`内，不在内部，忽略该字符串的查询。
2.  `Boolean`
    
    使用`IN BOOLEAN MODE`修饰符时，查询字符串的前后字符会有特殊的含义。例如
    
        #pease这个字符串一定存在但hot这个字符串不存在
        select * from fts_a
        where match(body) against ('+Pease -hot' in boolean mode)\G;
    
    `Boolean`全文索引支持以下几种操作符：
    
     *  \+表示该word必须存在
     *  \-表示该word必须被排除
     *  (no operator)表示该word是可选的，但是如果出现，相关性会更高
     *  @distance表示查询的多个单词之间的距离是否在distance之内，distance的单位是字节。这种全文检索的查询也成为`Proximity Search`，例如
        
            match(body) against ('"Pease pot"@30' IN BOOLEAN MODE)
        
        表示字符串Pease和hot之间的距离需要在30字节内。
     *  '>'表示出现该单词增加相关性
     *  '<'表示出现该单词降低相关性
     *  '~'表示允许出现该单词，但是出现时相关性为负（全文检索查询允许负相关性）。
     *  '\*'表示以该单词开的单词
     *  "表示短语。
3.  `Query Expansion`
    
    支持全文索引的扩展查询。通过在查询短语中添加`WITH QUERY EXPANSION`或`IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION`可以开启`bind query expansion`，查询分为两个阶段：
    
     *  第一阶段：根据搜索的单词进行全文索引查询。
     *  第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询

转载于:https://www.cnblogs.com/BlueMountain-HaggenDazs/p/9356598.html

[657755-20180723200928145-302376836.png]: /images/20211214/ceeedc9a861a4d63af479e7b91505f5d.png
[657755-20180723200936906-1027145476.png]: /images/20211214/9aa15735c0804c9aa90ebd3fdb1dcdc7.png
[657755-20180723200942560-1385275474.png]: /images/20211214/d74606f7c8424626b458658bd4976a51.png