当前位置： 首页 > news >正文

南大通用数据库-Gbase-8a-学习-33-空洞率查询与解决方法

news 2025/7/22 6:52:03

目录

 

一、个人理解

二、存储过程

三、虚机测试

四、解决方法

1、重建表

2、shrink space

 

一、个人理解

空洞率的产生是由于delete语句并不会真实的删除数据，只是在数据上打了一个不可见标签，但实际还是占用着相应的存储空间。

 

二、存储过程

自定义存储过程实现：查询全库（不计算系统库）中所有表的空洞率。

drop procedure if exists "CheckALLDbVoidRate";DELIMITER //
CREATE PROCEDURE "CheckALLDbVoidRate"(VoidRate varchar(30))
beginDECLARE SelectALLTableSql   text;DECLARE SelectTableVoidRate text;DECLARE ExitFlag            INT; DECLARE TmpDB               VARCHAR(100);DECLARE TmpTab              VARCHAR(100);DECLARE cur REF CURSOR; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;set ExitFlag            = 0;set SelectALLTableSql   = 'select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables where TABLE_TYPE = \'BASE TABLE\' AND TABLE_SCHEMA not in (\'information_schema\',\'performance_schema\',\'gbase\',\'gclusterdb\')';set SelectTableVoidRate = 'select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO from performance_schema.tables where table_schema=? and table_name=? and DELETE_RATIO >= '||VoidRate||';';set @ExecuteSql         = SelectALLTableSql;set @ExecuteSql_1       = SelectTableVoidRate;prepare stmt from @ExecuteSql_1;OPEN cur FOR @ExecuteSql;WHILE ExitFlag = 0 DOFETCH cur INTO TmpDB,TmpTab; if ExitFlag = 0 thenset @Tab    = TmpTab;set @DB     = TmpDB;# select @DB,@Tab,@ExecuteSql_1;EXECUTE stmt using @DB,@Tab ; end if;END WHILE;    CLOSE cur; deallocate prepare stmt;
end;//
DELIMITER ;call CheckALLDbVoidRate('0');
参数名解释
VoidRate输入参数，字符串类型，表示输出大于等于空洞率为VoidRate的表。

三、虚机测试

gbase> drop procedure if exists "CheckALLDbVoidRate";
Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase> 
gbase> DELIMITER //
gbase> CREATE PROCEDURE "CheckALLDbVoidRate"(VoidRate varchar(30))-> begin->     DECLARE SelectALLTableSql   text;->     DECLARE SelectTableVoidRate text;->     DECLARE ExitFlag            INT; -> DECLARE TmpDB               VARCHAR(100);-> DECLARE TmpTab              VARCHAR(100);->     DECLARE cur REF CURSOR; ->     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET ExitFlag = 1;->     ->     set ExitFlag            = 0;-> ->     set SelectALLTableSql   = 'select TABLE_SCHEMA ,TABLE_NAME from information_schema.tables '>                            where TABLE_TYPE = \'BASE TABLE\' AND TABLE_SCHEMA not in '>                                (\'information_schema\',\'performance_schema\',\'gbase\',\'gclusterdb\')';->    -> set SelectTableVoidRate = 'select TABLE_SCHEMA,TABLE_NAME,truncate(STORAGE_SIZE/1024/1024) as STORAGESIZE,DELETE_RATIO '>                            from performance_schema.tables '>                                where table_schema=? and table_name=? and DELETE_RATIO >= '||VoidRate||';';-> -> ->     set @ExecuteSql         = SelectALLTableSql;->     set @ExecuteSql_1       = SelectTableVoidRate;-> -> prepare stmt from @ExecuteSql_1;-> ->     OPEN cur FOR @ExecuteSql;->     WHILE ExitFlag = 0 DO->     FETCH cur INTO TmpDB,TmpTab; ->         if ExitFlag = 0 then->     set @DB     = TmpDB;-> set @Tab    = TmpTab;-> # select @DB,@Tab,@ExecuteSql_1;->             EXECUTE stmt using @DB,@Tab ; ->         end if;->     END WHILE;->     CLOSE cur; -> -> deallocate prepare stmt;-> end;//
Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase> DELIMITER ;
gbase> 
gbase> call CheckALLDbVoidRate('0');
+--------------+--------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME         | STORAGESIZE | DELETE_RATIO |
+--------------+--------------------+-------------+--------------+
| bd_db_a141   | t_hn_customer_list |           0 |            0 |
+--------------+--------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.00)+--------------+-------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME        | STORAGESIZE | DELETE_RATIO |
+--------------+-------------------+-------------+--------------+
| bd_db_a141   | t_hn_stage_target |           0 |            0 |
+--------------+-------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.00)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | a          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)+--------------+---------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME    | STORAGESIZE | DELETE_RATIO |
+--------------+---------------+-------------+--------------+
| czg          | alldbvoidrate |           0 |            0 |
+--------------+---------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | b          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | czg        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | czg_test   |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.01)+--------------+------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME       | STORAGESIZE | DELETE_RATIO |
+--------------+------------------+-------------+--------------+
| czg          | d_admin_kpi_code |           0 |            0 |
+--------------+------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | hash_tb_like |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | moon       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | moon_copy  |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | sun        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | test       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.02)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | test20230302 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | test_12_05 |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | test_table |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | test_table_1 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.03)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | test_table_2 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | test_table_3 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| czg          | testtab    |           9 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| czg          | testtab_copy |           4 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | a          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.04)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | b          |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | czg        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | czg_test   |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)+--------------+------------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME       | STORAGESIZE | DELETE_RATIO |
+--------------+------------------+-------------+--------------+
| zxj          | d_admin_kpi_code |           0 |            0 |
+--------------+------------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.05)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| zxj          | hash_tb_like |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | moon       |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | moon_copy  |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | sun        |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.06)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | test_12_05 |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | test_table |           0 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| zxj          | test_table_1 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| zxj          | test_table_2 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.07)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| zxj          | test_table_3 |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)+--------------+------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | STORAGESIZE | DELETE_RATIO |
+--------------+------------+-------------+--------------+
| zxj          | testtab    |           1 |            0 |
+--------------+------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)+--------------+--------------+-------------+--------------+
| TABLE_SCHEMA | TABLE_NAME   | STORAGESIZE | DELETE_RATIO |
+--------------+--------------+-------------+--------------+
| zxj          | testtab_copy |           0 |            0 |
+--------------+--------------+-------------+--------------+
1 row in set (Elapsed: 00:00:00.08)Query OK, 0 rows affected (Elapsed: 00:00:00.08)

 

参数名说明
TABLE_SCHEMA库名
TABLE_NAME表名
STORAGESIZE存储空间（单位：M）
DELETE_RATIO空洞率（单位：%）

四、解决方法

1、重建表

我本来想把SQL包到存储过程中的，发现存储过程中不允许执行lock语句，会提示如下错误。

ERROR 1314 (0A000): LOCK is not allowed in stored procedures

放到语句句柄中执行报错如下：

This command is not supported in the prepared statement protocol yet

所以我们手动执行了，或者想包到其它语言里也行，方便。

gbase> lock table zxj.testtab write;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)gbase> create table zxj.testtab_new like zxj.testtab;   
Query OK, 0 rows affected (Elapsed: 00:00:00.13)gbase> insert into zxj.testtab_new select * from  zxj.testtab;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)
Records: 0  Duplicates: 0  Warnings: 0gbase> rename table zxj.testtab to zxj.testtab_old;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)gbase> rename table zxj.testtab_new to zxj.testtab;
Query OK, 0 rows affected (Elapsed: 00:00:00.02)gbase> unlock tables;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)gbase> drop table zxj.testtab_old;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

2、shrink space

建议在有gcluster_shrink_to_rebalance这个参数的版本使用shrink space full，避免出现一些不必要的麻烦。

gbase> set gcluster_shrink_to_rebalance=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)gbase> alter table zxj.testtab shrink space full ;
Query OK, 0 rows affected (Elapsed: 00:00:00.20)

如果不加full，如下：

gbase> alter table zxj.testtab shrink space;
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

这种耗时会较短在实际操作中，但释放空间效率不高，因为gbase按照列进行存储，一个列存储成一个文件，文件大小为2G，也就是下面的这个参数，需要这个文件中的每一条数据都打上删除标记，才能删除文件来释放空间。

base> show variables like'_gbase_segment_size';
+---------------------+------------+
| Variable_name       | Value      |
+---------------------+------------+
| _gbase_segment_size | 2147483648 |
+---------------------+------------+
1 row in set (Elapsed: 00:00:00.00)gbase> select 2147483648 / 1024 / 1024 / 1024;
+---------------------------------+
| 2147483648 / 1024 / 1024 / 1024 |
+---------------------------------+
|                  2.000000000000 |
+---------------------------------+
1 row in set (Elapsed: 00:00:00.00)

 

 

http://www.lryc.cn/news/56281.html

相关文章：

  • 为什么我们认为GPT是一个技术爆炸
  • 程序员如何能提高自己的编程水平？
  • 从零使用vuepress搭建个人博客部署.github.io
  • Python 进阶指南（编程轻松进阶）：十一、注释、文档字符串和类型提示
  • python item()方法
  • 【day2】Android Jetpack Compose环境搭建
  • stable-diffusion安装和简单测试
  • MATLAB算法实战应用案例精讲-【智能优化算法】 基于帕累托包络的选择算法II（PESA-II）（附MATLAB代码实现）
  • 【华为机试真题详解JAVA实现】—坐标移动
  • 【软考五】数据库（做题）
  • 【Java Web】012 -- SpringBootWeb综合案例（登录功能、登录校验、异常处理）
  • 跨界智能手表：比亚迪向左，小鹏向右
  • 【c++初阶】第九篇：vector（常用接口的使用 + 模拟实现）
  • Taro React组件使用（6） —— RuiSendCode 短信验证码【倒计时】
  • 把ChatGPT接入我的个人网站
  • 关于数字游民是未来年轻人工作趋势的一种思考
  • 2022年 合肥市经开区信息学竞赛区赛 初中组
  • 【工作小札】自定义classloader实现热加载jar
  • spring—AOP
  • 自己曾经的C++笔记【在c盘爆满的时候找到的回忆】
  • Nginx 实战-负载均衡
  • 本周大新闻|128GB版Quest 2再降价,Mojo Vision完成“新A轮”融资
  • 【论文阅读】如何给模型加入先验知识
  • arm系列交叉编译器各版本区别
  • 随笔记录工作日志
  • LinkedHashMap源码分析以及LRU的应用
  • 【每日一题Day166】LC1053交换一次的先前排列 | 贪心
  • Canal增量数据订阅和消费——原理详解
  • 为什么要使用线程池
  • 在云服务部署前后端以及上传数据库