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



发表评论

表情(⊙o⊙)…
看不清楚?点图切换 Ctrl+Enter快速提交

网友评论(0)

sitemap