Mysql的全文检索

2022-02-21 18:04:33 浏览数 (1)

mysql分词索引

  • 前言
  • 使用范围及限制
  • 全文检索的全局配置
  • 数据准备
  • 全文检索元数据
    • INNODB_FT_CONFIG
    • INNODB_FT_BEING_DELETED
    • INNODB_FT_DELETED
    • INNODB_FT_DEFAULT_STOPWORD
    • INNODB_FT_INDEX_CACHE
    • INNODB_FT_INDEX_TABLE
  • 全文检索查询
    • 查询模式
    • 简单查询
    • 相关度分数查询
    • 布尔全文检索
    • 全文检索扩展查询(同义词效果)
  • 自定义停用词
  • ngram全文检索器(中文停用词)

前言

可以直接跟着官方敲一下: mysql官方文档-fulltext

现在的产品一言不合就想分词或者全模糊查询,之前的解决方案有:

  1. 数据量少呀,数据都抛给前端了; 前端看着办
  2. 很多代码里面都有like 两边% 查询的,我很讨厌这种sql,但是好像大多数开发无所谓,一般很难劝的住别人
  3. 不巧自己来了这种需求, 跟产品争执一下,目的: 模糊查询很合理,不过匹配规则要稍微改改,后模糊查询的话,需求我麻溜的接了(大多数情况需求产品会退一步的)
  4. 争执失败了,产品说了某某某产品就是这样子,老板发话了我们也要; 结果链路复杂不说请求量还不见得小, 需求评估下,是否需要上es

这次碰到一个类似需求处于设计阶段,因为时间充足,需求又简单,就照着官网学习下mysql的全文检索,万一很合适的话,后面就可以多一种备用方案了…

使用范围及限制

  1. 仅支持与InnoDB和MyISAM引擎,表现形式还略有不同,没有拿MyISAM进行测试
  2. 不支持分区表
  3. 不支持Unicode编码,usc2这个字符集最好也别使用
  4. 停用词默认不支持中文,日语….
    • 基于字符的 ngram 全文检索解析器支持中日韩三种语言
    • 日语还有一个MeCab解析器插件
  5. 虽然我们可以每一行都设置一个字符集,但是全文检索相关的列必须同字符
  6. %这个用于模糊查询,全文检索不支持这个通配符; 一般会使用 word* 这样子
  7. DML(增删改)操作中,事务提交后才会正式插入到全文索引表中, 不会有脏读之类的问题

全文检索的全局配置

代码语言：txt复制
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

数据准备

代码语言：txt复制
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';

全文检索元数据

代码语言：txt复制
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_FT_CONFIG

提供了一个InnoDB全文检索和相关处理的元信息

代码语言：txt复制
select * from INFORMATION_SCHEMA.INNODB_FT_CONFIG

KEY	                         VALUE
---
optimize_checkpoint_limit	  180
synced_doc_id	               8
stopword_table_name
use_stopword	               1

INNODB_FT_BEING_DELETED

用于监控或者调试; 正常情况下数据为空

INNODB_FT_DELETED

存储被删除的innoDB的行;索引重组代价太大; mysql采用将删除的行进行记录,查询是会从这个结果集中进行数据过滤;

但是这个数据不是永久存在的; 当执行 OPTIMIZE TABLE articles; 时索引重组会将表里的数据干掉

INNODB_FT_DEFAULT_STOPWORD

在innoDB表中创建全文检索索引时的默认停用词列表

代码语言：txt复制
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

INNODB_FT_INDEX_CACHE

新插入行的时候.为避免索引重组,索引会临时存放在缓存中

我们可以通过执行 OPTIMIZE TABLE articles; 后将cache清空,索引放到 INNODB_FT_INDEX_TABLE表中

代码语言：txt复制
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

INNODB_FT_INDEX_TABLE

首次insert后 , 表中并没有信息,需要执行 OPTIMIZE TABLE articles;

结构和 cache 一致

代码语言：txt复制
##
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

全文检索查询

mysql官方示例

查询模式

代码语言：txt复制
search_modifier:
  {
       IN NATURAL LANGUAGE MODE         -- 这个是默认的
     | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
     | IN BOOLEAN MODE
     | WITH QUERY EXPANSION
  }

简单查询

全文索引设置了两个字段,那么就得两个一起用; 想用一个字段需要单独为一个字段设置一个全文检索的索引

全文检索有相关度排名,当满足下面条件则按相关度进行排序

  1. 没有明确的order by
  2. 必须使用全文检索执行搜索
  3. 有多表联查时,全文索引必须是连接中最左边的非常量表
代码语言：txt复制
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;

分词默认不区分大小写,想区分从字符集排序规则中进行调整

相关度分数查询

  • 分词选项会进行分词
  • 没有where则所有行都会进行分数计算,如果不想有太多干扰,可以加where
代码语言：txt复制
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方式进行查询; 我们可以通过布尔修饰符来调整匹配的行文, 通过查询条件前加 表示包含, - 表示排除

代码语言：txt复制
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 ...

全文检索的一些使用操作说明:

  1. MYSQL DBMS 查找至少包含一个字符串的行
  2. MYSQL DBMS 查找包含两个的字符串
  3. MYSQL DBMS 查找包含MYSQL的行,如果有DBMS则按照优先级进行排序
  4. MYSQL -DBMS 查找包含MYSQL但是不包含DBMS的行
  5. '"MySQL Tutorial"' 双引号将词语进行组合
  6. 官网还有其他组合,比如 ~ 号,没太了解清楚,不记录了

全文检索扩展查询(同义词效果)

当我们使用 QUERY EXPANSION 模式的时候,可以实现类似es的同义词效果;

代码语言：txt复制
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 ...
代码语言：txt复制
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 就区分大小写

代码语言：txt复制
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'
	);

ngram全文检索器(中文停用词)

默认停用词大小为2; 修改值需要mysql启动的时候指定: mysqld --ngram_token_size=n 测试一个默认为2的效果

这里需要注意, 虽然默认停用词都是英文的; 但是前面已经提到可以自定义停用词; 可以加中文停词器

代码语言：txt复制
# 还是前面的表; 建一个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这一条
云数据库SQLServer 全文检索

0 人点赞