查看Mysql数据库大小的SQL语句

最新推荐文章于 2023-12-08 15:08:39 发布

海点

最新推荐文章于 2023-12-08 15:08:39 发布

阅读量754

点赞数

分类专栏： Mysql

本文链接：https://blog.csdn.net/cloud952788/article/details/37883211

版权

Mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

查看整个数据库大小

SELECT  sum(round(data_length / (1024 * 1024), 2) ) data_length,
       sum(round(index_length / (1024 * 1024), 2) ) index_length,
       sum(round(round(data_length + index_length) / (1024 * 1024), 2)
              ) total_size
  FROM information_schema.TABLES;

查看每个表空间的大小

SELECT table_schema,
        sum(round(data_length / (1024 * 1024), 2) ) data_length,
       sum(round(index_length / (1024 * 1024), 2) ) index_length,
       sum(round(round(data_length + index_length) / (1024 * 1024), 2)
              ) total_size
  FROM information_schema.TABLES
group by table_schema;

查看某个表空间的大小

SELECT concat(table_schema, '.', table_name) table_name,
       concat(round(data_length / (1024 * 1024), 2), 'M') data_length,
       concat(round(index_length / (1024 * 1024), 2), 'M') index_length,
       concat(round(round(data_length + index_length) / (1024 * 1024), 2),
              'M') total_size
  FROM information_schema.TABLES
where table_schema='test'
 ORDER BY data_length DESC;

查看所有表的大小

SELECT concat(table_schema, '.', table_name) table_name,
       concat(round(data_length / (1024 * 1024), 2), 'M') data_length,
       concat(round(index_length / (1024 * 1024), 2), 'M') index_length,
       concat(round(round(data_length + index_length) / (1024 * 1024), 2),
              'M') total_size
  FROM information_schema.TABLES
 ORDER BY data_length DESC;

查看某张表的大小

select table_schema, table_name,TABLE_TYPE,ENGINE,TABLE_ROWS,
 concat(round(data_length / (1024 * 1024), 2), 'M') data_length,
       concat(round(index_length / (1024 * 1024), 2), 'M') index_length,
       concat(round(round(data_length + index_length) / (1024 * 1024), 2),
              'M') total_size
 from information_schema.tables where table_name='user';