innodbmysql全⽂索引_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;
mysql文档手机版
-- 这是因为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
-- ~ 表⽰允许出现该单词，但是出现时相关性为负(全⽂检索查询允许负相关性)。
-- * 表⽰以该单词开头的单词，如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的查询，⽤户可能希望查询的不仅仅是包含databas
e的⽂档，可能还包含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 '),
('MYSQL Security', 'When configured properly, '),
('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个⽂档

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。