Jade Dungeon

MySQL全文检索

从 MySQL 5.6 开始，InnoDB 开始支持全文检索。

倒排索引

全文检索通常使用倒排索引（inverted index）来实现，倒排索引同 B+Tree 一样， 也是一种索引结构。

它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射， 这通常利用关联数组实现，拥有两种表现形式：

  • inverted file index：{单词，单词所在文档的id}
  • full inverted index：{单词，（单词所在文档的id，再具体文档中的位置）}
Number Text Documents
1 code 1,4
2 days 3,6
3 hot 1,4
4 in 2,5
5 it 4,5

上图为 inverted file index 关联数组，可以看到其中单词code存在于文档1,4中， 这样存储再进行全文查询就简单了，可以直接根据 Documents 得到包含查询关键字的文档；

而 full inverted index 存储的是对，即(DocumentId,Position)， 因此其存储的倒排索引如下图， 如关键字code存在于文档1的第6个单词和文档4的第8个单词。

Number Text Documents
1 code (1:6),(4:8)
2 days (3:2),(6:2)
3 hot (1:3),(4:4)
4 in (2:3),(5:4)
5 it (4:3,7),(5:3)

相比之下，full inverted index 占用了更多的空间，但是能更好的定位数据， 并扩充一些其他搜索特性。

建立全文索引

创建表jadedungeon.articles时创建content字段的全文索： 

CREATE TABLE `jadedungeon`.`articles` ( 
    `id`      INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, 
    `author`  VARCHAR(200), 
    `title`   VARCHAR(200), 
    `content` TEXT(500), 
    FULLTEXT `fulltext_content` (`content`) ) ENGINE=InnoDB;

已创建的表上创建title字段的全文索引语法如下： 

CREATE 
	FULLTEXT INDEX `fulltext_title` 
ON 
	`jadedungeon`.`articles`(`title`);

如果同时检索多个名字，那要加上多个字段的联合索引： 

CREATE 
	FULLTEXT INDEX `fulltext_title_body` 
ON 
	`jadedungeon`.`articles`(`title`,`content`);

删除全文索引

直接删除全文索引语法如下： 

DROP INDEX full_idx_name ON db_name.table_name;

使用 alter table 删除全文索引语法如下： 

ALTER TABLE db_name.table_name DROP INDEX full_idx_name;

使用全文索引

MySQL 数据库支持全文检索的查询，全文索引只能在 InnoDB 或 MyISAM 的表上使用， 并且只能用于创建 char,varchar,text 类型的列。

其语法如下： 

MATCH(col1, col2, ...) AGAINST(expr[search_modifier])

全文搜索使用MATCH() AGAINST()语法进行：

  • MATCH()采用逗号分隔的列表，命名要搜索的列。
  • AGAINST()接收一个要搜索的字符串，以及一个要执行的搜索类型的可选修饰符。 全文检索分为三种类型：
    • 自然语言搜索：
    • 布尔搜索：
    • 查询扩展搜索：

检索模式

自然语言搜索

自然语言搜索将搜索字符串解释为自然人类语言中的短语，是默认采用的模式，其表示查询带有指定关键字的文档。

例如查询查询title,body列中包含'MySQL'关键字的行数量： 

SELECT 
    COUNT(*) AS count 
FROM 
    `jadedungeon`.`articles` 
WHERE 
    MATCH (`title`, `content`) AGAINST ('MySQL');
count
6

同样的效果也可以写为： 

SELECT 
    COUNT(IF(MATCH (`title`, `content`) AGAINST ('MySQL'), 1, NULL)) AS count 
FROM 
    `jadedungeon`.`articles` ;
count
6

上述两种语句虽然得到的结果是一样的，但从内部运行来看， 第二句SQL的执行速度更快些，因为第一句SQL（基于where索引查询的方式） 还需要进行相关性的排序统计，而第二种方式是不需要的。

相关性

id author title content relevance
1 jade MySQL vs. YourSQL In the following database Oracle MySQL D 0.000000005657784907242558
2 jade MySQL Tutorial DBMS stans for Database. It enables cust 0.000000003771856604828372
3 teo Tuning DB2 For IBM database Oracle MySQL Database S 0.000000003771856604828372
4 jade IBM History DB2 history for IBM "Machine learning pu 0.000000001885928302414186
5 jade Optimizing MySQL In this tutorial we will show how Optimi 0.000000003771856604828372
6 teo MySQL Security When configured properly, MySql IDC stat 0.000000003771856604828372

相关性的计算依据以下四个条件：

  • word 是否在文档中出现
  • word 在文档中出现的次数
  • word 在索引列中的数量
  • 多少个文档包含该 word

InnoDB限制

对于 InnoDB 存储引擎的全文检索，还需要考虑以下的因素：

  • 查询的 word 在 stopword 列中，忽略该字符串的查询
  • 查询的 word 的字符长度是否在InnoDB引擎查询字符配置的长度范围内。
    • 参数innodb_ft_min_token_size控制最小长度，默认值是3。
    • 参数innodb_ft_min_token_size控制最大长度，默认值是84。

如果词在 stopword 中，则不对该词进行查询，如对'for'这个词进行查询，结果如下所示： 

SELECT 
    *,
    MATCH ( `content` ) AGAINST ('for') AS relevance 
FROM 
    `jadedungeon`.`articles` ;
id author title content relevance
1 jade MySQL vs. YourSQL In the following database Oracle MySQL D... 0
2 jade MySQL Tutorial DBMS stans for Database. It enables cust... 0
3 teo Tuning DB2 For IBM database Oracle MySQL Database S... 0
4 jade IBM History DB2 history for IBM "Machine learning pu... 0
5 jade Optimizing MySQL In this tutorial we will show how Optimi... 0
6 teo MySQL Security When configured properly, MySql IDC stat... 0

可以看到，'for'虽然在文档(2,4)中出现，但由于其是 stopword ,故其相关性为0。

布尔搜索

布尔搜索使用特殊查询语言的规则来解释搜索字符串，比如用+表示单词必需存在， -表示单词不能存在。

Boolean 全文检索支持的类型包括：

  • 没有操作符表示该 word 是可选的，但是如果出现，其相关性会更高
  • >：表示出现该单词时增加相关性
  • <：表示出现该单词时降低相关性
  • ~：表示允许出现该单词，但出现时相关性为负
  • +：表示该 word 必须存在
  • -：表示该 word 必须不存在
  • ""：表示严格匹配短语
  • *：表示以该单词开头的单词，如lik*,表示可以是 lik，like，likes
  • @distance表示查询的多个单词之间的距离是否在 distance 之内， distance 的单位是字节，这种全文检索的查询也称为 Proximity Search，

调整相关性

没有标识，代表 word 是可选的，如果出现，其相关性会更高： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE
    MATCH (`title`) AGAINST ('MySQL IBM' IN BOOLEAN MODE);

>与<可以增加与减少相关性。

查询同时包含 'MySQL'，'database'，'DBMS' 的行信息， 但不包含'DBMS'的行的相关性高于包含'DBMS'的行： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE 
    MATCH (`content`) AGAINST ('+MySQL +(>database <DBMS)' IN BOOLEAN MODE);

~设置相关性为负，查询包含 'MySQL' 的行，但如果该行同时包含 'database'，则降低相关性： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE 
    MATCH (`content`) AGAINST ('MySQL ~database' IN BOOLEAN MODE);

包含与排除

+查询的是包含；-查询是排除。

查询的是包含 'MySQL' 但不包含 'YourSQL' 的信息： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE
    MATCH (`title`) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

严格匹配与模糊匹配

""查询严格匹配短语'MySQL Security'的行信息。 

SELECT * FROM `jadedungeon`.`articles` 
WHERE 
    MATCH (`content`) AGAINST ('"MySQL Security"' IN BOOLEAN MODE);

*查询关键字中包含'My'的行信息： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE 
    MATCH (`content`) AGAINST ('My*' IN BOOLEAN MODE);

@查询字符串Pease和hot之间的距离需在30字节内： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE
    MATCH(context) AGAINST('"Pease hot"@30' IN BOOLEAN MODE)

查询 "DB2" ，"IBM"两个词之间的距离在3字节之内： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE
    MATCH (`title`) AGAINST ('"DB2 IBM"@3' IN BOOLEAN MODE);

查询扩展搜索

查询扩展搜索是对自然语言搜索的修改，这种查询通常在查询的关键词太短， 用户需要 implied knowledge（隐含知识）时进行，

例如，对于单词 database 的查询，用户可能希望查询的不仅仅是包含 database 的文档， 可能还指那些包含 MySQL、Oracle、RDBMS 的单词， 而这时可以使用 Query Expansion 模式来开启全文检索的 implied knowledge

通过在查询语句中添加 WITH QUERY EXPANSION / IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启 blind query expansion （又称为 automatic relevance feedback），该查询分为两个阶段：

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

扩展查询： 

SELECT * FROM `jadedungeon`.`articles` 
WHERE
    MATCH (`title`,`content`) AGAINST ('database' WITH QUERY expansion);

由于 Query Expansion 的全文检索可能带来许多非相关性的查询，因此在使用时， 用户可能需要非常谨慎。

© 2015 jade-dungeon.net. Valid CSS & XHTML
Website template by Arcsin