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(分析)表,然后确保存储的信息准确。

带符号 * 的表示必填项