mysql/Mariadb开启全文检索fulltext配置以及大数据搜索优化

1.使用Mysql全文检索fulltext的先决条件

• 表的类型必须是MyISAM或者Mysql 5.6以上的InnoDB
• 建立全文检索的字段类型必须是char,varchar,text 　　

  2.建立全文检索先期配置

由于Mysql的默认配置索引的词的长度是4,所以要支持中文单字的话,首先要更改ft_min_word_len参数。如果改为2可能会出现搜索不到数字或者英文的情况，我们这里把它改成1,*Unix用户要修改my.cnf,一般此文件在/etc/my.cnf,如果没有找到,先查找一下find / -name 'my.cnf'

在[mysqld]位置内加入:

ft_min_word_len = 1

其它属性还有

ft_wordlist_charset = gbk
ft_wordlist_file = /home/soft/mysql/share/mysql/wordlist-gbk.txt
ft_stopword_file = /home/soft/mysql/share/mysql/stopwords-gbk.txt

解释一下上面的意思:

• ft_wordlist_charset 表示词典的字符集, 目前支持良好的有(UTF-8, gbk, gb2312, big5)
• ft_wordlist_file 是词表文件, 每行包括一个词及其词频(用若干制表符或空格分开,消岐专用)
• ft_stopword_file 表示过滤掉不索引的词表, 一行一个.
• ft_min_word_len 加入索引的词的最小长度, 默认为4, 为了支持中文单字故改为2 　　

  3.建立全文检索

在建表中用FullText关键字标识字段,已存在的表用ALTER TABLE (或 CREATE INDEX)创建索引

CREATE fulltext INDEX index_name ON table_name(colum_name);
# 如果是两个字段，下面的查询也只能是两个
CREATE fulltext INDEX index_name ON table_name(colum_name,colum_name1);

4.使用全文检索

在SELECT的WHERE字句中用MATCH函数,索引的关键词用AGAINST标识,其中IN BOOLEAN MODE表示只要含有关键字就行,不用在乎位置,是不是起启位置。

SELECT * FROM articles WHERE MATCH (tags) AGAINST ('旅游' IN BOOLEAN MODE);
# 如果上面CREATE是两个字段，下面的查询也只能是两个
SELECT * FROM articles WHERE MATCH (colum_name,colum_name1) AGAINST ('旅游' IN BOOLEAN MODE);

5、使用MATCH AGAINST和LIKE语句的对比

在小数据量的情况下，两者差距不大，但是如果用户量达到百万级，千万级以后，你会发现，使用like一次需要10几秒或者几十秒，但是如果你使用MATCH AGAINST全文索引，速度非常快，一般就1秒以内。