接着講 MySQL 全文索引，這篇主要探讨 MySQL 全文索引的監測。

MySQL 有很完整的元數據表來監測全文索引表的插入，更新，删除；甚至全文索引表以及輔助表的數據追蹤。

這裡分為三個部分：

第一部分，介紹監測相關參數；

第二部分，介紹監測相關元數據表；

第三部分，實例演示如何進行監測。

innodb_ft_aux_table： 動态設置被監測的全文索引表名。 這個參數必須顯式設置，才能對全文索引正常監測。 值一般為：數據庫名/表名，比如 ytt/ft_sample.

innodb_ft_cache_size : 用來緩存對單個全文索引表的 INSERT/UPDATE 數據，當緩存被填滿後開始刷盤。默認8M，範圍1.6M-80M.

innodb_ft_total_cache_size :不同于參數innodb_ft_cache_size，範圍擴展到單個實例，而不是單張表。默認640M，範圍32M-1600M.

innodb_ft_result_cache_limit ： 設置全文索引檢索結果集緩存，超過這個值報錯。默認2G，最小1M，最大為2的32次方-1個字節.

innodb_ft_enable_diag_print ：打印額外的全文索引診斷信息，輸出到錯誤日志，默認關閉。

INNODB_optimize_fulltext_only ：默認關閉，不整理全文索引，設置為ON，則隻整理全文索引。

innodb_ft_num_word_optimize ：對全文索引表進行optimize table 時，需要操作的最小分詞個數。

InnoDB_ft_sort_pll_degree : 創建全文索引、分詞時線程的數量，默認為2，範圍1-32.

MySQL目前提供以下字典表，用來監測全文索引信息

INNODB_FT_CONFIG :存放全文索引元數據以及相關内部處理數據。隻有兩個字段，分别為 KEY 和 VALUE。前兩行記錄解釋下：

optimize_checkpoint_limit 規定 optimize table 語句執行的最長時間。

synced_doc_id ：下一個即将被處理的 DOC_ID 。

mysql> select * from information_schema.innodb_ft_config; --------------------------- ------- | KEY | VALUE | --------------------------- ------- | optimize_checkpoint_limit | 180 | | synced_doc_id | 10 | | stopword_table_name | | | use_stopword | 1 | --------------------------- ------- 4 rows in set (0.00 sec)

INNODB_FT_INDEX_TABLE :存放倒排索引數據表。

INNODB_FT_INDEX_CACHE :存放新插入的關鍵詞信息。為了避免頻繁 DML 操作對全文索引進行重建，MySQL把最新插入的關鍵詞存放在這張表，當新插入的關鍵詞填滿緩存（單表緩存：innodb_ft_cache_size/實例共享緩存： innodb_ft_total_cache_size ）後，與磁盤上的輔助表進行合并。或者在 MySQL 實例關閉、手動執行語句 optimize table 後，與磁盤上的輔助表進行合并。

INNODB_FT_DELETED/INNODB_FT_BEING_DELETED：

這兩張表用來保存已經從全文索引中删除的 DOC_ID 。隻有進行 optimize table 後，DOC_ID 才會從輔助表中徹底删除。 其中表 INNODB_FT_BEING_DELETED 是表 INNODB_FT_DELETED 的快照。在對全文索引表進行 optimize table 時，表 INNODB_FT_DELETED 數據被打快照，當這條語句執行完後，快照表被清空。快照表數據停留時間非常短，要想觀測這張表數據，得準備一張非常大的全文索引表。

1、##### 建立一張示例表 ft1.

mysql> create table ft1(id int not null auto_increment primary key, s1 text,fulltext ft_s1 (s1)); Query OK, 0 rows affected (0.39 sec) mysql> insert into ft1 values (1,'mysql oracle'),(2,'xfs ext3'),(3,'dml ddl dql others'),(4,'oltp olap'),(5,'sqlserver oracle'),(6,'ntfs gfs'),(7,'insert delete update select alter'),(8,'windows linux unix solaris'); Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0

2、##### 把目标表納入監測

設置參數 innodb_ft_aux_table

mysql> set global innodb_ft_aux_table ='ytt/ft1'; Query OK, 0 rows affected (0.01 sec)

3、##### 查詢剛才插入的那8條記錄，此時如果不進行 optimize table 、關閉 MySQL 實例、緩沖池也沒被寫滿，這張表的數據會一直存在。

可以看到這張表包含了關鍵詞在文檔中的文檔ID，文檔位置等信息。

mysql> select * from information_schema.innodb_ft_index_cache; ----------- -------------- ------------- ----------- -------- ---------- | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | ----------- -------------- ------------- ----------- -------- ---------- | alter | 8 | 8 | 1 | 8 | 28 | | ddl | 4 | 4 | 1 | 4 | 4 | | delete | 8 | 8 | 1 | 8 | 7 | | dml | 4 | 4 | 1 | 4 | 0 | | dql | 4 | 4 | 1 | 4 | 8 | | ext3 | 3 | 3 | 1 | 3 | 4 | | gfs | 7 | 7 | 1 | 7 | 5 | | insert | 8 | 8 | 1 | 8 | 0 | | linux | 9 | 9 | 1 | 9 | 8 | | mysql | 2 | 2 | 1 | 2 | 0 | | ntfs | 7 | 7 | 1 | 7 | 0 | | olap | 5 | 5 | 1 | 5 | 5 | | oltp | 5 | 5 | 1 | 5 | 0 | | oracle | 2 | 6 | 2 | 2 | 6 | | oracle | 2 | 6 | 2 | 6 | 10 | | others | 4 | 4 | 1 | 4 | 12 | | select | 8 | 8 | 1 | 8 | 21 | | solaris | 9 | 9 | 1 | 9 | 19 | | sqlserver | 6 | 6 | 1 | 6 | 0 | | unix | 9 | 9 | 1 | 9 | 14 | | update | 8 | 8 | 1 | 8 | 14 | | windows | 9 | 9 | 1 | 9 | 0 | | xfs | 3 | 3 | 1 | 3 | 0 | ----------- -------------- ------------- ----------- -------- ---------- 23 rows in set (0.00 sec)

4、##### 現在進行上面的任何一個條件來觸發刷盤，新插入的數據被合并到索引文件裡，此時緩存表數據被清空。

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; Query OK, 0 rows affected (0.00 sec) mysql> optimize table ft1; --------- ---------- ---------- ---------- | Table | Op | Msg_type | Msg_text | --------- ---------- ---------- ---------- | ytt.ft1 | optimize | status | OK | --------- ---------- ---------- ---------- 1 row in set (0.02 sec) mysql> select * from information_schema.innodb_ft_index_cache; Empty set (0.00 sec) mysql> SET GLOBAL innodb_optimize_fulltext_only=Off; Query OK, 0 rows affected (0.00 sec)

5、##### 現在查看全文索引關鍵詞表數據，發現數據已經被合并到索引文件裡了。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE limit 4; -------- -------------- ------------- ----------- -------- ---------- | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | -------- -------------- ------------- ----------- -------- ---------- | alter | 8 | 8 | 1 | 8 | 28 | | ddl | 4 | 4 | 1 | 4 | 4 | | delete | 8 | 8 | 1 | 8 | 7 | | dml | 4 | 4 | 1 | 4 | 0 | -------- -------------- ------------- ----------- -------- ---------- 4 rows in set (0.00 sec)

6、##### 嘗試删除幾條記錄

mysql> delete from ft1 where id in (2,3,4); Query OK, 3 rows affected (0.02 sec)

再查看删除的表，記錄了已經删除掉的文檔ID。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; -------- | DOC_ID | -------- | 3 | | 4 | | 5 | -------- 3 rows in set (0.00 sec)

7、##### 再次觸發删除記錄刷盤，執行 optimize table ， 表 INNODB_FT_DELETED 被清空。

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; Query OK, 0 rows affected (0.00 sec) mysql> optimize table ft1; --------- ---------- ---------- ---------- | Table | Op | Msg_type | Msg_text | --------- ---------- ---------- ---------- | ytt.ft1 | optimize | status | OK | --------- ---------- ---------- ---------- 1 row in set (0.02 sec) mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; Empty set (0.00 sec) mysql> SET GLOBAL innodb_optimize_fulltext_only=Off; Query OK, 0 rows affected (0.00 sec)

8、##### 現在開啟參數 innodb_ft_enable_diag_print 打印更多的診斷數據：

mysql> select @@innodb_ft_cache_size; ------------------------ | @@innodb_ft_cache_size | ------------------------ | 1600000 | ------------------------ 1 row in set (0.00 sec) mysql> set global innodb_ft_enable_diag_print=on; Query OK, 0 rows affected (0.00 sec) mysql> set global log_error_verbosity=3; Query OK, 0 rows affected (0.01 sec)

1）當 innodb_ft_cache_size 被填滿後，清空緩存，數據刷盤：

mysql> insert into ft1(s1) select replace(uuid(),'-',' ') from ft1 ; Query OK, 256 rows affected (0.07 sec) Records: 256 Duplicates: 0 Warnings: 0

觀測日志數據，表 ytt.ft1 ，向輔助表同步了兩次數據，第一次371個關鍵詞，第二次516個關鍵詞。

[Note] [MY-012299] [InnoDB] FTS SYNC for table `ytt`.`ft1`, deleted count: 0 size: 160010 bytes [Note] [MY-012300] [InnoDB] SYNC words: 371 [Note] [MY-012300] [InnoDB] SYNC words: 516

2）重建全文索引

mysql> SET GLOBAL innodb_optimize_fulltext_only=On; Query OK, 0 rows affected (0.00 sec) mysql> optimize table ft1; --------- ---------- ---------- ---------- | Table | Op | Msg_type | Msg_text | --------- ---------- ---------- ---------- | ytt.ft1 | optimize | status | OK | --------- ---------- ---------- ---------- 1 row in set (0.01 sec) mysql> SET GLOBAL innodb_optimize_fulltext_only=Off; Query OK, 0 rows affected (0.00 sec)

觀測日志數據，數據顯示 optimize table 語句開始時間以及完成時間，同步的關鍵詞個數。

[Note] [MY-012299] [InnoDB] FTS SYNC for table `ytt`.`ft1`, deleted count: 0 size: 0 bytes [Note] [MY-012300] [InnoDB] SYNC words: 0 [Note] [MY-012323] [InnoDB] FTS start optimize `ytt`.`ft1` [Note] [MY-012325] [InnoDB] FTS end optimize `ytt`.`ft1

3）清空表 ft1

mysql> truncate ft1; Query OK, 0 rows affected (0.59 sec)

觀測日志數據，表ID對應的表被移除。

[Note] [MY-012329] [InnoDB] FTS Optimize Removing table 1363

4） 進行一次查詢

mysql> select count(*) from ft1 where match(s1) against('806d'); ---------- | count(*) | ---------- | 512 | ---------- 1 row in set (0.01 sec)

觀測日志數據，輸出查詢耗時，行數，需要分配的内存大小等信息。

[Note] [MY-013045] [InnoDB] keynr=1, '806d [Note] [MY-012388] [InnoDB] NL search [Note] [MY-012340] [InnoDB] '806d' -> 512/512 4.3427e-05 [Note] [MY-012341] [InnoDB] FTS Search Processing time: 0 secs: 6 millisec: row(s) 512 [Note] [MY-012342] [InnoDB] Full Search Memory: 66196 (bytes), Row: 512.

5） 重建聚簇索引。

mysql> alter table ft1 engine innodb; Query OK, 512 rows affected (0.78 sec) Records: 512 Duplicates: 0 Warnings: 0

觀測日志數據，輸出重建聚簇索引需要的并發數，數據頁拆分個數，以及B 樹深度，記錄數。

[Note] [MY-011825] [InnoDB] Parallel scan: 4 [Note] [MY-011825] [InnoDB] ranges: 3 max_threads: 4 split: 4 depth: 1 [Note] [MY-011825] [InnoDB] n: 512

相信了解觀測全文索引的方法後，對MySQL如何處理全文索引的理解會更加透徹。

關于 MySQL 的技術内容，你們還有什麼想知道的嗎？趕緊留言告訴小編吧！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!