MySQL数据、索引信息统计

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>