vlambda博客
学习文章列表

全文搜索,原来mysql可以这样玩!

背景

全文索引是目前实现大数据搜索的关键技术,备受关注!目前支持全文索引功能的开源产品有很多,如elasticsearch,solr,Sphinx,Nutch等等,这些产品应用广泛,适用于大数据量的搜索,各个的拥趸者众多。但它们都有一个共同的特点:上手复杂。对于微小企业或者数据量不那么大的需求显得比较“重”,有没有一种人人可以上手,适合微小企业或者数据量不大业务的搜索查询呢?其实Mysql本身提供了针对CHAR, VARCHAR,或TEXT列的全文搜索功能,让我们来一探究竟吧!

Mysql 全文索引搜索简介

  • Mysql全文索引创建在基于文本的列(CHAR, VARCHAR,或TEXT列)上,以帮助加快查询和DML操作包含在这些列中的数据,省略任何定义为stopwords的单词。

  • FULLTEXT索引定义为CREATE TABLE语句的一部分,或者使用ALTER TABLE或CREATE index添加到已经存在的表中。

  • 全文搜索使用MATCH()…AGAINST语法。

InnoDB的全文索引采用了倒排索引设计。倒排索引存储单词列表,对于每个单词,存储该单词所在的文档列表。为了支持邻近搜索,每个单词的位置信息也以字节偏移量的形式存储。

Mysql 全文索引搜索实例

创建一个带全文索引列的表

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

初始化数据

 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');

测试

SELECT * FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');

总结

MySQL支持全文索引和搜索:

  • MySQL中的全文索引是FULLTEXT类型的索引。

  • MySQL全文索引只能用于InnoDB或MyISAM表,并且只能创建CHAR、VARCHAR或TEXT列。

  • MySQL提供了一个内置的支持中文、日文和韩文的全文解析器(CJK),以及一个可安装的MeCab日文全文解析器插件。

  • FULLTEXT索引定义可以在创建表时在CREATE TABLE语句中给出,也可以稍后使用ALTER TABLE或CREATE index添加。

  • 对于大型数据集,将数据加载到没有全文索引的表中,然后再创建索引,比将数据加载到已有全文索引的表中要快得多。

参考资料

【1】
https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html

【2】
https://dev.mysql.com/doc/refman/8.0/en/innodb-fulltext-index.html