全文索引介紹
5.6版本之后InnoDB存儲(chǔ)引擎開(kāi)始支持全文索引沦零，5.7版本之后通過(guò)使用ngram插件開(kāi)始支持中文祭隔。之前僅支持英文，因?yàn)槭峭ㄟ^(guò)空格作為分詞的分隔符路操，對(duì)于中文來(lái)說(shuō)是不合適的
MySQL允許在char疾渴、varchar、text類型上建立全文索引
全文索引使用
MySQL支持三種模式的全文檢索模式
1.自然語(yǔ)言模式：通過(guò)MATCH AGAINST 傳遞某個(gè)特定的字符串進(jìn)行檢索
2.布爾模式：可以為檢索的字符串增加操作符屯仗，
“+”表示必須包含
“-”表示必須排除
“>”表示出現(xiàn)該單詞時(shí)增加相關(guān)性
“<”表示出現(xiàn)該單詞時(shí)降低相關(guān)性
“*”表示通配符
“~”允許出現(xiàn)該單詞搞坝，但是出現(xiàn)時(shí)相關(guān)性為負(fù)
“""”表示短語(yǔ)
no operation表示find word是可選的，如果出現(xiàn)魁袜，相關(guān)性會(huì)更高
余布爾操作符可以通過(guò)sql語(yǔ)句查看：
mysql> show variables like '%ft_boolean_syntax%';
+-------------------+----------------+
| Variable_name | Value |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
3.查詢擴(kuò)展模式：
應(yīng)用場(chǎng)景：查詢的關(guān)鍵字太短桩撮，用戶需要implied knowledge(隱含知識(shí))時(shí)進(jìn)行。比如峰弹，對(duì)于單詞database的查詢店量，用戶可能希望查詢的不僅僅是包含database的文檔，還指那些包含mysql鞠呈、oracle融师、db2的單詞。
該查詢會(huì)執(zhí)行兩次檢索蚁吝，第一次使用給定的短語(yǔ)進(jìn)行檢索旱爆，第二次結(jié)合第一次相關(guān)性比較高的進(jìn)行檢索
建立數(shù)據(jù)庫(kù)：
mysql> create table articles(
-> id int auto_increment primary key,
-> title varchar(200),
-> body text,
-> fulltext(title, body) with parser ngram
-> )
-> ;
插入數(shù)據(jù)，插入數(shù)據(jù)庫(kù)時(shí)可能會(huì)遇到的問(wèn)題：
ERROR 1366 (HY000): Incorrect string value: '\xCA\xFD\xBE\xDD' for column 'title' at row 1
編碼方式有問(wèn)題窘茁，默認(rèn)好像為拉丁文
解決方式一：
alter table articles convert to charset utf8;
查看數(shù)據(jù)庫(kù)中的數(shù)據(jù)：
mysql> select * from articles;
+----+----------------+--------+
| id | title | body |
+----+----------------+--------+
| 1 | 數(shù)據(jù)庫(kù)管理 | 專業(yè)課 |
| 2 | 數(shù)據(jù)庫(kù) | 專業(yè)課 |
| 3 | 計(jì)算機(jī)操作系統(tǒng) | 專業(yè)課 |
| 4 | MySQL | 專業(yè)課 |
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 |
+----+----------------+--------+
使用1疼鸟、2、3三種模式查詢數(shù)據(jù)庫(kù)
語(yǔ)法：
select * from table
where match(fulltext_field)
against('find_key' in natural language mode
|in boolean mode
|with query expansion
|in natural language mode with query expansion)
match：指定需要被查詢的列
against：指定使用何種方法進(jìn)行查詢
模式一：自然語(yǔ)言模式
mysql> select * from articles where match(title, body) against ('MySQL數(shù)據(jù)庫(kù)' in natural language mode);
+----+-------------+--------+
| id | title | body |
+----+-------------+--------+
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 |
| 4 | MySQL | 專業(yè)課 |
| 1 | 數(shù)據(jù)庫(kù)管理 | 專業(yè)課 |
| 2 | 數(shù)據(jù)庫(kù) | 專業(yè)課 |
+----+-------------+--------+
只要存在find_key中的字庙曙，記錄就會(huì)被查詢到
默認(rèn)采用natural language模式，因此上述sql語(yǔ)句等同于：
select * from articles where match(title, body) against ('MySQL數(shù)據(jù)庫(kù)');
模式二：布爾模式
示例-1：查詢既有數(shù)據(jù)浩淘，又有管理的記錄
mysql> select * from articles where match(title, body) against ('+數(shù)據(jù) +管理' in boolean mode);
+----+------------+--------+
| id | title | body |
+----+------------+--------+
| 1 | 數(shù)據(jù)庫(kù)管理 | 專業(yè)課 |
+----+------------+--------+
1 row in set (0.00 sec)
示例-2：查詢有數(shù)據(jù)捌朴，沒(méi)有管理的記錄
mysql> select * from articles where match(title, body) against ('+數(shù)據(jù) -管理' in boolean mode);
+----+-------------+--------+
| id | title | body |
+----+-------------+--------+
| 2 | 數(shù)據(jù)庫(kù) | 專業(yè)課 |
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 |
+----+-------------+--------+
2 rows in set (0.00 sec)
示例-3：查詢有MySQL吴攒，可以有數(shù)據(jù)庫(kù)或沒(méi)有數(shù)據(jù)庫(kù)的記錄
mysql> select * from articles where match(title, body) against ('>數(shù)據(jù)庫(kù) +MySQL' in boolean mode);
+----+-------------+--------+
| id | title | body |
+----+-------------+--------+
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 |
| 4 | MySQL | 專業(yè)課 |
+----+-------------+--------+
2 rows in set (0.00 sec)
模式三：查詢擴(kuò)展模式
示例-4
為了驗(yàn)證模式三，首先在數(shù)據(jù)庫(kù)中插入以下數(shù)據(jù)：
mysql> 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 comparision...'),
-> ('Tuning DB2','For IBM database ...'),
-> ('IBM History','DB2 history for IBM ...');
假設(shè)使用自然模式
mysql> select * from articles where match(title,body) against('database' in natural language mode);
+----+-----------------------+------------------------------------------+
| id | title | body |
+----+-----------------------+------------------------------------------+
| 11 | MySQL Tutorial | DBMS stands for DataBase... |
| 12 | How To Use MySQL Well | After you went through a ... |
| 15 | MySQL vs. YourSQL | In the following database comparision... |
| 16 | Tuning DB2 | For IBM database ... |
+----+-----------------------+------------------------------------------+
只有四條查詢結(jié)果砂蔽，使用查詢擴(kuò)展模式
mysql> select * from articles where match(title,body) against('database' with query expansion);
+----+-----------------------+------------------------------------------+
| id | title | body |
+----+-----------------------+------------------------------------------+
| 12 | How To Use MySQL Well | After you went through a ... |
| 15 | MySQL vs. YourSQL | In the following database comparision... |
| 11 | MySQL Tutorial | DBMS stands for DataBase... |
| 8 | hello world | This is my first java project |
| 13 | Optimizing MySQL | In this tutorial we will show... |
| 16 | Tuning DB2 | For IBM database ... |
| 17 | IBM History | DB2 history for IBM ... |
| 14 | 1001 MySQL Tricks | 1.Never run mysqld as root. 2 . ... |
| 9 | article_1 | Some like it hot |
| 4 | MySQL | 專業(yè)課 |
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 |
| 6 | MySQL數(shù)據(jù)庫(kù) | 認(rèn)真學(xué)習(xí) |
| 10 | article_2 | I like hot |
+----+-----------------------+------------------------------------------+
查詢出了13條語(yǔ)句洼怔。原先查詢出的語(yǔ)句中即自然查詢中包含MySQL、DB2這些字左驾，所以進(jìn)行擴(kuò)展查詢第二步時(shí)會(huì)將包含這些關(guān)鍵字的記錄也查詢出來(lái)镣隶。
慎用查詢擴(kuò)展模式！因?yàn)榭赡軙?huì)帶來(lái)很多非相關(guān)性的查詢
查詢返回結(jié)果是根據(jù)相關(guān)性進(jìn)行降序排序的诡右，相關(guān)性最高的結(jié)果放在第一位安岂。相關(guān)性的計(jì)算依據(jù)四個(gè)條件
1.find key在文檔中是否存在
2.find key在文檔中出現(xiàn)的次數(shù)
3.find key在索引列的數(shù)量
4.多少個(gè)文檔包含該find key
以示例-3為例查詢相關(guān)性，因?yàn)楹竺嬗植辶藥讞l數(shù)據(jù)帆吻，結(jié)果和上述查詢結(jié)果不一致
mysql> select id,title,body, match(title, body) against ('MySQL數(shù)據(jù)庫(kù)' in natural language mode) as relevance from articles;
+----+----------------+-------------------------------+--------------------+
| id | title | body | relevance |
+----+----------------+-------------------------------+--------------------+
| 1 | 數(shù)據(jù)庫(kù)管理 | 專業(yè)課 | 0.1812381148338318 |
| 2 | 數(shù)據(jù)庫(kù) | 專業(yè)課 | 0.1812381148338318 |
| 3 | 計(jì)算機(jī)操作系統(tǒng) | 專業(yè)課 | 0 |
| 4 | MySQL | 專業(yè)課 | 0.6349670886993408 |
| 5 | MySQL數(shù)據(jù)庫(kù) | 專業(yè)課 | 1.178681492805481 |
| 6 | MySQL數(shù)據(jù)庫(kù) | 認(rèn)真學(xué)習(xí) | 1.178681492805481 |
| 7 | 作文 | 好好寫(xiě) | 0 |
| 8 | hello world | This is my first java project | 0.0906190574169159 |
+----+----------------+-------------------------------+--------------------+
id=8的記錄相關(guān)性為0.09因?yàn)橛袀€(gè)m吧
底層實(shí)現(xiàn)原理
只有了解了底層原理才可以更好的分析結(jié)果域那。全文索引的底層實(shí)現(xiàn)為倒排索引。
為什么叫倒排索引
倒排索引被稱為反向索引更為合適
當(dāng)表上存在全文索引時(shí)猜煮，就會(huì)隱式的建立一個(gè)名為FTS_DOC_ID的列次员，并在其上創(chuàng)建一個(gè)唯一索引，用于標(biāo)識(shí)分詞出現(xiàn)的記錄行王带。你也可以顯式的創(chuàng)建一個(gè)名為FTS_DOC_ID的列淑蔚，但需要和隱式創(chuàng)建的列類型保持一致，否則創(chuàng)建的時(shí)候?qū)?huì)報(bào)錯(cuò)愕撰，并且不能通過(guò)FTS_DOC_ID來(lái)查找列：
mysql> select * from articles where FTS_DOC_ID = 1;
ERROR 1054 (42S22): Unknown column 'FTS_DOC_ID' in 'where clause'
執(zhí)行報(bào)錯(cuò)
所以建立的articles表中列為
FTS_DOC_ID刹衫、id、title盟戏、body
常規(guī)的索引是文檔到關(guān)鍵詞的映射：文檔——>關(guān)鍵詞
倒排索引是關(guān)鍵詞到文檔的映射：關(guān)鍵詞——>文檔
全文索引通過(guò)關(guān)鍵字找到關(guān)鍵字所在文檔绪妹，可以提高查詢效率
倒排索引結(jié)構(gòu)
| Number | Text | Documents |
|---|---|---|
| 1 | code | (1:6),(4:8) |
| 2 | days | (3:2),(6:2) |
| 3 | hot | (1:3),(4:4) |
是word + ilist的存儲(chǔ)結(jié)構(gòu)
Text對(duì)應(yīng)于word，是一個(gè)分詞柿究。Document存儲(chǔ)的是鍵值對(duì)邮旷，鍵為FTS_DOC_ID，值為在文檔中的位置蝇摸，對(duì)應(yīng)于ilist婶肩。其中word保存在
Auxiliary Table中，總共有六張貌夕，每張表根據(jù)word的Latin編碼進(jìn)行分區(qū)律歼，下面有介紹
FTS Index Cache(全文檢索索引緩存)
- 在事務(wù)提交的時(shí)候?qū)⒎衷~寫(xiě)入到FTS Index Cache中
- 批量更新到Auxiliary Table，為了提高性能不會(huì)插入一條數(shù)據(jù)立刻更新到Auxiliary Table啡专。進(jìn)行批量更新的幾種情況：
- 全文檢索索引緩存已滿险毁，默認(rèn)大小為32M，可以通過(guò)修改innodb_ft_cache_size來(lái)改變FTS Index Cache的大小
- 關(guān)閉數(shù)據(jù)庫(kù)的時(shí)候，將FTS Index Cache中的數(shù)據(jù)庫(kù)會(huì)同步到磁盤(pán)上的Auxiliary Table中
- 當(dāng)對(duì)全文檢索進(jìn)行查詢時(shí)畔况，首先會(huì)將在FTS Index Cache中對(duì)應(yīng)的字段合并到Auxiliary Table中鲸鹦，然后在進(jìn)行查詢
- 當(dāng)數(shù)據(jù)庫(kù)突然宕機(jī)時(shí)，可能會(huì)導(dǎo)致一些FTS Index Cache中的數(shù)據(jù)未同步到Auxiliary Table上跷跪。數(shù)據(jù)庫(kù)重啟時(shí)馋嗜，當(dāng)用戶對(duì)表進(jìn)行全文檢索時(shí)，InnoDB存儲(chǔ)引擎會(huì)自動(dòng)讀取未完成的文檔吵瞻，然后進(jìn)行分詞操作葛菇，在將分詞的結(jié)果放入到FTS Index Cache中。innodb_ft_cache_size的大小會(huì)影響恢復(fù)的時(shí)間
- FTS Index Cache為紅黑樹(shù)結(jié)構(gòu)橡羞，會(huì)根據(jù)(word,ilist)進(jìn)行排序插入
Auxiliary Table(輔助表)
- Auxiliary Table存儲(chǔ)在磁盤(pán)中眯停，進(jìn)入保存mysql數(shù)據(jù)的目錄下
tianthe@L-SHC-15008567 MINGW64 /c/programdata/mysql/MySQL Server 5.7/data/study
$ ls -lh
total 1.6M
-rw-r--r-- 1 tianthe 1049089 8.5K Apr 17 16:37 articles.frm
-rw-r--r-- 1 tianthe 1049089 112K Apr 17 17:41 articles.ibd
-rw-r--r-- 1 tianthe 1049089 65 Apr 17 15:24 db.opt
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_1.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_2.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_3.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_4.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_5.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_0000000000000087_INDEX_6.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_BEING_DELETED.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_BEING_DELETED_CACHE.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_CONFIG.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_DELETED.ibd
-rw-r--r-- 1 tianthe 1049089 96K Apr 17 16:37 FTS_000000000000005e_DELETED_CACHE.ibd
看到有FTS_000000000000005e_0000000000000087_INDEX_0~6.ibd，其對(duì)應(yīng)的就是六張Auxiliary Table
其余文件介紹：
FTS_000000000000005e_DELETED.ibd
FTS_000000000000005e_DELETED_CACHE.ibd
記錄的是從Auxiliary Table中刪除的FTS_DOC_ID尉姨，后者是前者的內(nèi)存緩存
FTS_000000000000005e_BEING_DELETED.ibd
FTS_000000000000005e_BEING_DELETED_CACHE.ibd
記錄的是已經(jīng)被刪除索引記錄并真正從FTS Index Cache刪除的FTS_DOC_ID(即刪除FTS Index Cache并做了OPTIMIZE TABLE)庵朝，后者是前者的內(nèi)存緩存。這兩個(gè)表主要用于輔助進(jìn)行OPTIMIZE TABLE時(shí)將DELETED/DELETED_CACHED表中的記錄轉(zhuǎn)儲(chǔ)到其中
FTS_000000000000005e_CONFIG.ibd
包含全文索引的內(nèi)部信息又厉，最重要的存儲(chǔ)是FTS_SYNCED_DOC_ID九府，表示已經(jīng)解析并刷到磁盤(pán)的FTS_DOC_ID， 在系統(tǒng)宕機(jī)時(shí)覆致，可以根據(jù)這個(gè)值判斷哪些該重新分詞并加入到FTS Index Cache中
DML操作
- 插入操作
插入操作較為簡(jiǎn)單侄旬，當(dāng)往表中插入記錄時(shí)，提交事務(wù)時(shí)會(huì)對(duì)全文索引上的列進(jìn)行分詞存儲(chǔ)到FTS Index Cache煌妈，最后在批量更新到Auxiliary Table中 - 刪除操作
當(dāng)提交刪除數(shù)據(jù)的事務(wù)以后儡羔，不會(huì)刪除Auxiliary Table中的數(shù)據(jù)，而只會(huì)刪除FTS Index Cache中的數(shù)據(jù)璧诵。對(duì)于Auxiliary Table中被刪除的記錄汰蜘，InnoDB存儲(chǔ)引擎會(huì)記錄其FTS Document Id，并將其保存在DELETED Auxiliary Table中之宿∽宀伲可以通過(guò)OPTIMIZE TABLE手動(dòng)刪除索引中的記錄。 - 更新操作
- 查找操作
分為兩步比被。第一步：根據(jù)檢索詞搜集符合條件的FTS_DOC_ID色难，在搜集滿足條件的FTS_DOC_ID首先讀取delete表中記錄的FTS_DOC_ID，這些FTS_DOC_ID隨后被用做過(guò)濾
第二步：根據(jù)FTS_DOC_ID找到對(duì)應(yīng)的記錄等缀，找到的記錄是根據(jù)相關(guān)性大小降序返回的
查看插入記錄的分詞
mysql> insert into t1 values (NULL, 'hello, welcome to mysql world');
Query OK, 1 row affected (1.87 sec)
mysql> set global innodb_ft_aux_table = 'test/t1';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from INNODB_FT_INDEX_CACHE;
+---------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+---------+--------------+-------------+-----------+--------+----------+
| hello | 2 | 2 | 1 | 2 | 0 |
| mysql | 2 | 2 | 1 | 2 | 18 |
| welcome | 2 | 2 | 1 | 2 | 7 |
| world | 2 | 2 | 1 | 2 | 24 |
+---------+--------------+-------------+-----------+--------+----------+
皮皮甜這么做卻沒(méi)有查到分詞情況枷莉，已經(jīng)使用admin權(quán)限去設(shè)置global變量了，然而
mysql> select * from information_schema.INNODB_FT_INDEX_TABLE;
Empty set (0.00 sec)
參考：
http://mysql.taobao.org/monthly/2015/10/01/
https://blog.csdn.net/qq_33663251/article/details/69612619
https://www.zhihu.com/question/23202010
書(shū)籍：MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎
