MySQL模糊查询再也用不着 like+% 了!
阅读本文大概需要 7 分钟。
来自:juejin.cn/post/6989871497040887845
前言
%xx
" 会导致索引失效,但有时需求就是如此,类似这样的需求还有很多,例如,搜索引擎需要根基用户数据的关键字进行全文查找,电子商务网站需要根据用户的查询条件,在可能需要在商品的详细介绍中进行查找,这些都不是B+树索引能很好完成的工作。倒排索引
inverted file index
:{单词,单词所在文档的id}full inverted index
:{单词,(单词所在文档的id,再具体文档中的位置)}
相比之下,full inverted index 占用了更多的空间,但是能更好的定位数据,并扩充一些其他搜索特性。
全文检索
创建全文索引
1、创建表时创建全文索引语法如下:
CREATE TABLE table_name ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, author VARCHAR(200),
title VARCHAR(200), content TEXT(500), FULLTEXT full_index_name (col_name) ) ENGINE=InnoDB;
SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
WHERE name LIKE 'test/%';
2、在已创建的表上创建全文索引语法如下:
CREATE FULLTEXT INDEX full_index_name ON table_name(col_name);
使用全文索引
MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
IN NATURAL LANGUAGE MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| IN BOOLEAN MODE
| WITH QUERY EXPANSION
}
MATCH()
AGAINST()
语法进行,其中,MATCH()
采用逗号分隔的列表,命名要搜索的列。AGAINST()
接收一个要搜索的字符串,以及一个要执行的搜索类型的可选修饰符。全文检索分为三种类型:自然语言搜索、布尔搜索、查询扩展搜索,下面将对各种查询模式进行介绍。Natural Language
MATCH()
默认采用 Natural Language 模式,其表示查询带有指定关键字的文档。SELECT
count(*) AS count
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL' );
SELECT
count(IF(MATCH ( title, body )
against ( 'MySQL' ), 1, NULL )) AS count
FROM
`fts_articles`;
SELECT
*,
MATCH ( title, body ) against ( 'MySQL' ) AS Relevance
FROM
fts_articles;
word 是否在文档中出现 word 在文档中出现的次数 word 在索引列中的数量 多少个文档包含该 word
查询的 word 在 stopword 列中,忽略该字符串的查询 查询的 word 的字符长度是否在区间 [ innodb_ft_min_token_size,innodb_ft_max_token_size
] 内
SELECT
*,
MATCH ( title, body ) against ( 'for' ) AS Relevance
FROM
fts_articles;
innodb_ft_min_token_size
和 innodb_ft_max_token_size
控制 InnoDB 引擎查询字符的长度,当长度小于innodb_ft_min_token_size
或者长度大于innodb_ft_max_token_size
时,会忽略该词的搜索。在 InnoDB 引擎中,参数 innodb_ft_min_token_size
的默认值是3,innodb_ft_max_token_size
的默认值是84Boolean
select * from fts_test where MATCH(content) AGAINST('+Pease -hot' IN BOOLEAN MODE);
+
:表示该 word 必须存在-
:表示该 word 必须不存在(no operator)
表示该 word 是可选的,但是如果出现,其相关性会更高@distance
表示查询的多个单词之间的距离是否在 distance 之内,distance 的单位是字节,这种全文检索的查询也称为 Proximity Search,如MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)
语句表示字符串 Pease 和 hot 之间的距离需在30字节内>
:表示出现该单词时增加相关性<
:表示出现该单词时降低相关性~
:表示允许出现该单词,但出现时相关性为负*
:表示以该单词开头的单词,如 lik*,表示可以是 lik,like,likes"
:表示短语
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '+MySQL +(>databaseIN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );
SELECT
*
FROM
`fts_articles`
WHERE
MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );
Query Expansion
implied knowledge
通过在查询语句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
可以开启 blind query expansion
(又称为 automatic relevance feedback),该查询分为两个阶段。第一阶段:根据搜索的单词进行全文索引查询 第二阶段:根据第一阶段产生的分词再进行一次全文检索的查询
-- 创建索引
create FULLTEXT INDEX title_body_index on fts_articles(title,body);
-- 使用 Natural Language 模式查询
SELECT
*
FROM
`fts_articles`
WHERE
MATCH(title,body) AGAINST('database');
-- 当使用 Query Expansion 模式查询
SELECT
*
FROM
`fts_articles`
WHERE
MATCH(title,body) AGAINST('database' WITH QUERY expansion);
删除全文索引
1、直接删除全文索引语法如下:
DROP INDEX full_idx_name ON db_name.table_name;
2、使用 alter table 删除全文索引语法如下:
ALTER TABLE db_name.table_name DROP INDEX full_idx_name;
推荐阅读:
内容包含Java基础、JavaWeb、MySQL性能优化、JVM、锁、百万并发、消息队列、高性能缓存、反射、Spring全家桶原理、微服务、Zookeeper、数据结构、限流熔断降级......等技术栈!
⬇戳阅读原文领取! 朕已阅
评论