杨浦区建设和交通委员会官方网站中小企业管理培训班
目录
一、个人理解
二、存储过程
三、虚机测试
四、解决方法
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)