网站建设与维护专业,百度竞价托管外包,wordpress4.9默认主题,仿新浪微博网站代码我们前面介绍查询成本的时候经常用到一些统计数据#xff0c;比如通过SHOW TABLE STATUS可以看到关于表的统计数据#xff0c;通过SHOW INDEX可以看到关于索引的统计数据#xff0c;那么这些统计数据是怎么来的呢#xff1f;它们是以什么方式收集的呢#xff1f;本章将聚焦…我们前面介绍查询成本的时候经常用到一些统计数据比如通过SHOW TABLE STATUS可以看到关于表的统计数据通过SHOW INDEX可以看到关于索引的统计数据那么这些统计数据是怎么来的呢它们是以什么方式收集的呢本章将聚焦于InnoDB存储引擎的统计数据收集策略。
InnoDB提供了两种存储统计数据的方式
永久性的统计数据非永久性的统计数据
设计MySQL的大佬们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。
基于磁盘的永久性统计数据
当我们选择把某个表以及该表索引的统计数据存放到磁盘上时实际上是把这些统计数据存储到了两个表里
mysql SHOW TABLES FROM mysql LIKE innodb%;
---------------------------
| Tables_in_mysql (innodb%) |
---------------------------
| innodb_index_stats |
| innodb_table_stats |
---------------------------
2 rows in set (0.01 sec)可以看到这两个表都位于mysql系统数据库下面其中
innodb_table_stats存储了关于表的统计数据每一条记录对应着一个表的统计数据。innodb_index_stats存储了关于索引的统计数据每一条记录对应着一个索引的一个统计项的统计数据。
innodb_table_stats
可以看到我们熟悉的single_table表的统计信息就对应着mysql.innodb_table_stats的第三条记录。几个重要统计信息项的值如下
n_rows 的值是9693表明single_table表中大约有9693条记录注意这个数据是估计值。
clustered_index_size 的值是97表明single_table表的聚簇索引占用97个页面这个值是也是一个估计值。
sum_of_other_index_sizes 的值是175表明single_table表的其他索引一共占用175个页面这个值是也是一个估计值。n_rows统计项的收集
为什么老强调n_rows这个统计项的值是估计值呢现在就来揭晓答案。InnoDB统计一个表中有多少行记录的套路是这样的 按照一定算法并不是纯粹随机的选取几个叶子节点页面计算每个页面中主键值记录数量然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。 innodb_stats_persistent_sample_pages的系统变量来控制使用永久性的统计数据时计算统计数据时采样的页面数量。
clustered_index_size和sum_of_other_index_sizes统计项的收集 从数据字典里找到表的各个索引对应的根页面位置。 系统表SYS_INDEXES里存储了各个索引对应的根页面信息。 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的Segment Header。 在每个索引的根页面的Page Header部分都有两个字段 PAGE_BTR_SEG_LEAF表示B树叶子段的Segment Header信息。 PAGE_BTR_SEG_TOP表示B树非叶子段的Segment Header信息。 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。 -从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及FREE、NOT_FULL、FULL链表的基节点。 直接统计零散的页面有多少个然后从那三个链表的List Length字段中读出该段占用的区的大小每个区占用64个页所以就可以统计出整个段占用的页面。 分别计算聚簇索引的叶子结点段和非叶子节点段占用的页面数它们的和就是clustered_index_size 的值按照同样的套路把其余索引占用的页面数都算出来加起来之后就是sum_of_other_index_sizes的值。
这里需要大家注意一个问题我们说一个段的数据在非常多时超过32个页面会以区为单位来申请空间这里头的问题是以区为单位申请空间中有一些页可能并没有使用但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。
innodb_index_stats 先查看index_name列这个列说明该记录是哪个索引的统计信息从结果中我们可以看出来PRIMARY索引也就是主键占了3条记录idx_key_part索引占了6条记录。 针对 index_name 列相同的记录stat_name表示针对该索引的统计项名称stat_value展示的是该索引在该统计项上的值stat_description指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项 n_leaf_pages表示该索引的叶子节点占用多少页面。 size表示该索引共占用多少页面。 n_diff_pfxNN表示对应的索引列不重复的值有多少。其中的NN长得有点儿怪呀什么意思呢 其实NN可以被替换为01、02、03... 这样的数字。比如对于idx_key_part来说n_diff_pfx01表示的是统计key_part1这单单一个列不重复的值有多少。n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。n_diff_pfx03表示的是统计key_part1、key_part2、key_part3这三个列组合起来不重复的值有多少。n_diff_pfx04表示的是统计key_part1、key_part2、key_part3、id这四个列组合起来不重复的值有多少。在计算某些索引列中包含多少不重复值时需要对一些叶子节点页面进行采样size列就表明了采样的页面数量是多少。
基于内存的非永久性统计数据
当我们把系统变量innodb_stats_persistent的值设置为OFF时之后创建的表的统计数据默认就都是非永久性的了或者我们直接在创建表或修改表时设置STATS_PERSISTENT属性的值为0那么该表的统计数据就是非永久性的了。
innodb_stats_method的使用
设计MySQL的大佬蛮贴心的他们提供了一个名为 innodb_stats_method 的系统变量相当于在计算某个索引列不重复值的数量时如何对待NULL值这个锅甩给了用户这个系统变量有三个候选值 nulls_equal认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。 如果某个索引列中NULL值特别多的话这种统计方式会让优化器认为某个列中平均一个值重复次数特别多所以倾向于不使用索引进行访问。 nulls_unequal认为所有NULL值都是不相等的。 如果某个索引列中NULL值特别多的话这种统计方式会让优化器认为某个列中平均一个值重复次数特别少所以倾向于使用索引进行访问。 nulls_ignored直接把NULL值忽略掉。
反正这个锅是甩给用户了当你选定了innodb_stats_method值之后优化器即使选择了不是最优的执行计划那也跟设计MySQL的大佬们没关系了 当然对于用户的我们来说最好不在索引列中存放NULL值才是正解。
