Shulou(Shulou.com)06/01 Report--

In the previous article, we introduced InnoDB-level locks, transactions, and related statistical dictionary tables in detail. In this issue, we will bring you the seventh article in the series, "InnoDB layer full-text Index Dictionary Table | Comprehensive understanding of information_schema".

| | INNODB_FT_CONFIG |

This table provides metadata information about querying FULLTEXT indexes and associations of InnoDB tables.

Before querying this table, you need to set innodb_ft_aux_table='db_name/tb_name',db_name/tb_name to the name of the table and library that contains the full-text index

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

The following is the content of the information stored in the table

Root@localhost: test 11:58:58 > SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG +-- +-+ | KEY | VALUE | +-+-+ | optimize_checkpoint_limit | 180 | synced_doc_id | 0 | stopword_table_name | Use_stopword | 1 | +-- +-+ 4 rows in set (0.00 sec)

The field has the following meanings:

KEY: the name of the metadata item that represents the InnoDB table containing the FULLTEXT index

VALUE: a value that represents the value associated with the corresponding KEY column and reflects some limitation of the FULLTEXT index of the InnoDB table

PS:

This table is for internal configuration use only. You don't have to do statistics.

The value of the KEY column may vary depending on the performance tuning and debugging requirements of InnoDB full-text processing. The recorded metadata item name values include:

* time of execution of optimize_checkpoint_limit:OPTIMIZE TABLE statement (in second)

* synced_doc_id: the next DOC_ ID value to be executed

* stopword_table_name: the user-defined database / table name that holds the disabled thesaurus. If the deactivation vocabulary is not customized, the value column for the record is empty

* use_stopword: indicates whether to use the disabled word list, which is defined when the FULLTEXT index is created. The default is INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD.

| | INNODB_FT_BEING_DELETED |

This table is used only as snapshot data for the INNODB_FT_ delete table during maintenance operations performed by the OPTIMIZE TABLE statement. When you run the OPTIMIZE TABLE statement, it first clears the data in the INNODB_FT_BEING_ delete table, saves the snapshot data in the INNODB_FT_ delete table to the INNODB_FT_BEING_ delete table, and removes the DOC_ID from the INNODB_FT_ delete table. Because the contents of the INNODB_FT_BEING_ delete table usually have a short life cycle, the data in the table is not very useful for monitoring or debugging

By default, no data is recorded in this table. You need to set the system configuration parameter innodb_ft_aux_table=string (string stands for db_name.tb_name string), create a full-text index, set up stop words, etc.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

The following is the content of the information stored in the table

# set innodb_ft_aux_table system parameters root@localhost: test 11:50:16 > SET GLOBAL innodb_ft_aux_table = 'test/test';Query OK, 0 rows affected (0.00 sec) # create a full-text index root@localhost: test 11:26:30 > select * from test +-+-+ | id | test | +-+-+ | 1 | ab c d | 1 | ab c d | | 2 | ab 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: delete data from the table root@localhost: test 11:55:09 > delete from test where id=1 Query OK, 2 rows affected (0.06 sec) # query the data in the INNODB_FT_ delete table and the INNODB_FT_BEING_ delete table, you can find that INNODB_FT_BEING_DELETED is null, while the INNODB_FT_DELETED table stores the deleted full-text index value 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) # executes the optimize table statement, and then queries the INNODB_FT_BEING_DELETED and INNODB_FT_DELETED tables again. If the data in the table is large enough, during the execution of the optimize table statement, you can find that the INNODB_FT_DELETED table is null, and the INNODB_FT_BEING_DELETED table stores the previously deleted full-text index value 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)

The field has the following meanings:

DOC_ID: this field represents the DOC_ ID value of the row being deleted. This value is used to skip rows in the innodb_ft_index_ table when a text search is performed before the deleted rows are physically deleted from the FULLTEXT index using the OPTIMIZE TABLE statement on the table.

| | INNODB_FT_DELETED |

This table provides information about rows that are deleted by the query from the FULLTEXT index of the InnoDB table. It exists to avoid expensive index reorganization during the DML operation of the InnoDB FULLTEXT index. The information of the words in the newly deleted full-text index is stored separately in the table, and the search results are filtered out during the text search, and the information in the table is emptied only when the OPTIMIZE TABLE statement is executed.

The information in this table is not recorded by default. You need to use the innodb_ft_aux_table option (the default is an empty string) to specify which innodb engine table information needs to be recorded, for example: test/test

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

The following is the content of the information stored in the table

# use the innodb_ft_aux_table option to specify the Innodb engine table containing the full-text index root@localhost: test 11:41:01 > SET GLOBAL innodb_ft_aux_table = 'test/test';Query OK, 0 rows affected (0.00 sec) # delete rows in the table root@localhost: test 11:41:24 > delete from test where id=1 Query OK, 3 rows affected (0.02 sec) # query the INNODB_FT_DELETED table, and the INNODB_FT_DELETED table contains the DOC_ID value of the deleted full-text index root@localhost: test 11:41:29 > SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED +-+ | DOC_ID | +-+ | 4 | | 5 | 6 | 10 | 11 | 12 | 13 | +-+ 7 rows in set (0.00 sec)

The field has the following meanings:

DOC_ID: the full-text index DOC_ ID value of the newly deleted row from the library table specified by the innodb_ft_aux_table parameter. The values in this table are used to skip row queries in the innodb_ft_index_ table. The value in the INNODB_FT_ delete table is cleared when the OPTIMIZE TABLE statement is executed on the table specified by the innodb_ft_aux_table parameter

| | INNODB_FT_DEFAULT_STOPWORD |

This table is the default full-text index deactivated thesaurus and provides query deactivated word list values. To enable the deactivate thesaurus, you need to enable the parameter innodb_ft_enable_stopword=ON, which defaults to ON. After enabling the deactivate words function, if the innodb_ft_user_stopword_table option (effective for the full-text index in the specified innodb engine table) customizes the deactivate thesaurus name value, the deactivate word function uses the deactivate thesaurus specified by the innodb_ft_user_stopword_table option, if the innodb_ft_user_stopword_table option is not specified While the innodb_ft_server_stopword_table option (effective for full-text indexes in all innodb engine tables) customizes the deactivated thesaurus name value, the deactivated thesaurus specified by the innodb_ft_server_stopword_table option is used by the same deactivate words function, and if the innodb_ft_server_stopword_table option is not specified, the default deactivated thesaurus, the INNODB_FT_DEFAULT_ STOPWORD table, is used.

Querying this table requires the account to have PROCESS permission. This table is a temporary table of Memory engine.

The following is the content of the information stored in the table

# the default list values of stop words are as follows: information_schema 06:46:38 > select * from INNODB_FT_DEFAULT_STOPWORD +-+ | value | +-+ | a | | about | | an | | are | | at | | be | | by | | com | | de | | en | | for | | how | | I | in | | is | it | la | | of | | on | or | that | the | this | to | was | what | when | where | who | will | with | und | the | www | +-+ 36 rows in set (0.00 rows in set) |

The field has the following meanings:

Value: the deactivated word list value used as the FULLTEXT index of the InnoDB table by default. If the innodb_ft_server_stopword_table or innodb_ft_user_stopword_table option specifies the deactivate thesaurus value, the default deactivated thesaurus is overwritten and the default deactivated thesaurus (INNODB_FT_DEFAULT_STOPWORD table) is not used.

| | INNODB_FT_INDEX_CACHE |

This table provides full-text index tag information for querying newly inserted rows in the innodb storage engine table that contains FULLTEXT indexes. It exists to avoid expensive index reorganization during DML operations, where information for newly inserted full-text indexed words is stored separately in the table until the OPTIMIZE TABLE statement is executed on the table, or when the server is shut down, or when the size of the information stored in the cache exceeds the size specified by the innodb_ft_cache_size or innodb_ft_total_cache_size system configuration parameters

The table does not record data by default, and you need to use the innodb_ft_aux_table system configuration parameter to specify the full-text index data of the newly inserted rows in which table to record.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

The following is the content of the information stored in the table

# set the innodb_ft_aux_table option to specify the newly inserted value of the full-text index in which innodb table needs to be recorded root@localhost: test 11:41:01 > SET GLOBAL innodb_ft_aux_table = 'test/test';Query OK, 0 rows affected (0.00 sec) # execute insert root@localhost: test 11:40:57 > insert into test values (1maxiab dddd') Query OK, 1 row affected (0.00 sec) root@localhost: test 11:41:00 > insert into test values; Query OK, 1 row affected (0.01 sec) root@localhost: test 11:41:01 > insert into test values; Query OK, 1 row affected (0.00 sec) # View recorded data in the INNODB_FT_INDEX_ cache table root@localhost: test 11:59:18 > SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE +-+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +- -+ | dddd | 6 | 13 | 8 | 6 | 4 | | dddd | 6 | 13 | 8 | 7 | 4 | | dddd | 6 | 13 | 8 | 8 | 4 | dddd | 6 | 13 | 8 | 9 | 4 | dddd | 6 | 13 | 8 | 10 | 4 | dddd | 6 | 13 | 8 | 11 | 4 | dddd | 6 | 13 | 8 | 12 | 4 | | dddd | 6 | 13 | 8 | 13 | 4 | +-+-+ 8 rows in set (0.00 sec) |

The field has the following meanings:

WORD: the word text extracted from the column value text of the full-text index of the newly inserted row

FIRST_DOC_ID: the first DOC_ ID value of the word that appears in the FULLTEXT index

LAST_DOC_ID: the last DOC_ ID value of the word that appears in the FULLTEXT index

DOC_COUNT: the number of rows that the word appears in the FULLTEXT index. The same word can appear multiple times in the cache table, but the combination of each DOC_ID column value and POSITION column value will only appear once (that is, unique)

DOC_ID: the DOC_ ID value of the newly inserted row

POSITION: the specific location of the word in the document identified by the DOC_ ID value. This value is not an absolute position, it is the position offset of the WORD column value string in the entire string of the full-text index column value when adding a row of records (equivalent to the subscript position in the python string object, for example: add the full-text index column value to 'edf edfa eeeesdfs', and the WORD column value is recorded as' eeeesdfs', then the POSITION column value is recorded as 9 Indicates that the WORD column value is recorded from the 9th position of the entire full-text index column value string 'edf edfa eeeesdfs')

| | INNODB_FT_INDEX_TABLE |

This table provides word segmentation information about the inverted index used for reverse text lookup in the full-text index of the innodb table

You can observe the auxiliary table of the inverted index by setting innodb_ft_aux_table: after SET GLOBAL innodb_ft_aux_table='test/test'; is set, you can get the participle information in table test in table INNODB _ FT_INDEX_TABLE under information_schema. In order to support full-text search, a column must be mapped to word. In InnoDB, this column is named FTS_DOC_ID, its type is BIGINT UNSIGNED NOT NULL, and the InnoDB storage engine automatically adds a Unique Index named FTS_DOC_ID_INDEX to the column. These operations are done by the storage engine itself, and users can also automatically add FTS_DOC_ID and the corresponding Unique Index when creating the table. Because of the special significance of listing FTS_DOC_ID to gather friends, you must pay attention to the corresponding type when creating it, otherwise you will report an error.

The insertion of the participle in the document is completed when the transaction commits, but for the delete operation, when the transaction commits, it does not delete the record of disk Auxiliary Table, but only deletes the FTS Cache Index record. For the deleted record in Auxiliary Table, the storage engine records its FTS DOCUMENT ID and saves it in DELETE auxiliary table. After setting the parameter innodb_ft_aux_table Users can access the table INNODB _ FT_DELETED under the information_schema schema to observe the deleted FTS Document ID

Because the DML operation of the document does not actually delete the data in the index, on the contrary, records will be inserted in the corresponding DELETED table, so with the permission of the application, the index will become larger and larger, even if some data in the index has been deleted, the query will not select such records. For this reason, InnoDB provides a way to allow users to manually delete the deleted records completely from the index, which is OPTIMIZE TABLE. Because OPTIMIZE TABLE does some other things as well. For example, if Cardinality recalculates, if you want to operate on inverted indexes, you can set SET GLOBAL innodb_optimize_fulltext_only=1;OPTIMIZE TABLE test; via innodb_optimize_fulltext_only (this operation will flush the cache information of full-text indexes to disk)

If many documents are deleted, the OPTIMIZE TABLE operation may take up a lot of time, affect the program concurrency, and greatly reduce the user's response time. Users can limit the actual number of participles per deletion through the parameter innodb_ft_num_word_optimize. The default is 2000.

The account that queries this table requires PROCESS permission. This table is the temporary table of Memory engine.

The following is the content of the information stored in the table

# enable innodb_optimize_fulltext_only system configuration parameters root@localhost: test 12:28:29 > SET GLOBAL innodb_optimize_fulltext_only=ON;Query OK, 0 rows affected (0.00 sec) # execute optimization table statement root@localhost: test 12:28:41 > OPTIMIZE TABLE test +-+ | Table | Op | Msg_type | Msg_text | +-+ | test.test | optimize | status | OK | +-- -+ 1 row in set (0.02 sec) # set the innodb_ft_aux_table system configuration parameters to the table root@localhost: test 12:28:48 > SET GLOBAL innodb_ft_aux_table = 'test/test' Query OK, 0 rows affected (0.00 sec) # query the values recorded in the INNODB_FT_INDEX_TABLE table root@localhost: test 12:28:55 > select * from information_schema.INNODB_FT_INDEX_TABLE +-+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +-+- -+ | edf | 9 | 10 | 2 | 9 | 0 | edf | 9 | 10 | 2 | 10 | 0 | edfa | 9 | | 10 | 2 | 9 | 4 | edfa | 9 | 10 | 2 | 10 | 4 | | eeee | 8 | 8 | 1 | 8 | 4 | eeeesdf | 9 | 9 | 1 | 9 | 9 | eeeesdfs | 10 | 10 | 1 | 10 | 9 | | dddd | 3 | 5 | 3 | 3 | 4 | | dddd | 3 | 5 | 3 | 4 | 4 | dddd | 3 | 5 | 3 | 5 | 4 | ddde | 6 | 6 | 1 | 6 | 4 | | ddee | 7 | 7 | 1 | 7 | 4 | +-+-+ 12 rows in set (0.00 sec)

The meaning of the field is as follows: it has the same meaning as the field of INNODB_FT_INDEX_CACHE table.

This is the end of this issue, and the reference links for this issue are as follows:

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-config-table.html

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-being-deleted-table.html

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-deleted-table.html

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-default-stopword-table.html

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-table-table.html

Https://dev.mysql.com/doc/refman/5.7/en/innodb-ft-index-cache-table.html

| | author profile |

Luo Xiaobo Walk senior database technology expert

IT has worked for many years and is mainly responsible for the database support and after-sale second-line support of MySQL products. Participated in the design and preparation of version release system, lightweight monitoring system, operation and maintenance management platform, database management platform, familiar with MySQL architecture, Innodb storage engine, like to specialize in open source technology, has done offline database topic sharing in public many times, and published many database-related research articles.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.