索引是应用程序设计和开发的一个重要方面。若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响。要找到一个合适的平衡点，这对应用程序的性能至关重要。

————《MySQL技术内幕INNODB存储引擎》


  • 概述
  • 倒排索引
  • lnnoDB 全文检索(没耐心别看)
  • 全文检索
  • Natural Language
  • Boolean
  • Query Expansion
  • 总结
  • 💖感谢各位得暴击三连~💖


概述

ssdss 根据据B+ 树索引的特性，类似 SELECT * FROM blog WHERE content like’ %xxx%’ 这样的SQL语句即便添加了B+ 树索引也是需要进行索引的扫描来得到结果。而这种全文进行查找，这些都不是B+ 树索引所能很好地完成的工作。

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

aassa注：在之前的MySQL 数据库中， InnoDB 存储引擎并不支持全文检索技术。大多数的用户转向MyISAM 存储引擎，这可能需要进行表的拆分，并将需要进行全文检索的数据存储为MyISAM 表。这样的确能够解决逻辑业务的需求，但是却丧失了InnoDB 存储引擎的事务性，而这在生产环境应用中同样是非常关键的。从lnnoDB 1.2.x 版本开始， InnoDB 存储引擎开始支持全文检索，其支持MylSAM存储引擎的全部功能，并且还支持其他的一些特性。

倒排索引

ssdss 全文检索通常使用 倒排索引(inverted index) 来现。倒排索引同B+ 树索引一样，也是一种索引结构。它在辅助表(auxiliary table) 中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。

ssdss这通常利用关联数组实现，其拥有两种表现形式：

ssdsdss①、inverted file index, 其表现形式为 {单词，单词所在文档的ID}

ssdsdss②、full inverted index, 其表现形式为｛单词，（单词所在文档的ID, 在具体文档中的位置）｝

aassaeg：t 存储的内容如下表所示

mysql全文检索后高亮 mysql全文检索性能_算法


aassaDocumentld 表示进行全文检索文档的Id, Text 表示存储的内容，用户需要对存储的这些文档内容进行全文检索。例如，查找出现过Some 单词的文档Id, 又或者查找单个文档中出现过两个Some 单词的文档Id。对于inverted file index 的关联数组，其存储的内容如下表：

mysql全文检索后高亮 mysql全文检索性能_mysql_02


aassa可以看到单词cold 存在于文档1 和4 中，单词days 存在与文档3 和6 中。 之后再要进行全文查询就简单了，可以直接根据Documents 得到包含查询关键字的文档。对于inverted file index, 其仅存取文档Id, 而full inverted index 存储的是对(pair), 即(Documentld, Position), 因此其存储的倒排索引如下表：

mysql全文检索后高亮 mysql全文检索性能_存储引擎_03


aassafull inverted index 还存储了单词所在的位置信息，如code 这个单词出现在(1 : 6),即文档l 的第6 个单词为code 。 相比之下， full inverted index 占用更多的空间，但是能更好地定位数据，并扩充一些其他的搜索特性。

lnnoDB 全文检索(没耐心别看)

ssdss InnoDB 存储引擎从1.2.x 版本开始支持全文检索的技术，其采用 full inverted index 的方式。在InnoDB 存储引擎中，将(Documentld, Position) 视为一个"ilist" 。因此在全文检索的表中，有两个列，一个是word 字段，另一个是ilist 字段，并且在word 字段上设有索引。此外，由于InnoDB 存储引擎在ilist 字段中存放了Position 信息，故可以进行Proximity Search, 而MyISAM 存储引擎不支持该特性。

ssdss 倒排索引需要将 word 存放到一张表中，这个表称为 Auxiliary Table (辅助表）。在InnoDB 存储引擎中，为了提高全文检索的并行性能，共有 6 张Auxiliary Table, 目前每张表根据word 的Latin 编码进行分区。

ssdss注1：Auxiliary Table 是持久的表，存放于磁盘上。 然而在InnoDB 存储引擎的全文索引中，还有另外一个重要的概念FTS Index Cache （ 全文检索索引 缓存 ），其用来提高全文检索的性能。FTS Index Cache 是一个红黑树结构，其根据(word, ilist) 进行排序。这意味着插入的数据已经更新了对应的表，但是对全文索引的更新可能在分词操作后还在FTS Index Cache 中， Auxiliary Table 可能还没有更新。InnoDB 存储引擎会批量对Auxiliary Table进行更新，而不是每次插入后更新一次Auxiliary Table 。当对全文检索进行查询时，Auxiliary Table 首先会将在FTS Index Cache 中对应的word 字段合并到Auxiliary Table中，然后再进行查询。 这种merge 操作非常类似之前介绍的Insert Buffer 的功能，不同的是Insert Buffer 是一个持久的对象，并且其是B+ 树的结构。然而FTS Index Cache 的作用又和Insert Buffer 是类似的，它提高了InnoDB 存储引擎的性能，并且由于其根据红黑树排序后进行批量插入，其产生的Auxiliary Table 相对较小。

ssdssInnoDB 存储引擎允许用户查看指定倒排索引的Auxiliary Table 中分词的信息，可以通过设置参数innodb_ft_aux_table 来观察倒排索引的Auxiliary Table 。

ssdss注2：对于其他数据库，如Oracle Ilg, 用户可以选择手工在事务提交时，或者固定间隔时间时将倒排索引的更新刷新到磁盘。对于InnoDB 存储引擎而言，其总是在事务提交时将分词写入到 FTS Index Cache, 然后再通过批量更新写入到磁盘。虽然InnoDB 存储引擎通过一种延时的、批量的写入方式来提高数据库的性能，但是上述操作仅在事务提交时发生。

ssdss注3：当数据库关闭时，在FTS Index Cache 中的数据库会同步到磁盘上的Auxiliary Table中。然而，如果当数据库发生宕机时，一些FTS Index Cache 中的数据库可能未被同步到磁盘上。那么下次重启数据库时，当用户对表进行全文检索（查询或者插入操作）时，InnoDB 存储引擎会自动读取未完成的文档，然后进行分词操作，再将分词的结果放入到 FTS Index Cache 中。

ssdss 参数 innodb_ft_cache_ size 用来控制 FTS Index Cache 的大小，默认值为32M 。当该缓存满时，会将其中的(word, ilist) 分词信息同步到磁盘的 Auxiliary Table 中。增大该参数可以提高全文检索的性能，但是在宕机时，未同步到磁盘中的索引信息可能需要更长的时间进行恢复。

ssdssFTS Document ID 是另外一个重要的概念。在InnoDB 存储引擎中，为了支持全文检索，必须有一个列与word 进行映射，在InnoDB 中这个列被命名为 FTS_DOC_ID, 其类型必须是BIGINT UNSIGNED NOT NULL, 并且InnoDB 存储引擎自动会在该列上加入一个名为FTS_DOC_ ID_ INDEX 的Unique Index 。上述这些操作都由InnoDB 存储引擎自己完成，用户也可以在建表时自动添加FTS_DOC_ID, 以及相应的Unique Index 。由于列名为FTS_DOC_ID 的列具有特殊意义，因此创建时必须注意相应的类型，否则MySQL 数据库会抛出错误，如

mysql> CREATE TABLE fts_a(
- > FTS_DOC_ID INT UNSIGNED AUTO_INCREMENT NOT NULL,  ×××× 改为：FTS_DOC_ID BIGINT UNSIGNED AUTO_ 工NCREMENT NOT NULL,
- > body TEXT,
- > PRIMARY KEY(FTS_DOC_ID)
- > );
ERROR 1166 (42000): Incorrect column name'FTS DOC ID'

ssdss 文档中分词的插入操作是在事务提交时完成，然而对于删除操作，其在事务提交时，不删除磁盘Auxiliary Table 中的记录，而只是删除FTS Cache Index 中的记录。对于Auxiliary Table 中被删除的记录， InnoDB 存储引擎会记录其FTS Document ID, 并将其保存在DELETED auxiliary table 中。在设置参数innodb_ft_aux_table 后，用户同样可以访问information_schema 架构下的表INNODB_FT_DELETED 来观察删除的FTS Document ID 。

ssdss注4：由于文档的DML 操作实际并不删除索引中的数据，相反还会在对应的DELETED表中插入记录，因此随着应用程序的允许，索引会变得非常大，即使索引中的有些数据已经被删除，查询也不会选择这类记录。为此， InnoDB 存储引擎提供了一种方式，允许用户手工地将已经删除的记录从索引中彻底删除，该命令就是OPTIMIZE TABLE 。因为OPTIMIZE TABLE 还会进行一些其他的操作，如Cardinality 的重新统计，若用户希望仅对倒排索引进行操作，那么可以通过参数innodb_optimize_full text_ only 进行设置.(若被删除的文档非常多，那么OPTIMIZE TABLE 操作可能需要占用非常多的时间，这会影响应用程序的并发性，并极大地降低用户的响应时间。用户可以通过参数innodb_ft_num_word_ optimize 来限制每次实际删除的分词数量。该参数的默认值为2000).

ssdss stopword 列表( stopword list)表示该列表中的 word 不需要对其进行索引分词操作。例如，对于the 这个单词，由于其不具有具体的意义，因此将其视为stopword 。InnoDB 存储引擎有一张默认的stopword 列表，其在information_schema 架构下，表名为INNODB_FT_DEFAULT_STOPWORD, 默认共有36 个stopword 。此外用户也可以通过参数innodb_ft—server_stopword table 来自定义stopword 列表。

ssdss当前InnoDB 存储引擎的全文检索还存在以下的限制：

sdssdss①、每张表只能有一个全文检索的索引。

sdssdss②、由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。

sdssdss③、不支持没有单词界定符(delimiter) 的语言，如中文、日语、韩语等。

全文检索

ssdss MySQL 数据库支持全文检索(Full-Text Search) 的查询，其语法为：

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

ssdss注5：MySQL 数据库通过MATCH() … AGAINST() 语法支持全文检索的查询， MATCH 指定了需要被查询的列， AGAINST 指定了使用何种方法去进行查询。下面将对各种查询模式进行详细的介绍。

Natural Language

ssdss 全文检索通过MATCH 函数进行查询，默认采用Natural Language 模式，其表示查询带有指定word 的文档。

ssdsseg：查询body 字段中带有Pease的文档，若不使用全文索引技术，则允许使用下述SQL 语句：

mysql> SELECT* FROM fts_a WHERE body LIKE'%Pease%';

sssssdss显然上述SQL 语句不能使用B+ 树索引。若采用全文检索技术，可以用下面的SQL语句进行查询：

mysql> SELECT * FROM fts_a
-> WHERE MATCH(body)
->AGAINST ('Porridge'IN NATURAL LANGUAGE MODE);
+------------+-----------------------------------------+
| FTS DOC ID | body
+------------+-----------------------------------------+
|			2 | Pease porridge hot, pease porridge cold I
|			1 | Pease porridge in the pot
+------------+-----------------------------------------+
2 rows in set (0.00 sec)

ssssdss由于NATURAL LANGUAGE MODE 是默认的全文检索查询模式，因此用户可以省略查询修饰符，即上述SQL 语句可以写为：

SELECT* FROM fts_a WHERE MATCH(body) AGAINST ('Porridge');

ssssdss若执行成果结果为：

*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: fts_a
type: fulltext		 %全文检索技术
possible keys : idx_fts	
key: idx_fts   			%索引的名字
key_len: 0
ref: NULL
rows: 1
Extra: Using where
1 row in set (0 . 00 sec)

ssdssss若表没有创建倒排索引，则执行MATCH 函数会抛出类似如下错误：

mysql> SELECT* FROM fts_b
- > WHERE MATCH(body) AGAINST ('Porridge');
ERROR 1191 (HYOOO): Can't find FULLTEXT index matching the column list

ssdss 在 WHERE 条件中使用MATCH 函数，查询返回的结果是根据相关性(Relevance) 进行降序排序的，即相关性最高的结果放在第一位。相关性的值是一个非负的浮点数字， 0 表示没有任何的相关性。根据MySQL 官方的文档可知，其相关性的计算依据以下四个条件：

ssdss ①、word 是否在文档中出现。

ssdss ②、word 在文档中出现的次数。

ssdss ③、word 在索引列中的数量。

ssdss ④、多少个文档包含该word 。

ssdss 用户可以通过SQL 语句查看相关性。

rnysql> SELECT fts_doc_id,body,
-> MATCH(body) AGAINST ('Porridge'IN NATURAL LANGUAGE MODE) AS Relevance
- > FROM fts_a;

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

ssdss ①、查询的word 在stopword 列中，忽略该字符串的查询。（如果词在stopword 中，则不对该词进行查询）。

ssdss ②、查询的word 的字符长度是否在区间 [ innodb ft_ min_ token_ size, innodb ft_ max_token size ］内。

sssssdss注：参数innodb_ft_min_token_size 和innodb_ ft_ max_ token_ size 控制InnoDB 存储引擎查询字符的长度，当长度小于 innodb_ft_ min_ token_ size, 或者长度大于innodb_ft_max_token_size 时，会忽略该词的搜索。在InnoDB 存储引擎中，参数innodb_ft_ min_ token_size 的默认值为 3 , 参数innodb_ ft_ max_ token_ size 的默认值为 84 。

Boolean

ssdssMySQL 数据库允许使用IN BOOLEAN MODE 修饰符来进行全文检索。当使用该修饰符时，查询字符串的前后字符会有特殊的含义。

ssdsseg1：要求查询有字符串Pease 但没有hot 的文档，其中＋和－分别表示这个单词必须出现，或者一定不存在。

mysql> SELECT * FROM fts_a
->WHERE MATCH(body) AGAINST ('+Pease -hot' IN BOOLEAN MODE)\G;

ssdssBoolean 全文检索支持以下几种操作符：

mysql全文检索后高亮 mysql全文检索性能_存储引擎_04

ssdsseg2：返回有pease 又有hot 的文档：

mysql> SELECT* FROM fts_a
-> WHERE MATCH(body) AGAINST ('+Pease +hot'IN BOOLEAN MODE)\G;
Query Expansion

ssdss MySQL 数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要implied knowledge (隐含知识）时进行。
ssdsseg：对于单词database 的查询，用户可能希望查询的不仅仅是包含database 的文档，可能还指那些包含MySQL 、Oracle 、DB2 、RDBMS的单词。而这时可以使用Query Expansion 模式来开启全文检索的 implied knowledge 。

ssdss使用：通过在查询短语中添加WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启 blind query expansion (又称为automatic relevance feedback) 。

ssdss该查询分为两个阶段：

ssdsdss①、根据搜索的单词进行全文索引查询。

ssdsdss②、根据第一阶段产生的分词再进行一次全文检索的查询。

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

总结

ssdss近几篇文章介绍了一些常用的数据结构，如二分查找树、平衡树、B+ 树、直接寻址表和哈希表，以及InnoDBl.2 版本开始支持的全文索引。从数据结构的角度切入数据库中常见的B+ 树索引和哈希索引的使用，并从内部机制上讨论了使用上述索引的环境和优化方法。

💖感谢各位得暴击三连~💖