全部产品

全文检索最佳实践

更新时间:2019-04-29 15:51:48

分析型数据库MySQL版除了支持基本的全文检索方式还支持以下方式

  • 按近似度排序
  • 结果集过滤
  • 多列查询
  • 短语查询、精确匹配
  • 逻辑操作符AND OR NOT
  • 结构化、非结构化联合检索
  • 高级SQL语法:结构化、非结构化GROUP BY, JOIN, UNION

按近似度排序

SQL语义规定在不带ORDER BY的情况下不会按照近似度排序。如果需要将所有命中结果按照近似度从高到低排序,则使用如下SQL语法。

如果需要将结果按照近似度从高到低排序,则加上ORDER BY DESC

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(title) against ('浙江省杭州市')
  4. ORDER BY match(title) against ('浙江省杭州市') DESC
  5. LIMIT 100;

结果集过滤

全文检索会召回所有跟关键词近似的结果。在某些数据量很大的场景中,命中关键词的结果集可能也很大,但是往往只需要取出近似度较高的部分结果。

分析型数据库MySQL版提供了结果集过滤的功能,如下列子中where match() against() > 0.9 表示只取近似度排在前10%的结果,过滤掉了90%的低近似度结果。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(title) against ('浙江省杭州市') > 0.9
  4. ORDER BY match(title) against ('浙江省杭州市') DESC
  5. LIMIT 100;

多列查询

在title,body两列中同时检索”浙江省杭州市”关键字,返回近似度排在前10%的结果行,并且将结果行按照近似度逆序排列。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(title,body) against ('浙江省杭州市') > 0.9
  4. ORDER BY match(title, body) against ('浙江省杭州市') DESC
  5. LIMIT 100;

短语查询、精确匹配

默认情况下,分析型数据库MySQL版全文检索会对词语进行分词后,再进行检索。比如检索”中华人民共和国”会被分词为”中华”、”人民”、”共和国”三个词分别检索。但是某些特殊情况下,可能需要完全精确的短语匹配。比如只希望返回完全精确匹配”中华人民共和国”的结果,而不希望返回分词后的检索结果,则使用短语查询语法。语法格式为:在检索关键词上加双引号。

基本查询:分词后再检索。注意关键词的写法:没有双引号。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('中华人民共和国') > 0.9
  4. ORDER BY match(body) against ('中华人民共和国') DESC
  5. LIMIT 100;

短语查询:精确匹配,不会做分词处理。注意关键词的写法,有双引号。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('"中华人民共和国"') > 0.9
  4. ORDER BY match(body) against ('"中华人民共和国"') DESC
  5. LIMIT 100;

逻辑操作符 AND OR NOT

分析型数据库MySQL版支持利用逻辑操作符对结果进行控制。目前支持的逻辑操作符包括:AND OR NOT

其中AND表示要求操作符两边的关键词都必须出现。OR表示操作符两边的关键词出现一个即可。NOT表示右侧的关键词不能出现。

语法为:<word1> AND/OR/NOT <word2>

注意:1)AND/OR/NOT必须大写;2)AND/OR/NOT两边必须有空格。

要求 “浙江省”和”杭州市”都必须出现。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('浙江省 AND 杭州市') > 0.9
  4. ORDER BY match(body) against ('浙江省 AND 杭州市') DESC
  5. LIMIT 100;

— 要求 “浙江省”,”杭州市”出现一个即可。效果等同于如 “浙江省杭州市”, “浙江省 杭州市”

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('浙江省 OR 杭州市') > 0.9
  4. ORDER BY match(body) against ('浙江省 OR 杭州市') DESC
  5. LIMIT 100;

要求 “浙江省”一定不能出现

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('杭州市 NOT 浙江省') > 0.9
  4. ORDER BY match(body) against ('杭州市 NOT 浙江省') DESC
  5. LIMIT 100;

结构化、非结构化联合检索

分析型数据库MySQL版支持对结构化列、非结构化列进行联合条件检索。

要求查询create_time在20180201-20180930之间的、body命中“浙江省杭州市”、且comment不为空的数据行。

  1. SELECT id, title, author, body
  2. FROM articles_test
  3. WHERE create_time > '2018-02-01 00:00:00'
  4. AND create_time < '2018-09-30 00:00:00'
  5. AND match(body) against ('浙江省杭州市') > 0.9
  6. AND comment is not null
  7. ORDER BY match(body) against ('浙江省杭州市') DESC
  8. LIMIT 100;

要求在body中检索“浙江省杭州市”, 且在author中检索“张三”。

  1. SELECT id, author, body
  2. FROM articles_test
  3. WHERE match(body) against ('浙江省杭州市') > 0.9
  4. AND match(author) against('张三') > 0.9
  5. ORDER BY match(body) against ('浙江省杭州市') DESC, match(author) against('张三') DESC
  6. LIMIT 100;

高级SQL语法:结构化、非结构化融合GROUP BY, JOIN, UNION

分组

  1. SELECT author, body, max(match(body) against ('浙江省杭州市')) as score
  2. FROM articles_test
  3. WHERE match(body) against ('浙江省杭州市') > 0.9
  4. GROUP BY author, body
  5. ORDER BY score DESC
  6. LIMIT 100;

表连接: 在文章表articles_test中检索“浙江省杭州市”, 在作者信息表author_info中检索“张三”,并且利用id列将两表进行join。

  1. CREATE TABLE author_info (
  2. id bigint COMMENT '',
  3. name varchar COMMENT '',
  4. addr varchar COMMENT '',
  5. FULLTEXT INDEX name_fulltext (name),
  6. FULLTEXT INDEX addr_fulltext (addr),
  7. PRIMARY KEY (id)
  8. )
  9. PARTITION BY HASH KEY(id)
  10. PARTITION NUM 8
  11. CLUSTERED BY (id)
  12. TABLEGROUP test_group
  13. OPTIONS (UPDATETYPE='realtime')
  14. COMMENT '';
  15. insert into author_info(id, name, addr) values(0, '张三', '湖南省张家界市武陵源区');
  16. SELECT articles_test.id, articles_test.title, author_info.name, author_info.addr
  17. FROM articles_test
  18. JOIN author_info
  19. ON articles_test.id = author_info.id
  20. WHERE
  21. match (articles_test.body) against ('浙江省杭州市')
  22. AND match(author_info.name) against ('张三')
  23. AND articles_test.create_time between '2018-01-01 00:00:00' and '2018-09-30 00:00:00';

合并

  1. SELECT id, title
  2. FROM articles_test
  3. WHERE
  4. match (articles_test.body) against ('浙江省杭州市')
  5. UNION ALL
  6. SELECT id, addr
  7. FROM author_info
  8. WHERE
  9. match(author_info.name) against ('张三');