快速查看MySQL所有库中哪个表数据最大
MySQL中有几十上百甚至上千张表，要怎么查看哪个表的数据量最大呢？
可以通过查看information_schema库中的tables表来获取哪个库中哪个表的数据最大：
mysql> select table_schema,table_name,table_rows from INFORMATION_SCHEMA.tables order by table_rows desc limit 10;
+--------------------+-----------------------------+------------+
| table_schema | table_name | table_rows |
+--------------------+-----------------------------+------------+
| test | UC_USER_1 | 92529720 |
| test | UC_USER | 90396596 |
| earth_plocc_system | GATEWAY_RECONICILIATION_LOG | 1017275 |
| plocc_back | UC_USER_INFO | 799422 |
| plocc_back | UC_USER | 749713 |
| earth_plocc_system | WIFI_PICKLED_CLIENT_INFOS | 627443 |
| earth_plocc_system | SCHE_EXECUTER | 536726 |
| crm | member_ids | 402739 |
| dna_bis_db_back | TB_BIS_POS_ORDER | 301389 |
| earth_plocc_system | SEARCH_AUTO_COMPLETE_STOCK | 215817 |
+--------------------+-----------------------------+------------+
10 rows in set (1.50 sec)test库中的UC_USER_1表里面的记录最多·
mysql> 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表是内存表，启动的时候，会读取分析各表中数据，然后填充tables表。如果某些表更新频繁，而来不及更新tables表的时候，tables中存储的数据就不一定准确了，这会影响到执行计划的分析，索引在执行计划的时候，可以analyze(分析)表，然后确保存储的信息准确。
Tag标签:「information_schema 表数据 内存表」更新时间:「2021-11-03 13:40:47」阅读次数:「513」