MYSQL全文索引(fulltext)模糊查询抛弃like

对于少量数据来说，使用like和全文索引进行模糊查询耗时看不出来太大差距，

但是一单数据量上去了（10w+条）你就会感觉崩溃了。。。。。

使用like "%key%"简直就是个噩梦，因为用双%会进行全表扫描，啧啧啧想想就恐怖的一批

消耗资源，mysqld占用超高CPU，耗费大量时间，虽然使用单%可以使用索引，但是匹配结果有问题。

今天看到数据库里面的数据，太可怕了，就想找个效率高一点的，看到了全文索引，试了一下，效果不错

因为数据中含有中文字符，所以推荐使用MYSQL5.7

MySql5.7 innodb支持中文全文索引

1、ngram and MeCab full-text parser plugins

全文检索在MySQL里面很早就支持了，只不过一直以来只支持英文。缘由是他从来都使用空格来作为分词的分隔符，而对于中文来讲，显然用空格就不合适，需要针对中文语义进行分词。但从MySQL 5.7开始，MySQL内置了ngram全文检索插件，用来支持中文分词，并且对MyISAM和InnoDB引擎有效。

2、必要的参数设置

在使用中文检索分词插件ngram之前，先得在MySQL配置文件里面设置他的分词大小（默认是2），比如，

[mysqld]

ngram_token_size=2

show VARIABLES like '%ft%';

使用上面语句可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引，而刚刚搜索的只有 aaaa 的长度大于等于 4。

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

分词的SIZE越小，索引的体积就越大，所以要根据自身情况来设置合适的大小。

创建索引的两种方式：

①建表指定

CREATE TABLE test(
    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT(title, body) WITH PARSER ngram
) ENGINE = InnoDB CHARACTER SET utf8mb4;

②alter插入

ALTER TABLE
    test ADD FULLTEXT INDEX testfulltext(clumn1, clumn2) WITH PARSER ngram;

#重新建立索引命令：repair table tablename quick

按自然语言搜索模式查询

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('关键词' IN NATURAL LANGUAGE MODE);

按布尔全文搜索模式查询

2.1 匹配既有管理又有数据库的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 +管理' IN BOOLEAN MODE);

2.2匹配有数据库，但是没有管理的记录

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+数据库 -管理' IN BOOLEAN MODE);

2.3匹配MySQL，但是把数据库的相关性降低

SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('>数据库 +MySQL' INBOOLEAN MODE);

• + 必须包含该词

• - 必须不包含该词

• > 提高该词的相关性，查询的结果靠前

• < 降低该词的相关性，查询的结果靠后

• (*)星号 通配符，只能接在词后面

性能对比测试

以上部分内容来自csdn