innodb中统计数据是如何收集的

参考：https://www.phpmianshi.com/?id=142

InnoDB 统计数据如何查看

    1. 通过SHOW TABLE STATUS可以看到关于表的统计数据

    2. 通过SHOW INDEX可以看到关于索引的统计数据

 

InnoDB提供了两种存储统计数据的方式

    1. 永久性的统计数据:这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

    2. 非永久性的统计数据:这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

    3. 通过innodb_stats_persistent来决定是采用哪种方式存储统计数据，在5.6.6之前默认是OFF--即存储到内存，后来默认存储到磁盘

    4. 以表为单位来收集和存储统计数据的，即一些表的相关统计信息可以到内存，另外一些可以到磁盘。

    5. 创建表的时候指定属性STATS_PERSISTENT，等于1代表到磁盘 等于0代表到内存，未指定则采用innodb_stats_persistent

基于磁盘的永久性统计数据

1. 存储索引和表的统计数据的系统表分别是：innodb_index_stats  和innodb_table_stats

 

2. innodb_table_stats

包含的属性

database_name   数据库名
table_name  表名
last_update 本条记录最后更新时间
n_rows  表中记录的条数
clustered_index_size    表的聚簇索引占用的页面数量
sum_of_other_index_sizes    表的其他索引占用的页面数量

 

n_rows统计项的收集

1. 按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量

2. 然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。

3. 通过innodb_stats_persistent_sample_pages可以控制采样的页面数量--默认是20

4. 可以在创建表的时候指定STATS_SAMPLE_PAGES来决定不同的表计算nrows的页面数量。没有指定的话就用innodb_stats_persistent_sample_pages作为默认值

 

clustered_index_size和sum_of_other_index_sizes统计项的收集

1. 从数据字典里找到表的各个索引对应的根页面位置，系统表SYS_INDEXES里存储了各个索引对应的根页面信息。

2. 从根页面的Page Header里找到叶子节点段和非叶子节点段对应的Segment Header。
-在每个索引的根页面的Page Header部分都有两个字段：PAGE_BTR_SEG_LEAF：表示B+树叶子段的Segment Header信息。
PAGE_BTR_SEG_TOP：表示B+树非叶子段的Segment Header信息。

3. 从叶子节点段和非叶子节点段的Segment Header中找到这两个段对应的INODE Entry结构。

4. 从对应的INODE Entry结构中可以找到该段对应所有零散的页面地址以及FREE、NOT_FULL、FULL链表的基节点

5. 直接统计零散的页面有多少个，然后从那三个链表的List Length字段中读出该段占用的区的大小，每个区占用64个页，所以就可以统计出整个段占用的页面

6. 分别计算聚簇索引的叶子节点段和非叶子节点段占用的页面数，它们的和就是clustered_index_size的值

7. 按照同样的套路把其余索引占用的页面数都算出来，加起来之后就是sum_of_other_index_sizes的值

8. 我们说一个段的数据在非常多时（超过32个页面），会以区为单位来申请空间，这里头的问题是以区为单位申请空间中有一些页可能并没有使用，但是在统计clustered_index_size和sum_of_other_index_sizes时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。

 

3. innodb_index_stats

包含的属性

database_name   数据库名
table_name  表名
index_name  索引名
last_update 本条记录最后更新时间
stat_name   统计项的名称
stat_value  对应的统计项的值
sample_size 为生成统计数据而采样的页面数量
stat_description    对应的统计项的描述

 

stat_name的类型

1. n_leaf_pages：表示该索引的叶子节点占用多少页面。

2. size：表示该索引共占用多少页面。

3. n_diff_pfxNN：表示对应的索引列不重复的值有多少

n_diff_pfx01表示的是统计key_part1这单单一个列不重复的值有多少。
n_diff_pfx02表示的是统计key_part1、key_part2这两个列组合起来不重复的值有多少。
n_diff_pfx03表示的是统计key_part1、key_part2、key_part3这三个列组合起来不重复的值有多少。
类似01代表索引列只有一个，02则代表2个，后面依次如此，对于非主键或者非唯一索引，他们的n_diff_pfxNN 不仅仅包含索引列本身，还需要再加上主键。

4. 在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，size列就表明了采样的页面数量是多少。

5. 对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数

6. 当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了

 

定期更新统计数据

1. innodb_table_stats和innodb_index_stats表更新的方式：

2. innodb_stats_auto_recalc：决定着服务器是否自动重新计算统计数据，它的默认值是ON，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的

3. STATS_AUTO_RECALC参数可以指定某个表是否采用这种方式。

4. 手动调用ANALYZE TABLE语句来更新统计信息，如果是innodb_stats_auto_recalc是OFF的情况，可以手动。该操作是同步的比较损耗性能。

 

让修改的统计数据生效

1. 首先通过update修改统计表

2. 然后调用FLUSH TABLE table_name 这个tablename是我们统计的table，而不是统计表

 

基于内存的非永久性统计数据

1.与永久性的统计数据不同，非永久性的统计数据采样的页面数量是由innodb_stats_transient_sample_pages控制的，这个系统变量的默认值是8

2.所以导致MySQL查询优化器计算查询成本的时候依赖的是经常变化的统计数据，也就会生成经常变化的执行计划

innodb_stats_method

1.我们知道索引列不重复的值的数量这个统计数据对于MySQL查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行

2.通过重复多少行可以应用到单表查询中单点区间太多，SELECT * FROM tbl_name WHERE key IN ('xx1', 'xx2', ..., 'xxn');所以直接依赖统计数据中的平均一个值重复多少行来计算单点区间对应的记录数量。

3.连接查询时，如果有涉及两个表的等值匹配连接条件，该连接条件对应的被驱动表中的列又拥有索引时，则可以使用ref访问方法来对被驱动表进行查询

4.通过innodb_stats_method 来可以设置对于列值为NULL的处理

当结果为nulls_equal:认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值
如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。
nulls_unequal：认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。
nulls_ignored：直接把NULL值忽略掉。

5.最好不在索引列中存放NULL值才是正解。