MySQL -- 全文检索

mysql支持全文索引和全文检索
--全文索引的索引类型是fulltext
--全文索引只能用于innodb表和myisam表，对应的列类型只是支持char、varchar、text
--mysql5.7.6中，提供了一个内嵌的全文ngram 解释器，支持中文、日语和韩语
--全文索引可以在create table语句中定义，或者后期使用alter table、create index添加
--对于数据量大的，可以先将数据导入，然后再创建全文索引，这样效率会更高

全文检索使用match()...against语法：

match (col1,col2,...) against (expr [search_modifier])

search_modifier:
  {
       in natural language mode
     | in natural language mode with query expansion
     | in boolean mode
     | with query expansion
  }

　　

match()中的参数是要检索的列名，用逗号分开；against参数是用来检索的字符串；可选项modifier表示执行检索的类型。

 

有三种类型的全文检索：
--自然语言检索：没有什么特殊的操作，只是有时需要使用到双引号。还需要stopword列表。如果modifier内容为 in natural language mode或者没有指定，就是自然语言检索。
--布尔检索：使用特殊查询语言的规则检索字符串。modifier内容为in boolean mode
--查询扩展检索：是自然语言检索的修改版本。检索字符串被进行自然语言检索，单词出现的相关行会被返回，然后从这些行中进行第二次查询。modifier内容为in natural language mode with query expansion或者是with query expansion

 

工具myisam_ftdump可以dump出MyISAM全文索引的内容。

 

Innodb全文检索中，倒排索引需要将word存放到一张表中，叫做辅助表。innodb引擎为了提高全文检索的并行性能，有六张辅助表，每张表根据word的latin编码进行分区。

mysql>  CREATE TABLE articles (
    ->     id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->     title VARCHAR(200),
    ->     body TEXT,
    ->     FULLTEXT ( title , body )
    -> )  ENGINE=INNODB; 
Query OK, 0 rows affected (0.05 sec)

mysql> system ls -l /usr/local/mysql/data/abce
total 1184
-rw-r----- 1 mysql mysql   8618 Sep 13 11:07 articles.frm
-rw-r----- 1 mysql mysql 114688 Sep 13 11:07 articles.ibd
-rw-r----- 1 mysql mysql     65 Aug 30 15:05 db.opt
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_1.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_2.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_3.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_4.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_5.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_00000000000000da_INDEX_6.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_BEING_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_BEING_DELETED.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_CONFIG.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_DELETED_CACHE.ibd
-rw-r----- 1 mysql mysql  98304 Sep 13 11:07 FTS_00000000000000b9_DELETED.ibd
mysql> 

innodb支持用户查看指定倒排索引的辅助表中的分词信息，通过参数innodb_ft_aux_table指定数据库和表。设置完成后就可以通过查看information_schema中的innodb_ft_index_table得到分词信息。
如果不设置，是查看不到的：

information_schema库中和全文检索有关的表

mysql> show tables from information_schema like 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_information_schema (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG                          |
| INNODB_FT_BEING_DELETED                   |
| INNODB_FT_DELETED                         |
| INNODB_FT_DEFAULT_STOPWORD                |
| INNODB_FT_INDEX_TABLE                     |
| INNODB_FT_INDEX_CACHE                     |
+-------------------------------------------+
6 rows in set (0.00 sec)

mysql> show variables like 'innodb_ft_aux_table';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| innodb_ft_aux_table |       |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> select count(*) from information_schema.INNODB_FT_INDEX_CACHE;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> 

设置参数innodb_ft_aux_table

mysql> set global innodb_ft_aux_table='abce/articles';
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'innodb_ft_aux_table';
+---------------------+---------------+
| Variable_name       | Value         |
+---------------------+---------------+
| innodb_ft_aux_table | abce/articles |
+---------------------+---------------+
1 row in set (0.01 sec)

mysql> 

　　

除了辅助表，还有新的特性FTS Index Cache(全文检索索引缓存)

文档分词的插入操作是在事务提交时完成。而删除操作事务提交时，不删除磁盘辅助表中的记录。而只是删除FTS Cache Index中的记录。
对于辅助表中被删除的记录，innodb引擎会记录其FTS Documnet ID，并保存在deleted辅助表中。

对文档的dml操作实际并不删除索引中的数据，相反会在deleted表找那个插入记录，所以索引会越来越大。可以手动使用命令optimize table xxx将记录从索引中彻底删除。