发布于 由admin

MySQL高阶讲座之：`MySQL`的`Fulltext Search`：其在`InnoDB`中的实现与`N-Gram`解析器。

各位观众老爷，大家好！欢迎来到“MySQL高阶讲座”！今天，咱们要聊聊MySQL里的文本搜索大杀器——Fulltext Search。不过，今天咱们要玩点高级的，深入到InnoDB存储引擎的底层，再扒一扒N-Gram解析器的皮。准备好了吗？Let’s go！

一、 Fulltext Search：文本搜索，so easy?

话说，当咱们需要在一个文本字段里找东西的时候，LIKE '%keyword%'是不是大家的第一反应？ 这招在数据量小的时候勉强凑合，但一旦数据量上去了，那查询速度，简直就是蜗牛爬。 为啥？因为LIKE是全表扫描啊，一条条记录比对，效率低得令人发指。

Fulltext Search就是来拯救大家的。它通过建立倒排索引（Inverted Index），大大提高了文本搜索的速度。 简单来说，倒排索引就是把文档里出现的词语（term）和包含这些词语的文档ID对应起来。 这样，当咱们搜索某个词语时，直接从索引里找到包含这个词语的文档ID，然后取出对应的文档，速度自然就快多了。

举个例子，假设咱们有个articles表，包含id和content两个字段：

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

INSERT INTO articles (content) VALUES
('MySQL is a powerful database.'),
('InnoDB is the default storage engine in MySQL.'),
('Fulltext search is a useful feature in MySQL.'),
('N-Gram parser is important for fulltext search.');

然后，咱们创建一个fulltext索引：

CREATE FULLTEXT INDEX idx_content ON articles (content);

现在，咱们就可以使用MATCH...AGAINST来进行全文搜索了：

SELECT id, content FROM articles WHERE MATCH(content) AGAINST('MySQL' IN NATURAL LANGUAGE MODE);

这条SQL语句会返回包含“MySQL”的文章。 注意，IN NATURAL LANGUAGE MODE是默认模式，表示自然语言模式。 还有其他模式，比如IN BOOLEAN MODE，可以支持更复杂的搜索语法，比如+表示必须包含，-表示必须排除。

二、 InnoDB中的Fulltext Search：底层探秘

InnoDB从MySQL 5.6版本开始支持Fulltext Search。 它使用一系列的内部表来存储索引数据。 这些表都位于INFORMATION_SCHEMA数据库中，咱们可以通过查询这些表来了解InnoDB的Fulltext Search的内部结构。

主要有以下几个表：

  • INNODB_FT_DEFAULT_STOPWORD： 存储默认的停用词（Stopwords）。 停用词是指那些在文本中频繁出现，但对搜索意义不大的词语，比如“is”、“a”、“the”等。 InnoDB在建立索引时会忽略这些停用词。

  • INNODB_FT_DELETED： 存储已删除的文档ID。 当文档被删除时，对应的索引项并不会立即删除，而是被标记为已删除。 这是为了提高删除操作的性能。

  • INNODB_FT_CONFIG： 存储Fulltext Search的配置信息。

  • INNODB_FT_INDEX_CACHE： 存储Fulltext索引的缓存数据。 InnoDB会将一部分索引数据缓存在内存中，以提高搜索性能。

  • INNODB_FT_INDEX_TABLE： 存储实际的Fulltext索引数据。 这个表是Fulltext Search的核心，它存储了词语（term）和包含这些词语的文档ID的对应关系。

咱们可以通过以下SQL语句来查看这些表的内容：

SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;

不过，INNODB_FT_INDEX_TABLE表的内容是经过编码的，直接查看可能看不懂。 需要通过MySQL的内部函数来解析。

InnoDB Fulltext Search的工作流程：

  1. 分词（Tokenization）： 将文本分割成一个个的词语（term）。 InnoDB默认使用空格作为分隔符。

  2. 停用词过滤（Stopword Removal）： 移除停用词。

  3. 词干提取（Stemming）： 将词语还原成词根。 InnoDB默认不支持词干提取。

  4. 建立倒排索引（Inverted Index）： 将词语和包含这些词语的文档ID对应起来，存储到INNODB_FT_INDEX_TABLE表中。

  5. 搜索： 根据搜索条件，从倒排索引中找到对应的文档ID，然后取出对应的文档。

三、 N-Gram解析器：中文分词的救星

InnoDB默认的分词器对英文支持较好，因为英文单词之间有空格作为分隔符。 但是，对于中文来说，词语之间没有空格，所以需要使用专门的中文分词器。

N-Gram解析器就是一种常用的中文分词器。 它的原理很简单，就是将文本分割成长度为N的子串。 比如，对于文本“我是中国人”，如果N=2，那么N-Gram解析器会将其分割成“我是”、“是中”、“中国”、“国人”四个子串。

N-Gram解析器的优点是简单易用，不需要词典。 缺点是分词精度不高，可能会产生一些无意义的子串。

在MySQL中使用N-Gram解析器：

MySQL 5.7.6及以上版本提供了ngram全文解析器插件，支持中文分词。

  1. 安装ngram插件：
INSTALL PLUGIN ngram SONAME 'ngram.so';
  1. 创建fulltext索引时指定ngram解析器：
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    content TEXT
);

INSERT INTO articles (content) VALUES
('我是中国人'),
('我喜欢MySQL数据库'),
('全文搜索功能很强大'),
('N-Gram解析器用于中文分词');

CREATE FULLTEXT INDEX idx_content ON articles (content) WITH PARSER ngram;
  1. 使用MATCH...AGAINST进行中文搜索：
SELECT id, content FROM articles WHERE MATCH(content) AGAINST('中国' IN NATURAL LANGUAGE MODE);

这条SQL语句会返回包含“中国”的文章。

ngram解析器的配置：

ngram解析器有一些配置参数，可以通过修改MySQL的配置文件（my.cnf）来调整。

  • ngram_token_size： 指定N的值，即子串的长度。 默认值为2。 可以根据实际情况调整。 值越大，分词精度越高，但索引大小也会增加。

例如，修改ngram_token_size为3：

[mysqld]
ngram_token_size=3

修改配置文件后，需要重启MySQL服务。

N-Gram解析器的局限性：

  • 分词精度不高： N-Gram解析器只是简单地将文本分割成子串，没有考虑词语的语义和上下文关系，所以分词精度不高。

  • 索引大小较大： 由于N-Gram解析器会将文本分割成大量的子串，所以索引大小会比较大。

四、 Fulltext Search的优化：

Fulltext Search的性能优化是一个复杂的话题，涉及到很多方面。 这里简单介绍几个常用的优化技巧：

  • 调整ft_min_word_len和ft_max_word_len参数： 这两个参数分别指定了最小和最大索引词语的长度。 默认情况下，ft_min_word_len为4，ft_max_word_len为84。 可以根据实际情况调整这两个参数，以减少索引的大小和提高搜索效率。

  • 使用OPTIMIZE TABLE命令： OPTIMIZE TABLE命令可以重建Fulltext索引，优化索引结构，提高搜索性能。

  • 使用IN BOOLEAN MODE进行更精确的搜索： IN BOOLEAN MODE支持更复杂的搜索语法，可以进行更精确的搜索。 比如，可以使用+表示必须包含，-表示必须排除，*表示通配符。

  • 定期更新Fulltext索引： 当数据发生变化时，需要定期更新Fulltext索引，以保证搜索结果的准确性。

  • 使用缓存： 可以使用MySQL的查询缓存或者第三方缓存工具，来缓存Fulltext Search的结果，提高搜索性能。

五、 实战案例：

假设咱们要在一个电商网站的商品描述中搜索商品。 商品表products包含id、name和description三个字段。

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    description TEXT
);

CREATE FULLTEXT INDEX idx_description ON products (description) WITH PARSER ngram;

现在，咱们要搜索包含“手机”和“智能”的商品：

SELECT id, name FROM products WHERE MATCH(description) AGAINST('+手机 +智能' IN BOOLEAN MODE);

这条SQL语句会返回description字段中既包含“手机”又包含“智能”的商品。

六、 总结：

Fulltext Search是MySQL提供的一种强大的文本搜索功能。 通过建立倒排索引，可以大大提高文本搜索的速度。 InnoDB存储引擎从MySQL 5.6版本开始支持Fulltext Search。 N-Gram解析器是一种常用的中文分词器，可以用于中文文本的Fulltext Search。 通过调整配置参数和使用优化技巧，可以进一步提高Fulltext Search的性能。

七、 扩展阅读：

八、 答疑环节：

（此处省略N个问题和答案，因为我不知道你们会问啥，哈哈！）

好了，今天的讲座就到这里。 希望大家有所收获！ 下次再见！ 感谢各位的观看！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注