|
查看mysql表数据量大小的方法，mysql information_schema中存储数据库基本信息，通过此数据字典表查询tables表来获得表相关信息。 在mysql information_schema中存储数据库基本信息的数据字典表，可以通过查询tables表来获得所需要的表相关信息。
例子：
mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | |mysql | |report | | report_result | |test | +--------------------+ 5 rows in set (0.02 sec) mysql> use information_schema; Database changed mysql> show tables; +---------------------------------------+ |Tables_in_information_schema | +---------------------------------------+ |CHARACTER_SETS | |COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | |COLUMNS | |COLUMN_PRIVILEGES | |KEY_COLUMN_USAGE | |PROFILING | |ROUTINES | |SCHEMATA | |SCHEMA_PRIVILEGES | |STATISTICS | |TABLES | |TABLE_CONSTRAINTS | |TABLE_PRIVILEGES | |TRIGGERS | |USER_PRIVILEGES | |VIEWS | +---------------------------------------+ 17 rows in set (0.00 sec)
2、查看talbes表结构信息，查看存储的具体信息：
mysql> desc tables;
+-----------------+--------------+------+-----+---------+-------+ | Field |Type | Null | Key | Default |Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE |varchar(64) | YES | |NULL | | | VERSION |bigint(21) | YES | |NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) | YES | | NULL | | | DATA_LENGTH | bigint(21) | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | YES | | NULL | | | DATA_FREE | bigint(21) |YES | | NULL | | | AUTO_INCREMENT | bigint(21) | YES | | NULL | | | CREATE_TIME | datetime |YES | | NULL | | | UPDATE_TIME | datetime |YES | | NULL | | | CHECK_TIME | datetime |YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | |NULL | | | CHECKSUM | bigint(21) |YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | |NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 21 rows in set (0.00 sec) 主要存储了表的信息如表使用的引擎，表的类型等信息。
3、可以通过查询table_rows属性获得哪些表数据量比较大。
mysql> select table_name,table_rows from tables order by table_rows desc limi 10;
+---------------+------------+ | table_name |table_rows | +---------------+------------+ | task6 | 1558845 | | task | 1554399 | | task5 | 1539009 | | task3 | 1532169 | | task1 | 1531143 | | task2 | 1531143 | | task4 | 1521225 | | task7 | 980865 |
思考：存储数据是否准确，是否真实反应了表中数据量大小？
mysql> show create table tables \G;
*************************** 1. row*************************** Table: TABLES Create Table: CREATE TEMPORARY TABLE`TABLES` ( `TABLE_CATALOG` varchar(512) default NULL, `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) default NULL, `ROW_FORMAT` varchar(10) default NULL, `TABLE_ROWS` bigint(21) default NULL, `AVG_ROW_LENGTH` bigint(21) default NULL, `DATA_LENGTH` bigint(21) default NULL, `MAX_DATA_LENGTH` bigint(21) default NULL, `INDEX_LENGTH` bigint(21) default NULL, `DATA_FREE` bigint(21) default NULL, `AUTO_INCREMENT` bigint(21) default NULL, `CREATE_TIME` datetime default NULL, `UPDATE_TIME` datetime default NULL, `CHECK_TIME` datetime default NULL, `TABLE_COLLATION` varchar(64) default NULL, `CHECKSUM` bigint(21) default NULL, `CREATE_OPTIONS` varchar(255) default NULL, `TABLE_COMMENT` varchar(80) NOT NULL default '' ) ENGINE=MEMORY DEFAULTCHARSET=utf8
information_schema下的表tables是内存表，数据库启动时，会读取分析各表中数据，然后填充tables表。 (责任编辑：IT) |