MySQL——Innodb相关统计
程序员文章站
2022-06-05 09:28:26
...
前言
mysql中有innodb_index_stats和innodb_table_stats对innodb的信息进行统计，我们可根据统计信息来分析表的复杂度，以及为后期的优化做准备。
Innodb_table_stats
Innodb_table_stats：是对这个库中的Innodb表进行的统计。
示例1：
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 | |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
名词解释：
database_name 数据库名
table_name 表名
last_update 最后一次更新时间
n_rows 表中总有多少列数据
clustered_index_size 聚集索引大小(数据页)
sum_of_other_index_sizes 其他索引大小(数据页)
示例2
mysql> select * from innodb_table_stats order by n_rows desc;
+-------------------+---------------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+-------------------+---------------------------+---------------------+--------+----------------------+--------------------------+
| sonar | issues | 2016-06-13 17:51:10 | 117017 | 2590 | 2924 |
| sonar | project_measures | 2016-06-15 03:34:26 | 93801 | 1497 | 395 |
| sonar | resource_index | 2016-06-13 17:51:11 | 26448 | 161 | 123 |
| sonar | projects | 2016-06-13 17:51:10 | 3139 | 97 | 74 |
| sonar | snapshots | 2016-06-15 03:34:16 | 2411 | 17 | 23 |
| sonar | file_sources | 2016-06-13 17:51:09 | 1227 | 2912 | 17 |
| sonar | issue_changes | 2016-06-13 17:51:09 | 833 | 8 | 5 |
| sonar | rules | 2016-06-13 17:51:11 | 552 | 97 | 1 |
| sonar | activities | 2016-06-13 17:51:09 | 419 | 7 | 1 |
| sonar | active_rules | 2016-06-13 17:51:09 | 419 | 3 | 1 |
| sonar | schema_migrations | 2016-06-13 17:51:11 | 216 | 1 | 0 |
| sonar | metrics | 2016-06-13 17:51:10 | 136 | 3 | 1 |
名词解释：
执行该：select @@innodb_page_size;
默认：16k (16384)
clustered_index_size 为 2590
聚集索引所需磁盘空间为2590 x innodb_page_size/1024=41M
其他索引所需磁盘空间为2924 x innodb_page_size/1024=46M
另一种检索索引的方式
mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name='issues' AND stat_name = 'size' GROUP BY index_name;
+-------+------------------------+----------+
| pages | index_name | size |
+-------+------------------------+----------+
| 2590 | PRIMARY | 42434560 |
| 151 | issues_action_plan_key | 2473984 |
| 151 | issues_assignee | 2473984 |
| 328 | issues_component_uuid | 5373952 |
| 209 | issues_creation_date | 3424256 |
| 652 | issues_kee | 10682368 |
| 329 | issues_project_uuid | 5390336 |
| 146 | issues_resolution | 2392064 |
| 271 | issues_rule_id | 4440064 |
| 212 | issues_severity | 3473408 |
| 209 | issues_status | 3424256 |
| 266 | issues_updated_at | 4358144 |
+-------+------------------------+----------+
Innodb_index_stats
对Innodb中所有索引进行统计
示例1
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 | |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
名词解释：
database_name 数据库名
table_name 表名
index_name 索引名
last_update 最后一次更新时间
stat_name 统计名
stat_value 统计值
sample_size 样本大小
stat_description 统计说明-索引对应的字段名
示例2
mysql> select * from innodb_index_stats order by stat_value desc;
+-------------------+---------------------------+--------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+-------------------+---------------------------+--------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| sonar | issues | issues_severity | 2016-06-13 17:51:10 | n_diff_pfx02 | 118618 | 20 | severity,id |
| sonar | issues | issues_resolution | 2016-06-13 17:51:10 | n_diff_pfx02 | 118508 | 20 | resolution,id |
| sonar | issues | issues_action_plan_key | 2016-06-13 17:51:10 | n_diff_pfx02 | 118416 | 20 | action_plan_key,id |
| sonar | issues | issues_creation_date | 2016-06-13 17:51:10 | n_diff_pfx02 | 118244 | 20 | issue_creation_date,id |
| sonar | issues | PRIMARY | 2016-06-13 17:51:10 | n_diff_pfx01 | 117017 | 20 | id |
| sonar | issues | issues_assignee | 2016-06-13 17:51:10 | n_diff_pfx02 | 116678 | 20 | assignee,id |
| sonar | issues | issues_status | 2016-06-13 17:51:10 | n_diff_pfx02 | 115722 | 20 | status,id |
| sonar | issues | issues_rule_id | 2016-06-13 17:51:10 | n_diff_pfx02 | 115692 | 20 | rule_id,id |
| sonar | issues | issues_project_uuid | 2016-06-13 17:51:10 | n_diff_pfx02 | 115544 | 20 | project_uuid,id |
| sonar | issues | issues_updated_at | 2016-06-13 17:51:10 | n_diff_pfx02 | 112466 | 20 | updated_at,id |
| sonar | issues | issues_kee | 2016-06-13 17:51:10 | n_diff_pfx01 | 107901 | 20 | kee |
| sonar | issues | issues_component_uuid | 2016-06-13 17:51:10 | n_diff_pfx02 | 105440 | 20 | component_uuid,id |
| sonar | project_measures | measures_sid_metric | 2016-06-15 03:34:26 | n_diff_pfx03 | 95257 | 20 | snapshot_id,metric_id,id |
| sonar | project_measures | PRIMARY | 2016-06-15 03:34:26 | n_diff_pfx01 | 93801 | 20 | id |
名词解释：
stat_name 中n_diff_pfx02表示有两列在索引，具体列为stat_description中的severity,id
stat_value值为118618，表示severity,id两列中有severity,id不一样的值。
数据表创建
CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
注意事项：
当执行上面语句报错：innodb_table_stats表不存在时，尝试如下操作：（开启相关统计信息）
mysql> set GLOBAL innodb_stats_auto_recalc='ON';
mysql> set GLOBAL innodb_stats_persistent='ON';
mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+
1 row in set (0.00 sec)
参考
上一篇： java的单元测试[email protected]
下一篇： Java日期处理-本期，同期，上期
推荐阅读
-
可以改善mysql性能的InnoDB配置参数
-
MYSQL无法启动提示： Default storage engine (InnoDB) is not available的解决方法
-
Mysql启动中 InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes 的问题
-
MySQL从MyISAM转换成InnoDB错误与常用解决办法
-
Mysql获取id最大值、表的记录总数等相关问题的方法汇总
-
MySQL数据库innodb启动失败无法重启的解决方法
-
将MySQL从MyISAM转换成InnoDB错误和解决办法
-
mysql 提示INNODB错误的解决方法
-
Mysql更改默认引擎为Innodb的步骤方法
-
MySQL存储引擎中MyISAM和InnoDB区别详解