GBase的每张表，可以通过count来统计行数，本文提供了一个自定义存储过程来统计符合条件的一批表的行数。
目录导航
提示
本文提供的自定义存储过程，采用的也是循环查询每个表，虽然方便，但同样存在表数量过多时，系统资源占用时间长的问题，所以请尽量通过参数，减少统计范围。
另外，为提升性能，采用了多个count一次执行的方式，所以需要一次性打开多个表。这涉及到数据库的同时可打开的表数量的参数
GBase 8a表打开缓冲参数table_open_cache和表定义缓冲参数table_definition_cache
所以在存储过程中，判断了如果超过3000字符，则先执行一批。如果调高了数据库的参数，可以适当增加3000到10000长度。
存储过程
表类型
默认只查询Express引擎的表，对性能影响小，省去了目录扫描部分的开销，其SQL语句如下
select tbname table_name,isreplicate
from gbase.table_distribution b
where b.dbname=DB and tbname like TBL order by tbname ;如果要查询当前节点所有的表，则使用下面的SQL,注意information_schema.tables，其like操作将做整个目录的扫描，如果表特别多，耗时长，资源影响高，非特殊场景不建议使用。
select table_name,isreplicate
from information_schema.tables a
left join gbase.table_distribution b on b.tbname=a.table_name
where table_schema=DB and table_name like TBL
order by table_name;返回结果
该存储过程最后返回时，可以自行指定排序，默认是中间表的表名字。
select * from COUNT_TABLE;该表为临时比表，执行完存储过程后，可以重复查询，比如
select * from COUNT_TABLE order by countValue desc;完整存储过程
-- 调用information的table,比较方便，但性能不好
-- 自己每个表count,性能也不好，必须多个表count union在一起才可以。
-- 调用样例如下，参数为db的名字
-- call countDB('dap_model');
-- call countDB('dap_model.asc%');
delimiter ;
drop procedure if exists countDB;
DELIMITER //
CREATE DEFINER="root"@"%" PROCEDURE "countDB"(
in dbname varchar(64)
)
BEGIN
DECLARE TBL varchar(64);
DECLARE DB varchar(64);
DECLARE idx int;
select instr(dbname,'.') into idx;
if idx > 0 then
set DB=substr(dbname,0,instr(dbname,'.')-1);
set TBL=substr(dbname,instr(dbname,'.')+1,length(dbname));
else
set DB=dbname;
set TBL='%';
end if;
call _countDB(DB,TBL);
END //
DELIMITER ;
drop procedure if exists _countDB;
DELIMITER //
CREATE DEFINER="root"@"%" PROCEDURE "_countDB"(
in DB varchar(64),
in TBL varchar(64)
)
BEGIN
DECLARE tableName varchar(64);
DECLARE isReplicate varchar(64);
DECLARE SQL_TMP varchar(21000);
DECLARE done INT DEFAULT 0;
DECLARE CUR_PM_POINFO CURSOR FOR
select tbname table_name,isreplicate from gbase.table_distribution b where b.dbname=DB and tbname like TBL order by tbname ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
drop temporary table if exists COUNT_TABLE;
create temporary table COUNT_TABLE(
tableName varchar(64),
isReplicate varchar(64),
countValue bigint
);
OPEN CUR_PM_POINFO;
FETCH CUR_PM_POINFO INTO tableName, isReplicate;
set SQL_TMP='';
while ( done = 0 ) do
-- select tableName,isReplicate;
if(SQL_TMP='')then
set SQL_TMP=concat('select ''',tableName,''',''',nvl(isReplicate,'NO'),''',count(*) from ',DB,'.',tableName);
else
set SQL_TMP=concat(SQL_TMP,' \nunion all select ''',tableName,''',''',nvl(isReplicate,'NO'),''',count(*) from ',DB,'.',tableName);
end if;
if(length(SQL_TMP)>=3000) then
set SQL_TMP=concat('insert into COUNT_TABLE select * from (',SQL_TMP,')a');
set @sql = SQL_TMP;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
set SQL_TMP='';
end if;
FETCH CUR_PM_POINFO INTO tableName, isReplicate;
end while;
if(SQL_TMP <> '') then
set SQL_TMP=concat('insert into COUNT_TABLE select * from (',SQL_TMP,')a');
set @sql = SQL_TMP;
-- select @sql;
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
set SQL_TMP='';
end if;
select * from COUNT_TABLE;
-- drop temporary table COUNT_TABLE;
END //
delimiter ;执行样例
查询某个库下的所有表的行数
再次警告：如果符合条件的表非常多，则查询时间长，且系统资源占用也长。
gbase> call countdb('testdb');
+-----------+-------------+------------+
| tableName | isReplicate | countValue |
+-----------+-------------+------------+
| d1 | NO | 1000001 |
| t1 | NO | 14 |
| t2 | NO | 1 |
| t3 | NO | 1 |
| t4 | NO | 3 |
| test20c | NO | 1000001 |
| test20c_e | NO | 1000000 |
+-----------+-------------+------------+
7 rows in set (Elapsed: 00:00:00.41)
gbase> call countdb('gclusterdb');
+----------------------+-------------+------------+
| tableName | isReplicate | countValue |
+----------------------+-------------+------------+
| dual | NO | 1 |
| gcs_1694629898_140_7 | NO | 0 |
| nodedatamap | NO | 65536 |
| rebalancing_status | NO | 0 |
+----------------------+-------------+------------+
4 rows in set (Elapsed: 00:00:00.32)
Query OK, 0 rows affected (Elapsed: 00:00:00.32)
统计某个库下，匹配的表行数
匹配的方式和like相同，库名和表名间用点分割，具体算法看countDB的代码。
gbase> call countdb('testdb.t%');
+-----------+-------------+------------+
| tableName | isReplicate | countValue |
+-----------+-------------+------------+
| t1 | NO | 14 |
| t2 | NO | 1 |
| t3 | NO | 1 |
| t4 | NO | 3 |
| test20c | NO | 1000001 |
| test20c_e | NO | 1000000 |
+-----------+-------------+------------+
6 rows in set (Elapsed: 00:00:00.38)
Query OK, 0 rows affected (Elapsed: 00:00:00.38)
gbase> call countdb('testdb.%20%');
+-----------+-------------+------------+
| tableName | isReplicate | countValue |
+-----------+-------------+------------+
| test20c | NO | 1000001 |
| test20c_e | NO | 1000000 |
+-----------+-------------+------------+
2 rows in set (Elapsed: 00:00:00.30)
Query OK, 0 rows affected (Elapsed: 00:00:00.30)