相信大家都了解MySQL中的统计信息,那么统计信息是存放在哪里呢?我们怎么去查看?
在MySQL中提供了两个表记录统计信息的相关内容,分别是 innodb_table_stats
与innodb_index_stats
。下面就这两个表的内容,与大家进行一些分享。
innodb_table_stats
这个表里面的内容还是比较好理解。
重要的列:
last_update
就是最后一次收集统计信息的时间clustered_index_size
聚集索引的page数量sum_of_other_index_sizes
非聚集索引的page数量
通过这些信息我们可以算出聚集索引的大小:
innodb_index_stats
这个表里面输出的内容相对会比较复杂一些。
表结构和测试数据:
我们主要关注的的列:
stat_value
:显示统计值的大小stat_description
:类型的描述stat_name
:此列显示统计的类型 , 会出现下面这些:size
:此时stat_value
显示索引的page数量n_leaf_pages
:此时stat_value
显示叶子节点的数量n_diff_pfxNN
:显示索引字段上唯一值的数量,这里需要特殊说明:
1 主键索引与唯一索引 例如上面结果中
index_name = PRIMARY
时:1)
index_name = PRIMARY
且stat_name = n_diff_pfx01
则stat_value
代表主键索引中第一列distinct之后的数量,从上面的例子我们看到stat_value
是1,因为a这一列的值都是(1)2)
index_name = PRIMARY
且stat_name = n_diff_pfx02
则stat_value
代表主键索引中第一列和第二列distinct之后的数量,从上面的例子我们看到stat_value
是5,因为a,b两列存在的值是(1,1)(1,2)(1,3)(1,4)(1,5)3)
stat_description
中我们可以看到是那几个列的信息4)
n_diff_pfxNN
以此类推2 非唯一索引不同的地方是在原有的列之后会添加上主键索引,这样说可能比较难理解,针对上面查询出来的记过下面详细说明下:
1) 根据表结构定义我们知道i1是一个非唯一索引,是由(c,d)两个列组成的。我们根据上面的结果可以看到除了
n_diff_pfx01,n_diff_pfx02
又多出来了n_diff_pfx03,n_diff_pfx04
,通过stat_description
我们可以看到n_diff_pfx03,n_diff_pfx04
是在原有的(c,d)两列上又多出了(c,d,a) (c,d,a,b)这里就是将主键索引添加到了这里。2) 例如
n_diff_pfx03
的stat_value
是2 代表的就是在原有的非唯一索引上添加了主键索引的第一列(a), 这个时候distinct之后的值是2 所存在的值就是: (10,11,1) (10,12,1)
通过这个表我们可以查看索引选择性如何,并且可以看到组合索引中每一列选择性如何,还可以计算索引的大小:
总结
数据库中的一些系统表对DBA是非常重要的,可以帮助我们排查问题、性能分析、去更好的了解一些机制。
innodb_table_stats
与innodb_index_stats
两张表我们可以了解统计信息、计算索引的大小、索引的选择性如何,也可以做到监控中。
通过5.7的MySQL中添加了Sys Schema也就是让大家不用通过去查看代码的方式去排查各种问题、故障处理等,可见对系统表的学习在日后会更重要。
【参考资料】
作者: 董红禹,沃趣科技(woqutech)。