进入information_schema 数据库(存放了其他的数据库的信息)
use information_schema;
查看所有库的大小
mysql> use information_schema;
Database changed
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data
from TABLES;
+----------+
| data
|
+----------+
| 104.21MB |
+----------+
1 row in set (0.11 sec)
查看指定库的大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data
from TABLES where table_schema='jishi';
+---------+
| data
|
+---------+
| 26.17MB |
+---------+
1 row in set (0.01 sec)
查看指定库的指定表的大小
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data
from TABLES wheretable_schema='jishi' and table_name='a_ya';
+--------+
| data
|
+--------+
| 0.02MB |
+--------+
1 row in set (0.00 sec)
查看指定库的索引大小
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES
WHERE table_schema = 'jishi';
+------------------+
| Total Index Size |
+------------------+
| 0.94 MB
|
+------------------+
1 row in set (0.01 sec)
查看指定库的指定表的索引大小
mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES
WHERE table_schema = 'test' and table_name='a_yuser';
+------------------+
| Total Index Size |
+------------------+
| 21.84 MB
|
+------------------+
1 row in set (0.00 sec)