MySQL 的 information_schema 数据库, 保存着数据库的容量和使用信息. 可查询数据库中每个表占用的空间, 表记录的行数.
Tables 表的结构
- 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(20) 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;
主要字段说明
TABLE_SCHEMA : 数据库名
TABLE_NAME: 表名
ENGINE: 所使用的存储引擎
TABLE_ROWS: 记录数
DATA_LENGTH: 数据大小
INDEX_LENGTH: 索引大小
实例
查询指定数据库的所有表名称, 行数
- USE information_schema;
- SELECT TABLE_NAME,table_rows FROM TABLES WHERE TABLE_SCHEMA = '数据库名字' ORDER BY table_rows DESC;
查询指定库的数据大小, 索引大小
SELECT CONCAT(ROUND(SUM(DATA_LENGTH/1024/1024),2),'MB') AS total_data_size, CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), 'MB') AS total_index_size FROM TABLES WHERE table_schema = '数据库名字';
来源: http://www.jianshu.com/p/8f086c98d591