InnoDB全文索引

### 如果想了解全文索引，可以直接将本文复制到mysql的新建查询中，依次执行，即可了解全文索引的相关内容及特性。-- InnoDB全文索引-- 建表
CREATE TABLE fts_a (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY(FTS_DOC_ID)
);-- 插入数据
INSERT INTO fts_a
SELECT NULL, 'Please poorridge in the pot';
INSERT INTO fts_a
SELECT NULL, 'Please poorridge hot, pease porridge cold';
INSERT INTO fts_a
SELECT NULL, 'Nine days old';
INSERT INTO fts_a
SELECT NULL, 'Some like it hot, some like it cold';
INSERT INTO fts_a
SELECT NULL, 'Some like it in the pot';
INSERT INTO fts_a
SELECT NULL, 'Nine days old';
INSERT INTO fts_a
SELECT NULL, 'I like code days';-- 建立全文索引
CREATE FULLTEXT INDEX idx_fts ON fts_a(body);-- 创建完成后观察表fts_a中数据
SELECT * FROM fts_a;-- 通过设置参数innodb_ft_aux_table来查看分词对应的信息
SET GLOBAL innodb_ft_aux_table="mytest/fts_a";   -- 查看分词对应的信息
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;-- 每个word都对应一个DOC_ID 和 POSITION， （7,7）第一个7代表了第7个文档的，第二个7代表在文档中的起始位置
-- 除此之外还有FIRST_DOC_ID, LAST_DOC_ID, DOC_COUNT 分别代表了该word第一次出现的文档ID，最后一次出现的文档ID，以及该word一共在多少个文档中存在。-- 此时我们删除ID为7的文档
DELETE FROM mytest.fts_a WHERE FTS_DOC_ID=7;-- 此时再次查看分词对应的信息，发现分词信息未发生变化
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;-- 这是因为InnoDB并不会直接删除索引中对应的记录，而是将删除的文档ID插入到DELETED表，因此我们进行如下查询：
SELECT * FROM information_schema.INNODB_FT_DELETED;-- 可以看到删除的文档ID已经插入到了INNODB_FT_DELETED表中，这样看将来索引只会越来越多而不会减少，如果想要彻底删除倒排索引中该文档的分词信息，可以执行如下操作：-- 设置此参数为1，将仅进行倒排索引的操作，否则还会对Cardinality重新统计等其它操作
SET GLOBAL innodb_optimize_fulltext_only=1;  -- 彻底删除索引
OPTIMIZE TABLE mytest.fts_a;
-- 此时第三次查看分词对应的信息，发现分词信息已经更新
SELECT * FROM information_schema.INNODB_FT_INDEX_TABLE;
-- 但是，INNODB_FT_DELETED中的记录仍在：
SELECT * FROM information_schema.INNODB_FT_DELETED;
-- 并且，会将彻底删除的文档ID记录到表INNNODB_FT_BEING_DELETED中。
SELECT * FROM information_schema.INNODB_FT_BEING_DELETED;
-- 并且不允许再次插入这个文档ID，否则数据库会异常 // Invalid InnoDB FTS Doc ID
INSERT INTO fts_a
SELECT 7, 'I like code days';-- 还有一个概念，stopword列表，该列表中的word不需要进行索引分词操作。比如 the 这个单词没有实际意义，故不进行分词。
-- InnoDB有一张默认的stopword表，在information_schema架构下，表名为INNODB_FT_DEFAULT_STOPWORD, 默认共有36个stopword。
-- 你也可以通过参数innodb_ft_server_stopword_table来自定义stopword列表，如：
CREATE TABLE user_stopword(
value VARCHAR(30)
)ENGINE = INNODB;SET GLOBAL innodb_ft_server_stopword_table="mytest/user_stopword";-- 当前InnoDB的全文索引还存在以下限制：v5.6
-- 每张表只能有一个全文检索的索引
-- 由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则
-- 不支持没有单词界定符的语言，如中文、日语、韩语-- 全文检索的使用1（Natural Language）-- 普通查询
SELECT * FROM fts_a WHERE body LIKE '%Please%';  -- explain查看 type ALL
-- 使用全文检索，根据相关性进行降序排序（如果未建立全文索引，使用下面两条语句会抛出异常can't find FULLTEXT）
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('porridge' IN NATURAL LANGUAGE MODE);  -- explain查看 type fulltext
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('porridge'); -- IN NATURAL LANGUAGE MODE 是默认模式，可以省略
-- 你会发现FTS_DOC_ID为2 的排在第一位，这是由于porridge在文档2中出现了2次，具有更高的相关性
-- 相关性四个条件：
-- word是否在文档中出现
-- word在文档中出现的次数
-- word在索引列中的数量
-- 多少个文档包含该word-- 统计MATCH函数得到的结果数量
SELECT count(*) FROM fts_a WHERE MATCH(body) AGAINST ('Porridge' IN NATURAL LANGUAGE MODE);
SELECT COUNT(IF(MATCH (body) AGAINST ('Porridge' IN NATURAL LANGUAGE MODE), 1, NULL)) AS count FROM fts_a;
-- 以上两个SQL，从内部运行来看第二个执行的可能更快些，因为第一条还需要进行相关性的排序统计。-- 查看相关性
SELECT FTS_DOC_ID, body, MATCH(body) AGAINST('Porridge' IN NATURAL LANGUAGE MODE) AS Relevance FROM fts_a;
-- 但是对于在stopword表中的单词是查询不到相关性的
SELECT FTS_DOC_ID, body, MATCH(body) AGAINST('the' IN NATURAL LANGUAGE MODE) AS Relevance FROM fts_a;
-- 另外参数 innodb_ft_min_token_size和innodb_ft_max_token_size控制查询字符的长度，小于最小值，大于最大值的都会忽略改词的搜索。
-- 最小值默认为3，最大值默认为84-- 全文检索的使用2（Boolean）
-- 下面的SQL返回有please或有hot的文档
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Please hot' IN BOOLEANMODE);
-- +号表示必须存在
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('+Please +hot' IN BOOLEANMODE);
-- -号表示一定不存在
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('+Please -hot' IN BOOLEANMODE);
-- (no operator) 表示该word是可选的， 但是如果出现， 其相关性会更高
-- @distance 表示查询的多个单词之间的距离是否在distance之内，distance的单位是字节。这种全文检索的查询也称为Proximity Search。如MATCH(body) AGAINST('"Please pot"@30' IN BOOLEAN MODE) 表示字符串Please和pot之间的距离需在30字节内。
SELECT FTS_DOC_ID, body FROM fts_a WHERE MATCH(body) AGAINST('"Please pot" @30' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, body FROM fts_a WHERE MATCH(body) AGAINST('"Please pot" @5' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, body FROM fts_a WHERE MATCH(body) AGAINST('"Please pot" @4' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, body FROM fts_a WHERE MATCH(body) AGAINST('"hot Porridge" @2' IN BOOLEAN MODE);
SELECT FTS_DOC_ID, body FROM fts_a WHERE MATCH(body) AGAINST('"Please Pease" @3' IN BOOLEAN MODE);
-- 在5.7.17版本下，经过上面的几个SQL测试，仅英文时，distance单位是4个字符（字节），即distance为2时，可以间隔8个字符(字节)
-- > 表示出现该单词时增加相关性, 增加1
SELECT FTS_DOC_ID,body, MATCH(body) AGAINST('like pot' IN BOOLEANMODE) AS Relevance FROM fts_a;
SELECT FTS_DOC_ID,body, MATCH(body) AGAINST('like >pot' IN BOOLEANMODE) AS Relevance FROM fts_a;
-- < 表示出现该单词时降低相关性  减少0.4左右， 可以减至负数
SELECT FTS_DOC_ID,body, MATCH(body) AGAINST('like >pot <some' IN BOOLEANMODE) AS Relevance FROM fts_a;
-- ~ 表示允许出现该单词，但是出现时相关性为负（全文检索查询允许负相关性）。
-- * 表示以该单词开头的单词，如lik*, 表示可以是lik、like，又或者likes。
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('po*' IN BOOLEANMODE);
-- " 表示短语，而不加双引号，表示有please或有hot的文档
INSERT INTO fts_a SELECT NULL, 'Please hot tea to me';
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('"Please hot "' IN BOOLEANMODE);  -- 短语不可分割
SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Please hot ' IN BOOLEANMODE); -- 全文检索的使用3（Query Expansion）
-- MySQL还支持全文检索的扩展查询。这种查询通常是在查询的关键词太短，用户需要implied knowledge（隐含知识）时进行。对于单词database的查询，用户可能希望查询的不仅仅是包含database的文档，可能还包含MySQL、Oracle、DB2、RDBMS的单词。而这时可以使用Query Expansion 模式来开启全文检索的implied knowledge。
-- 通过在查询短语中添加 WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION 可以开启blind query expansion（又称为automatic relevance feedback）。该查询分为两个阶段。
-- 第一阶段：根据搜索的单词进行全文索引查询
-- 第二阶段：根据第一阶段产生的分词再进行一次全文检索的查询。
-- 来看一个具体的例子，创建表articles：
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title, body)) ENGINE=InnoDB;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...'),
('Tuning DB2', 'For IBM database ...'),
('IBM History', 'DB2 history for IBM ...');
-- 在这个表中并没有显式创建FTS_DOC_ID列， 因此InnoDB会自动创建该列，并添加唯一索引，
-- 此外，表articles的全文检索索引是根据列title和body的联合索引
-- 接着根据database关键字进行的全文检索查询。-- 使用扩展查询与使用 NATURAL查询的区别。
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' IN NATURAL LANGUAGE MODE); -- 3个文档
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('database' WITH QUERY EXPANSION); -- 8个文档