GBase 8a MPP Cluster—— 按照schema、用户、库的角度统计表大小

1、整库统计

drop procedure if exists calc_db_size;
DELIMITER //

CREATE PROCEDURE calc_db_size()
BEGIN
    DECLARE v_table_schema VARCHAR(64);
    DECLARE v_table_name VARCHAR(64);
    DECLARE v_table_size BIGINT;
    DECLARE total_size BIGINT DEFAULT 0;
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE cur CURSOR FOR 
    SELECT table_schema, table_name 
    FROM information_schema.tables 
    WHERE table_schema NOT IN (
        'information_schema', 
        'gbase', 
        'gclusterdb', 
        'performance_schema', 
        'gctmpdb'
    );
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


    SET @size = 0;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO v_table_schema, v_table_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        
        SET @schema_var = v_table_schema;
        SET @table_var = v_table_name;
        
        SET @sql = CONCAT(
            'SELECT COALESCE(SUM(TABLE_STORAGE_SIZE),0) INTO @size ',
            'FROM information_schema.cluster_table_segments ',
            'WHERE table_schema=? AND table_name=?'
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING @schema_var, @table_var;
        DEALLOCATE PREPARE stmt;
        
        SET total_size = total_size + @size;
    END LOOP;
    
    CLOSE cur;
    
    SELECT total_size AS total_database_size;
END  //

DELIMITER ;

2、按照用户统计

drop procedure if exists calc_user_size;
delimiter //
create procedure calc_user_size(username varchar(10))
begin
    -- 必须先完成所有DECLARE才能执行其他语句
    DECLARE total_size BIGINT DEFAULT 0;
    DECLARE v_uid INT;             -- 用户UID变量
    DECLARE v_schema VARCHAR(64);  -- 表所属库名
    DECLARE v_table VARCHAR(64);   -- 表名变量
    DECLARE v_size BIGINT;         -- 单个表大小
    DECLARE done INT DEFAULT FALSE;-- 游标结束标志
    
    -- 必须在所有DECLARE之后才能声明游标
    DECLARE cur_tables CURSOR FOR 
        SELECT table_schema, table_name 
        FROM information_schema.tables 
        WHERE owner_uid = v_uid;  -- 通过UID关联用户表
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 现在可以执行SQL语句了
    -- 获取用户UID（必须放在DECLARE之后）
    SELECT uid INTO v_uid FROM gbase.user WHERE user = username;

    OPEN cur_tables;

    read_loop: LOOP
        FETCH cur_tables INTO v_schema, v_table;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 精确匹配库名+表名
        SELECT SUM(TABLE_STORAGE_SIZE) INTO v_size 
        FROM information_schema.CLUSTER_TABLE_SEGMENTS 
        WHERE table_schema = v_schema 
          AND table_name = v_table;

        SET total_size = total_size + COALESCE(v_size, 0);
    END LOOP;

    CLOSE cur_tables;

    SELECT total_size AS user_total_size;
end //
delimiter ;

3、按照schema统计

drop procedure if exists calc_schema_size;
delimiter //
create procedure calc_schema_size(schemaname varchar(10))
begin
    DECLARE total_size BIGINT DEFAULT 0;
    DECLARE tbl_name VARCHAR(64);
    DECLARE v_size BIGINT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur_tables CURSOR FOR 
        SELECT table_name FROM information_schema.tables WHERE table_schema = schemaname;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur_tables;

    read_loop: LOOP
        FETCH cur_tables INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SELECT SUM(TABLE_STORAGE_SIZE) INTO v_size 
        FROM information_schema.CLUSTER_TABLE_SEGMENTS 
        WHERE table_schema = schemaname AND table_name = tbl_name;

        SET total_size = total_size + COALESCE(v_size, 0);
    END LOOP;

    CLOSE cur_tables;

    SELECT total_size AS database_size;
end //
delimiter ;