InnoDB的统计信息表
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=DYNAMICmysql库中有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 等，这里不做介绍了，在早先的博文里面有相关的介绍。
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2018/02/25 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
腾讯云开发者
