INNODB_FT_BEING_DELETED - xiaoboluo768/mysql-system-schema GitHub Wiki

该表仅在OPTIMIZE TABLE语句执行维护操作期间作为INNODB_FT_DELETED表的快照数据存放使用。运行OPTIMIZE TABLE语句时，会先清空INNODB_FT_BEING_DELETED表中的数据，保存INNODB_FT_DELETED表中的快照数据到INNODB_FT_BEING_DELETED表，并从INNODB_FT_DELETED表中删除DOC_ID。由于INNODB_FT_BEING_DELETED表中的内容通常生命周期较短，因此该表中的数据对于监控或者调试来说用处并不大

该表中默认不记录数据，需要设置系统配置参数innodb_ft_aux_table=string（string表示db_name.tb_name字符串），并创建好全文索引，设置好停用词等。关于全文索引详见链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-fulltext_index-tables.html

查询该表的账户需要有PROCESS权限，该表为Memory引擎临时表

表定义语句

CREATE TEMPORARY TABLE `INNODB_FT_BEING_DELETED` (
  `DOC_ID` bigint(21) unsigned NOT NULL DEFAULT '0'
) ENGINE=MEMORY DEFAULT CHARSET=utf8

表字段含义

DOC_ID：该字段表示正在被删除的行的DOC_ID值。当对表使用OPTIMIZE TABLE语句将已删除行的数据从FULLTEXT索引中物理删除之前，执行了文本搜索时，此值用于跳过innodb_ft_index_table表中的行

表记录内容示例

# 设置innodb_ft_aux_table系统参数
root@localhost : test 11:50:16> SET GLOBAL innodb_ft_aux_table = 'test/test';
Query OK, 0 rows affected (0.00 sec)

# 创建全文索引
root@localhost : test 11:26:30> select * from test;
+------+---------+
| id  | test    |
+------+---------+
|    1 | a b c d |
|    1 | a b c d |
|    2 | a b c d |
+------+---------+
3 rows in set (0.00 sec)

root@localhost : test 11:51:06> alter table test add fulltext i_test(test);
Query OK, 0 rows affected, 1 warning (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 1

# 删除表中的数据
root@localhost : test 11:55:09> delete from test where id=1;
Query OK, 2 rows affected (0.06 sec)

# 查询INNODB_FT_DELETED表和INNODB_FT_BEING_DELETED表中的数据，可以发现INNODB_FT_BEING_DELETED为空值，而INNODB_FT_DELETED表存放着被删除的全文索引值
root@localhost : test 11:56:12> select * from information_schema.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
+--------+
2 rows in set (0.00 sec)

root@localhost : test 11:57:10> select * from information_schema.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

# 执行optimize table语句，然后再次查询INNODB_FT_BEING_DELETED和INNODB_FT_DELETED表，如果表中数据够大，在执行optimize table语句期间，可以发现INNODB_FT_DELETED表为空值，INNODB_FT_BEING_DELETED表存放着之前被删除的全文索引值
root@localhost : test 11:57:15> optimize table test;
+-----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op      | Msg_type | Msg_text                                                          |
+-----------+----------+----------+-------------------------------------------------------------------+
| test.test | optimize | note    | Table does not support optimize, doing recreate + analyze instead |
| test.test | optimize | status  | OK                                                                |
+-----------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.08 sec)

root@localhost : test 11:58:50> select * from information_schema.INNODB_FT_DELETED;
Empty set (0.00 sec)

root@localhost : test 11:58:55> select * from information_schema.INNODB_FT_BEING_DELETED;
Empty set (0.00 sec)

上一篇：INNODB_CMP和INNODB_CMP_RESET表 |下一篇：INNODB_CMP_PER_INDEX和INNODB_CMP_PER_INDEX_RESET表

INNODB_FT_BEING_DELETED - xiaoboluo768/mysql-system-schema GitHub Wiki

⚠️ **GitHub.com Fallback** ⚠️

⚠️ GitHub.com Fallback ⚠️