最近在複習資料庫索引部分，看到了 fulltext，也即全文索引，雖然全文索引在平時的業務中用到的不多，但是感覺它有點兒意思，是以花了點時間研究一下，特此記錄。
引入
概念
通過數值比較、範圍過濾等就可以完成絕大多數我們需要的查詢，但是，如果希望通過關鍵字的比對來進行查詢過濾，那麼就需要基于相似度的查詢，而不是原來的精确數值比較。全文索引就是為這種場景設計的。
你可能會說，用 like + % 就可以實作模糊比對了，為什麼還要全文索引？like + % 在文本比較少時是合适的，但是對于大量的文本資料檢索，是不可想象的。全文索引在大量的資料面前，能比 like + % 快 N 倍，速度不是一個數量級，但是全文索引可能存在精度問題。
你可能沒有注意過全文索引，不過至少應該對一種全文索引技術比較熟悉：各種的搜尋引擎。雖然搜尋引擎的索引對象是超大量的資料，并且通常其背後都不是關系型資料庫，不過全文索引的基本原理是一樣的。
版本支援
開始之前，先說一下全文索引的版本、存儲引擎、資料類型的支援情況
- MySQL 5.6 以前的版本，隻有 MyISAM 存儲引擎支援全文索引；
- MySQL 5.6 及以後的版本，MyISAM 和 InnoDB 存儲引擎均支援全文索引;
- 隻有字段的資料類型為 char、varchar、text 及其系列才可以建全文索引。
測試或使用全文索引時，要先看一下自己的 MySQL 版本、存儲引擎和資料類型是否支援全文索引。
操作全文索引
索引的操作随便一搜都是，這裡還是再啰嗦一遍。
建立
- 建立表時建立全文索引
create table fulltext_test (
id int() NOT NULL AUTO_INCREMENT,
content text NOT NULL,
tag varchar(),
PRIMARY KEY (id),
FULLTEXT KEY content_tag_fulltext(content,tag) // 建立聯合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- 在已存在的表上建立全文索引
create fulltext index content_tag_fulltext
on fulltext_test(content,tag);
- 通過 SQL 語句 ALTER TABLE 建立全文索引
alter table fulltext_test
add fulltext index content_tag_fulltext(content,tag);
修改
修改個 O，直接删掉重建。
删除
- 直接使用 DROP INDEX 删除全文索引
drop index content_tag_fulltext
on fulltext_test;
- 通過 SQL 語句 ALTER TABLE 删除全文索引
alter table fulltext_test
drop index content_tag_fulltext;
使用全文索引
和常用的模糊比對使用 like + % 不同，全文索引有自己的文法格式，使用 match 和 against 關鍵字，比如
select * from fulltext_test
where match(content,tag) against('xxx xxx');
注意： match() 函數中指定的列必須和全文索引中指定的列完全相同，否則就會報錯，無法使用全文索引，這是因為全文索引不會記錄關鍵字來自哪一列。如果想要對某一列使用全文索引，請單獨為該列建立全文索引。
測試全文索引
添加測試資料
有了上面的知識，就可以測試一下全文索引了。
首先建立測試表，插入測試資料
create table test (
id int() unsigned not null auto_increment,
content text not null,
primary key(id),
fulltext key content_index(content)
) engine=MyISAM default charset=utf8;
insert into test (content) values ('a'),('b'),('c');
insert into test (content) values ('aa'),('bb'),('cc');
insert into test (content) values ('aaa'),('bbb'),('ccc');
insert into test (content) values ('aaaa'),('bbbb'),('cccc');
按照全文索引的使用文法執行下面查詢
select * from test where match(content) against('a');
select * from test where match(content) against('aa');
select * from test where match(content) against('aaa');
根據我們的慣性思維，應該會顯示 4 條記錄才對，然而結果是 1 條記錄也沒有，隻有在執行下面的查詢時
才會搜到 aaaa 這 1 條記錄。
為什麼？這個問題有很多原因，其中最常見的就是 最小搜尋長度 導緻的。另外插一句，使用全文索引時，測試表裡至少要有 4 條以上的記錄，否則，會出現意想不到的結果。
MySQL 中的全文索引，有兩個變量，最小搜尋長度和最大搜尋長度，對于長度小于最小搜尋長度和大于最大搜尋長度的詞語，都不會被索引。通俗點就是說，想對一個詞語使用全文索引搜尋，那麼這個詞語的長度必須在以上兩個變量的區間内。
這兩個的預設值可以使用以下指令檢視
可以看到這兩個變量在 MyISAM 和 InnoDB 兩種存儲引擎下的變量名和預設值
// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;
可以看到最小搜尋長度 MyISAM 引擎下預設是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引隻會對長度大于等于 4 或者 3 的詞語建立索引，而剛剛搜尋的隻有 aaaa 的長度大于等于 4。
配置最小搜尋長度
全文索引的相關參數都無法進行動态修改，必須通過修改 MySQL 的配置檔案來完成。修改最小搜尋長度的值為 1，首先打開 MySQL 的配置檔案 /etc/my.cnf，在 [mysqld] 的下面追加以下内容
[mysqld]
innodb_ft_min_token_size =
ft_min_word_len =
然後重新開機 MySQL 伺服器，并修複全文索引。注意，修改完參數以後，一定要修複下索引，不然參數不會生效。
兩種修複方式，可以使用下面的指令修複
repair table test quick;
或者直接删掉重建立立索引，再次執行上面的查詢，a、aa、aaa 就都可以查出來了。
但是，這裡還有一個問題，搜尋關鍵字 a 時，為什麼 aa、aaa、aaaa 沒有出現結果中，講這個問題之前，先說說兩種全文索引。
兩種全文索引
自然語言的全文索引
預設情況下，或者使用 in natural language mode 修飾符時，match() 函數對文本集合執行自然語言搜尋，上面的例子都是自然語言的全文索引。
自然語言搜尋引擎将計算每一個文檔對象和查詢的相關度。這裡，相關度是基于比對的關鍵詞的個數，以及關鍵詞在文檔中出現的次數。在整個索引中出現次數越少的詞語，比對時的相關度就越高。相反，非常常見的單詞将不會被搜尋，如果一個詞語的在超過 50% 的記錄中都出現了，那麼自然語言的搜尋将不會搜尋這類詞語。上面提到的，測試表中必須有 4 條以上的記錄，就是這個原因。
這個機制也比較好了解，比如說，一個資料表存儲的是一篇篇的文章，文章中的常見詞、語氣詞等等，出現的肯定比較多，搜尋這些詞語就沒什麼意義了，需要搜尋的是那些文章中有特殊意義的詞，這樣才能把文章區分開。
布爾全文索引
在布爾搜尋中，我們可以在查詢中自定義某個被搜尋的詞語的相關性，當編寫一個布爾搜尋查詢時，可以通過一些字首修飾符來定制搜尋。
MySQL 内置的修飾符，上面查詢最小搜尋長度時，搜尋結果 ft_boolean_syntax 變量的值就是内置的修飾符，下面簡單解釋幾個，更多修飾符的作用可以查手冊
- + 必須包含該詞
- - 必須不包含該詞
- > 提高該詞的相關性，查詢的結果靠前
- < 降低該詞的相關性，查詢的結果靠後
- (*)星号 通配符，隻能接在詞後面
對于上面提到的問題，可以使用布爾全文索引查詢來解決，使用下面的指令，a、aa、aaa、aaaa 就都被查詢出來了。
總結
好了，差不多寫完了，又到了總結的時候。
MySQL 的全文索引最開始僅支援英語，因為英語的詞與詞之間有空格，使用空格作為分詞的分隔符是很友善的。亞洲文字，比如漢語、日語、漢語等，是沒有空格的，這就造成了一定的限制。不過 MySQL 5.7.6 開始，引入了一個 ngram 全文分析器來解決這個問題，并且對 MyISAM 和 InnoDB 引擎都有效。
事實上，MyISAM 存儲引擎對全文索引的支援有很多的限制，例如表級别鎖對性能的影響、資料檔案的崩潰、崩潰後的恢複等，這使得 MyISAM 的全文索引對于很多的應用場景并不适合。是以，多數情況下的建議是使用别的解決方案，例如 Sphinx、Lucene 等等第三方的插件，亦或是使用 InnoDB 存儲引擎的全文索引。
幾個注意點
- 使用全文索引前，搞清楚版本支援情況；
- 全文索引比 like + % 快 N 倍，但是可能存在精度問題；
- 如果需要全文索引的是大量資料，建議先添加資料，再建立索引；
- 對于中文，可以使用 MySQL 5.7.6 之後的版本，或者第三方插件。
參考文章
mysql全文索引__簡介
MySQL 官方參考手冊
高性能 MySQL（第三版）
本文原始連結：MySQL 之全文索引
![資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)