MySQL实验之-全文索引

红太狼 2024-03-24 13:51 108阅读 0赞

### 如何启用全文索引 ###

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

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)

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