MySQL 中的 InnoDB 统计相关说明:
参考: https://www.jianshu.com/p/0b8d2f9cee7b
其他参考: https://www.cnblogs.com/sunss/p/6110383.html
叶总的相关推文: https://mp.weixin.qq.com/s/1MsyxhtG6Zk3Q9gIV2QVbA
itdks 董爷的分享: http://www.itdks.com/eventlist/detail/1161
下面的介绍都是以 MySQL 社区版 5.7 为例
mysql 库中有 innodb_index_statsinnodb_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) 这个信息)
stat_name 中 n_diff_pfx02 表示有两列在索引
另, 附上 dashboard 表的 DML 语句:
- (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
关于 innodb_index_statsinnodb_table_stats 这 2 张表, 还有很多的参数与之相关, 例如 innodb_stats_persistentinnodb_stats_persistent_sample_pagesinnodb_stats_auto_recalc 等, 这里不做介绍了, 在早先的博文里面有相关的介绍
来源: http://www.bubuko.com/infodetail-2506709.html