查看mysql数据库容量大小

查看mysql数据库容量大小

第一种情况：查询所有数据库的总大小，方法如下：

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;
+-----------+
| data      |
+-----------+
| 951.69MB |
+-----------+
1 row in set (0.02 sec)
或者
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES;
+----------+
| data     |
+----------+
| 951.69MB |
+----------+
1 row in set, 4 warnings (0.13 sec)

统计一下所有库数据量
每张表数据量=AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH

mysql> SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES;
+--------------+
| total_mb     |
+--------------+
| 998.59816265 |
+--------------+
1 row in set, 4 warnings (0.12 sec)

统计每个库大小：

mysql> SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema; 
+--------------------+--------------+
| table_schema       | total_mb     |
+--------------------+--------------+
| baijiahua          |   0.89011669 |
| film               |   0.22139740 |
| information        |   0.14402962 |
| information_schema |         NULL |
|  ................  | ..............|
+--------------------+--------------+
15 rows in set, 4 warnings (0.14 sec)

第二种情况：查看指定数据库的大小，比如说：数据库test，方法如下：

mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='baijiahua';
+--------+
| data   |
+--------+
| 1.06MB |
+--------+
1 row in set (0.01 sec)
或者
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES where table_schema=''baijiahua';
+--------+
| data   |
+--------+
| 1.06MB |
+--------+
1 row in set (0.00 sec)

1.查看所有数据库各容量大小

mysql> select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc;
+--------------------+-----------+------------------+------------------+
| 数据库             | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+--------------------+-----------+------------------+------------------+
| baijiahua          |      1039 |             0.75 |             0.08 |
| film               |       211 |             0.14 |             0.01 |
| information        |       271 |             0.16 |             0.00 |
| information_schema |      NULL |             0.10 |             0.00 |
| .................. |   ....... |             .... |             .... |
+--------------------+-----------+------------------+------------------+
15 rows in set, 4 warnings (0.13 sec)

2.查看所有数据库各表容量大小

select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;

3.查看指定数据库容量大小
例：查看baijiahua库容量大小

mysql> select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables where table_schema='baijiahua';
+-----------+-----------+------------------+------------------+
| 数据库    | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+-----------+-----------+------------------+------------------+
| baijiahua |      1039 |             0.75 |             0.08 |
+-----------+-----------+------------------+------------------+
1 row in set (0.01 sec)
　

4.查看指定数据库各表容量大小
例：查看baijiahua库各表容量大小

mysql> select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='baijiahua' order by data_length desc, index_length desc;
+-----------+--------------------------+-----------+------------------+------------------+
| 数据库    | 表名                     | 记录数    | 数据容量(MB)     | 索引容量(MB)     |
+-----------+--------------------------+-----------+------------------+------------------+
| baijiahua | commodity                |       117 |             0.09 |             0.01 |
| baijiahua | productmap               |       354 |             0.09 |             0.00 |
| baijiahua | article                  |         9 |             0.04 |             0.00 |
| baijiahua | stores_commodity         |       152 |             0.01 |             0.01 |
| baijiahua | coupon_member            |        11 |             0.01 |             0.01 |
| ..........| ...............          |       ... |             .... |             .... |
+-----------+--------------------------+-----------+------------------+------------------+
56 rows in set (0.01 sec)

好了，这就是查看mysql数据库容量大小的所有方法了，如有问题可与博主一起交流讨论！