MySQL实验之-全文索引

如何启用全文索引

配置文件加入如下内容, 重启数据库

ngram_token_size=2

innodb_ft_max_token_size=8

innodb_ft_min_token_size=1

boolean 搜索操作符

• 包含, 词必须存在

• 排除, 词必须不出现

包含, 而且增加等级值

< 包含, 且减少等级值

() 把词组成子表达式(运行这些子表达式作为一个组被包含, 排除, 排列等)

取消一个词的排序值

• 词尾的通配符

“” 定义一个短语(与单词的列表不一样, 它匹配整个短语以便包含或排除这个词语)

实验案例

create table test(

id int(11) not null primary key auto_increment,

name varchar(100) not null comment '工商名',

brand varchar(100) default null comment '省份',

en varchar(100) default null comment '英文名',

fulltext key (name,brand,en) with parser ngram

)engine=innodb default charset=utf8mb4 collate=utf8mb4_bin;

insert into test (name,brand,en) values ('江苏苏州大漠孤烟直有限公司','js','dmgyz');

insert into test (name,brand,en) values ('江苏苏州长河落日圆电子商务有限公司','js','chlry');

insert into test (name,brand,en) values ('白日依山尽 (苏州)科技有限公司','js','brysj');

insert into test (name,brand,en) values ('黄河入海流(苏州)有限公司','js','hhrhl');

insert into test (name,brand,en) values ('江苏欲穷千里目通讯有限公司','js','yqqlm');

insert into test (name,brand,en) values ('更上一层楼江苏畅捷通支付技术有限公司','js','gsycl');

insert into test (name,brand,en) values ('万里悲秋常作客通信息技术股份有限公司','js','wlbqczk');

insert into test (name,brand,en) values ('江苏风急天高猿啸哀科技有限公司','js','fjtgyxa');

insert into test (name,brand,en) values ('中国渚清沙白鸟飞回咨询有限公司','js','zqsbnfh');

insert into test (name,brand,en) values ('南京·无边落木萧萧下有限公司','aa','wblmxxx');

insert into test (name,brand,en) values ('不尽长江滚滚来','js','bjcjggl');

测试

select * from test where match (name,brand,en) against ('电子 信息' IN BOOLEAN MODE); -- 要搜索包含两个单词中至少一个的行

select * from test where match (name,brand,en) against ('+电子 +商务' IN BOOLEAN MODE); -- 要搜索包含两个单词的行

select * from test where match (name,brand,en) against ('+江苏 -科技' IN BOOLEAN MODE); -- 要搜索包含单词1但不包含2的行

select * from test where match (name,brand,en) against ('+江苏 科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 但为包含2的行添加更高的排名

select * from test where match (name,brand,en) against ('+江苏 >科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 如果它包含单词2则将行排名更高

select * from test where match (name,brand,en) against ('+江苏 <科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 如果它包含单词2则将行排名更低

select * from test where match (name,brand,en) against ('+江苏 ~科技' IN BOOLEAN MODE); -- 要搜索包含单词1的行,取消单词2的排序

select * from test where match (name,brand,en) against ('+江苏 +(通讯 电子 支付)' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 子组合里必须包含1个

select * from test where match (name,brand,en) against ('+江苏 -(商务 通讯)' IN BOOLEAN MODE); -- 要搜索包含单词1的行, 子组合里必须不包含

速度测试

select count(*) from ARTICLE; -- 表共 72924 rows 500MB

-- 正则表达式查询 1.997s

select count(*) from ARTICLE

WHERE ((INFO_TITLE LIKE '%长三角%' AND INFO_TITLE REGEXP '一体化|协同发展|经济|贸易|工业|转型'))

OR ((INFO_TEXT LIKE '%长三角%' AND INFO_TEXT REGEXP '一体化|协同发展|经济|贸易|工业|转型'));

-- 正则表达式修改 2.327s

update ARTICLE

set REFERENCE_LABEL = CONCAT(REFERENCE_LABEL, ',长三角一体化')

WHERE ((INFO_TITLE LIKE '%长三角%' AND INFO_TITLE REGEXP '一体化|协同发展|经济|贸易|工业|转型'))

OR ((INFO_TEXT LIKE '%长三角%' AND INFO_TEXT REGEXP '一体化|协同发展|经济|贸易|工业|转型'));

-- 加全文索引

create fulltext index ARTICLE on ARTICLE(INFO_TITLE,INFO_TEXT) with parser ngram;

-- 全文索引查询 0.357s

select count(*) from ARTICLE

where match (INFO_TITLE,INFO_TEXT) AGAINST ('+长三角 +(一体化 协同发展 经济 贸易 工业 转型)' IN BOOLEAN MODE)

-- 全文索引修改 1.076s

update ARTICLE

set REFERENCE_LABEL = CONCAT(REFERENCE_LABEL, ',长三角一体化')

where match (INFO_TITLE,INFO_TEXT) AGAINST ('+长三角 +(一体化 协同发展 经济 贸易 工业 转型)' IN BOOLEAN MODE)

可见: 全文索引速度比正则表达式快的多