526互联

MySQL学习(9)统计数据

发布时间 2023-10-26 12:52:44作者: 哪过晓得

存储方式

MySQL提供了两种存储统计数据的方式，分别是永久性地存储统计数据和非永久性地存储统计数据，分别存储在磁盘和内存中。系统变量innodb_stats_persistent用来控制统计数据存储在哪里。值为OFF表示存储在内存，值为ON表示存储在磁盘。

SHOW VARIABLES LIKE 'innodb_stats_persistent';

 

image-20231025154044203

磁盘中的永久统计数据

在mysql数据库中有这样两张表：

SHOW TABLES FROM mysql LIKE 'innodb%stats';

 

image-20231025154932440

  • innodb_index_stats存储了索引的统计数据，每一条记录对应一个表的统计数据；

  • innodb_table_stats存储了表的统计数据，每一条记录对应一个索引的统计数据。

innodb_table_stats

SELECT * FROM mysql.innodb_table_stats;

 

image-20231025155257815

这个表的主键是（database_name, table_name）。

  • n_rows

从聚簇索引中选取几个叶子节点页面，统计每个页面中包含的记录数量，然后计算一个页面中平均包含的记录数量，再乘以全部叶子节点数量，得到n_rows值。系统变量innodb_stats_persistent_sample_pages表示统计用到的采样页面数量。

SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';

 

image-20231025223142169

  • clustered_index_size表示聚簇索引占用的页面数量，包含叶子节点和非叶子节点。

  • sum_of_other_index_sizes表示其他索引总共占用的页面数量，包含叶子节点和非叶子节点。

在统计索引占用的页面数量时，需要统计哥哥索引对应的叶子节点段和非叶子节点段分别占用的页面数量。

  1. 从数据字典中找到索引对应的根页面位置。

  2. 从根页面的Page Header中找到叶子节点段和非叶子节点段对应的Segment Header。

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

  4. 针对某个段对应的INODE Entry结构，从中找出该段对应的所有零散热面的地址以及FREE、NOT_FULL和FULL链表的基节点。

  5. 直接统计零散页面的数量，然后从FREE、NOT_FULL和FULL链表的List Length字段中读取该段占用的区的数量。每个区占用64个页，就可以统计出整个段占用的页面。

注意：区中有一些页可能并没有使用，但是在统计clustered_index_size和sum_of_other_index_sizes还是讲这些都计入，索引实际占用的页面可能比这两个统计值小一点。

innodb_index_stats

SELECT * FROM mysql.innodb_index_stats;

 

image-20231025155239507

这个表的主键是（database_name, table_name, index_name, stat_name）。stat_value表示该索引的统计项。

  • n_leaf_pages：表示该索引的叶子节点实际占用多少页面。

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

  • n_diff_pfxNN：表示对应的索引列不重复的值有多少，其中NN表示索引列组合序号，当索引为联合索引呢时表示索引从左到右全部不重复的值。普通二级索引01表示本身，02表示本身+id。对于主键和唯一二级索引，则不需要根据id统计不重复值数量。

sample_size表示采样的页面数量。对于联合索引来说，需要采样的页面数量是innodb_stats_persistent_sample_pages ✖️ 索引中包含的列的个数。当需要采样的页面数量大于该索引的叶子节点的数量时，所有的叶子节点都需要被采样。

自动更新统计数据

  • 开启innodb_stats_auto_recalc

SHOW VARIABLES LIKE 'innodb_stats_auto_recalc';

 

image-20231026122249269

系统变量innodb_stats_auto_recalc决定服务器是否自动重新计算统计数据。每个表都维护了一个变量，该变量记录着对表进行增删改的记录数量。如果发生变动的记录数量超过表大小的10%，就会自动重新计算统计数据，并更新innodb_table_stats表和innodb_index_stats表。

自动计算统计数据的过程时异步发生的，存在一定的延迟。

  • 调用ANALYZE TABLE语句

ANALYZE TABLE single_table;

 

image-20231026122638533

调用ANALYZE TABLE会立刻重新计算统计数据，这个过程是同步的。

手动更新统计数据

通过UPDATE语句可以直接修改innodb_table_stats表和innodb_index_stats表的记录，修改完后，需要使用FLUSH TABLE语句让MySQL优化器重新加载。

FLUSH TABLE single_table;

 

 

内存中的非永久统计数据

非永久性的统计数据在服务器关机或重启后，就会丢失，下次访问表时要重新计算，MySQL现在很少使用这种方式统计数据了。

Innodb_stats_method的使用

innodb_stats_method决定在统计某个索引列中不重复值的数量时如何对待NULL值。

SHOW VARIABLES LIKE 'innodb_stats_method';

 

image-20231026123647511

  • nulls_equal：认为所有 NULL值都是相等的。如果某个索引列的NULL值特别多，这种统计方式会让优化器认为一个值的平均重复次数特别多，导致不使用索引进行访问。

  • nulls_unequal：认为所有NULL值是不想等的。如果某个索引列的NULL值特别多，这种统计方式会让优化器认为一个值的平均重复次数特别少，导致使用索引进行访问。

  • nulss_ignored：直接把NULL值忽略。

 

阅读学习《MySQL是怎样运行的》小孩子4919.