mysql 全文索引

发布时间:2020-08-18 21:48:56 作者:zyclove2008
来源:ITPUB博客 阅读:200

一、什么是停止词?
不能用于搜索的词,如敏感词汇:法轮功、李宏志、器官移植等;非常常见的无任何具体含议的词汇: and、or、what 、好的、我们、你们、这样 等等。

二、停止词相关参数
mysql> show variables like '%innodb%stop%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| innodb_ft_enable_stopword       | ON    |
| innodb_ft_server_stopword_table |       |
| innodb_ft_user_stopword_table   |       |
+---------------------------------+-------+


innodb_ft_server_stopword_table 和 innodb_ft_user_stopword_table:指定停止词的 innodb 表,这两个参唯一的区别就是 innodb_ft_user_stopword_table 的优先级更高。如果没有设置这2 个参数中的任何一个,则使用默认停止词表 information_schema.INNODB_FT_DEFAULT_STOPWORD

三、创建停止词
1、创建保存停止词的表
create table stopword(value varchar(18));
注:字符串长度设置不能少于 ngram_token_size * 字符长度,字符长度根字符集有关,如:utf8 是一个中文字符占 3  个字节,如果 ngram_token_size 的值是 2 ,则 2 * 3 = 6,最少不能少于6个字节(个人理解)

2、插入停止词
insert into stopword values('法轮功')('李宏志')('我们的')('你们的');

四、innodb 全文索引的停止词如何设置?
set global innodb_ft_user_stopword_table='test/stopword';
注: 停止词更新后,需要重建全文索引才能生效,重建索引时,stopword 表中的停止词不再创建索引
注意格式:test 是 schema, stopword 是保证停止词的表,中间用 "/" 连接


innodb 的中文全分词使用的是 ngram 支持,其算法是二元分词法,可以通过 ngram_token_size 参数设置分词的长度,默认是 2 ,该值越大,索引越大。

mysql> show variables like '%ngram%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 2     |
+------------------+-------+
注:ngram_token_size的值为2 说明以2个字符为一个词,可以设置 innodb_ft_aux_table 参数查看分词结果

一、查看分词结果
1、设置参数 innodb_ft_aux_table
set global innodb_ft_aux_table='test/test6';
注:test为 schema 名, test6 为具有全文索引的表名

2、查看分词结果
--原数据
mysql> select * from test6 where id >= 7;
+----+-----------------------------+
| id | name                        |
+----+-----------------------------+
|  7 | 器官健康很重要              |
|  8 | 要做就做大买卖              |
|  9 | 官买官卖是不靠普的          |
+----+-----------------------------+

--分词后的数据(全文索引)
mysql> select * from information_schema.innodb_ft_index_cache;
+--------+--------------+-------------+-----------+--------+----------+
| WORD   | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 买卖   |           11 |          11 |         1 |     11 |       15 |
| 做大   |           11 |          11 |         1 |     11 |        9 |
| 做就   |           11 |          11 |         1 |     11 |        3 |
| 健康   |           10 |          10 |         1 |     10 |        6 |
| 器官   |           10 |          10 |         1 |     10 |        0 |
| 大买   |           11 |          11 |         1 |     11 |       12 |
| 官健   |           10 |          10 |         1 |     10 |        3 |
| 就做   |           11 |          11 |         1 |     11 |        6 |
| 康很   |           10 |          10 |         1 |     10 |        9 |
| 很重   |           10 |          10 |         1 |     10 |       12 |
| 要做   |           11 |          11 |         1 |     11 |        0 |
| 重要   |           10 |          10 |         1 |     10 |       15 |
+--------+--------------+-------------+-----------+--------+----------+

innodb_ft_index_cache 和 innodb_ft_index_table 表的关系:
创建好全文索引后,所有的数据都保存在 innodb_ft_index_table 表中,当有新的数据 insert 后,这些新数据的全文索引保存在内存表中,即 innodb_ft_index_cache,当做 optimize table 操作时,批量把表写入磁盘中,即 innodb_ft_index_table 表中。


--在建表时指定
CREATE TABLE `test6` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(90) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `idx_name` (`name`) /*!50100 WITH PARSER `ngram` */
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8


-- 用 alter table 创建全文索引
alter table test6 add fulltext key idx_name(name) with parser ngram
注:对于中文全文索引必须指定  with parser ngram 关键字,否则innodb 默认的分词算法对 中文的支持很差。


一、自然语言模式
-- 原数据
mysql> select * from test6 ;
+----+------------------------------------------+
| id | name                                     |
+----+------------------------------------------+
|  1 | 工地城工 有城                            |
|  2 | adfadsfadsfadsf                          |
|  3 | 产戟 要棵地地要要地大叶黄杨              |
|  4 | 宸ュ伐鍦板湴鏈夋湁鐨勭殑                 |
|  5 | 人人地工地                               |
|  6 | 霜国工直有直功发顺丰                     |
|  7 | 器官健康很重要                           |
|  8 | 要做就做大买卖                           |
|  9 | 官买官卖是不靠普的                       |
+----+------------------------------------------+

-- 使用全文索引检查
mysql> select * from test6 where match(name) against('买卖');
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  8 | 要做就做大买卖        |
+----+-----------------------+

mysql> explain select * from test6 where match(name) against('买卖' in  NATURAL LANGUAGE MODE);
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type     | possible_keys | key      | key_len | ref   | rows | filtered | Extra                         |
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
|  1 | SIMPLE      | test6 | NULL       | fulltext | idx_name      | idx_name | 0       | const |    1 |   100.00 | Using where; Ft_hints : sorted |
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
注:innodb 全文索引默认就是使用 自然语言模式,所以可不用加   in  NATURAL LANGUAGE MODE


二、布尔模式
mysql> select * from test6 where match(name) against('买卖' in boolean mode );
+----+-----------------------+
| id | name                  |
+----+-----------------------+
|  8 | 要做就做大买卖        |
| 10 | 做买卖了              |
+----+-----------------------+

如果不想要包含了 “要做”的行,则可以:
mysql> select * from test6 where match(name) against('买卖 -要做' in boolean mode);
+----+--------------+
| id | name         |
+----+--------------+
| 10 | 做买卖了     |
+----+--------------+
注:只有在布尔模式下才可用以上操作符。

布尔模式下支持以下操作符:
“+”表示必须包含
“-”表示必须排除
“>”表示出现该单词时增加相关性
“<”表示出现该单词时降低相关性
“*”表示通配符
“~”允许出现该单词,但是出现时相关性为负
“""”表示短语
no operation表示find word是可选的,如果出现,相关性会更高

可以通过以下命令查看 布尔模式支持的操作符:
mysql> show variables like '%ft_boolean_syntax%';
+-------------------+----------------+
| Variable_name     | Value          |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
 

一、DML操作对全文索引的影响
    1、插入操作
插入操作较为简单,当往表中插入记录时,提交事务时会对全文索引上的列进行分词存储到FTS Index Cache(INNODB_FT_INDEX_CACHE),最后在批量更新到Auxiliary Table(INNODB_FT_INDEX_TABLE)中
    2、删除操作
当提交删除数据的事务以后,不会删除Auxiliary Table中的数据,而只会删除FTS Index Cache中的数据。对于Auxiliary Table中被删除的记录,InnoDB存储引擎会记录其FTS Document Id,并将其保存在DELETED Auxiliary Table中。可以通过OPTIMIZE TABLE手动删除索引中的记录。
    3、更新操作
    4、查找操作
分为两步。第一步:根据检索词搜集符合条件的FTS_DOC_ID,在搜集满足条件的FTS_DOC_ID首先读取delete表中记录的FTS_DOC_ID,这些FTS_DOC_ID随后被用做过滤
第二步:根据FTS_DOC_ID找到对应的记录,找到的记录是根据相关性大小降序返回的

二、innodb 全文索引相关表:
mysql> select table_schema, table_name from information_schema.tables where table_name like 'innodb_ft%';
+--------------------+----------------------------+
| table_schema       | table_name                 |
+--------------------+----------------------------+
| information_schema | INNODB_FT_CONFIG           |
| information_schema | INNODB_FT_BEING_DELETED    |
| information_schema | INNODB_FT_DELETED          |
| information_schema | INNODB_FT_DEFAULT_STOPWORD |
| information_schema | INNODB_FT_INDEX_TABLE      |
| information_schema | INNODB_FT_INDEX_CACHE      |
+--------------------+----------------------------+

INNODB_FT_DELETED :保存的是innodb 表中删除的全文索引的doc_id,避免DML操作时重组全文索引。当  OPTIMIZE TABLE 操作时才更新重组全文索引。 所以要时常的对具有全文索引的表进行 OPTIMIZE TABLE 操作 ,要不然INNODB_FT_DELETED会很大,导至性能问题
INNODB_FT_BEING_DELETED :只有在作 OPTIMIZE TABLE 操作时才使用,这是一个中间表,使用的时间很短。
INNODB_FT_INDEX_CACHE :当新插入数据时,保存新的全文索引,避免 insert 操作导致索引重组,当遇到 OPTIMIZE TABLE、关闭服务、超过 innodb_ft_cache_size或 innodb_ft_total_cache_size  的限制时才合并到主索引表中(INNODB_FT_INDEX_TABLE)。
INNODB_FT_CONFIG :innodb 全文索上的刷新、停止词等信息。
注:要想正常使用INNODB_FT_DELETED、INNODB_FT_INDEX_CACHE等表,必需设置参数 innodb_ft_aux_table,否则看不到任何信息,该参数应该是用于调试使用

创建全文索引时,可以设置并行度,通过参数 innodb_ft_sort_pll_degree 控制。

三、innodb 全文索引相关参数:
innodb_ft_aux_table: 设置调式表
ngram_token_size :分词长度
innodb_ft_server_stopword_table / innodb_ft_user_stopword_table :设置停止词表,innodb_ft_user_stopword_table这个表的优先级更高。
innodb_ft_min_token_size / innodb_ft_max_token_size :如果使用 ngram 全文索引中日韩语言插件,这 2 个参数不再有用。
innodb_ft_sort_pll_degree :创建全文索引时的并行度。
innodb_ft_cache_size / innodb_ft_total_cache_size :前一个是定议每个表的全文索引内存大小,后一个设置所有表的全文索引内存大小,如果全文索引大小超过 innodb_ft_total_cache_size 的设置,则强制同步(我想是fulltext index 的cache 大小与全文索引大小强制保持一致,意味着有内存中不能全部加载全文索引)被取消。
innodb_optimize_fulltext_only :optimize table 操作时,只优化全文索引。set innodb_optimize_fulltext_only = 1;

推荐阅读:
  1. MySQL索引类型分类有哪些
  2. mysql中索引有什么类型

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 全文 索引

上一篇:技术面试题分享:含答案的10道Jvm面试专题

下一篇:Jquery+div+css实现弹出登录窗口

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》