查询mysql数据库、表大小

mysql information_schema 数据库中存放了其他的数据库的信息，从其中的tables表可查看其它库表的信息。

mysql> use information_schema;

a. 查看所有数据库大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),3),‘MB’) as data from tables；

b. 查看所有指定库大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),3),‘MB’) as data from tables where table_schema=‘pttask1’；

c. 查看指定库表大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),3),‘MB’) as data_size, concat(round(sum(MAX_DATA_LENGTH/1024/1024),3),‘MB’) as max_data_size,concat(round(sum(INDEX_LENGTH/1024/1024),3),‘MB’) as index_size,concat(round(sum(DATA_FREE/1024/1024),3),‘MB’) as data_free from tables where table_schema=‘pttask1’ and table_name=‘task_001’;

d. 查看指定表索引信息
mysql> use pttask1;
mysql> show index from task_001;