MySQL8.0.31统计信息更新INFORMATION_SCHEMA
导入完数据以后查看最大的表 top 10：
root@kgoatestdb 16:55:51 [(none)]>select table_schema,table_name,table_rows from INFORMATION_SCHEMA.tables order by table_rows desc limit 10;
+--------------+----------------------------+------------+
| table_schema | table_name | table_rows |
+--------------+----------------------------+------------+
| oadb | schedulerunlog | 2158070 |
| oadb | hrmonlinecount | 1321563 |
| oadb | hrmsysmaintenancelog | 538537 |
| oadb | workflow_trackdetail | 353748 |
| oadb | workflow_node_fix_flowtime | 226093 |
| oadb | htmllabelinfo | 177120 |
| oadb | workflow_requestflowinfo | 160161 |
| oadb | workflow_approvelog | 156978 |
| oadb | docshare | 142524 |
| oadb | workflow_track | 141102 |
+--------------+----------------------------+------------+
10 rows in set, 16 warnings (1.02 sec)看看其中任意一个大小：
root@kgoatestdb 16:56:22 [(none)]>SELECT count(*) from oadb.hrmsysmaintenancelog
-> ;
+----------+
| count(*) |
+----------+
| 584943 |
+----------+
1 row in set (0.51 sec)实际数量和INFORMATION_SCHEMA.tables是有差异的，查看选项：
root@kgoatestdb 16:41:20 [oadb]>show variables like '%innodb_stats_persistent%';
+--------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------+-------+
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
+--------------------------------------+-------+
2 rows in set (0.00 sec)采样统计信息可以有两种选择：
持久化: 默认是持久化，也就是存磁盘。
非持久化.
innodb_stats_on_metadata: 是否每次都重新计算统计信息(配合非持久化使用),默认off;
innodb_stats_auto_recalc: 插入数据量超过原表20%的时候更新统计信息,默认on。
MySQK什么时候更新统计信息:
手动运行触发语句如analyze table xx的时候;
如果innodb_stats_auto_recalc为on: 插入数据量超过原表20%的时候更新统计信息;
如果innodb_stats_on_metadata为on: 每次查询schema.table表的是更新统计信息(一般不开启，性能太差)。手工触发下top 10的表:
analyze table oadb.schedulerunlog ;
analyze table oadb.hrmonlinecount ;
analyze table oadb.hrmsysmaintenancelog ;
analyze table oadb.workflow_trackdetail ;
analyze table oadb.workflow_node_fix_flowtime ;
analyze table oadb.htmllabelinfo ;
analyze table oadb.workflow_requestflowinfo ;
analyze table oadb.workflow_approvelog ;
analyze table oadb.docshare ;
analyze table oadb.workflow_track ;查看information_schema.tables表信息：
root@kgoatestdb 17:12:26 [(none)]>show create table information_schema.tables\G;
*************************** 1. row ***************************
Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)information_schema.tables表引擎是MEMORY比之前的视图好多了·
再次查看数据变化了：
root@kgoatestdb 17:14:01 [(none)]>select table_schema,table_name,table_rows from INFORMATION_SCHEMA.tables order by table_rows desc limit 10;
+--------------+----------------------------+------------+
| table_schema | table_name | table_rows |
+--------------+----------------------------+------------+
| oadb | schedulerunlog | 2157134 |
| oadb | hrmonlinecount | 1321563 |
| oadb | hrmsysmaintenancelog | 579516 |
| oadb | workflow_trackdetail | 381871 |
| oadb | workflow_node_fix_flowtime | 226093 |
| oadb | htmllabelinfo | 165282 |
| oadb | workflow_requestflowinfo | 163376 |
| oadb | workflow_track | 153792 |
| oadb | workflow_approvelog | 150641 |
| oadb | docshare | 142784 |
+--------------+----------------------------+------------+
10 rows in set, 16 warnings (0.95 sec)
Tag标签:「information_schema mysql8」更新时间:「2022-12-09 17:17:21」阅读次数:「1054」