查看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数据库容量大小的所有方法了,如有问题可与博主一起交流讨论!