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 等,这里不做介绍了,在早先的博文里面有相关的介绍。