MySQL高阶讲座之：`MySQL`的`Fulltext Search`：其索引实现与`TF-IDF`算法。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位好，我是你们今天的MySQL Fulltext Search专题讲座主持人。今天咱们不讲“Hello, World!”，直接来点硬核的——聊聊MySQL的全文本搜索，特别是它的索引实现和TF-IDF算法。保证让你听完之后，感觉自己也能手撸一个搜索引擎似的！

一、Fulltext Search：告别Like的慢生活

你肯定用过LIKE '%keyword%'来做模糊查询吧？慢吧？特别慢吧？数据量一大，简直是灾难现场。 LIKE是全表扫描，效率低到尘埃里。

Fulltext Search就是来拯救世界的。它可以建立全文索引，然后通过索引来快速定位包含关键词的文档。

二、Fulltext Index：索引的秘密花园

Fulltext Index就像一个倒排索引（Inverted Index）。倒排索引是啥？别怕，其实很简单。

假设我们有三句话：

Document 1: "The quick brown fox"
Document 2: "The quick gray fox"
Document 3: "Fox jumped over the lazy dog"

用倒排索引来表示，大概是这样：

关键词	包含该关键词的文档ID
the	1, 2
quick	1, 2
brown	1
fox	1, 2, 3
gray	2
jumped	3
over	3
lazy	3
dog	3

当我们搜索 "quick fox" 的时候，只需要查找 "quick" 和 "fox" 对应的文档ID，然后取交集，就能找到同时包含这两个词的文档。看到了吧，这比全表扫描快多了。

2.1 创建Fulltext Index

在MySQL中，创建Fulltext Index非常简单：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX content_index (content)
);

这里，我们在articles表的content列上创建了一个名为content_index的全文索引。

注意：

只有MyISAM和InnoDB存储引擎支持Fulltext Index。
Fulltext Index只能在CHAR, VARCHAR, 和 TEXT类型的列上创建。
MySQL 5.6.4之前，只有MyISAM支持Fulltext Index。 5.6.4之后，InnoDB也开始支持。
InnoDB在创建全文索引时，需要设置innodb_ft_min_token_size参数。默认值是3，表示最小索引词的长度是3个字符。如果你的词语长度小于3，需要修改这个参数。例如，要索引中文的单字词，需要将其设置为1。

2.2 修改innodb_ft_min_token_size

修改innodb_ft_min_token_size参数需要修改MySQL的配置文件（my.cnf或my.ini），添加或修改如下内容：

[mysqld]
innodb_ft_min_token_size=1

修改后，需要重启MySQL服务才能生效。

然后，务必重新创建全文索引，否则修改后的参数不会应用到已有的索引上。

三、Fulltext Search语法：玩转搜索

有了索引，我们就可以开始搜索了。 MySQL提供了几种Fulltext Search的语法。

3.1 MATCH...AGAINST

这是最常用的语法：

SELECT id, title
FROM articles
WHERE MATCH (content) AGAINST ('quick fox');

这条SQL语句会查找content列中包含 "quick" 和 "fox" 的文档，并返回id和title。

3.2 Boolean Mode

Boolean Mode提供了更灵活的搜索方式，可以使用操作符来组合关键词。

常用的操作符：

+: 必须包含该关键词
-: 必须不包含该关键词
>: 提高该关键词的权重
<: 降低该关键词的权重
*: 通配符

例子：

SELECT id, title
FROM articles
WHERE MATCH (content) AGAINST ('+quick -lazy' IN BOOLEAN MODE);

这条SQL语句会查找content列中必须包含 "quick"，并且不能包含 "lazy" 的文档。

SELECT id, title
FROM articles
WHERE MATCH (content) AGAINST ('>quick <lazy' IN BOOLEAN MODE);

这条SQL语句会提高 "quick" 的权重，降低 "lazy" 的权重。

3.3 Query Expansion

Query Expansion会根据关键词自动扩展搜索范围。

SELECT id, title
FROM articles
WHERE MATCH (content) AGAINST ('fox' WITH QUERY EXPANSION);

这条SQL语句会查找content列中包含 "fox" 以及与 "fox" 相关的词语的文档。 MySQL会根据内部的词库来扩展搜索范围。效果嘛，仁者见仁，智者见智。

四、TF-IDF算法：让搜索更精准

仅仅知道哪些文档包含关键词是不够的，我们还想知道哪些文档与关键词的相关度更高。这时候，TF-IDF算法就派上用场了。

TF-IDF (Term Frequency – Inverse Document Frequency) 是一种用于信息检索和文本挖掘的常用加权技术。它用于评估一个词语对于一个文档集或语料库中的其中一份文档的重要程度。

TF (Term Frequency)：词频

指的是一个词语在一个文档中出现的次数。词频越高，说明该词语在该文档中越重要。
IDF (Inverse Document Frequency)：逆文档频率

指的是一个词语在整个文档集中出现的频率的倒数。逆文档频率越高，说明该词语越稀有，越能区分不同的文档。

TF-IDF的计算公式：

TF-IDF = TF * IDF

MySQL的Fulltext Search也使用了TF-IDF算法来计算文档与关键词的相关度。

4.1 TF的计算

MySQL计算TF的方式比较简单，就是统计关键词在文档中出现的次数。

4.2 IDF的计算

MySQL计算IDF的方式稍微复杂一些：

IDF = log( (N - n + 0.5) / (n + 0.5) )

其中：

N：文档总数
n：包含该关键词的文档数

这个公式看起来有点吓人，其实就是为了避免分母为0，以及对结果进行平滑处理。

4.3 相关度排序

在执行Fulltext Search的时候，MySQL会根据TF-IDF值来对结果进行排序，相关度越高的文档排在前面。

我们可以通过以下SQL语句来查看相关度：

SELECT id, title, MATCH (content) AGAINST ('quick fox') AS relevance
FROM articles
WHERE MATCH (content) AGAINST ('quick fox')
ORDER BY relevance DESC;

这条SQL语句会返回id，title以及relevance（相关度），并按照relevance降序排列。

五、Stopwords：拦路虎还是好帮手？

Stopwords是指在文本中频繁出现，但通常没有实际意义的词语，例如 "the", "a", "is" 等。

MySQL的Fulltext Search默认会忽略Stopwords。

5.1 为什么忽略Stopwords？

减少索引的大小
提高搜索效率
避免干扰搜索结果

5.2 Stopwords列表

MySQL自带一个Stopwords列表，可以在INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD表中查看。

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;

5.3 自定义Stopwords列表

我们可以自定义Stopwords列表，方法是修改MySQL的配置文件（my.cnf或my.ini），添加如下内容：

[mysqld]
ft_stopword_file=/path/to/my_stopwords.txt

其中/path/to/my_stopwords.txt是你的Stopwords文件的路径。

Stopwords文件是一个纯文本文件，每行一个Stopword。

修改后，需要重启MySQL服务，并重新创建全文索引。

5.4 Stopwords的争议

有些人认为忽略Stopwords会影响搜索结果的准确性。例如，搜索 "To be or not to be"，如果忽略了 "to" 和 "be"，结果可能就大相径庭了。

所以，是否使用Stopwords，需要根据实际情况来决定。

六、N-Gram：中文分词的救星

英文的单词之间有空格分隔，所以可以直接按照空格来分词。但是中文没有空格，需要进行分词才能建立Fulltext Index。

MySQL 5.7.6之后，InnoDB支持N-Gram分词。

N-Gram是指将一个字符串分解成N个连续的字符序列。例如，对于字符串 "中国"，如果使用2-Gram，可以分解成 "中国" 和 "国"。

6.1 创建N-Gram Fulltext Index

CREATE TABLE articles_zh (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT INDEX content_index (content) WITH PARSER ngram
);

注意：

需要指定WITH PARSER ngram来使用N-Gram分词器。
ngram_token_size参数控制N的值，默认值是2。可以通过修改MySQL的配置文件来修改这个参数。

[mysqld]
ngram_token_size=1

修改后，需要重启MySQL服务，并重新创建全文索引。

6.2 N-Gram的优缺点

优点：

简单易用，不需要额外的分词工具
适用于各种语言

缺点：

分词效果可能不够准确
索引大小可能会比较大

七、Fulltext Search的优化

Fulltext Search的性能优化是一个复杂的话题，这里只介绍一些常用的技巧。

合理设置innodb_ft_min_token_size和ngram_token_size参数

这两个参数会影响索引的大小和搜索的准确性。需要根据实际情况进行调整。
定期优化索引
```
OPTIMIZE TABLE articles;
```
这条SQL语句会重新组织表的物理存储，并重建索引，可以提高搜索效率。
使用缓存

可以使用MySQL Query Cache或者其他缓存技术来缓存搜索结果，减少数据库的压力。
避免使用LIKE

尽量使用Fulltext Search来代替LIKE，提高搜索效率。
使用合适的存储引擎

选择合适的存储引擎也很重要。 InnoDB支持事务和行级锁定，更适合高并发的场景。 MyISAM的全文索引性能可能略好一些，但不支持事务。

八、总结

今天我们深入探讨了MySQL的Fulltext Search，包括索引实现，TF-IDF算法以及一些优化技巧。希望通过这次讲座，你能对Fulltext Search有一个更深入的了解，并在实际项目中灵活运用。

Fulltext Search是一个强大而复杂的工具，需要不断学习和实践才能掌握。希望大家在学习的道路上越走越远！

如果有什么问题，欢迎提问！咱们下期再见！

发表回复 取消回复

发表回复取消回复