MySQL -- 全文检索
2017-09-12 16:33 abce 阅读(520) 评论(0) 编辑 收藏 举报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将记录从索引中彻底删除。