项目背景
从gbase8a迁移数据到mysql5.6.46中,获取大表记录数
生成查询某个schema下所有表的记录数的SQL语句
select concat(‘select ‘’’,table_schema,’.’,table_name,’’’ as table_name,count() as total_num from ‘,table_name,’ union all’) from information_schema.tables where table_schema=‘test’;
结果如下
select ‘test.cmst_i_h_data’ as table_name,count() as total_num from cmst_i_h_data union all
select ‘test.info_config’ as table_name,count() as total_num from info_config union all
select ‘test.m_page_view_log’ as table_name,count() as total_num from m_page_view_log union all
select ‘test.sync_table’ as table_name,count() as total_num from sync_table union all
select ‘test.test’ as table_name,count() as total_num from test union all
select ‘test.test1’ as table_name,count() as total_num from test1 union all
select ‘test.test_100’ as table_name,count() as total_num from test_100 union all
去掉最后一行的union all部分,并将查询放入下面查询结构体中
select table_name,total_num from (
上述查询语句
) as t
where total_num>1000000
说明: total_num>1000000代表查询某个schema下超过1百万记录的表
总结:
1、获取大表记录数也有其他方式,只要有相应权限即可
2、count()不加条件,速度一样非常快,8a采用express存储引擎,这里怀疑表记录总数是单独存储的
gbase> select count() from test_100;
±---------+
| count(*) |
±---------+
| 9463294 |
±---------+
1 row in set (Elapsed: 00:00:00.00)