1.查看所有数据库容量大小
|
1 2 3 4 5 6 7 8 |
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; |
2.查看所有数据库各表容量大小
|
1 2 3 4 5 6 7 8 |
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.查看指定数据库容量大小
例：查看mysql库容量大小：代码如下：
|
1 2 3 4 5 6 7 |
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='mysql'; |
4.查看指定数据库各表容量大小*
例：查看mysql库各表容量大小
|
1 2 3 4 5 6 7 8 9 |
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='mysql' order by data_length desc, index_length desc; |
PS：查看MySql数据空间使用情况：
information_schema是MySQL的系统数据库，information_schema里的tables表存放了整个数据库各个表的使用情况。
可以使用sql来统计出数据库的空间使用情况，相关字段：
- table_schema：数据库名
- table_name：表名
- table_rows：记录数
- data_length：数据大小
- index_length：索引大小
使用空间
1、统计表使用空间
|
1 |
select concat(round(sum(data_length/1024/1024),2),'mb') as data from tables where table_schema='mydb' and table_name='mytable'; |
| data |
| 0.02mb |
1 row in set (0.00 sec)
2、统计数据库使用空间
|
1 |
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='mydb'; |
| data |
| 6.64MB |
1 row in set (0.00 sec)
3、统计所有数据使用空间
|
1 |
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables; |
| data |
| 6.64MB |
1 row in set (0.01 sec)
