Ngram + FULLTEXT

Ngram + FULLTEXT

ngram + fulltext索引能把我遇到的一个需要耗时68秒的sql，优化到1秒以内。所以还是很有必要了解一下这个知识点的。先说下具体操作，然后在讲解下具体是什么。

我原来的sql: select from where name like '%钢坯%' and name like '%库存%'

优化过程包括两步：

1. 添加索引：

ALTER TABLE 表名
ADD FULLTEXT INDEX 索引名 (字段名)
WITH PARSER ngram

2. sql修改：select from where MATCH(字段名) AGAINST ('+钢坯 +库存' IN BOOLEAN MODE)

要像理解前面在做什么需要理解几个概念，什么是ngram，什么是fulltext索引，什么是倒排索引。

一、Ngram 到底在干什么

ngram = 把字符串切成连续滑动窗口的 n 个字符，并全部建索引

默认：

• ngram_token_size = 2

• 也就是 2-gram（bigram）

二、举个真实例子

假设一条数据：

钢坯库存周度

ngram_token_size = 2 时，索引内容是：

钢坯 坯库 库存 存周 周度

全部都会进 FULLTEXT 倒排索引

三、那为什么「3 个字」也能搜出来？

比如你搜：

AGAINST ('钢坯库' IN BOOLEAN MODE)

MySQL 内部是这样做的：

1 查询词也会被 按同样的 ngram 规则切分

钢坯库

切成：

钢坯 坯库

2.BOOLEAN MODE 默认是 OR 关系

AGAINST ('钢坯库' IN BOOLEAN MODE)

≈

包含「钢坯」或「坯库」

3.原文档中同时存在：

• 钢坯 ✔

• 坯库 ✔

所以命中

四、如果你强制 AND，会发生什么？

AGAINST ('+钢坯 +坯库' IN BOOLEAN MODE)

含义是：

• 必须有「钢坯」

• 必须有「坯库」

依然能命中同一条记录

五、那是不是「所有可能的组合」都建了索引？

不完全是

ngram 不会建这些：

钢库 （非连续） 库存钢 （顺序颠倒）

只建 连续子串

这点非常重要：

ngram ≠ 模糊匹配

它是「连续切片」

FULLTEXT 索引

ngram 只能配合 FULLTEXT 索引使用。

一、什么是 FULLTEXT 索引？

FULLTEXT 会先使用分词器（parser）把文本拆成一组 token，
建立「token → 文档」的倒排索引；
查询时，搜索词也会用同样的分词规则拆成 token，
然后基于这些 token 做匹配（AND / OR / NOT / 前缀），
最终返回匹配文档，并计算相关度（score）。

二、怎么使用FULLTEXT

AGAINST 是使用 Fulltext索引的专有语法，如果不适用AGAINST，mysql会忽略Fulltext索引，即使你的表上有这个索引，它也会当作没看到。

AGAINST有两种用法

1. MATCH(col) AGAINST('xxx')

2. MATCH(col) AGAINST('xxx' IN BOOLEAN MODE)

区别就在于是否携带IN BOOLEAN MODE这个参数，如果不携带这个参数会实现类似于搜索引擎自然搜索的效果，比如搜索钢坯库存的时候结果可能是：

• 只包含「钢坯」

• 只包含「库存」

• 两个都包含

而携带上IN BOOLEAN MODE之后，可以严格制定匹配规则。

符号	含义
+词	必须包含
-词	不能包含
词*	前缀匹配
"词1 词2"	短语
()	分组

比如+钢坯 +库存表示同时包含钢坯 和 库存这两个token的记录才会被搜索到。+库存* 是必须以库存开头的token才会被搜索到。这里要注意是token而不是记录。

比如 钢坯库存，有两个token，钢坯，库存，那么这个就是符合条件的。因为第二个token确实是以库存开头的。

如果不带+，那么所有记录都会被搜索到，只是包含库存的记录大分更高，排名更靠前。

倒排索引

上面的查询之所以这么快就是因为采用了倒排索引的方式，简单举个例子说明一下什么是倒排索引。

普通索引

我们先看下普通的索引，假设数据如下

id=1 → 钢坯库存
id=2 → 钢坯价格
id=3 → 螺纹钢价格

查询sql：WHERE index_short_name LIKE '%钢坯%' ，由于只有前缀匹配（'钢坯%'）会走索引，而全匹配不会走索引，所以数据库只能一行一行比较，无法提前知道哪些记录能命中，数据量很大的时候耗时会很严重。

倒排索引

然后再看下倒排索引，数据库会建立这样一个倒排表

token	出现在哪些文档
钢坯	1, 2
坯库	1
库存	1
坯价	2
价格	2, 3
螺纹	3
纹钢	3
钢价	3

当你搜索时使用：MATCH(index_short_name) AGAINST('钢坯')。数据库的流程是这样的：

• 分词 → 钢坯

• 查倒排表

• 直接拿到文档 {1,2}

• 回表取数据

这样的话，速度就会非常快了。

现在市面上很多非关系型数据库其实也是使用类似的原理，比如 Elasticsearch，所以比传统数据库查询大数据的话快上很多。