计算数据和索引大小

实验准备

# 创建实验数据库
CREATE DATABASE IF NOT EXISTS demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

# 创建实验数据表
USE demo;

DROP TABLE IF EXISTS test1;
CREATE TABLE IF NOT EXISTS test1(
    id                  BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    uuid             	VARCHAR(64) NOT NULL COMMENT 'uuid字段',
    random_str          VARCHAR(1024) NOT NULL COMMENT '随机字符串'
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

# 测试分区表
DROP TABLE IF EXISTS test2;
CREATE TABLE IF NOT EXISTS test2(
    partition_field     INT NOT NULL COMMENT '分区字段',
    `uuid`          	VARCHAR(64) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
partition by range columns(partition_field) (
	partition p0 values less than(1),
	partition p1 values less than(2),
	partition p2 values less than(3),
	partition p3 values less than(4),
	partition p4 values less than(5)
);

DELIMITER $$

DROP FUNCTION IF EXISTS `randStr`$$

CREATE FUNCTION `randStr`(length SMALLINT(3)) RETURNS varchar(1024) CHARSET utf8 NO SQL
begin
    SET @returnStr = '';
    SET @allowedChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    SET @i = 0;

    WHILE (@i < length) DO
        SET @returnStr = CONCAT(@returnStr, substring(@allowedChars, FLOOR(RAND() * LENGTH(@allowedChars) + 1), 1));
        SET @i = @i + 1;
    END WHILE;

    RETURN @returnStr;
END$$
DELIMITER ;

delimiter |

# 删除存储过程
drop procedure if exists proc_temp|

# 生成随机实验数据
create procedure proc_temp()
begin
	declare var_counter int default 0;
    while var_counter<50000 do
		insert into test1(uuid,random_str) values(uuid(),randStr(32));
		insert into test2(partition_field,`uuid`) values(FLOOR(0+RAND()*5),uuid());
        set var_counter = var_counter + 1;
    end while;
end|

delimiter ;

# 调用存储过程
call proc_temp();

# 删除存储过程
drop procedure if exists proc_temp;

手动创建索引

create index test1_idx1 on test1(uuid);

# 使用analyze table更新information_schema.tables Index_length数据
# https://www.cnblogs.com/PiscesCanon/p/18232608
analyze table test1;

计算整个数据库的大小

要获取整个数据库的大小，可以使用information_schema库中的tables表。这个表包含了MySQL中所有表的信息，包括每个表的数据大小和索引大小。通过将这些大小相加并除以适当的值（如1024*1024，以MB为单位），可以得到整个数据库的大小。

SELECT 
    table_schema AS 'Database',
    SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)'
FROM
    information_schema.tables
GROUP BY table_schema;

以上sql同样适用于分区表。

计算特定表的大小

SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024),
            2) AS 'Size (MB)'
FROM
    information_schema.tables
WHERE
    table_schema = 'demo'
        AND table_name = 'test1';

另一个查看表大小的方法是使用SHOW TABLE STATUS命令。这个命令会返回表的详细状态信息，包括行数、数据大小（Data_length）、索引大小（Index_length）等。

SHOW TABLE STATUS LIKE 'test1'\G;

然后，您可以查看Data_length和Index_length字段来获取表的数据大小和索引大小。注意，这些值是以字节为单位的，您可能需要将它们除以适当的值（如1024*1024）来得到更易于理解的单位（如MB）。

以上两句sql同样适用于分区表。