《MySQL是怎样运行的 —— 从跟上理解MySQL》—— 第十三章
一、统计数据的存储方式
InnoDB提供了两种存储统计数据的方式：
- 永久性的统计数据：这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。
- 非永久性的统计数据：这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。
MySQL提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。
在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。
InnoDB默认是以表为单位来收集和存储统计数据的。可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0); |
当STATS_PERSISTENT=1时，表明把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明把该表的统计数据临时的存储到内存中。
二、基于磁盘的永久性统计数据
统计数据在落盘的时候，实际上是存储到了两个表里：
mysql> SHOW TABLES FROM mysql LIKE 'innodb%'; |
innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。
2.1 innodb_table_stats
innodb_table_stats表中的各个列：
| 字段名 | 描述 |
|---|---|
database_name |
数据库名 |
table_name |
表名 |
last_update |
本条记录最后更新时间 |
n_rows |
表中记录的条数 |
clustered_index_size |
表的聚簇索引占用的页面数量 |
sum_of_other_index_sizes |
表的其他索引占用的页面数量 |
这个表的主键是(database_name,table_name)，即innodb_table_stats表的每条记录代表着一个表的统计信息。
2.1.1 n_rows统计项的收集
估计值。InnoDB统计一个表中有多少行记录：按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的n_rows值。
可以看出这个n_rows值精确与否取决于统计时采样的页面数量，MySQL提供了innodb_stats_persistent_sample_pages系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的n_rows值越精确，但是统计耗时也就最久；该值设置的越小，统计出的n_rows值越不精确，但是统计耗时特别少。所以在实际使用是需要权衡利弊，该系统变量的默认值是20。
可以单独设置某个表的采样页面的数量，设置方式就是在创建或修改表的时候通过指定STATS_SAMPLE_PAGES属性来指明该表的统计数据存储方式：
CREATE TABLE 表名 (...) Engine=InnoDB, STATS_SAMPLE_PAGES = 具体的采样页面数量; |
2.1.2 clustered_index_size和sum_of_other_index_sizs统计项的收集
- 从数据字典里找到表的各个索引对应的根页面位置。系统表
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时都把它们算进去了，所以说聚簇索引和其他的索引占用的页面数可能比这两个值要小一些。
2.2 innodb_index_stats
innodb_index_stats表中的各个列：
| 字段名 | 描述 |
|---|---|
database_name |
数据库名 |
table_name |
表名 |
index_name |
索引名 |
last_update |
本条记录最后更新时间 |
stat_name |
统计项的名称 |
stat_value |
对应的统计项的值 |
sample_size |
为生成统计数据而采样的页面数量 |
stat_description |
对应的统计项的描述 |
这个表的主键是(database_name,table_name,index_name,stat_name)，其中的stat_name是指统计项的名称，也就是说innodb_index_stats表的每条记录代表着一个索引的一个统计项。
mysql> SELECT * FROM mysql.innodb_index_stats WHERE table_name = 'single_table'; |
index_name列，这个列说明该记录是哪个索引的统计信息，从结果中可以看出来，PRIMARY索引（也就是主键）占了3条记录，idx_key_part索引占了6条记录。针对
index_name列相同的记录，stat_name表示针对该索引的统计项名称，stat_value展示的是该索引在该统计项上的值，stat_description指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：n_leaf_pages：表示该索引的叶子节点占用多少页面。size：表示该索引共占用多少页面。n_diff_pfxNN：表示对应的索引列不重复的值有多少（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这四个列组合起来不重复的值有多少。
⚠️注意：对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于idx_key1来说，key1列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。对于主键和唯一二级索引则没有这个问题，它们本身就可以保证索引列值的不重复，所以也不需要再统计一遍在索引列后加上主键值的不重复值有多少。比如上面的idx_key1有n_diff_pfx01、n_diff_pfx02两个统计项，而idx_key2却只有n_diff_pfx01一个统计项。
在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采样，
size列就表明了采样的页面数量是多少。
⚠️注意：对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。当需要采样的页面数量大于该索引的叶子节点数量的话，就直接采用全表扫描来统计索引列的不重复值数量了。
2.3 定期更新统计数据
随着不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats和innodb_index_stats表里的统计数据需要变更。MySQL提供了如下两种更新统计数据的方式：
开启
innodb_stats_auto_recalc。系统变量
innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新innodb_table_stats和innodb_index_stats表。自动重新计算统计数据的过程是异步发生的，即使表中变动的记录数超过了10%，自动重新计算统计数据也不会立即发生，可能会延迟几秒才会进行计算。可以单独为某个表设置是否自动重新计算统计数的属性，设置方式就是在创建或修改表的时候通过指定
STATS_AUTO_RECALC属性来指明该表的统计数据存储方式：CREATE TABLE 表名 (...) Engine=InnoDB, STATS_AUTO_RECALC = (1|0);
ALTER TABLE 表名 Engine=InnoDB, STATS_AUTO_RECALC = (1|0);手动调用
ANALYZE TABLE语句来更新统计信息如果
innodb_stats_auto_recalc系统变量的值为OFF的话，可以手动调用ANALYZE TABLE语句来重新计算统计数据。ANALYZE TABLE table_nameANALYZE TABLE语句会立即重新计算统计数据，即这个过程是同步的。
三、基于内存的非永久性统计数据
详情见书即可。
MySQL提供了一个名为innodb_stats_method的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL值交给用户处理，这个系统变量有三个候选值：
nulls_equal：认为所有NULL值都是相等的。这个值也是innodb_stats_method的默认值。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别多，所以倾向于不使用索引进行访问。nulls_unequal：认为所有NULL值都是不相等的。如果某个索引列中NULL值特别多的话，这种统计方式会让优化器认为某个列中平均一个值重复次数特别少，所以倾向于使用索引进行访问。nulls_ignored：直接把NULL值忽略掉。