MySQL 如何查询某些行或列占用了多少磁盘空间

在MySQL中，我们可以通过一些简单的语句查询某些行或列占用了多少磁盘空间。这对于数据库管理人员来说非常有用，因为他们需要保证数据库的效率和安全性。本文将介绍几种方法，以获得MySQL数据行或列的磁盘空间使用情况。

阅读更多：MySQL 教程

使用信息_schema表

MySQL在默认安装时会安装一个名为information_schema的数据库，其中包含了许多内置的数据表和视图，用于存储数据库结构、统计信息和其他有用的信息。我们可以使用information_schema表查询某些行或列的磁盘空间使用情况。

查询某些表或数据库中所有占用磁盘空间的行

以下示例代码查询了所有行的数据库“sample_db”中的所有表的磁盘空间使用情况。

SELECT 
    table_schema as `Database`, 
    table_name AS `Table`, 
    data_length as `Data Size`, 
    index_length as `Index Size`
FROM information_schema.tables 
WHERE table_schema = 'sample_db'
ORDER BY `Data Size` + `Index Size` DESC;

这条查询语句从information_schema.tables表中获取了数据库“sample_db”中的所有表的磁盘空间使用情况，将数据库名称、表名称、数据大小和索引大小作为结果返回。最后，我们按总磁盘空间大小排序结果。

查询某些表或数据库中特定行的磁盘空间使用情况

以下示例代码查询了表“users”中所有行的磁盘空间使用情况。

SELECT 
    table_schema as `Database`,
    table_name as `Table`,
    data_length + index_length as `Size`
FROM information_schema.tables
WHERE table_schema = 'sample_db' AND table_name = 'users';

这条查询语句从information_schema.tables表中获取了表“users”的磁盘空间使用情况，将数据库名称、表名称和总磁盘空间大小作为结果返回。

查询表的每一列的磁盘空间使用情况

以下示例代码查询了表“users”的每个列的磁盘空间使用情况。

SELECT 
    COLUMN_NAME as `Column Name`,
    round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.columns 
WHERE table_name = 'users' 
AND table_schema = 'sample_db'
ORDER BY `Size (MB)` DESC;

这条查询语句从information_schema.columns表中获取了表“users”的每个列的磁盘空间使用情况，将列名称和大小（以MB为单位）作为结果返回。最后，我们按列的大小降序返回结果。

使用MySQL命令行客户端

我们还可以使用MySQL命令行客户端统计某些行或列的磁盘空间使用情况。这种方法简便、直接，适用于快速查询多个表的磁盘空间使用情况。

查询数据库中所有表的磁盘空间使用情况

以下示例代码查询数据库“sample_db”的所有表的磁盘空间使用情况。

USE sample_db;

SELECT TABLE_NAME AS `Table`, 
    round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.TABLES 
ORDER BY `Size (MB)` DESC;

这条查询语句从information_schema.TABLES表中获取数据库“sample_db”的所有表的磁盘空间使用情况，将表名称和大小（以MB为单位）作为结果返回。我们再次按照磁盘空间大小排序结果。

查询表中所有列的磁盘空间使用情况

以下示例代码查询了表“users”的所有列的磁盘空间使用情况。

USE sample_db;

SELECT COLUMN_NAME as `Column Name`,
    round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) AS `Size (MB)`
FROM information_schema.columns 
WHERE table_name = 'users' 
ORDER BY `Size (MB)` DESC;

这条查询语句从information_schema.columns表中获取表“users”的所有列的磁盘空间使用情况，将列名称和大小（以MB为单位）作为结果返回。最后，我们按列的大小降序返回结果。

使用统计表和存储过程

MySQL还提供了一个名为“table_stat”的统计表，列出了数据库中所有表的行和磁盘空间使用情况。我们可以使用存储过程获取某些行或列的磁盘空间使用情况。

创建存储过程

以下示例代码创建了一个名为“GetColumnSize”的存储过程，用于获取表的每个列的磁盘空间使用情况。

DELIMITER //
CREATE PROCEDURE GetColumnSize(IN dbName VARCHAR(100), IN tableName VARCHAR(100))
BEGIN
    DECLARE columnName VARCHAR(100); 
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR 
        SELECT COLUMN_NAME 
        FROM information_schema.columns 
        WHERE table_schema = dbName AND table_name = tableName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS `tmp_table_statistics` (
        `Table`             VARCHAR(100),
        `Data Size`         INT(11),
        `Index Size`        INT(11),
        `Total Size`        INT(11)
    );

    OPEN cur;
    SET @s = '';
    read_loop: LOOP
        FETCH cur INTO columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @s = CONCAT(@s, 
            'SELECT "', tableName, '",',
            ' SUM(DATA_LENGTH),',
            ' SUM(INDEX_LENGTH),',
            ' SUM(DATA_LENGTH) + SUM(INDEX_LENGTH)', 
            ' FROM ', dbName, '.', tableName, 
            ' WHERE ', columnName, ' IS NOT NULL;',
            ' INSERT INTO tmp_table_statistics');
        PREPARE query FROM @s;
        EXECUTE query;
        DEALLOCATE PREPARE query;
        SET @s = '';
    END LOOP;
    CLOSE cur;

    SELECT * FROM tmp_table_statistics ORDER BY `Total Size` DESC;
    DROP TEMPORARY TABLE IF EXISTS `tmp_table_statistics`;
END//
DELIMITER ;

查询表的每一列的磁盘空间使用情况

以下示例代码查询表“users”的每一列的磁盘空间使用情况。

USE sample_db;

CALL GetColumnSize('sample_db', 'users');

这里我们调用了存储过程“GetColumnSize”，其中第一个参数为数据库名称“sample_db”，第二个参数为表名称“users”。存储过程将使用游标遍历表“users”的每一列，然后创建一个临时表“tmp_table_statistics”来存储每列的磁盘空间使用情况。存储过程的最后一步是返回结果，然后删除临时表。