innodb_index_stats
NAME
innodb_index_stats: Estimated InnoDB depth & split factor of key's B+ TreeTYPE
ViewDESCRIPTION
innodb_index_stats extends the INNODB_INDEX_STATS patch in Percona Server, and presents with estimated depth & split factor of InnoDB keys.
Estimations are optimistic, in that they assume condensed trees. It is possible that the depth is larger than estimated, and that split factor is lower than estimated.
Estimated values are presented as floating point values, although in reality these are integer types.
This view is experimental and in BETA stage.
This view depends upon the INNODB_INDEX_STATS patch in Percona Server.
Note that Percona Server 5.5.8-20.0 version introduced changes to the INNODB_INDEX_STATS schema. This view is compatible with the new schema, and is incompatible with older releases.
STRUCTURE
mysql> DESC common_schema.innodb_index_stats; +--------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------------+------+-----+---------+-------+ | table_schema | varchar(192) | NO | | | | | table_name | varchar(192) | NO | | | | | index_name | varchar(192) | NO | | | | | fields | bigint(21) unsigned | NO | | 0 | | | row_per_keys | varchar(256) | NO | | | | | index_size | bigint(21) unsigned | NO | | 0 | | | leaf_pages | bigint(21) unsigned | NO | | 0 | | | split_factor | decimal(23,1) | NO | | 0.0 | | | index_depth | double(18,1) | NO | | 0.0 | | +--------------+---------------------+------+-----+---------+-------+
SYNOPSIS
Columns of this view map directly to those of INNODB_INDEX_STATS, with the addition of:
- split_factor: Estimated split factor of the index tree
- index_depth: Estimated depth of the index tree. Value is a floating point, though the depth of an index is an integer.
EXAMPLES
Examine index attributes on a specific table:
mysql> SELECT * FROM common_schema.innodb_index_stats WHERE TABLE_NAME='docs_template'; +--------------+---------------+-----------------+--------+--------------------------+------------+------------+--------------+-------------+ | table_schema | table_name | index_name | fields | row_per_keys | index_size | leaf_pages | split_factor | index_depth | +--------------+---------------+-----------------+--------+--------------------------+------------+------------+--------------+-------------+ | databus | docs_template | unique_docs_idx | 4 | 28697340, 28697340, 1, 1 | 834310 | 725102 | 7.6 | 7.6 | | databus | docs_template | PRIMARY | 1 | 1 | 18851201 | 16485198 | 8.0 | 9.0 | | databus | docs_template | doc_timestamp | 2 | 12, 1 | 127577 | 126428 | 111.0 | 3.5 | +--------------+---------------+-----------------+--------+--------------------------+------------+------------+--------------+-------------+