先说下全文索引吧
在表information_schema下面,存在下列表:
其中除了FT_DEFAULT_STOPWORD表外,其余的表要访问的话,需要将全局变量指向表。
This table is empty initially. Before querying it, set the value of the innodb_ft_aux_table
system variable to the name (including the database name) of the table that contains the FULLTEXT
index; for example test/articles
.
set global innodb_ft_aux_table='tpcc1000/article';
1、表mysql.innodb_index_stats
这个表记录了索引名称,以及对应在磁盘上的大小,大小是用number of pages来衡量的。
其中n_diff_pfxNN展示了,在前NN列中,有多少是独立值。
-
n_diff_pfx
: WhereNN
stat_name
=n_diff_pfx01
, thestat_value
column displays the number of distinct values in the first column of the index. Wherestat_name
=n_diff_pfx02
, thestat_value
column displays the number of distinct values in the first two columns of the index, and so on. Wherestat_name
=n_diff_pfx
, theNN
stat_description
column shows a comma separated list of the index columns that are counted.
其中叶子页就是索引中底层的页,存数据的页,非叶子页是存key值和指向其余非叶子页的指针。
Non-leaf level pages contain the "road map" to the leaf level pages/data by including the key value as well as a pointer to either another non-leaf level page (depending on the B-tree depth and location of the intermediate page) or the resulting leaf level page (index page for a nonclustered index, and data page for a clustered index).
2、表information_schema.innodb_sys_indexes
存了Index_id, table_id,类型。
其中若索引为全文索引,page_no是-1。全文索引的表是单独存的。
3、表information_schema.INNODB_SYS_TABLES;
在这个表中能看到全文索引建立的系统表,能根据表id联系起来。