mysql数据库空间查询_mysql数据库查询占用空间

那天领导问mysql的数据库占用多少空间，似乎没关注过这个问题，以前关注的都是多少条。因此，特意将这个问题记下来

1.查询某个数据库占用空间

mysql> SELECT round(sum(data_length / 1024 / 1024),2) 'Data Size in MB',round(sum(index_length / 1024 / 1024),2) 'Index Size in MB',round(sum((index_length + data_length) / 1024 / 1024),2) 'All Size in MB' FROM information_schema. TABLES WHERE table_schema = '库名';

+-----------------+------------------+----------------+

| Data Size in MB | Index Size in MB | All Size in MB |

+-----------------+------------------+----------------+

| 55.17 | 65.27 | 120.44 |

+-----------------+------------------+----------------+

1 row in set

2.查询某个库中所有表占用空间

mysql> SELECT TABLE_NAME,concat(TRUNCATE (data_length / 1024 / 1024, 2),' MB') AS data_size,concat(TRUNCATE (index_length / 1024 / 1024, 2),' MB') AS index_size FROM information_schema. TABLES

WHERE TABLE_SCHEMA = '库名' GROUP BY TABLE_NAME ORDER BY data_length DESC;

+----------------+-----------+------------+

| TABLE_NAME | data_size | index_size |

+----------------+-----------+------------+

| properties | 50.57 MB | 60.34 MB |

| assets | 4.51 MB | 4.90 MB |

| structures | 0.06 MB | 0.01 MB |

| files | 0.01 MB | 0.00 MB |

+----------------+-----------+------------+

4 rows in set

3.查询所有数据库占用空间

mysql> select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;

+---------------------+-----------+------------+

| TABLE_SCHEMA | data_size | index_size |

+---------------------+-----------+------------+

| db3 | 0.45 MB | 0.00MB |

| db2 | 0.45 MB | 0.00MB |

| db1 | 0.45 MB | 0.00MB |

| db0 | 0.45 MB | 0.00MB |

| bms | 0.07 MB | 0.01MB |

| information_schema | 0.15 MB | 0.00MB |

| performance_schema | 0.00 MB | 0.00MB |

| mysql | 2.60 MB | 0.21MB |

| sys | 0.01 MB | 0.00MB |

+---------------------+-----------+------------+

9 rows in set

5.查询某个表的状态

mysql> show table status from 数据库名 where name = '表名';

+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------+

| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |

+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------+

| files | InnoDB | 10 | Dynamic | 21 | 780 | 16384 | 0 | 0 | 0 | 47 | 2020-01-10 10:46:52 | NULL | NULL | utf8_general_ci | NULL | | 文件信息 |

+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+----------+

1 row in set