InnoDB统计资料是如何收集的

innodb以表为单位来搜集统计资料，分为两种资料

  1. 基於磁碟的永久性统计资料。顾名思义存在磁碟，服务器重启依然存在。
  2. 基於记忆体的非永久性统计资料。存在记忆体，服务器关闭就会把资料清除。

基於磁碟的永久性统计资料

当我们选择把某表及该表索引的统计资料存放在磁碟中，实际上是把这些统计资料存在下面两个表中。

mysql> show tables from mysql like 'innodb%stats';
+--------------------------------+
| Tables_in_mysql (innodb%stats) |
+--------------------------------+
| innodb_index_stats             |
| innodb_table_stats             |
+--------------------------------+
2 rows in set (0.11 sec)

innodb_table_stats：存放表的统记资料，每笔纪录对应着一个表的统计资料。
innodb_index_stats：存放索引的统记资料，每笔纪录对应着一个索引的统计项资料。

mysql> select * from mysql.innodb_table_stats;
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| database_name | table_name           | last_update         | n_rows   | clustered_index_size | sum_of_other_index_sizes |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+
| mysql         | component            | 2021-09-02 12:49:09 |        1 |                    1 |                        0 |
| ryan_demo_db  | per2                 | 2021-09-10 18:15:15 | 18329781 |                50688 |                        0 |
| ryan_demo_db  | ryan_demo2_table     | 2021-09-07 16:29:23 |       16 |                    1 |                        0 |
| ryan_demo_db  | ryan_demo_table      | 2021-09-07 15:41:08 |       16 |                    1 |                        0 |
| ryan_demo_db  | score                | 2021-09-20 14:28:38 |        4 |                    1 |                        0 |
| ryan_demo_db  | single_table         | 2021-09-10 13:56:05 |    10146 |                   97 |                      144 |
| ryan_demo_db  | single_table2        | 2021-09-24 12:55:16 |        0 |                    1 |                        4 |
| ryan_demo_db  | student              | 2021-09-20 14:18:24 |        3 |                    1 |                        0 |
| ryan_demo_db  | t1                   | 2021-09-20 13:52:33 |        3 |                    1 |                        0 |
| ryan_demo_db  | t2                   | 2021-09-20 13:52:54 |        2 |                    1 |                        0 |
| sys           | sys_config           | 2018-07-17 11:08:23 |        6 |                    1 |                        0 |
+---------------+----------------------+---------------------+----------+----------------------+--------------------------+

可以直接看到资料库下每个表的纪录笔数(n_rows)，以及表的聚簇索引所占用的页面数(clustered_index_size)和表的其他索引所占用的页面数(sum_of_other_index_sizes)。

mysql> select * from mysql.innodb_index_stats;
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name           | index_name                  | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | n_diff_pfx01 |   18329781 |          20 | DB_ROW_ID                         |
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | n_leaf_pages |      50544 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | per2                 | GEN_CLUST_INDEX             | 2021-09-10 18:15:15 | size         |      50688 |        NULL | Number of pages in the index      |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | n_diff_pfx01 |         16 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | ryan_demo2_table     | GEN_CLUST_INDEX             | 2021-09-07 16:29:23 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | n_diff_pfx01 |         16 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | ryan_demo_table      | GEN_CLUST_INDEX             | 2021-09-07 15:41:08 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_diff_pfx01 |          2 |           1 | number                            |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_diff_pfx02 |          4 |           1 | number,subject                    |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | score                | PRIMARY                     | 2021-09-20 14:28:38 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | n_diff_pfx01 |      10146 |          20 | id                                |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | n_leaf_pages |         70 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | PRIMARY                     | 2021-09-10 13:56:05 | size         |         97 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          17 | key1                              |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          17 | key1,id                           |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | n_leaf_pages |         17 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key1                    | 2021-09-10 13:56:05 | size         |         18 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          17 | key3                              |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          17 | key3,id                           |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | n_leaf_pages |         17 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key3                    | 2021-09-10 13:56:05 | size         |         18 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          59 | key_part1                         |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx02 |      10000 |          59 | key_part1,key_part2               |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx03 |      10000 |          59 | key_part1,key_part2,key_part3     |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_diff_pfx04 |      10000 |          59 | key_part1,key_part2,key_part3,id  |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | n_leaf_pages |         59 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | idx_key_part                | 2021-09-10 13:56:05 | size         |         97 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | n_diff_pfx01 |      10000 |          10 | key2                              |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | n_leaf_pages |         10 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table         | uk_key2                     | 2021-09-10 13:56:05 | size         |         11 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | id                                |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | PRIMARY                     | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key1                              |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key1,id                           |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key1                    | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key3                              |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key3,id                           |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key3                    | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key_part1                         |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx02 |          0 |           1 | key_part1,key_part2               |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx03 |          0 |           1 | key_part1,key_part2,key_part3     |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_diff_pfx04 |          0 |           1 | key_part1,key_part2,key_part3,id  |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | idx_key_part                | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | n_diff_pfx01 |          0 |           1 | key2                              |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | single_table2        | uk_key2                     | 2021-09-24 12:55:16 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | n_diff_pfx01 |          3 |           1 | number                            |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | student              | PRIMARY                     | 2021-09-20 14:18:24 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | n_diff_pfx01 |          3 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | t1                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:33 | size         |          1 |        NULL | Number of pages in the index      |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | n_diff_pfx01 |          2 |           1 | DB_ROW_ID                         |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| ryan_demo_db  | t2                   | GEN_CLUST_INDEX             | 2021-09-20 13:52:54 | size         |          1 |        NULL | Number of pages in the index      |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | n_diff_pfx01 |          6 |           1 | variable                          |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| sys           | sys_config           | PRIMARY                     | 2018-07-17 11:08:23 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+----------------------+-----------------------------+---------------------+--------------+------------+-------------+-----------------------------------+

可以直接看到索引下的统计项名称(stat_name)、数量(stat_value)及对应的取样页面数(sample_size)。
统计项名称有以下三种：

  • n_leaf_pages：该索引的叶子节点实际占用多少页面
  • size：该索引占用多少页面
  • n_diff_pfxNN：表示该索引列不重复的值有多少。其中的NN的意思，以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这四个列组合不重复的值有多少

而这两个表可以透过设定参数自动更新也可以手动执行更新，这边不再多做了解。

基於记忆体的非永久性统计资料

当系统变数innodb_stats_persistent设为off的时候，之後创建的表就都是非永久性的。
但由於後续的新版本已经很少使用这个方式，所以也就不深入说明它了。


<<:  Day07 Flutter 和 Native 通讯的原理 01

>>:  【Day 04】阿公级的系统分析方法 -- DFD

Day24 ( 高级 ) 骇客任务背景特效

骇客任务背景特效 教学原文参考：骇客任务背景特效 这篇文章会介绍，如何在 Scratch 3 里使用...

DAY 2：Single Threaded Execution Pattern，门就只有一个大家好好排队啊～

什麽是 Single Threaded Execution Pattern？ 透过 lock，只会有...

Spring Framework X Kotlin Day 29 Observability

GitHub Repo https://github.com/b2etw/Spring-Kotlin...

You can't always get what you want

其实今天是想延续昨天继续讨论「每个专案的程序码都该这样开始」，为什麽会变成这个标题？ 因为我写的每篇...

第5车厢-一切都是假的！::before应用篇

本篇介绍伪元素系列中的::before/::after概念及实例 假的真不了，真的假不了～一起来看...