索引实践分享（第29期索引设计监测全文索引）

接着讲 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 打印更多的诊断数据：

必要先决条件：把参数innodb_ft_cache_size=设置为最小，开启参数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 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

,