我们前面介绍过mysql全文检索的一个插件Onesql,了解了全文检索

其实Mysql的Innodb引擎默认也是支持全文检索的，只支持英文。其背后的原理都是倒排索引

本文默认Mysql支持的全文检索

倒排索引 

倒排索引跟B+树一样，也是一种数据结构。

一般利用关联数组，在辅助表中存储单词与文档中所在位置的映射。

-- 创建索引 
CREATE TABLE test(
title VARCHAR(40),
FULLTEXT(title)
);
-- 插入数据
INSERT INTO test
VALUES('Some like it hot, Some like it cold'),
('Some like it in the pot'),
('Nine days old'),
('Pease porridge in the pot'),
('Pease porridage hot, pease porridge cold'),
('Nine days old');

然后查看一下information_schema下的INNODB_FT_INDEX_TABLE表.如果不允许访问

mysql 全文索引优化 弊端 mysql全文索引实现原理_倒排索引

就设置一下：SET GLOBAL innodb_ft_aux_table = 'test/test';

然后再查看一下INNODB_FT_INDEX_TABLE或者INNODB_FT_INDEX_CACHE表

mysql 全文索引优化 弊端 mysql全文索引实现原理_mysql 全文索引优化 弊端_02

会出现类似的记录，表明已经建立了映射关系

Innodb采用的是full inverted index的存储方式。这种方式会占用更多的空间，因为它不仅会存储单词和单词所在文档的ID，还会存储单词所在文档的ID中具体的位置。可以用一个简单的表格来解释

Number

Text

Documents

1

cold

(2:31),(6:36)

2

days

(4:5),(7:5)

3

hot

(2:13),(6:16)

4

like

(2:5)

相对的，还有一种存储方式：inverted file index，只存储单词及对应的单词所在文档。这种理节省空间，但是查找时，只能根据关键字得到相应文档，现进行查找

分词

通过上面的例子，我们发现，innodb会把单词拆分进行存储，查找时，根据单词匹配(默认是英文符号)

  • 但是有一些词，我们可能是不能索引查询的，比如'to',这称之为stopword;
-- 默认停止词
SELECT * FROM information_schema.INNODB_FT_DEFAULT_STOPWORD;

mysql 全文索引优化 弊端 mysql全文索引实现原理_倒排索引_03

  • 或者word的字符长度不在innodb_ft_min_token_size到innodb_ft_max_token_size。默认是3-84个字符区间
INSERT INTO test VALUES
-- 90字符
('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890'),
-- 80字符
('12345678901234567890123456789012345678901234567890123456789012345678901234567890');

插入一个80，90的字符长度，会现在只有80的字符被分词了：INNODB_FT_INDEX_CACHE表可查，

同理，也只有80的字符记录被索引

SELECT * FROM test
WHERE MATCH(title) AGAINST('12345678901234567890123456789012345678901234567890123456789012345678901234567890');

当然，也可以定制stopword，可以参考mysql stopwords

相关性

如果一个查询，匹配到多条记录，是怎么返回呢？根据相关性

-- 查询相关性
SELECT title, MATCH(title) AGAINST('like') AS relevance
FROM test

mysql 全文索引优化 弊端 mysql全文索引实现原理_倒排索引_04

发现只有前面2条记录的相关性>0，推断查询结果就是取相关性>0的记录，其实也正是如此。那相关性是怎么计算呢

  • word(查询关键字)是否在文档中出现 
  • word在文档中出现的次数
  • word在索引列中的数量
  • 多少个文档包含该word

所以Some like it hot, Some like it cold，出现了2次like，相关性高

Some like it in who pot出现了1次，相关性低

而其它记录没有相关性

检索模式
Natural Language

上面的例子我们是用的默认的检索模式，Natural Language模式！表示查询带有指定word的文档。下面2种方式是等价的

SELECT * FROM test WHERE MATCH(title) AGAINST('what' in NATURAL LANGUAGE MODE);
SELECT * FROM test WHERE MATCH(title) AGAINST('what');
Boolean

当使用这种模式时，表示字符串前后的字符有特殊含义。比如要查找有Pease单词的记录

SELECT * FROM test WHERE MATCH(title) AGAINST('+Pease' in BOOLEAN MODE);

mysql 全文索引优化 弊端 mysql全文索引实现原理_mysql 全文索引优化 弊端_05

假设，我们需要查找有Pease，但是没有hot的记录呢？用+,-符号，分别表示一定存在，或者一定不存在

SELECT * FROM test WHERE MATCH(title) AGAINST('+Pease -hot' in BOOLEAN MODE);