此处将为大家介绍关于MySQL全文索引应用简明教程的详细内容，并且为您解答有关mysql全文索引应用简明教程视频的相关问题，此外，我们还将为您介绍关于mssql全文索引、MySQL之全文索引、mysq
此处将为大家介绍关于MySQL全文索引应用简明教程的详细内容，并且为您解答有关mysql全文索引应用简明教程视频的相关问题，此外，我们还将为您介绍关于mssql全文索引、MySQL 之全文索引、mysql 全文索引、MySQL 全文索引实现简单版搜索引擎的有用信息。
本文目录一览：
MySQL全文索引应用简明教程（mysql全文索引应用简明教程视频）
本文从以下几个方面介绍下MysqL全文索引的基础知识：
- MysqL全文索引的几个注意事项
- 全文索引的语法
- 几种搜索类型的简介
- 几种搜索类型的实例
全文索引的几个注意事项
- 搜索必须在类型为fulltext的索引列上，match中指定的列必须在fulltext中指定过
- 仅能应用在表引擎为MyIsam类型的表中（MysqL 5.6以后也可以用在Innodb表引擎中了）
- 仅能再char、varchar、text类型的列上面创建全文索引
- 像普通索引一样，可以在定义表时指定，也可以在创建表后添加或者修改
- 对于一个大数量级记录插入，向没有索引的表中插入数据后创建索引比向有索引的数据表中插入的过程要快很多
- 搜索字符串必须是一个常量字符串，不能是表的列名
- 在搜索记录的选择性超过50%的时候，认为没有匹配（只在自然搜索中限制）
全文索引搜索语法
MATCH (列名1,列名2,…) AGAINST (搜索字符串 [搜索修饰符])
其中在match里面指定的列名1、2等，就是在建立全文索引中指定的列名， 后面的搜索修饰符说明如下：
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}
几种搜索类型的简介
上面的搜索修饰符，实际上说明了3种全文搜索类型
IN NATURAL LANGUAGE MODE
简介：默认的搜索形式（不加任何搜索修饰符或者修饰符为 IN NATURAL LANGUAGE MODE 的情况）
特点：
- 对于搜索字符串中的字符都解析为正常的字符，没有特殊意义
- 对屏蔽字符列表中的字符串进行过滤
- 当记录的选择性超过50%的时候，通常被认为是不匹配。
- 返回记录按照记录的相关性进行排序显示
IN BOOLEAN MODE
简介：布尔模式搜索（搜索修饰符为IN BOOLEAN MODE的情况）
特点：
- 会按照一定的规则解析搜索字符串中的特殊字符的含义，进行一些逻辑意义的规则。如：某个单词必须出现，或者不能出现等。
- 这种类型的搜索返回的记录是不按照相关性进行排序的
WITH QUERY EXPANSION
简介：一种稍微复杂的搜索形式，实际上是进行了2次自然搜索，可以返回记录直接简介性关系的记录，修饰词IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 或者WITH QUERY EXPANSION modifier
特点：这种类型的搜素，实际上提供了一种间接的搜索功能，比如：我搜索某个词，而且返回的第一行中却不包含搜索词中的任意字符串。可以根据第一次搜索结果的记录词进行第二次匹配，从而可能找到一些间接关系的匹配记录。
几种搜索类型的实例介绍
IN NATURAL LANGUAGE MODE模式下的应用：
还是应用在product表，其中在name字段我们建立了全文索引，因为我需要根据关键词在name列匹配出相关记录
sql语句如下：
SELECT * FROM product WHERE match(name) against(‘auto')
时间还不错，在将近87w的记录中命中了1w多条，耗时1.15秒，效果还是不错
注意：在默认的情况下已经是根据相关性从高到低返回记录了
我们可以SELECT match(name) against(‘auto') FROM product 查看记录的相关性值，值都在0和1之间， 0代表记录不匹配
重要的几个特性：
1. 哪些词会被忽略
搜索词太短 默认全文索引认为4个以上字符的单词是有效词，我们可以在配置中修改ft_min_word_len进行配置
屏蔽词表中的词 默认的全文索引将一些常用词屏蔽掉，因为这些词太常见了，没有任何语义作用，所以搜索过程中是忽略不计的。当然这个列表也是可以配置的。
2. 如何进行分词的
全文索引认为一个连续的有效字符（正则中\w匹配的字符集）是一个单词，也可以包含一个“'”， 但是连续的两个'会被认为是一个分隔符。其他的分隔符如：空格、逗号、句点等
IN BOOLEAN MODE 模式下的应用：
在布尔匹配模式中，我们可以添加一些特殊的符号，增加一些搜索过程的逻辑功能。如官方网站中提供的实例（搜索含有MysqL字符串 且 不含Yousql的语句）：
SELECT * FROM articles WHERE MATCH (title,body) -> AGAINST (‘+MysqL -Yoursql' IN BOOLEAN MODE);
可见，我们对搜索的控制又多了一些，看起来“高大上”了些。
实际上，上面的操作隐含了几个意思：
加号：相当于and
减号：相当于not
没有：相当于or
下面看下布尔类型搜索的几个重要特性：
1. 没有50%记录选择性的限制，即使搜索结果记录超过总数的50%也同样返回结果
2. 不会自动的按记录的相关性进行降序排序
3. 可以直接应用在没有创建fulltext的全文索引上，但是这样会查询的非常慢，所以说还是别用了。
4. 支持最小、最大单词长度
5. 应用屏蔽词列表
布尔搜索支持的操作符：
n 加号 +：指示修饰的单词必须出现在记录中
n 减号 -：指示修饰的单词必须不能出现在记录中
n 没有任何操作符：单词可有可无，但是包含该词的记录相关性高
n 双引号 “ ： 将一个词组作为一个匹配。如：”one word” 匹配one word在一起的单词
下面是官方的一些实例：
至少包含一个词的记录 ‘apple banana' 必须包含着两个词 ‘+apple +juice' 必须包含apple，包含macintosh的记录相关性高，也可以不包含 ‘+apple macintosh' 必须包含apple且不能喊有macintosh ‘+apple -macintosh' 查找apple开头单词的记录 ‘apple*' 完整匹配some words单词 ‘”some words”‘
了解了基本的MysqL全文索引知识，觉得它的全文索引比like当然是强了很多。但是面对高级的搜索还是略显简陋，且性能问题也是担忧。
本人只是作为入门了解，也是对官网一些基本知识的翻译。
mssql全文索引
如果由于某些原因全文索引文件目录丢失或损坏，可以：1、重建原来目录所在的文件目录结构
2、执行EXEC sp_fulltext_database ''enable'' 重新生成索引。
MySQL 之全文索引
最近在复习数据库索引部分，看到了 fulltext，也即全文索引，虽然全文索引在平时的业务中用到的不多，但是感觉它有点儿意思，所以花了点时间研究一下，特此记录。
引入
概念
通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。
你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的。全文索引在大量的数据面前，能比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在精度问题。
你可能没有注意过全文索引，不过至少应该对一种全文索引技术比较熟悉：各种的搜索引擎。虽然搜索引擎的索引对象是超大量的数据，并且通常其背后都不是关系型数据库，不过全文索引的基本原理是一样的。
版本支持
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
- MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
- MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
- 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
操作全文索引
索引的操作随便一搜都是，这里还是再啰嗦一遍。
创建
创建表时创建全文索引
create table fulltext_test (
id int(11) NOT NULL AUTO_INCREMENT,
content text NOT NULL,
tag varchar(255),
PRIMARY KEY (id),
FULLTEXT KEY content_tag_fulltext(content,tag) // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;在已存在的表上创建全文索引
create fulltext index content_tag_fulltext
on fulltext_test(content,tag);通过 SQL 语句 ALTER TABLE 创建全文索引
alter table fulltext_test
add fulltext index content_tag_fulltext(content,tag);修改
修改个 O，直接删掉重建。
删除
直接使用 DROP INDEX 删除全文索引
drop index content_tag_fulltext
on fulltext_test;通过 SQL 语句 ALTER TABLE 删除全文索引
alter table fulltext_test
drop index content_tag_fulltext;使用全文索引
和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如
select * from fulltext_test
where match(content,tag) against(''xxx xxx'');注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。
测试全文索引
添加测试数据
有了上面的知识，就可以测试一下全文索引了。
首先创建测试表，插入测试数据
create table test (
id int(11) unsigned not null auto_increment,
content text not null,
primary key(id),
fulltext key content_index(content)
) engine=MyISAM default charset=utf8;
insert into test (content) values (''a''),(''b''),(''c'');
insert into test (content) values (''aa''),(''bb''),(''cc'');
insert into test (content) values (''aaa''),(''bbb''),(''ccc'');
insert into test (content) values (''aaaa''),(''bbbb''),(''cccc'');按照全文索引的使用语法执行下面查询
select * from test where match(content) against(''a'');
select * from test where match(content) against(''aa'');
select * from test where match(content) against(''aaa'');根据我们的惯性思维，应该会显示 4 条记录才对，然而结果是 1 条记录也没有，只有在执行下面的查询时
select * from test where match(content) against(''aaaa'');才会搜到 aaaa 这 1 条记录。
为什么？这个问题有很多原因，其中最常见的就是 最小搜索长度 导致的。另外插一句，使用全文索引时，测试表里至少要有 4 条以上的记录，否则，会出现意想不到的结果。
MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。
这两个的默认值可以使用以下命令查看
show variables like ''%ft%'';可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值
// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引，而刚刚搜索的只有 aaaa 的长度大于等于 4。
配置最小搜索长度
全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容
[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1然后重启 MySQL 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效。
两种修复方式，可以使用下面的命令修复
repair table test quick;或者直接删掉重新建立索引，再次执行上面的查询，a、aa、aaa 就都可以查出来了。
但是，这里还有一个问题，搜索关键字 a 时，为什么 aa、aaa、aaaa 没有出现结果中，讲这个问题之前，先说说两种全文索引。
两种全文索引
自然语言的全文索引
默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。
这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。
布尔全文索引
在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。
MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册。
- + 必须包含该词
- - 必须不包含该词
提高该词的相关性，查询的结果靠前
- < 降低该词的相关性，查询的结果靠后
- (*)星号 通配符，只能接在词后面
对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。
select * test where match(content) against(''a*'' in boolean mode);总结
好了，差不多写完了，又到了总结的时候。
MySQL 的全文索引最开始仅支持英语，因为英语的词与词之间有空格，使用空格作为分词的分隔符是很方便的。亚洲文字，比如汉语、日语、汉语等，是没有空格的，这就造成了一定的限制。不过 MySQL 5.7.6 开始，引入了一个 ngram 全文分析器来解决这个问题，并且对 MyISAM 和 InnoDB 引擎都有效。
事实上，MyISAM 存储引擎对全文索引的支持有很多的限制，例如表级别锁对性能的影响、数据文件的崩溃、崩溃后的恢复等，这使得 MyISAM 的全文索引对于很多的应用场景并不适合。所以，多数情况下的建议是使用别的解决方案，例如 Sphinx、Lucene 等等第三方的插件，亦或是使用 InnoDB 存储引擎的全文索引。
-
几个注意点
使用全文索引前，搞清楚版本支持情况；全文索引比 like + % 快 N 倍，但是可能存在精度问题；如果需要全文索引的是大量数据，建议先添加数据，再创建索引；对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。
mysql 全文索引
mysql全文索引适合比较大的数据查询，而且会用到索引查询，其效率会比like高很多，但是他会占用额外的空间，使数据空间变大。
在做实验的时候遇到了2个问题，1查询不到数据，2对中文的分词支持的不好
1的问题是由于SELECT
`mid`
FROM
members_company_access
WHERE MATCH (`mid`) AGAINST (''1,2,3,4,44'') ;
如果1,2,3,4,44在数据中出现50%以上它被列入停止字。不会被查询 可以通过 WITH QUERY EXPANSION解决
SELECT
`mid`
FROM
members_company_access
WHERE MATCH (`mid`) AGAINST (''你好'') ; 如果后面还有字符比如 你好明天 则此查询查询不到因为对中文分词支持问题
MySQL 全文索引实现简单版搜索引擎
[toc]
前言
- 只有Innodb和myisam存储引擎能用全文索引（innodb支持全文索引是从mysql5.6开始的）
- char、varchar、text类型字段能创建全文索引（fulltext index type）
- 全文索引的基于关键词的，如何区分不同的关键词了，就要用到分词（stopword）
- 英文单词用空格，逗号进行分词；中文分词不方便（一个句子不知道怎样区分不同的关键词）
- 内置分词解析器ngram支持中文，日文，韩文（将句子分成固定数字的短语）
- 当对表写入大量数据时，写入数据后再创建全文索引的速度更快（减少了维护索引的开销）
- 全文索引的原理的倒排索引（一种数据结构），一般利用关联数组，在辅助表中存储单词与文档中所在位置的映射
使用
用MATCH() ... AGAINST 方式来进行搜索 match()表示搜索的是那个列，against表示要搜索的是那个字符串
查看默认的分词（以这些词来区分不同的关键词）；也可以自定义分词，以这些词来区分不同的关键词 SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD; 如
+-------+
| value |
+-------+
| a |
| about |
| an |
| are |
| as |
| at |
| be |
| by |
| com |
| de |
| en |
| for |
| from |
三种类型的全文搜索方式
natural language search（自然语言搜索） 通过MATCH AGAINST 传递某个特定的字符串来进行检，默认方式
boolean search（布尔搜索） 为检索的字符串增加操作符，如“+”表示必须包含，"-"不包含，"*" 表示通配符，即使传递的字符串较小或出现在停词中，也不会被过滤掉
query expansion search（查询扩展搜索） 搜索字符串用于执行自然语言搜索，然后，搜索返回的最相关行的单词被添加到搜索字符串，并且再次进行搜索，查询将返回来自第二个搜索的行
相关参数
配置相关参数 innodb_ft_min_token_size 默认3，表示最小3个字符作为一个关键词，增大该值可减少全文索引的大小
innodb_ft_max_token_size 默认84，表示最大84个字符作为一个关键词，限制该值可减少全文索引的大小
ngram_token_size 默认2，表示2个字符作为内置分词解析器的一个关键词，如对“abcd”建立全文索引，关键词为''ab''，''bc''，''cd'' 当使用ngram分词解析器时，innodb_ft_min_token_size和innodb_ft_max_token_size 无效
注意 这三个参数均不可动态修改，修改了这些参数，需重启MySQL服务，并重新建立全文索引
测试innodb引擎使用全文索引
准备
1、目标
- 查询文章中是否含有某个关键词；一系列文章出现某个关键词的次数
- 查询文章的标题是否含有某个关键词
2、设置以下参数减少磁盘IO压力
SET GLOBAL sync_binlog=100;
SET GLOBAL innodb_flush_log_at_trx_commit=2;
3、导入1kw 数据进行测试全文索引 该数据来源网上搜索 提取码：60l7
4、某个文章表 的结构
CREATE TABLE `article` (
`id` bigint(10) NOT NULL,
`url` varchar(1024) CHARACTER SET latin1 NOT NULL DEFAULT '''',
`title` varchar(256) NOT NULL DEFAULT '''',
`source` varchar(32) DEFAULT '''' COMMENT ''真实来源'',
`keywords` varchar(32) DEFAULT NULL,
`publish_time` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `title_idx` (`title`)
) ENGINE=InnoDB
使用myloader 多线程导入测试数据
先把测试数据进行解压
tar -zxf mydumper_dump_article.tar.gz
time myloader -u $user -p $passwd -S $socket -t 32 -d /datas/dump_article -v 3
5、导入数据后总数据量和数据文件、索引文件大小
SELECT COUNT(*) FROM `article`;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (7.85 sec)
SELECT table_name, CONCAT(FORMAT(SUM(data_length) / 1024 / 1024,2),''M'') AS dbdata_size, CONCAT(FORMAT(SUM(index_length) / 1024 / 1024,2),''M'') AS dbindex_size, CONCAT(FORMAT(SUM(data_length + index_length) / 1024 / 1024 / 1024,2),''G'') AS `db_size(G)`, AVG_ROW_LENGTH,table_rows,update_time FROM information_schema.tables WHERE table_schema = DATABASE() and table_name=''article'';
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| table_name | dbdata_size | dbindex_size | db_size(G) | AVG_ROW_LENGTH | table_rows | update_time |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
| article | 3,710.00M | 1,003.00M | 4.60G | 414 | 9388739 | 2019-07-05 15:31:37 |
+------------+-------------+--------------+------------+----------------+------------+---------------------+
使用默认方式创建全文索引
1、该表已有关键词字段（对文章内容的简述），并以“,”作为分词符
select keywords from article limit 10;
+-------------------------------------------------+
| keywords |
+-------------------------------------------------+
| NULL |
| NULL |
| ,婚姻,爱情 |
| 发型,偏分,化妆,时尚 |
| 小A, |
| ,服装搭配,女性,时尚 |
| 漂亮,女性 |
| 情人节,东莞,女性 |
| 皮肤,护肤,护肤,食品营养,美容,养生 |
| 三里屯,北京,时尚 |
+-------------------------------------------------+
2、不建全文索引时搜索某个关键词 需要进行全表扫描
select count(*) from article where keywords like ''%时尚%'';
+----------+
| count(*) |
+----------+
| 163 |
+----------+
1 row in set (7.56 sec)
3、对关键词字段创建全文索引（以 , 作为分词）
- my.cnf配置文件中设置innodb_ft_min_token_size，并重启MySQL服务（最小两个字符作为一个关键词，默认三个字符作为一个关键词）
[mysqld]
innodb_ft_min_token_size=2
- 3.1 设置自定义stopwords（即分词）
USE mysql;
CREATE TABLE my_stopwords(VALUE VARCHAR(30)) ENGINE = INNODB;
INSERT INTO my_stopwords(VALUE) VALUE ('','');
SET GLOBAL innodb_ft_server_stopword_table = ''mysql/my_stopwords'';
SHOW GLOBAL VARIABLES WHERE Variable_name IN(''innodb_ft_min_token_size'',''innodb_ft_server_stopword_table'');
+---------------------------------+--------------------+
| Variable_name | Value |
+---------------------------------+--------------------+
| innodb_ft_min_token_size | 2 |
| innodb_ft_server_stopword_table | mysql/my_stopwords |
+---------------------------------+--------------------+
- 3.2 创建全文索引
alter table article add fulltext index idx_full_keyword(keywords);
* [ ] Query OK, 0 rows affected, 1 warning (1 min 27.92 sec)
* [ ] Records: 0 Duplicates: 0 Warnings: 1
- 3.3 剩余磁盘空间需足够，原表4.6G，剩余5.7G磁盘，添加全文索引也会失败
df -h
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 7.8G 6.3G 1.2G 85% /
tmpfs 1.9G 0 1.9G 0% /dev/shm
/dev/mapper/vg_opt-lvol0
19G 12G 5.7G 68% /datas
会创建原表大小的临时文件
8.6K Jul 5 16:19 #sql-5250_3533.frm
4.4G Jul 5 16:20 #sql-ib117-1768830977.ibd
alter table article add fulltext index idx_full_keyword(keywords);
ERROR 1114 (HY000): The table ''article'' is full
- 3.4 利用创建的全文索引进行查询某个关键词出现的次数 查询响应时间有了很大的提升，只需0.05s；使用where keywords like ''%时尚%'' 需要7.56s
select count(*) from article where match(keywords) against(''%时尚%'');
+----------+
| count(*) |
+----------+
| 163 |
+----------+
1 row in set (0.05 sec)
- 3.5 如需同时完全匹配多个关键词，用布尔全文搜索
表示完全匹配 "三里屯,北京" 的记录数
select count(*) from article where match(keywords) against(''+三里屯,北京'' in boolean mode);
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.06 sec)
表示匹配“三里屯” 或者 “北京”的记录数
select count(*) from article where match(keywords) against(''三里屯,北京'');
+----------+
| count(*) |
+----------+
| 8 |
+----------+
1 row in set (0.06 sec)
- 3.6 创建全文索引后，会创建一些其它文件 96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_1.ibd 96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_2.ibd 96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_3.ibd 96K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_4.ibd 128K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_5.ibd 256K Jul 5 16:30 FTS_00000000000000a7_00000000000000c0_INDEX_6.ibd 96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED_CACHE.ibd 96K Jul 5 16:29 FTS_00000000000000a7_BEING_DELETED.ibd 96K Jul 5 16:30 FTS_00000000000000a7_CONFIG.ibd 96K Jul 5 16:29 FTS_00000000000000a7_DELETED_CACHE.ibd 96K Jul 5 16:29 FTS_00000000000000a7_DELETED.ibd - 前6个表示倒排索引（辅助索引表） - 第7，8个表示包含已删除文档的文档ID（DOC_ID），其数据当前正在从全文索引中删除 - 第9个表示FULLTEXT索引内部状态的信息 - 第10，11个表示包含已删除但尚未从全文索引中删除其数据的文档
使用ngram分词解析器创建全文索引
1、对title字段建立全文索引（该字段没有固定的stopwords 分词，使用ngram分词解析器） 需先在my.cnf 配置文件中设置ngram_token_size（默认为2，2个字符作为ngram 的关键词），并重启mysql服务 这里使用默认的 2
select title from article limit 10;
+------------------------------------------------------------------------------+
| title |
+------------------------------------------------------------------------------+
| worth IT |
|Launchpad 江南皮革厂小show |
|Raw 幕后罕见一刻 “疯子”被抬回后台 |
|Raw:公子大骂老爸你就是个绿茶 公子以一打四 |
|四组30平米精装小户型，海量图片，附户型图 |
|夜店女王性感烟熏猫眼妆 |
|大秀哥重摔“巨石”强森 |
|少女时代 崔秀英 服饰科普 林允儿 黄美英 金泰妍 郑秀晶 |
|德阳户外踏青，花田自助烧烤 |
+------------------------------------------------------------------------------+
2、对title字段创建全文索引
alter table article add fulltext index ft_index_title(title) with parser ngram;
Query OK, 0 rows affected (3 min 29.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
3、会创建倒排索引（title字段越长长，创建的倒排索引越大） 112M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_1.ibd 28M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_2.ibd 20M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_3.ibd 140M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_4.ibd 128M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_5.ibd 668M Jul 5 21:46 FTS_00000000000000a7_00000000000000cd_INDEX_6.ibd
4、不建立全文索引搜索title的某个关键词
select count(*) from article where title like ''%户外%'';
+----------+
| count(*) |
+----------+
| 22058 |
+----------+
1 row in set (8.60 sec)
select count(*) from article where title like ''%后台%'';
+----------+
| count(*) |
+----------+
| 1142 |
+----------+
5、使用全文索引搜索某个关键词 响应时间有很大的提升
select count(*) from article where match(title) against(''户外'');
+----------+
| count(*) |
+----------+
| 22058 |
+----------+
1 row in set (0.07 sec)
select count(*) from article where title like ''%后台%'';
+----------+
| count(*) |
+----------+
| 1142 |
+----------+
1 row in set (8.31 sec)
6、注意当搜索的关键词字符数大于2 （ngram_token_size定义大小）会出现不一致问题
普通搜索，实际中出现该关键词的记录数为6
select count(*) from article where title like ''%公子大%'';
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (8.40 sec)
全文搜索，出现关键字的记录数为9443
select count(*) from article where match(title) against(''公子大'');
+----------+
| count(*) |
+----------+
| 9443 |
+----------+
1 row in set (0.06 sec)
实际出现该关键字的记录数为1
select count(*) from article where title like ''%花田自助%'';
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (8.33 sec)
全文搜索出现该关键词的记录数为3202
select count(*) from article where match(title) against(''花田自助'');
+----------+
| count(*) |
+----------+
| 3202 |
+----------+
1 row in set (0.06 sec)
结论
- 当mysql 某字段中有固定的stopword 分词（英文的空格符，中文的“,”"-"等），对该字段建立全文索引，能快速搜索出现某个关键词的相关记录信息，实现简单搜索引擎的效果
- 当mysql 某字段没有固定的stopword 分词，使用内置解析器ngram 可将字段值分成固定数量（ngram_token_size定义大小）的关键词快速进行搜索；当搜索的关键词的字符数量不等于ngram_token_size定义大小时，会出现与实际情况不一致的问题
- 全文索引能快速搜索，也存在维护索引的开销；字段长度越大，创建的全文索引也越大，会影响DML语句的吞吐量，可用专门的全文搜索引擎ES来做这件事
参考 InnoDB FULLTEXT Indexes
原文出处：https://www.cnblogs.com/YangJiaXin/p/11153579.html
关于MySQL全文索引应用简明教程和mysql全文索引应用简明教程视频的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于mssql全文索引、MySQL 之全文索引、mysql 全文索引、MySQL 全文索引实现简单版搜索引擎的相关信息，请在本站寻找。
本文标签：
