InnoDB的统计数据存储方案

InnoDB支持永久性的统计和非永久性的统计，即磁盘存储统计信息和内存存储统计信息

系统变量innodb_stats_persistent用于配置使用的统计方案，5.6以前默认值为OFF，即存储到内存，5.6以后默认值为ON，即存储到磁盘

也可以指定特定表的统计方案，使用STATS_PERSISTENT关键字，例如：

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0); 

ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

其中，基于内存的非永久统计数据是不常用的，可以不用太关注

基于磁盘的统计数据 - 系统表

在mysql库里面有两个表：innodb_index_stats、innodb_table_stats

看名字也大概可以看出来，一个是存索引的统计信息，一个是存表的统计信息

innodb_table_stats

打开这个表，可以看到有六个列：

• database_name、table_name：库名和表名

• last_update：更新时间

• n_rows：表行数

• clustered_index_size：聚簇索引树的页数

• sum_of_other_index_sizes：表的其他索引占用的页面数量

n_rows是一个估计值，因为它是受到统计周期影响的，统计周期越小，肯定越准确，统计周期也可以使用系统变量innodb_stats_persistent_sample_pages控制，默认值为20，配置越大，统计越精确

也可以指定单表

CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;
ALTER TABLE 表名 Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量;

innodb_index_stats

这个表共有8个列：

• database_name、table_name、index_name：库名表名索引名

• last_update：更新时间

• stat_name：统计项名称

• stat_value：统计项值

• sample_size：生成该统计项采样了多少页

• stat_description：统计项描述

其中stat_name和stat_description是对应的，共有三种枚举：

• n_leaf_pages：描述该索引的叶子节点占多少页

• size：描述该索引共占

• n_diff_pfxNN：对应的索引列不重复的有多少条记录

n_diff_pfxNN可以额外解释下，假如表有两个索引：primarykey(id, create_time)、key1(name, age, sex)

则这个表将会生成如下数据（忽略其他行和无关列）：

即它会自左向右统计索引中每一列，统计完自己的列，再去统计主键，直到把索引列和主键列全部纳入统计，这时候每个维度是唯一一条，因为主键肯定是唯一的

如果索引是UNIQUE KEY，那就无需把主键纳入统计了，因为它自己就是唯一的

NULL值统计 - innodb_stats_method

如果某一列的枚举值为1、2、NULL、NULL

那么计算该列的不重复数据量，应该要不要把NULL算进去？NULL算同一个还是不同的？

其实是有一个系统变量决定的：innodb_stats_method，枚举值有三个：

• nulls_equal：NULL值认为是单独的数据，且所有NULL值含义相同，因此上面枚举中，不重复数据为3条，这种模式下，如果NULL值太多了，都会被认为是重复数据，不利于索引访问

• nulls_unequal：NULL值认为是单独的数据，且每个NULL含义不同，因此上面枚举中，不重复数据为4条，这种模式下利于索引访问

• nulls_ignore：忽略NULL值，因此上面枚举中，不重复数据为2条，根据业务选择即可