mysql分词索引
可以直接跟着官方敲一下: mysql官方文档-fulltext
现在的产品一言不合就想分词或者全模糊查询,之前的解决方案有:
这次碰到一个类似需求处于设计阶段,因为时间充足,需求又简单,就照着官网学习下mysql的全文检索,万一很合适的话,后面就可以多一种备用方案了…
ngram
全文检索解析器支持中日韩三种语言word*
这样子show global VARIABLES where Variable_name like 'innodb_ft%'
Variable_name Value
---
innodb_ft_aux_table
innodb_ft_cache_size 8000000
innodb_ft_enable_diag_print OFF
innodb_ft_enable_stopword ON
innodb_ft_max_token_size 84
innodb_ft_min_token_size 3
innodb_ft_num_word_optimize 2000
innodb_ft_result_cache_limit 2000000000
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree 2
innodb_ft_total_cache_size 640000000
innodb_ft_user_stopword_table
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body)
) ENGINE = INNODB;
INSERT INTO articles (title, body)
VALUES
(
'MySQL Tutorial',
'DBMS stands for DataBase ...'
),
(
'How To Use MySQL Well',
'After you went through a ...'
),
(
'Optimizing MySQL',
'In this tutorial we show ...'
),
(
'1001 MySQL Tricks',
'1. Never run mysqld as root. 2. ...'
),
(
'MySQL vs. YourSQL',
'In the following database comparison ...'
),
(
'MySQL Security',
'When configured properly, MySQL ...'
);
# 后面查询表数据时需要执行下面sql才能将调试定位到这个表中
set GLOBAL innodb_ft_aux_table = 'test/articles';
SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';
Tables_in_information_schema (INNODB_FT%)
---
INNODB_FT_CONFIG
INNODB_FT_BEING_DELETED
INNODB_FT_DELETED
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
提供了一个InnoDB全文检索和相关处理的元信息
select * from INFORMATION_SCHEMA.INNODB_FT_CONFIG
KEY VALUE
---
optimize_checkpoint_limit 180
synced_doc_id 8
stopword_table_name
use_stopword 1
用于监控或者调试; 正常情况下数据为空
存储被删除的innoDB的行;索引重组代价太大; mysql采用将删除的行进行记录,查询是会从这个结果集中进行数据过滤;
但是这个数据不是永久存在的; 当执行 OPTIMIZE TABLE articles;
时索引重组会将表里的数据干掉
在innoDB表中创建全文检索索引时的默认停用词列表
select * from INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD
value
---
a
about
an
are
as
at
be
by
com
de
en
for
from
how
i
in
is
it
la
of
on
or
that
the
this
to
was
what
when
where
who
will
with
und
the
www
新插入行的时候.为避免索引重组,索引会临时存放在缓存中
我们可以通过执行 OPTIMIZE TABLE articles;
后将cache清空,索引放到 INNODB\_FT\_INDEX\_TABLE
表中
select * from INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE limit 5
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
---
1001 5 5 1 5 0
after 3 3 1 3 22
comparison 6 6 1 6 44
configured 7 7 1 7 20
database 2 6 2 2 31
首次insert后 , 表中并没有信息,需要执行 OPTIMIZE TABLE articles;
结构和 cache
一致
##
OPTIMIZE TABLE articles;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE LIMIT 5;
## 上面select的结果集
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
---
1001 5 5 1 5 0
after 3 3 1 3 22
comparison 6 6 1 6 44
configured 7 7 1 7 20
database 2 6 2 2 31
search_modifier:
{
IN NATURAL LANGUAGE MODE -- 这个是默认的
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}
全文索引设置了两个字段,那么就得两个一起用; 想用一个字段需要单独为一个字段设置一个全文检索的索引
全文检索有相关度排名,当满足下面条件则按相关度进行排序
SELECT count(*) count FROM articles WHERE MATCH(title,body) AGAINST('database')
count
---
2
# 由于全文检索默认是进行优先级排序;count可以通过下面sql来避开排序来提升性能
SELECT COUNT(IF(MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count FROM articles;
分词默认不区分大小写,想区分从字符集排序规则中进行调整 |
---|
select id,MATCH(title,body) AGAINST ('tutorial abdc esf') as score FROM articles
# WHERE MATCH(title,body) AGAINST ('tutorial abdc esf' );
id score
---
1 0.22764469683170319
2 0
3 0.22764469683170319
4 0
5 0
6 0
前面有提到默认是NATURAL方式进行查询; 我们可以通过布尔修饰符来调整匹配的行文, 通过查询条件前加 +
表示包含, -
表示排除
select * FROM articles where MATCH(title,body) AGAINST ('+MYSQL -configured -tutorial' IN BOOLEAN MODE);
id title body
---
2 How To Use MySQL Well After you went through a ...
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
5 MySQL vs. YourSQL In the following database comparison ...
全文检索的一些使用操作说明:
MYSQL DBMS
查找至少包含一个字符串的行+MYSQL +DBMS
查找包含两个的字符串+MYSQL DBMS
查找包含MYSQL的行,如果有DBMS则按照优先级进行排序+MYSQL -DBMS
查找包含MYSQL但是不包含DBMS的行'"MySQL Tutorial"'
双引号将词语进行组合~
号,没太了解清楚,不记录了当我们使用 QUERY EXPANSION
模式的时候,可以实现类似es的同义词效果;
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' IN NATURAL LANGUAGE MODE);
id title body
---
1 MySQL Tutorial DBMS stands for DataBase ...
5 MySQL vs. YourSQL In the following database comparison ...
SELECT * FROM articles
WHERE MATCH (title,body)
AGAINST ('database' WITH QUERY EXPANSION);
id title body
---
5 MySQL vs. YourSQL In the following database comparison ...
1 MySQL Tutorial DBMS stands for DataBase ...
3 Optimizing MySQL In this tutorial we show ...
6 MySQL Security When configured properly, MySQL ...
2 How To Use MySQL Well After you went through a ...
4 1001 MySQL Tricks 1. Never run mysqld as root. 2. ...
前面检索元数据中有提到的 INNODB_FT_DEFAULT_STOPWORD
是mysql的默认停用词; 不过停用词可以自定义, 但是字段是必须得是value
停用词是否区分大小写和服务器的排序规则有关,比如: latin1_swedish_ci
不区分大消息, latin1_general_cs
/ latin1_bin
就区分大小写
CREATE TABLE my_stopwords(value VARCHAR(25)) ENGINE INNODB;
INSERT into my_stopwords (value) values ('Ishmael'),('Ralph');
# 将新表设置为停用词使用的表
SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';
# 新建另一张表进行测试
CREATE TABLE `opening_lines` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`opening_line` text,
`author` varchar(200) DEFAULT NULL,
`title` varchar(200) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `ft_opening_lines` (`opening_line`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO opening_lines (opening_line, author, title)
VALUES
(
'Call me Ishmael.',
'Herman Melville',
'Moby-Dick'
),
(
'A screaming comes across the sky.',
'Thomas Pynchon',
'Gravity\'s Rainbow'
),
(
'I am an invisible man.',
'Ralph Ellison',
'Invisible Man'
),
(
'Where now? Who now? When now?',
'Samuel Beckett',
'The Unnamable'
),
(
'It was love at first sight.',
'Joseph Heller',
'Catch-22'
),
(
'All this happened, more or less.',
'Kurt Vonnegut',
'Slaughterhouse-Five'
),
(
'Mrs. Dalloway said she would buy the flowers herself.',
'Virginia Woolf',
'Mrs. Dalloway'
),
(
'It was a pleasure to burn.',
'Ray Bradbury',
'Fahrenheit 451'
);
默认停用词大小为2; 修改值需要mysql启动的时候指定: mysqld --ngram_token_size=n
测试一个默认为2的效果
这里需要注意, 虽然默认停用词都是英文的; 但是前面已经提到可以自定义停用词; 可以加中文停词器
# 还是前面的表; 建一个ngram全文检索索引,前一个全文索引一定要删掉,不然这个不生效
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title,body) WITH PARSER ngram;
# 插入数据
INSERT INTO articles (title,body) VALUES
('数据库管理','在本教程中我将向你展示如何管理数据库'),
('数据库应用开发','学习开发数据库应用程序');
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE ORDER BY doc_id, position LIMIT 15;
WORD FIRST_DOC_ID LAST_DOC_ID DOC_COUNT DOC_ID POSITION
---
数据库管理 9 9 1 9 0
数据 9 10 2 9 0
据库 9 10 2 9 3
库管 9 9 1 9 6
管理 9 9 1 9 9
在本教程中我将向你展示如何管理数据库 9 9 1 9 16
在本 9 9 1 9 16
本教 9 9 1 9 19
教程 9 9 1 9 22
程中 9 9 1 9 25
中我 9 9 1 9 28
我将 9 9 1 9 31
将向 9 9 1 9 34
向你 9 9 1 9 37
你展 9 9 1 9 40
SELECT * FROM articles where MATCH(title,body) AGAINST('数据库应用')
id title body
---
8 数据库应用开发 学习开发数据库应用程序
7 数据库管理 在本教程中我将向你展示如何管理数据库
不同的模式有细微的不同, 官网有一个小示例:
natural language mode
模式; 一个ab文档,一个abc文档,搜 ab bc
都可以搜到boolean mode search,
模式; 一个ab文档,一个abc文档, 搜 ab bc
只能搜到abc这一条原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。