Mysql的全文检索

jiezi

5 年前

当检索数据库时，为了优化性能，我们使用一种叫索引的数据结构。当执行一个查询时，索引能够快速定位所需数据。在这篇文章中，我将分享使用全文检索处理小型项目的经验，该项目使用倒排索引概念优化了查询性能。

全文检索 是一种检查记录中的所有单词以匹配提供的搜索条件的搜索技术（经常被搜索引擎所使用）。

让我们设想一个场景，一个电商网站想提供一个搜索功能，让用户能够使用相同的搜索字段对商品名称，商品种类，商品描述进行搜索。这可以通过 Mysql 的 ’LIKE’ 查询对三个列（商品名称，商品种类，商品描述）进行关键字搜索。

SELECT * FROM product_test WHERE name LIKE‘%top%’and category LIKE‘%top%’and description LIKE‘%top%’;

上述查询语句会随着数据体量的增加而降低性能。使用 ’LIKE‘ 语句，有些搜索字词可以被很好地索引并且 (性能) 表现更好，而其他字词却不能被很好地索引从而提供更好的性能结果。基本上，通配符在查询语句中的位置决定了性能的高低。

索引范围越小，查询越快

‘LIKE’ 表达式包含两个部分 a)访问谓词 (在第一个通配符前的内容) b) 筛选谓词 (除去访问谓词的其他内容)
访问谓词应该更具选择性，以使索引范围更小，查询更快。在上述示例中，’like’ 表达式不包含访问谓词，以致扫描了所有的记录。

全文检索技术使用了倒排索引的概念。倒排索引将每个单词映射到其在记录或一组记录中的位置。这种结构在全文检索中有广泛的应用。我希望我们所有人都能够熟悉这种数据结构，因为这种数据结构可以影射为现实中书本的索引, 我们可以通过书本的索引找到对应主题的页码。倒排索引会告诉你特定搜索词所在的记录。

全文索引在 MySQL 中是一种名为 FULLTEXT 类型的索引。FULLTEXT 索引可以在创建表时创建，或者在创建表之后通过 ALTER TABLE/CREATE INDEX，从VARCHAR、TEXT 类型的列创建。

CREATE TABLE product_test (id INT PRIMARY KEY, name VARCHAR(200),category VARCHAR(200),description TEXT, FULLTEXT (name,category, description));

搜索由 Match 函数执行。Match 函数将列名作为参数，搜索的字词作为参数传递给 Against 函数。

SELECT id, MATCH (name,category, description) AGAINST (‘Top’) FROM product_test;

搜索结果默认按相关性的降序排序，相关性最高的记录将先出现，然后出现相关性低的结果。

在 30,000 条记录的上运行 ’LIKE’ 查询语句，耗时 0.062 秒，全文检索查询耗时 0.011 秒。结果显示，使用索引的全文检索方式比 ’LIKE’ 语句更块。

什么是全文检索？