在MySQL中查看数据库和表的数据大小

在MySQL中查看数据库和表的数据大小

在管理和维护MySQL数据库时，了解数据库和表的数据大小是非常重要的。这可以帮助您监控数据库的增长、优化性能以及规划存储需求。本博客将介绍如何使用SQL查询来查看MySQL数据库和表的数据大小。

查看MySQL数据库的总数据大小

首先，我们将学习如何查看整个MySQL数据库的总数据大小，包括所有数据库。以下是SQL查询示例：

-- 显示所有数据库的总计
SELECT
    '总和' AS `数据库`,
    CONCAT(ROUND(SUM(table_rows) / 1000000, 2), 'M') AS `行数`,
    CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), 'GB') AS `数据大小`,
    CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
    CONCAT(ROUND((SUM(data_length) + SUM(index_length)) / (1024 * 1024 * 1024), 2), 'GB') AS `总大小`
FROM
    information_schema.TABLES
WHERE
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys');

这个查询计算了MySQL服务器上所有数据库的总数据大小，包括数据和索引。总计会显示在第一行，总和列下。这将帮助您了解整个MySQL服务器上的数据占用情况。

查看MySQL连接下所有数据库的数据大小

使用了GROUP BY子句来按数据库名进行分组，并使用SUM函数来计算每个数据库的总数据长度、总索引长度和总大小：

SELECT  
    table_schema AS `数据库`,  
    SUM(data_length) / (1024 * 1024 * 1024) AS `数据大小(GB)`,  
    SUM(index_length) / (1024 * 1024 * 1024) AS `索引大小(GB)`,  
    SUM(data_length + index_length) / (1024 * 1024 * 1024) AS `总大小(GB)`  
FROM  
    information_schema.TABLES  
WHERE   
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')  
GROUP BY  
    table_schema  
ORDER BY  
    `总大小(GB)` DESC;
--     SUM(data_length + index_length) DESC;

查看单个MySQL数据库的数据大小

如果您想查看特定数据库的数据大小，可以使用以下SQL查询，并将 your_database_name 替换为您要查看的数据库名称：

-- 显示单个数据库的数据大小
SELECT
    table_schema AS `数据库`,
    table_name AS `表名`,
    CONCAT(ROUND(table_rows / 1000000, 2), 'M') AS `行数`,
    CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 2), 'GB') AS `数据大小`,
    CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
    CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `总大小`
FROM
    information_schema.TABLES
WHERE 
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys') 
    AND table_schema = 'your_database_name'
ORDER BY
    data_length + index_length DESC;

这个查询将显示特定数据库中每个表的数据大小，包括数据和索引。只需将 your_database_name 替换为您要查看的数据库名称。

查看所有数据库下所有数据表数据大小

-- 显示所有数据库的总计
SELECT
    '总和' AS `数据库`,
    '' AS `表名`,
    CONCAT(ROUND(SUM(table_rows) / 1000000, 2), 'M') AS `行数`,
    CONCAT(ROUND(SUM(data_length) / (1024 * 1024 * 1024), 2), 'GB') AS `数据大小`,
    CONCAT(ROUND(SUM(index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
    CONCAT(ROUND((SUM(data_length) + SUM(index_length)) / (1024 * 1024 * 1024), 2), 'GB') AS `总大小`
FROM
    information_schema.TABLES
WHERE
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
UNION
-- 显示每个表的数据大小
SELECT
    table_schema AS `数据库`,
    table_name AS `表名`,
    CONCAT(ROUND(table_rows / 1000000, 2), 'M') AS `行数`,
    CONCAT(ROUND(data_length / (1024 * 1024 * 1024), 2), 'GB') AS `表数据大小`,
    CONCAT(ROUND(index_length / (1024 * 1024 * 1024), 2), 'GB') AS `索引大小`,
    CONCAT(ROUND((data_length + index_length) / (1024 * 1024 * 1024), 2), 'GB') AS `表总大小`
FROM
    information_schema.TABLES
WHERE
    table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
ORDER BY
    `总大小` DESC; -- 修改ORDER BY子句

总结

通过使用上述SQL查询，您可以轻松查看MySQL数据库和表的数据大小。这些信息对于数据库管理、性能优化和存储规划非常有帮助。根据您的需求，您可以定期运行这些查询来监控数据库的增长并采取适当的措施。希望这个博客对您有所帮助！