能不能用一句SQL或者一个存储过程,计算出一张表分区内的数据总量统计

我有一张表
大概是这样的
CREATE TABLE tab_certoperate_log (
id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘编号’,
mastersetid int(11) DEFAULT NULL COMMENT ‘master外键’,
certsn varchar(50) DEFAULT NULL COMMENT ‘序列号’,
createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘操作时间’,
PRIMARY KEY (id, createtime)
) COMMENT = ‘日志表’
partition by range(Year(createtime)-1) subpartition by range(Month(createtime)-1) subpartition template (
subpartition m1 values less than (1),
subpartition m2 values less than (2),
subpartition m3 values less than (3),
subpartition m4 values less than (4),
subpartition m5 values less than (5),
subpartition m6 values less than (6),
subpartition m7 values less than (7),
subpartition m8 values less than (8),
subpartition m9 values less than (9),
subpartition m10 values less than (10),
subpartition m11 values less than (11),
subpartition m12 values less than (12))
(partition p2018 values less than (2018),
partition p2019 values less than (2019),
partition p2020 values less than (2020),
partition p2021 values less than (2021),
partition p2022 values less than (2022),
partition p2023 values less than (2023),
partition p2024 values less than (2024),
partition p2025 values less than (2025))

能不能一句SQL 返回
每个分区的数据量
p2018sm1 100
p2018sm2 150

类似上面的表格

ob的数据库版本号发一下

查看某表单副本占用磁盘大小

SELECT sum(size)/1024/1024/1024 FROM (SELECT DATABASE_NAME,TABLE_NAME,TABLE_ID,PARTITION_NAME,TABLET_ID,ROLE
FROM oceanbase.DBA_OB_TABLE_LOCATIONS ) AA full join
(SELECT distinct(TABLET_ID) ,size
FROM oceanbase.GV$OB_SSTABLES ) BB on AA.TABLET_ID=BB.TABLET_ID
WHERE AA.role=‘leader’ and AA.table_name=’${表名}’;

1 个赞

select table_name,table_schema,partition_name,subpartition_name,table_rows from information_schema.PARTITIONS mysql 租户查询这个视图就可以了,但是这个视图的数据需要收集统计信息之后才会显示的准确

@论坛小助手

根据您的问题,您希望使用一条 SQL 或存储过程来统计一张二级分区表中每个分区的数据量。以下是一个可行的解决方案:

解决方案

方法 1:通过 EXPLAIN PARTITIONS 获取分区信息

OceanBase 支持通过 EXPLAIN PARTITIONS 查看分区表的分区信息,并结合查询语句统计每个分区的数据量。

EXPLAIN PARTITIONS SELECT * FROM tab_certoperate_log;

虽然 EXPLAIN PARTITIONS 只能展示分区的元信息,但可以结合以下方法进一步统计每个分区的数据量。


方法 2:直接查询每个分区的数据量

您可以使用如下 SQL 查询每个一级分区和二级分区的数据量。此方法需要手动构造查询语句或通过动态 SQL 实现。

SELECT 
    CONCAT(partition_name, 's', subpartition_name) AS partition_info,
    COUNT(*) AS row_count
FROM 
    tab_certoperate_log PARTITION (p2018)
WHERE 
    SUBSTRING_INDEX(SUBSTRING_INDEX(partition_name, 'p', -1), 's', 1) = '2018'
GROUP BY 
    partition_name, subpartition_name
WITH ROLLUP;

-- 动态生成所有分区的查询
SELECT 
    CONCAT('p', YEAR(createtime) - 1, 'sm', MONTH(createtime) - 1) AS partition_info,
    COUNT(*) AS row_count
FROM 
    tab_certoperate_log
GROUP BY 
    YEAR(createtime) - 1, MONTH(createtime) - 1;

上述 SQL 的逻辑是:

  1. 使用 YEAR(createtime)MONTH(createtime) 提取分区和子分区的关键字段。
  2. 拼接分区名称(如 p2018sm1)。
  3. 统计每个分区的数据量。

方法 3:通过存储过程实现动态查询

如果分区较多,可以通过存储过程动态生成并执行查询语句。

DELIMITER $$

CREATE PROCEDURE count_partition_rows()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE year_val INT;
    DECLARE month_val INT;
    DECLARE cur CURSOR FOR 
        SELECT DISTINCT YEAR(createtime) - 1 AS year_val, MONTH(createtime) - 1 AS month_val 
        FROM tab_certoperate_log;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    CREATE TEMPORARY TABLE IF NOT EXISTS partition_counts (
        partition_info VARCHAR(50),
        row_count BIGINT
    );

    TRUNCATE TABLE partition_counts;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO year_val, month_val;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @partition_name = CONCAT('p', year_val, 'sm', month_val);
        SET @sql = CONCAT('INSERT INTO partition_counts (partition_info, row_count) 
                          SELECT ''', @partition_name, ''' AS partition_info, COUNT(*) AS row_count 
                          FROM tab_certoperate_log PARTITION (p', year_val, ') 
                          WHERE MONTH(createtime) - 1 = ', month_val);

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;

    SELECT * FROM partition_counts ORDER BY partition_info;
END$$

DELIMITER ;

CALL count_partition_rows();

存储过程逻辑说明:

  1. 创建一个临时表 partition_counts 用于存储分区名称和数据量。
  2. 使用游标遍历表中的所有年份和月份组合。
  3. 动态生成 SQL 查询每个分区的数据量,并插入到临时表中。
  4. 最终查询临时表的结果。

方法 4:通过系统视图获取分区信息

OceanBase 提供了系统视图 oceanbase.DBA_OB_PARTITIONSoceanbase.DBA_OB_SUBPARTITIONS,可以用来查询分区和子分区的信息。

SELECT 
    CONCAT(t1.partition_name, 's', t2.subpartition_name) AS partition_info,
    t2.num_rows AS row_count
FROM 
    oceanbase.DBA_OB_PARTITIONS t1
JOIN 
    oceanbase.DBA_OB_SUBPARTITIONS t2 
ON 
    t1.table_id = t2.table_id AND t1.partition_name = t2.partition_name
WHERE 
    t1.table_name = 'tab_certoperate_log';

依据文档库中的信息

  • oceanbase.DBA_OB_PARTITIONS 包含分区的元信息。
  • oceanbase.DBA_OB_SUBPARTITIONS 包含子分区的元信息。
  • num_rows 字段表示分区或子分区的数据量。

总结

以上方法均可实现对分区数据量的统计。推荐使用 方法 4,因为它直接利用系统视图,无需额外计算,性能更优。如果需要动态生成查询语句,则可以选择 方法 3

检索到的文档如下:

  1. 数据分布
  2. 分区概述
  3. 使用 OceanBase 数据库分区表进行水平拆分
  4. oceanbase.CDB_WR_SYSSTAT
  5. oceanbase.CDB_WR_STATNAME
  6. 数据表设计最佳实践
  7. OceanBase 术语
2 个赞

方法四

Cause of Failure:

ErrorCode = 1146, SQLState = 42S02, Details = Table ‘oceanbase.dba_ob_partitions’ doesn’t exist

@论坛小助手

4.3.3

:+1:

小助手回答的应该有问题