InnoDB INFORMATION_SCHEMA FULLTEXT索引表
下表提供了FULLTEXT
索引的元数据:
mysql>SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%'; +------------------------------------------- + | Tables_in_INFORMATION_SCHEMA (INNODB_FT%) | +------------------------------------------- + | INNODB_FT_CONFIG | | INNODB_FT_BEING_DELETED | | INNODB_FT_DELETED | | INNODB_FT_DEFAULT_STOPWORD | | INNODB_FT_INDEX_TABLE | | INNODB_FT_INDEX_CACHE | +------------------------------------------- +
表格总览
INNODB_FT_CONFIG
:提供有关表的FULLTEXT
索引和相关处理的元数据InnoDB
。INNODB_FT_BEING_DELETED
:提供INNODB_FT_DELETED
表的快照;仅在OPTIMIZE TABLE
维护操作期间使用。当OPTIMIZE TABLE
运行时,该INNODB_FT_BEING_DELETED
表被清空,并且DOC_ID
值是从除去INNODB_FT_DELETED
表。由于INNODB_FT_BEING_DELETED
通常的内容的生存期很短,因此该表的监视或调试实用程序有限。有关OPTIMIZE TABLE
在具有FULLTEXT
索引的表上运行的信息,请参见“微调MySQL全文搜索”。INNODB_FT_DELETED
:存储从表的FULLTEXT
索引中删除的行InnoDB
。为了避免在DML操作中为索引进行昂贵的索引重组InnoDB
FULLTEXT
,有关新删除的单词的信息将单独存储,在进行文本搜索时会从搜索结果中过滤掉,并且仅在发布表OPTIMIZE TABLE
语句时才从主搜索索引中删除InnoDB
。INNODB_FT_DEFAULT_STOPWORD
:保存在表上创建索引时默认使用的停用词列表。FULLTEXT
InnoDB
有关该
INNODB_FT_DEFAULT_STOPWORD
表的信息,请参见“全文停用词”。INNODB_FT_INDEX_TABLE
:提供有关用于对表FULLTEXT
索引进行文本搜索的反向索引的信息InnoDB
。INNODB_FT_INDEX_CACHE
:提供有关FULLTEXT
索引中新插入的行的令牌信息。为了避免在DML操作期间进行昂贵的索引重组,有关新索引词的信息将单独存储,并且仅在OPTIMIZE TABLE
运行,服务器关闭或高速缓存大小超过innodb_ft_cache_size
或所定义的限制时才与主搜索索引组合innodb_ft_total_cache_size
系统变量。
注意除了
INNODB_FT_DEFAULT_STOPWORD
表外,这些表最初都是空的。在查询任何一个之前,请将innodb_ft_aux_table
系统变量的值设置为包含FULLTEXT
索引的表的名称(包括数据库名称)(例如test/articles
)。
示例15.5 InnoDB FULLTEXT索引INFORMATION_SCHEMA表
本示例使用带有FULLTEXT
索引的表来演示FULLTEXT
索引INFORMATION_SCHEMA
表中包含的数据。
创建一个带有
FULLTEXT
索引的表并插入一些数据:mysql>
CREATE TABLE articles ( id INTUNSIGNED AUTO_INCREMENT NOT NULLPRIMARY KEY , title VARCHAR(200), body TEXT,FULLTEXT (title,body) )ENGINE =InnoDB; mysql>INSERT INTO articles (title,body)VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'), ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we will show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...');将
innodb_ft_aux_table
变量设置为带有FULLTEXT
索引的表的名称。如果未设置此变量,则InnoDB
FULLTEXT
INFORMATION_SCHEMA
表为空,但除外INNODB_FT_DEFAULT_STOPWORD
。mysql>
SET GLOBAL innodb_ft_aux_table = 'test/articles';查询该
INNODB_FT_INDEX_CACHE
表,该表显示有关FULLTEXT
索引中新插入的行的信息。为了避免在DML操作期间进行昂贵的索引重组,新插入的行的数据将保留在FULLTEXT
索引缓存中,直到OPTIMIZE TABLE
运行为止(或者直到服务器关闭或超出缓存限制为止)。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHELIMIT 5; +------------ +-------------- +------------- +----------- +-------- +---------- + | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------ +-------------- +------------- +----------- +-------- +---------- + | 1001 | 5 | 5 | 1 | 5 | 0 | | after | 3 | 3 | 1 | 3 | 22 | | comparison | 6 | 6 | 1 | 6 | 44 | | configured | 7 | 7 | 1 | 7 | 20 | | database | 2 | 6 | 2 | 2 | 31 | +------------ +-------------- +------------- +----------- +-------- +---------- +启用
innodb_optimize_fulltext_only
系统变量并OPTIMIZE TABLE
在包含FULLTEXT
索引的表上运行。此操作将FULLTEXT
索引缓存的内容刷新到主FULLTEXT
索引。innodb_optimize_fulltext_only
更改OPTIMIZE TABLE
语句在InnoDB
表上的操作方式,并打算在对InnoDB
带有FULLTEXT
索引的表进行维护操作期间临时启用该语句。mysql>
SET GLOBAL innodb_optimize_fulltext_only=ON ; mysql>OPTIMIZE TABLE articles; +--------------- +---------- +---------- +---------- + | Table | Op | Msg_type | Msg_text | +--------------- +---------- +---------- +---------- + | test.articles | optimize | status | OK | +--------------- +---------- +---------- +---------- +查询该
INNODB_FT_INDEX_TABLE
表以参见有关主FULLTEXT
索引中数据的信息,包括有关刚从FULLTEXT
索引缓存中刷新的数据的信息。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLELIMIT 5; +------------ +-------------- +------------- +----------- +-------- +---------- + | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------ +-------------- +------------- +----------- +-------- +---------- + | 1001 | 5 | 5 | 1 | 5 | 0 | | after | 3 | 3 | 1 | 3 | 22 | | comparison | 6 | 6 | 1 | 6 | 44 | | configured | 7 | 7 | 1 | 7 | 20 | | database | 2 | 6 | 2 | 2 | 31 | +------------ +-------------- +------------- +----------- +-------- +---------- +INNODB_FT_INDEX_CACHE
由于该OPTIMIZE TABLE
操作刷新了FULLTEXT
索引缓存,因此该表现在为空。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHELIMIT 5; Empty set (0.00 sec)从
test/articles
表中删除一些记录。mysql>
DELETE FROM test.articlesWHERE id < 4;查询
INNODB_FT_DELETED
表。该表记录了从FULLTEXT
索引中删除的行。为了避免在DML操作期间进行昂贵的索引重组,有关新删除记录的信息将单独存储,在进行文本搜索时会从搜索结果中过滤掉,并在运行时从主搜索索引中删除OPTIMIZE TABLE
。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +-------- + | DOC_ID | +-------- + | 2 | | 3 | | 4 | +-------- +运行
OPTIMIZE TABLE
以删除已删除的记录。mysql>
OPTIMIZE TABLE articles; +--------------- +---------- +---------- +---------- + | Table | Op | Msg_type | Msg_text | +--------------- +---------- +---------- +---------- + | test.articles | optimize | status | OK | +--------------- +---------- +---------- +---------- +该
INNODB_FT_DELETED
表现在应该为空。mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; Empty set (0.00 sec)查询
INNODB_FT_CONFIG
表。下表包含有关FULLTEXT
索引和相关处理的元数据:optimize_checkpoint_limit
:OPTIMIZE TABLE
停止运行的秒数。synced_doc_id
:下一个DOC_ID
要发行。stopword_table_name
:database/table
用户定义的停用词表的名称。VALUE
如果没有用户定义的停用词表,则该列为空。use_stopword
:指示是否使用停用词表,该词在FULLTEXT
创建索引时定义。
mysql>
SELECT *FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +--------------------------- +------- + | KEY | VALUE | +--------------------------- +------- + | optimize_checkpoint_limit | 180 | | synced_doc_id | 8 | | stopword_table_name | | | use_stopword | 1 | +--------------------------- +------- +禁用
innodb_optimize_fulltext_only
,因为仅打算暂时启用它:mysql>
SET GLOBAL innodb_optimize_fulltext_only=OFF;