InnoDB的统计信息表

  • 2019 年 10 月 4 日
  • 筆記

MySQL中的InnoDB统计相关说明:

参考:

https://www.jianshu.com/p/0b8d2f9cee7b

https://www.cnblogs.com/sunss/p/6110383.html

https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA 

https://yq.aliyun.com/articles/396153

http://www.itdks.com/eventlist/detail/1161

下面的介绍都是以MySQL社区版5.7为例,测试用的表是随便找了个grafana上面的dashboard表,数据内容不方便贴出来( ╯□╰ )。

(mysql) > show create table test.dashboard G  ***************************[ 1. row ]***************************  Table        | dashboard  Create Table | CREATE TABLE `dashboard` (    `id` bigint(20) NOT NULL AUTO_INCREMENT,    `version` int(11) NOT NULL,    `slug` varchar(255) NOT NULL,    `title` varchar(255) NOT NULL,    `data` mediumtext,    `org_id` bigint(20) NOT NULL,    `created` datetime NOT NULL,    `updated` datetime NOT NULL,    `updated_by` int(11) DEFAULT NULL,    `created_by` int(11) DEFAULT NULL,    `gnet_id` bigint(20) DEFAULT NULL,    `plugin_id` varchar(255) DEFAULT NULL,    PRIMARY KEY (`id`),    UNIQUE KEY `UQE_dashboard_org_id_slug` (`org_id`,`slug`),    KEY `IDX_dashboard_org_id` (`org_id`),    KEY `IDX_dashboard_gnet_id` (`gnet_id`),    KEY `IDX_dashboard_org_id_plugin_id` (`org_id`,`plugin_id`)  ) ENGINE=InnoDB AUTO_INCREMENT=582 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

mysql库中有innodb_index_stats、innodb_table_stats 对innodb的信息进行统计,可根据统计信息,分析表的复杂度,为优化做准备。

1) innodb_table_stats

(mysql) > desc innodb_table_stats;  +--------------------------+---------------------+--------+-------+-------------------+-----------------------------+  | Field                    | Type                | Null   | Key   | Default           | Extra                       |  |--------------------------+---------------------+--------+-------+-------------------+-----------------------------|  | database_name            | varchar(64)         | NO     | PRI   | <null>            |                             |  | table_name               | varchar(64)         | NO     | PRI   | <null>            |                             |  | last_update              | timestamp           | NO     |       | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |  | n_rows                   | bigint(20) unsigned | NO     |       | <null>            |                             |  | clustered_index_size     | bigint(20) unsigned | NO     |       | <null>            |                             |  | sum_of_other_index_sizes | bigint(20) unsigned | NO     |       | <null>            |                             |  +--------------------------+---------------------+--------+-------+-------------------+-----------------------------+

字段详解:

database_name 数据库名

table_name 表名

last_update 最后一次更新时间

n_rows 表中总有多少列数据

clustered_index_size 聚集索引大小(数据页)

sum_of_other_index_sizes 其他索引大小(数据页)

(mysql) > select * from mysql.innodb_table_stats order by n_rows desc;  +-----------------+---------------+---------------------+----------+------------------------+----------------------------+  | database_name   | table_name    | last_update         |   n_rows |   clustered_index_size |   sum_of_other_index_sizes |  |-----------------+---------------+---------------------+----------+------------------------+----------------------------|  | test            | dashboard     | 2017-12-25 16:38:10 |      296 |                   1441 |                          4 |  | sys             | sys_config    | 2017-11-02 16:05:29 |        6 |                      1 |                          0 |  | db1             | t1            | 2018-02-25 13:57:14 |        3 |                      1 |                          0 |  | db1             | t2            | 2018-02-25 13:57:16 |        0 |                      1 |                          0 |  | mysql           | gtid_executed | 2017-11-02 16:05:20 |        0 |                      1 |                          0 |  | test            | a             | 2017-11-02 18:39:37 |        0 |                      1 |                          0 |  | test            | article       | 2017-12-25 16:46:04 |        0 |                      1 |                          0 |  | test            | t3            | 2018-02-25 13:57:06 |        0 |                      1 |                          1 |  | test            | user_task     | 2018-01-10 10:43:54 |        0 |                      1 |                          1 |  +-----------------+---------------+---------------------+----------+------------------------+----------------------------+

数据详解(以test.dashboard表为例):

select @@innodb_page_size;  默认为16K

clustered_index_size 为1441个page   —>  聚集索引所需磁盘空间为  1441*16K= 22MB

sum_of_other_index_sizes 为4个page  —>  其他索引所需磁盘空间为  4*16K=64KB

另一种检索索引大小的方式:

SELECT    SUM(stat_value) pages,    index_name,    (SUM(stat_value)*@@innodb_page_size)/1024/1024 as size_MB  FROM    mysql.innodb_index_stats  WHERE table_name = 'dashboard'    AND stat_name = 'size'  GROUP BY index_name;  +-------+--------------------------------+-------------+  | pages | index_name                     | size_MB     |  +-------+--------------------------------+-------------+  |     1 | IDX_dashboard_gnet_id          |  0.01562500 |  |     1 | IDX_dashboard_org_id           |  0.01562500 |  |     1 | IDX_dashboard_org_id_plugin_id |  0.01562500 |  |  1441 | PRIMARY                        | 22.51562500 |  |     1 | UQE_dashboard_org_id_slug      |  0.01562500 |  +-------+--------------------------------+-------------+

# 聚集索引约22MB,其余4个非聚集索引加起来约64KB。

可以看到,上面两种检查索引大小的命令结果是相近的。

2) innodb_index_stats

对innodb中所有索引进行统计

(mysql) > desc innodb_index_stats;  +------------------+---------------------+--------+-------+-------------------+-----------------------------+  | Field            | Type                | Null   | Key   | Default           | Extra                       |  |------------------+---------------------+--------+-------+-------------------+-----------------------------|  | database_name    | varchar(64)         | NO     | PRI   | <null>            |                             |  | table_name       | varchar(64)         | NO     | PRI   | <null>            |                             |  | index_name       | varchar(64)         | NO     | PRI   | <null>            |                             |  | last_update      | timestamp           | NO     |       | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |  | stat_name        | varchar(64)         | NO     | PRI   | <null>            |                             |  | stat_value       | bigint(20) unsigned | NO     |       | <null>            |                             |  | sample_size      | bigint(20) unsigned | YES    |       | <null>            |                             |  | stat_description | varchar(1024)       | NO     |       | <null>            |                             |  +------------------+---------------------+--------+-------+-------------------+-----------------------------+

字段详解:

database_name 数据库名

table_name 表名

index_name 索引名

last_update 最后一次更新时间

stat_name 统计名

stat_value 统计值

sample_size 样本大小

stat_description 统计说明-索引对应的字段名

(mysql) > select * from mysql.innodb_index_stats where database_name='test'  and table_name='dashboard' ;   +-----------------+---------------+--------------------------------+---------------------+--------------+--------------+---------------+-----------------------------------+  | database_name   | table_name    | index_name                     | last_update         | stat_name    |   stat_value |   sample_size | stat_description                  |  |-----------------+---------------+--------------------------------+---------------------+--------------+--------------+---------------+-----------------------------------|  | test            | dashboard     | PRIMARY                        | 2017-12-25 16:38:10 | size         |         1441 |        <null> | Number of pages in the index      |  | test            | dashboard     | PRIMARY                        | 2017-12-25 16:38:10 | n_leaf_pages |         1225 |        <null> | Number of leaf pages in the index |  | test            | dashboard     | PRIMARY                        | 2017-12-25 16:38:10 | n_diff_pfx01 |          296 |            20 | id                                |  | test            | dashboard     | IDX_dashboard_org_id           | 2017-12-25 16:38:10 | n_diff_pfx02 |          295 |             1 | org_id,id                         |  | test            | dashboard     | IDX_dashboard_gnet_id          | 2017-12-25 16:38:10 | n_diff_pfx02 |          295 |             1 | gnet_id,id                        |  | test            | dashboard     | UQE_dashboard_org_id_slug      | 2017-12-25 16:38:10 | n_diff_pfx02 |          295 |             1 | org_id,slug                       |  | test            | dashboard     | IDX_dashboard_org_id_plugin_id | 2017-12-25 16:38:10 | n_diff_pfx03 |          295 |             1 | org_id,plugin_id,id               |  | test            | dashboard     | IDX_dashboard_org_id_plugin_id | 2017-12-25 16:38:10 | n_diff_pfx02 |           24 |             1 | org_id,plugin_id                  |  | test            | dashboard     | IDX_dashboard_org_id           | 2017-12-25 16:38:10 | n_diff_pfx01 |           14 |             1 | org_id                            |  | test            | dashboard     | UQE_dashboard_org_id_slug      | 2017-12-25 16:38:10 | n_diff_pfx01 |           14 |             1 | org_id                            |  | test            | dashboard     | IDX_dashboard_org_id_plugin_id | 2017-12-25 16:38:10 | n_diff_pfx01 |           14 |             1 | org_id                            |  | test            | dashboard     | IDX_dashboard_gnet_id          | 2017-12-25 16:38:10 | n_diff_pfx01 |            7 |             1 | gnet_id                           |  ..........   其余的内容忽略 ................  +-----------------+---------------+--------------------------------+---------------------+--------------+--------------+---------------+-----------------------------------+

数据详解:

  可以看到IDX_dashboard_org_id_plugin_id 实际上存了3个统计信息(原因:统计索引信息时,是根据最左原则,要统计各种组合的。比如(a,b) 索引,要统计(a), (a,b), (a,b,pk) 三种信息,而不是只统计(a,b)这个信息)

  1 stat_name=size时:stat_value表示索引的页的数量

  2 stat_name=n_leaf_pages时候,此时stat_value显示的是叶子节点的数量。

  3 stat_name=n_diff_pfxNN时候,此时stat_value显示的索引字段中唯一值的数量,具体点就是:

    3.1) n_diff_pfx01表示索引第一列distinct之后的数量

    3.2) n_diff_pfx02表示索引前两列distinct之后的数量

    3.3) 对于非唯一索引,会在原有列之后加上主键索引

         例如: 

           index_name=IDX_dashboard_org_id_plugin_id AND stat_name=n_diff_pfx03情况下:             > select count(distinct plugin_id,org_id,id)  from test.dashboard ;   -- 结果是 295             index_name=IDX_dashboard_org_id_plugin_id AND stat_name=n_diff_pfx02情况下:             > select count(distinct plugin_id,org_id)  from test.dashboard ;   -- 结果是 24             index_name=IDX_dashboard_org_id_plugin_id AND stat_name=n_diff_pfx01情况下:             > select count(distinct org_id)  from test.dashboard ;   -- 结果是 14

其他:

关于innodb_index_stats、innodb_table_stats 这2张表,还有很多的参数与之相关,例如innodb_stats_persistent、innodb_stats_persistent_sample_pages、innodb_stats_auto_recalc 等,这里不做介绍了,在早先的博文里面有相关的介绍。