MySQL系统表中的InnoDB索引统计

MySQL系统库中可以通过mysql系统库中的innodb_table_stats表和innodb_index_stats表来查询优化器的统计数据，这两张表是基于磁盘的永久性统计数据。

innodb_table_stats表的介绍：

innodb_table_stats中存储了关于所有innodb引擎表的统计数据，每一条记录对应着一个表的统计数据。

对于该表中每一列中记录的数据的含义介绍如下:

database_name：记录相应数据库的名称

table_name：记录相应的表名，分区名或子分区名

last_update：记录最后一次更新统计信息的时间

n_rows：估算的表中包含的行数

cluster_index_size：聚簇索引中的页数（单位page）

sum_of_other_index_sizes：其它索引的总页数（单位page）

Innodb_index_stats表的介绍：

innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。对于每个B-tree索引，此表中都有几行来包含相关数据，例如它提供索引的每个部分的唯一值数量（基数）、索引中的叶子页数量以及索引的总大小。

对于该表中每一列中记录的数据的含义介绍如下:

database_name：记录相应数据库的名称

table_name：记录相应的表名，分区名或子分区名

index_name：索引名称

last_update：记录最后一次更新统计信息的时间

stat_name：记录统计信息的名称，其值在stat_value列中报告

stat_value：stat_name列中命名的统计信息的值

sample_size：stat_value列中提供的估值页面的采样页数

stats_description：stat_name列中统计信息的描述信息

示例：

创建一个新表t4包含一个主索引(列a、b)二级索引(列c、d)和唯一索引(列e、f)，并插入五行数据，如下所示。

首先查看innodb_table_stats表中关于t4表的相关信息，如下图所示，表t4的表统计信息显示InnoDB最后更新表统计信息的时间为(2023-04-27 17:49:56)，表中的行记录数为5，集簇索引大小为1个索引页，其它索引大小为2个索引页。

innodb_index_stats表包含每个索引的多行。innodb_index_stats表中的每一行都提供了与特定索引统计相关的数据，在stat_name列中显示命名，在stat_description列中显示描述。

n_diff_pfxNN:索引中前NN列的基数，因此对于多列的索引，就会对应多行来显示，stat_description列包含了该统计信息对应的列。例如上面示例中索引a的统计信息n_diff_pfx01对应列，即统计即基数值为1是对应这一列的，n_diff_pfx02对应列（列a，b），也就是统计的基数值5是对应两个列的。

n_leaf_pages:索引中的叶子页总数。可将其与n_diff_pfxNN统计信息的样本大小进行比较，从而确定索引的采样比例。比如索引a，n_leaf_pages为1，n_diff_pfxNN对应sample_size为1，则采样比例为100%。

size:索引中总页数，包含非叶子页。

接下来进一步说明基数数据的n_diff_pfxNN统计量：

表t1使用主索引（列a，b），辅助索引（列c，d）和唯一索引（列e，f）。

• 对于PRIMARY索引，有两个n_diff％行。行数等于索引中的列数。
  对于非唯一索引，索引会附加主键列。

• • index_name = PRIMARY和stat_name = n_diff_pfx01
    stat_value为1，表示索引的第一列中存在单个不同的值（列a）。 通过查看表t1中的列a中的数据来确认列a中的不同值的数量，其中存在单个不同的值（1）。 计数列（a）显示在结果集的stat_description列中。
  • index_name = PRIMARY和stat_name = n_diff_pfx02
    stat_value为5，表示索引（a，b）的两列中有五个不同的值。 通过查看表t1中列a和b中的数据来确认列a和b中的不同值的数量，其中有五个不同的值：（1,1），（1,2），（1,3） ，（1,4）和（1,5）。 计数列（a，b）显示在结果集的stat_description列中。

• 对于二级索引（i1），有四个n_diff％行
  对于二级索引（i1），有四个n_diff％行。 仅为辅助索引（c，d）定义了两列，但是辅助索引有四个n_diff％行，因为InnoDB使用主键为所有非唯一索引添加后缀。

• • index_name = i1和stat_name = n_diff_pfx01
    stat_value为1，表示索引的第一列（列c）中存在单个不同的值。通过查看表t1中列c中的数据来确认列c中的不同值的数量，其中存在单个不同的值：（10）。计数列（c）显示在结果集的stat_description列中。
  • index_name = i1和stat_name = n_diff_pfx02
    stat_value为2，表示索引（c，d）的前两列中有两个不同的值。通过查看表t1中列c和d中的数据来确认列c和d中的不同值的数量，其中存在两个不同的值：（10,11）和（10,12）。计数列（c，d）显示在结果集的stat_description列中。
  • index_name = i1和stat_name = n_diff_pfx03
    stat_value为2，表示索引的前三列中有两个不同的值（c，d，a）。通过查看表c1中的列c，d和a中的数据来确认列c，d和a中的不同值的数量，其中有两个不同的值：（10,11,1）和（10， 12,1）。计数列（c，d，a）显示在结果集的stat_description列中。
  • index_name = i1和stat_name = n_diff_pfx04
    stat_value为5，表示索引的四列中有五个不同的值（c，d，a，b）。通过查看表t1中列c，d，a和b中的数据来确认列c，d，a和b中的不同值的数量，其中有五个不同的值：(10,11,1,1)，(10,11,1,2)，(10,11,1,3)，(10,12,1,4)和(10,12,1,5)。计数列（c，d，a，b）显示在结果集的stat_description列中。

• 对于唯一索引（i2uniq），有两个n_diff％行

• • index_name = i2uniq和stat_name = n_diff_pfx01
    stat_value为2，表示索引的第一列中有两个不同的值（列e）。 通过查看表t1中的列e中的数据来确认列e中的不同值的数量，其中存在两个不同的值：（100）和（200）。 计数列（e）显示在结果集的stat_description列中。
  • index_name = i2uniq和stat_name = n_diff_pfx02
    stat_value为5，表示索引的两列中有五个不同的值（e，f）。 通过查看表t1中的列e和f中的数据来确认列e和f中的不同值的数量，其中存在五个不同的值：（100,101），（200,102），（100,103），（200,104）和（100105）。 计数列（e，f）显示在结果集的stat_description列中。