全文索引介绍和示例

参考整理自：

https://www.cnblogs.com/jimmy-muyuan/p/5874410.html

https://www.cnblogs.com/php-linux/p/6565268.html

全文索引

MySQL支持全文索引(Full-Text) ，目前，fulltext是一种只适用于MyISAM表的一个索引类型，而且对定义索引列的数据类型也有限制，只能是以下三种的组合char、 varchar、text。fulltext可以在创建表的同时就一起定义好，或者在表创建完成之后，通过语句alter table或create index来追加索引，总之先后的效果是一样的，但是两者的效率却是存在很大差异的，大量的实验证明，对于大数量的表来说，先加载数据再来定义全文索引的 速度要远远优于在一个已经定义好全文索引的表里面插入大量数据的速度。一定会问：这是问什么呢？其实，道理很简单，前者只需要一次性对你的索引列表进行操 作，排序比较都是在内存中完成，然后写入硬盘；后者则要一条一条去硬盘中读取索引表然后再进行比较最后写入，自然这样速度就会很慢。MySQL是 通过match()和against()这两个函数来实现它的全文索引查询的功能。match()中的字段名称要和fulltext中定义的字段一致，如 果采用boolean模式搜索，也允许只包括fulltext中的某个字段，不需要全部列出。against()中定义的是所要搜索的字符串以及要求数据 库通过哪种模式去执行全文索引的搜索查询

全文索引的限制
MATCH() 函数的所有参数必须是从来自于同一张表的列，同时必须是同一个FULLTEXT 索引中的一部分，除非 MATCH() 是 IN BOOLEAN MODE 的。

MATCH() 列列表必须确切地匹配表的某一 FULLTEXT 索引中定义的列列表，除非 MATCH() 是 IN BOOLEAN MODE 的。

AGAINST() 的参数必须是一个常量字符串。

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

    1）表的类型必须是MyISAM        

    2）建立全文检索的字段类型必须是char,varchar,text

2.建立全文检索先期配置
   由于Mysql的默认配置是索引的词的长度是4,所以要支持中文单字的话,首先更改这个.
   Unix用户要修改my.cnf,一般此文件在/etc/my.cnf,如果没有找到,先查找一下find / -name 'my.cnf'
     在 [mysqld] 位置内加入:  
       ft_min_word_len     = 2 
其它属性还有
       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);

4.使用全文检索
   在SELECT的WHERE字句中用MATCH函数,索引的关键词用AGAINST标识,IN BOOLEAN MODE是只有含有关键字就行,不用在 乎位置,是不是起启位置.

   SELECT * FROM articles WHERE MATCH (tags) AGAINST ('旅游' IN BOOLEAN MODE);

示例：

新建一个utf8 MyISAM类型的表并建立一个全文索引  ：

CREATE TABLE articles (

    id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

    title VARCHAR(200),   

    body TEXT,  

    FULLTEXT (title,body)

) ENGINE=MyISAM  DEFAULT >

其中FULLTEXT(title, body) 给title和body这两列建立全文索引，之后检索的时候注意必须同时指定这两列。

给这个表添加点测试数据

INSERT INTO articles (title,body) VALUES

  ('MySQL Tutorial','DBMS stands for DataBase ...'),

   ('How To Use MySQL Well','After you went through a ...'),

    ('Optimizing MySQL','In this tutorial we will show ...'),

    ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),

   ('MySQL vs. YourSQL','In the following database comparison ...'),

   ('MySQL Security','When configured properly, MySQL ...');

3. 全文检索测试

1. SELECT * FROM articles   WHERE MATCH (title,body) AGAINST ('database'); 

注意 MATCH (title,body) 里面的值必须是前面建立全文索引的两个字段不能少。

mysql 默认支持全文检索的字符长度是4，可以用SHOW VARIABLES LIKE 'ft_min_word_len' 来查看指定的字符长度，也可以在mysql配置文件my.ini 更改最小字符长度，方法是在my.ini 增加一行 比如：ft_min_word_len = 2，改完后重启mysql即可。  

另外，MySQL还会计算一个词的权值，以决定是否出现在结果集中，具体如下：

mysql在集合查询中的对每个合适的词都会先计算它们的权重，一个出现在多个文档中的词将有较低的权重(可能甚至有一个零权重)，因为在这个特定的集中，它有较低的语义值。否则，如果词是较少的，它将得到一个较高的权重，mysql默认的阀值是50%，上面‘you’在每个文档都出现，因此是100%，只有低于50%的才会出现在结果集中。也就是说，有可能有些太常见词可能不会出现在结果集中，因为他不满足这个要求

全文索引会有更多的碎片，可能需要做更多的optimize Table操作

什么是optimize Table呢，我们来看看手册中关于 OPTIMIZE 的描述：

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表（含有VARCHAR, BLOB或TEXT列的表）进行了很多更

改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用

OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

在多数的设置中，您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新，您也不需要经常运行，每周

一次或每月一次即可，只对特定的表运行。

OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁定表