Mysql全文索引实现模糊查询

导语

基本上所有的产品都离不开模糊搜索，无论是C端的社交产品、或者B端的一些SaaS服务。解决模糊搜索，我们最典型的解决方案是大家都可以想到的，使用SQL的like功能来实现，如下：

SELECT * FROM t_demo WHERE col_name LIKE '%aaa%';

问题场景

众所周知的问题是，LIKE命令在数据量大的时候性能特别低，甚至大数据量下的一个LIKE查询可以拖垮整个DB，这是因为LIKE语句是不能利用索引的。所以基本上所有的生产环境的DB都会关闭LIKE命令，取而代之的是通过mysql->binlog->canal->elasticsearch这种方式来实现搜索功能。

当然，借助canal和es来实现搜索是在大数据量下一个很常见的解决方案，那至于如何采用这种方式来实现搜索不是本文要说明的问题，有需要的可以根据关键字再去找找。这儿要说的是另一个场景，因为考虑生产DB的稳定性，运维关闭了数据库的LIKE功能，但同时我们有个搜索需求，这个搜索的数据量特别低的时候，比如模糊搜索商品的类别（几千/几万个）的这种需求。如果也借用ES这一套来做当然是可以的，但是从开发时间和精力的角度，显然有点得不偿失，那么在这种场景下，我们如何实现模糊搜索。

全文索引

全文索引，在MyISAM中早已支持，但是现在基本上大家用的都是InnoDB，而InnoDB对于FULLTEXT索引的支持是从MySQL5.6新引入的特性。

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。但是从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中、日、韩文的分词。 本文使用的MySQL 版本是5.7.18，采用InnoDB数据库引擎。

首先，我们来看一下ngram，ngram是来自文本序列的多个字符的连续序列，其中n表示n个字符的连续序列。而ngram全文解析器，作为内置的服务器插件，这意味着当MySQL数据库服务器启动时，MySQL会自动加载该插件。该插件主要功能是将文本序列标记为n个字符的连续序列。下面例子说明了ngram全文解析器如何进行标记文本，例如，使用ngram对今天真好进行分词：

n=1: '今', '天', '真', '好' 
n=2: '今天', '天真', '真好' 
n=3: '今天真', '天真好' 
n=4: '今天真好'

上面的例子，展示了在n取值分别为1、2、3、4时对“今天真好”这句话的分词。

在MySQL中，使用全局变量ngram_token_size来配置ngram中n的大小，它的取值范围是1到10，默认值是2。

ngram_token_size设置的是查询的单词最小字数，也就是如果在默认值是2的情况下，搜索单字是得不到任何结果的。譬如上单独搜索'今','天','真','好'这四个字都是拿不到结果的。如果需要搜索单字，需要把ngram_token_size设置为1。因为中文单词最少是两个汉字，推荐使用默认值2，不过这个也看使用场景，很多时候单字搜索也是必要的。

启动方法

配置mysql的ngram，打开mysql server的配置文件，编辑在[mysqld]下面加入这样的配置

# vim /etc/my.cnf
[mysqld]
ngram_token_size=2

保存退出，并重启mysql

# service mysql restart

再登入mysql，并通过命令查看：

mysql> show variables like 'ngram_token_size%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| ngram_token_size | 1     |
+------------------+-------+

创建测试表

下面我们创建一个开启了全文索引的表

CREATE TABLE `t_testfulltext` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(128) DEFAULT NULL,
  `data` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_index` (`name`) /*!50100 WITH PARSER `ngram` */ 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

上面我们创建了一个InnoDB引擎的表t_testfulltext，同时对表中的name字段添加了全文索引。

接下来我们插入几条数据到表中，如下：

+----+-----------------------+------+
| id | name                  | data |
+----+-----------------------+------+
|  1 | 今天真好              | NULL |
|  2 | 今天不好              | NULL |
|  3 | 明天真好              | NULL |
|  4 | 昨天不好              | NULL |
|  5 | 今天天气真好          | NULL |
|  6 | 今天天气真不好        | NULL |
|  7 | 昨天天气真好          | NULL |
|  8 | 昨天天气真好          | NULL |
|  9 | 昨天天气真好          | NULL |
+----+-----------------------+------+

查询语句

与LIKE不同，全文索引有着自己的语法，通过MATCH...AGAINST...来实现。

mysql> SELECT * FROM t_testfulltext WHERE MATCH (name) AGAINST ('今天');

ngram=1时结果

变更ngram方法

注意，当我们打开mysql server的配置文件，并更新ngram_token_size的配置之后，重启mysql server后，为了使新的ngram生效，需要重建索引才能生效。当然最简单的方式就是删除之前的索引，并重新创建全文索引即可。