接着讲 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
  1. 把目标表纳入监测

设置参数 innodb_ft_aux_table

 mysql> set global innodb_ft_aux_table ='ytt/ft1';
 Query OK, 0 rows affected (0.01 sec)
  1. 查询刚才插入的那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)
  1. 现在进行上面的任何一个条件来触发刷盘,新插入的数据被合并到索引文件里,此时缓存表数据被清空。
   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)
   
  1. 现在查看全文索引关键词表数据,发现数据已经被合并到索引文件里了。
   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)
  1. 尝试删除几条记录
   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)
  1. 再次触发删除记录刷盘,执行 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)
  1. 现在开启参数 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 的技术内容,你们还有什么想知道的吗?赶紧留言告诉小编吧!