mysql> desc information_schema.tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.02 sec)
统计所有库的数据、索引大小
mysql> select
-> round(sum(data_length+index_length)/1024/1024) as wholedb_mb,
-> round(sum(data_length)/1024/1024) as wholedata_mb,
-> round(sum(index_length)/1024/1024) as wholeindex_mb
-> from information_schema.tables;
+------------+--------------+---------------+
| wholedb_mb | wholedata_mb | wholeindex_mb |
+------------+--------------+---------------+
| 54 | 54 | 0 |
+------------+--------------+---------------+
1 row in set (0.54 sec)
按模式、引擎分组
mysql> select table_schema,
-> engine,
-> round(sum(data_length+index_length)/1024/1024) as wholedb_mb,
-> round(sum(data_length)/1024/1024) as wholedata_mb,
-> round(sum(index_length)/1024/1024) as wholeindex_mb,
-> count(*) as tables
-> from information_schema.tables
-> group by table_schema, engine
-> order by 3 desc;
+--------------------+--------------------+------------+--------------+---------------+--------+
| table_schema | engine | wholedb_mb | wholedata_mb | wholeindex_mb | tables |
+--------------------+--------------------+------------+--------------+---------------+--------+
| test | InnoDB | 53 | 53 | 0 | 5 |
| world_innodb | InnoDB | 1 | 1 | 0 | 3 |
| mysql | MyISAM | 1 | 1 | 0 | 21 |
| mysql | InnoDB | 0 | 0 | 0 | 5 |
| information_schema | MyISAM | 0 | 0 | 0 | 10 |
| mysql | CSV | 0 | 0 | 0 | 2 |
| information_schema | MEMORY | 0 | 0 | 0 | 49 |
| cookbook | InnoDB | 0 | 0 | 0 | 6 |
| performance_schema | PERFORMANCE_SCHEMA | 0 | 0 | 0 | 52 |
| bob | InnoDB | 0 | 0 | 0 | 5 |
+--------------------+--------------------+------------+--------------+---------------+--------+
10 rows in set (0.05 sec)
mysql>