Mysql 查看数据库各个库表占用空间

最新推荐文章于 2025-03-13 09:00:58 发布

狂奔的小棕棕

最新推荐文章于 2025-03-13 09:00:58 发布

阅读量447

点赞数 4

分类专栏： MySql 文章标签：数据库 mysql

本文链接：https://blog.youkuaiyun.com/ToBeMaybe_/article/details/145567864

版权

MySql 专栏收录该内容

5 篇文章

订阅专栏

truncate(x,y) 返回数字X截断为Y位小数的值

一、查看所有数据库容量大小

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;

在这里插入图片描述

【注意】table_rows在MySQL中，information_schema.TABLES表中的table_rows列并不总是准确反映实际表中的行数。这是因为table_rows列的值是基于表的统计信息估算的，而不是实时计算的。因此，它可能与实际行数存在差异。

二、查看所有数据库各表容量大小

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;

在这里插入图片描述

三、查看指定数据库容量大小

    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 = 'voyage_ma';

在这里插入图片描述

四、查看指定数据库各表容量大小

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 = 'voyage_ma'
ORDER BY
    data_length DESC,
    index_length DESC;

在这里插入图片描述

五、查看指定数据库指定表容量大小

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 = 'voyage_ma'
    and table_name = 't_plugin';

在这里插入图片描述