mysql全文索引
-
停止词(stopword)
一、什么是停止词？
不能用于搜索的词，如敏感词汇：法轮功、李宏志、器官移植等；非常常见的无任何具体含议的词汇： 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 表中。
-
innodb 如何创建支持中文的全文索引？
--在建表时指定
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;
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：申请域名、网络空间、营销软件、网站建设、城区网站维护、网站推广。
文章标题：mysql全文索引
本文路径：http://mswzjz.cn/article/ggpchc.html