mysql全文索引_mysql的全文索引

        众所周知，使用like '%xxx%'进行模糊查询时，字段的索引就会失效。因此，在数据量大的情况下，通过此种方式查询的效率极低。这个时候，就可通过全文索引（Full-Text Search）来进行优化。

        全文索引（Full-Text Search）是将存储于数据库中的整本书或整篇文章中的任意信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。

        全文索引一般是通过倒排索引实现的。

一、倒排索引

        倒排索引同 B+Tree 一样，也是一种索引结构。它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射，这通常利用关联数组实现，拥有两种表现形式：

        inverted file index：{单词，单词所在文档的id}

        full inverted index：{单词，（单词所在文档的id，再具体文档中的位置）}

创建删除全文索引

若需对大量数据设置全文索引，建议先添加数据再创建索引。

1、创建表时创建全文索引

create table 表名(字段名1,字段名2,字段名3,字段名4,FULLTEXT full_index_name (字段名))ENGINE=InnoDB;

2、为已有表添加全文索引

create fulltext index 索引名称 on 表名(字段名)

3、删除全文索引

alter table 表名
    drop index 索引名;

二、使用全文索引

语法：

MATCH(col1,col2,...) AGAINST(expr[search_modifier])
search_modifier:
{
    IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
}

全文索引分为三种类型：自然语言的全文索引（Natural Language）、布尔全文索引（Boolean）和查询扩展搜索。

在不使用in ... mode的情况下，默认采用的是自然语言的全文索引。

1、自然语言的全文索引

        自然语言的全文索引可以计算每个文档对象和查询的相关性。

 
SELECT
    *,
    MATCH (字段名) against ( '需要符合的内容' ) AS Relevance 
FROM
    表名;

相关性主要与以下条件有关：

• word 是否在文档中出现

• word 在文档中出现的次数

• word 在索引列中的数量

• 多少个文档包含该 word

2、布尔全文索引

        布尔搜索使用特殊查询语言的规则来解释搜索字符串，该字符串包含要搜索的词，它还可以包含指定要求的运算符，例如匹配行中必须存在或不存在某个词，或者它的权重应高于或低于通常情况。

Boolean 全文检索支持的类型（可以通过ft_boolean_syntax变量来查看）包括：

• +：表示该 word 必须存在

• -：表示该 word 必须不存在

• (no operator)表示该 word 是可选的，但是如果出现，其相关性会更高

• @distance表示查询的多个单词之间的距离是否在 distance 之内，distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，如 MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)语句表示字符串 Pease 和 hot 之间的距离需在30字节内

• >：表示出现该单词时增加相关性

• <：表示出现该单词时降低相关性

• ~：表示允许出现该单词，但出现时相关性为负

• * ：表示以该单词开头的单词，如 lik*,表示可以是 lik，like，likes

• " ：表示短语

例子：

--+ -
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL -YourSQL' IN BOOLEAN MODE );
 
--no operator
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL IBM' IN BOOLEAN MODE );
 
--@
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '"DB2 IBM"@3' IN BOOLEAN MODE );
 
 
--> <
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '+MySQL +(>database <DBMS)' IN BOOLEAN MODE );
 
--~
 
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'MySQL ~database' IN BOOLEAN MODE );
 
--*
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( 'My*' IN BOOLEAN MODE );
 
--"
SELECT
    * 
FROM
    `fts_articles` 
WHERE
    MATCH ( title, body ) AGAINST ( '"MySQL Security"' IN BOOLEAN MODE );

3、查询扩展搜索

查询扩展相当于在自然语言索引的结果上，基于结果的关键字再进行一次查询。也就是说分成两个阶段：

• 第一阶段：根据搜索的单词进行全文索引查询

• 第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询

用法：

SELECT column1, column2
FROM table1
WHERE MATCH(column1,column2)
      AGAINST('keyword',WITH QUERY EXPANSION);

三、注意点

1、自然语言全文索引创建索引时的字段需与查询的字段保持一致，即MATCH里的字段必须和FULLTEXT里的一模一样；

2、自然语言检索时，检索的关键字在所有数据中不能超过50%（即常见词），则不会检索出结果。可以通过布尔检索查询；

3、在mysql的stopword中的单词检索不出结果。可通过SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD查询所有的stopword。遇到这种情况，有两种解决办法：

（1）stopword一般是mysql自建的，但可以通过设置ft_stopword_file变量为自定义文件，从而自己设置stopword，设置完成后需要重新创建索引。但不建议使用这种方法；

（2）使用布尔索引查询。

4、小于最短长度和大于最长长度的关键词无法查出结果。可以通过设置对应的变量来改变长度限制，修改后需要重新创建索引。

myisam引擎下对应的变量名为ft_min_word_len和ft_max_word_len

innodb引擎下对应的变量名为innodb_ft_min_token_size和innodb_ft_max_token_size

5、MySQL 5.7.6 之前的版本不支持中文，需使用第三方插件

6、全文索引只能在 InnoDB（MySQL 5.6以后） 或 MyISAM 的表上使用，并且只能用于创建 char,varchar,text 类型的列。