mysql 全文索引
一、什么是停止詞？
不能用于搜索的詞，如敏感詞匯：法輪功、李宏志、器官移植等；非常常見的無任何具體含議的詞匯： and、or、what 、好的、我們、你們、這樣 等等。
二、停止詞相關參數
mysql> show variables like '%innodb%stop%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| innodb_ft_enable_stopword | ON |
| innodb_ft_server_stopword_table | |
| innodb_ft_user_stopword_table | |
+---------------------------------+-------+
innodb_ft_server_stopword_table 和 innodb_ft_user_stopword_table：指定停止詞的 innodb 表，這兩個參唯一的區別就是 innodb_ft_user_stopword_table 的優先級更高。如果沒有設置這2 個參數中的任何一個，則使用默認停止詞表 information_schema.INNODB_FT_DEFAULT_STOPWORD
三、創建停止詞
1、創建保存停止詞的表
create table stopword（value varchar(18)）;
注：字符串長度設置不能少于 ngram_token_size * 字符長度，字符長度根字符集有關，如：utf8 是一個中文字符占 3 個字節，如果 ngram_token_size 的值是 2 ，則 2 * 3 = 6，最少不能少于6個字節（個人理解）
2、插入停止詞
insert into stopword values('法輪功')('李宏志')('我們的')('你們的');
四、innodb 全文索引的停止詞如何設置？
set global innodb_ft_user_stopword_table='test/stopword';
注：
停止詞更新后，需要重建全文索引才能生效，重建索引時，stopword 表中的停止詞不再創建索引
注意格式：test 是 schema， stopword 是保證停止詞的表，中間用 "/" 連接
innodb 的中文全分詞使用的是 ngram 支持，其算法是二元分詞法，可以通過 ngram_token_size 參數設置分詞的長度，默認是 2 ，該值越大，索引越大。
mysql> show variables like '%ngram%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| ngram_token_size | 2 |
+------------------+-------+
注：ngram_token_size的值為2 說明以2個字符為一個詞，可以設置 innodb_ft_aux_table 參數查看分詞結果
一、查看分詞結果
1、設置參數 innodb_ft_aux_table
set global innodb_ft_aux_table='test/test6';
注：test為 schema 名， test6 為具有全文索引的表名
2、查看分詞結果
--原數據
mysql> select * from test6 where id >= 7;
+----+-----------------------------+
| id | name |
+----+-----------------------------+
| 7 | 器官健康很重要 |
| 8 | 要做就做大買賣 |
| 9 | 官買官賣是不靠普的 |
+----+-----------------------------+
--分詞后的數據（全文索引）
mysql> select * from information_schema.innodb_ft_index_cache;
+--------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+--------+--------------+-------------+-----------+--------+----------+
| 買賣 | 11 | 11 | 1 | 11 | 15 |
| 做大 | 11 | 11 | 1 | 11 | 9 |
| 做就 | 11 | 11 | 1 | 11 | 3 |
| 健康 | 10 | 10 | 1 | 10 | 6 |
| 器官 | 10 | 10 | 1 | 10 | 0 |
| 大買 | 11 | 11 | 1 | 11 | 12 |
| 官健 | 10 | 10 | 1 | 10 | 3 |
| 就做 | 11 | 11 | 1 | 11 | 6 |
| 康很 | 10 | 10 | 1 | 10 | 9 |
| 很重 | 10 | 10 | 1 | 10 | 12 |
| 要做 | 11 | 11 | 1 | 11 | 0 |
| 重要 | 10 | 10 | 1 | 10 | 15 |
+--------+--------------+-------------+-----------+--------+----------+
innodb_ft_index_cache 和 innodb_ft_index_table 表的關系：
創建好全文索引后，所有的數據都保存在 innodb_ft_index_table 表中，當有新的數據 insert 后，這些新數據的全文索引保存在內存表中，即 innodb_ft_index_cache，當做 optimize table 操作時，批量把表寫入磁盤中，即 innodb_ft_index_table 表中。
--在建表時指定
CREATE TABLE `test6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(90) DEFAULT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `idx_name` (`name`) /*!50100
WITH PARSER `ngram`
*/
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
-- 用 alter table 創建全文索引
alter table test6 add fulltext key idx_name(name)
with parser ngram
注：對于中文全文索引必須指定
with parser ngram
關鍵字，否則innodb 默認的分詞算法對 中文的支持很差。
一、自然語言模式
-- 原數據
mysql> select * from test6 ;
+----+------------------------------------------+
| id | name |
+----+------------------------------------------+
| 1 | 工地城工 有城 |
| 2 | adfadsfadsfadsf |
| 3 | 產戟 要棵地地要要地大葉黃楊 |
| 4 | 宸ュ伐鍦板湴鏈夋湁鐨勭殑 |
| 5 | 人人地工地 |
| 6 | 霜國工直有直功發順豐 |
| 7 | 器官健康很重要 |
| 8 | 要做就做大買賣 |
| 9 | 官買官賣是不靠普的 |
+----+------------------------------------------+
-- 使用全文索引檢查
mysql> select * from test6 where match(name) against('買賣');
+----+-----------------------+
| id | name |
+----+-----------------------+
| 8 | 要做就做大買賣 |
+----+-----------------------+
mysql> explain select * from test6 where match(name) against('買賣' in NATURAL LANGUAGE MODE);
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | test6 | NULL | fulltext | idx_name | idx_name | 0 | const | 1 | 100.00 | Using where;
Ft_hints
: sorted |
+----+-------------+-------+------------+----------+---------------+----------+---------+-------+------+----------+-------------------------------+
注：innodb 全文索引默認就是使用 自然語言模式，所以可不用加
in NATURAL LANGUAGE MODE
二、布爾模式
mysql> select * from test6 where match(name) against('買賣'
in boolean mode
);
+----+-----------------------+
| id | name |
+----+-----------------------+
| 8 | 要做就做大買賣 |
| 10 | 做買賣了 |
+----+-----------------------+
如果不想要包含了 “要做”的行，則可以：
mysql> select * from test6 where match(name) against('買賣 -要做' in boolean mode);
+----+--------------+
| id | name |
+----+--------------+
| 10 | 做買賣了 |
+----+--------------+
注：只有在布爾模式下才可用以上操作符。
布爾模式下支持以下操作符：
“+”表示必須包含
“-”表示必須排除
“>”表示出現該單詞時增加相關性
“<”表示出現該單詞時降低相關性
“*”表示通配符
“~”允許出現該單詞，但是出現時相關性為負
“""”表示短語
no operation表示find word是可選的，如果出現，相關性會更高
可以通過以下命令查看 布爾模式支持的操作符：
mysql> show variables like '%ft_boolean_syntax%';
+-------------------+----------------+
| Variable_name | Value |
+-------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
+-------------------+----------------+
一、DML操作對全文索引的影響
1、插入操作
插入操作較為簡單，當往表中插入記錄時，提交事務時會對全文索引上的列進行分詞存儲到FTS Index Cache（INNODB_FT_INDEX_CACHE），最后在批量更新到Auxiliary Table（INNODB_FT_INDEX_TABLE）中
2、刪除操作
當提交刪除數據的事務以后，不會刪除Auxiliary Table中的數據，而只會刪除FTS Index Cache中的數據。對于Auxiliary Table中被刪除的記錄，InnoDB存儲引擎會記錄其FTS Document Id，并將其保存在DELETED Auxiliary Table中。可以通過OPTIMIZE TABLE手動刪除索引中的記錄。
3、更新操作
4、查找操作
分為兩步。第一步：根據檢索詞搜集符合條件的FTS_DOC_ID，在搜集滿足條件的FTS_DOC_ID首先讀取delete表中記錄的FTS_DOC_ID，這些FTS_DOC_ID隨后被用做過濾
第二步：根據FTS_DOC_ID找到對應的記錄，找到的記錄是根據相關性大小降序返回的
二、innodb 全文索引相關表：
mysql> select table_schema, table_name from information_schema.tables where table_name like 'innodb_ft%';
+--------------------+----------------------------+
| table_schema | table_name |
+--------------------+----------------------------+
| information_schema | INNODB_FT_CONFIG |
| information_schema | INNODB_FT_BEING_DELETED |
| information_schema | INNODB_FT_DELETED |
| information_schema | INNODB_FT_DEFAULT_STOPWORD |
| information_schema | INNODB_FT_INDEX_TABLE |
| information_schema | INNODB_FT_INDEX_CACHE |
+--------------------+----------------------------+
INNODB_FT_DELETED
：保存的是innodb 表中刪除的全文索引的doc_id，避免DML操作時重組全文索引。當 OPTIMIZE TABLE 操作時才更新重組全文索引。
所以要時常的對具有全文索引的表進行 OPTIMIZE TABLE 操作
，要不然INNODB_FT_DELETED會很大，導至性能問題
INNODB_FT_BEING_DELETED
：只有在作 OPTIMIZE TABLE 操作時才使用，這是一個中間表，使用的時間很短。
INNODB_FT_INDEX_CACHE
：當新插入數據時，保存新的全文索引，避免 insert 操作導致索引重組，當遇到 OPTIMIZE TABLE、關閉服務、超過 innodb_ft_cache_size或 innodb_ft_total_cache_size 的限制時才合并到主索引表中（INNODB_FT_INDEX_TABLE）。
INNODB_FT_CONFIG
：innodb 全文索上的刷新、停止詞等信息。
注：要想正常使用INNODB_FT_DELETED、INNODB_FT_INDEX_CACHE等表，必需設置參數 innodb_ft_aux_table，否則看不到任何信息，該參數應該是用于調試使用
創建全文索引時，可以設置并行度，通過參數 innodb_ft_sort_pll_degree 控制。
三、innodb 全文索引相關參數：
innodb_ft_aux_table：
設置調式表
ngram_token_size
：分詞長度
innodb_ft_server_stopword_table / innodb_ft_user_stopword_table
：設置停止詞表，innodb_ft_user_stopword_table這個表的優先級更高。
innodb_ft_min_token_size / innodb_ft_max_token_size
：如果使用 ngram 全文索引中日韓語言插件，這 2 個參數不再有用。
innodb_ft_sort_pll_degree
：創建全文索引時的并行度。
innodb_ft_cache_size / innodb_ft_total_cache_size
：前一個是定議每個表的全文索引內存大小，后一個設置所有表的全文索引內存大小，如果全文索引大小超過 innodb_ft_total_cache_size 的設置，則強制同步（我想是fulltext index 的cache 大小與全文索引大小強制保持一致，意味著有內存中不能全部加載全文索引）被取消。
innodb_optimize_fulltext_only
：optimize table 操作時，只優化全文索引。set innodb_optimize_fulltext_only = 1;
