小白学习MySQL - “投机取巧”统计表的记录数
同事提了个统计需求,MySQL某个库60%的表都有个isdel字段(char(1)),值是0或1,现在要检索该数据库所有存在isdel字段且isdel=‘0’的表的记录数,举个例子,执行如下的count操作,
select count(*) from test where isdel = '0';
但是库中有上千张表,一张张地拼,不符合程序猿的风格。
开始考虑的,是打算通过information_schema的tables中有个table_rows字段,显示表的行数,
select table_name, table_rows
from information_schema.tables
where table_schema='test';
但是从官方文档的介绍,MyISAM存储引擎存储的是精确值,但是对其他引擎,例如InnoDB,这个值就是不准确的了,有可能和实际的相差40%-50%,针对这种场景,应该用count(*),得到准确的值。而且他特意指出,InnoDB存储引擎的表,这个值仅仅是SQL优化器优化过程中用到的一个“粗略”预估的值,
https://dev.mysql.com/doc/refman/5.7/en/information-schema-tables-table.html
穿插一句,Oracle中,我们知道,dba/all/user_tables视图的num_rows字段表示这张表的记录数,和上述含义相同,但是这个信息,只有当统计信息更新的时候,才会更新,而统计信息的更新除了手动调用dbms_stats,另外就是等每天凌晨的定时任务调用了,但是定时任务执行的时候,不是所有表都会采集,而是判断如果该表执行过truncate,或者增删改数据量超过10%,才触发统计信息的更新操作,10%的量就通过dba_tab_modifications(数据字典基表是mon_mods、mon_mods_all,DML操作记录到mon_mods,然后merge到mon_mods_all)来统计的,他会记录数据库表的DML操作,包括insert、delete、update。因此dba/all/user_tables视图的num_rows的值不是实时准确的。
针对上述场景,这些表是InnoDB,因此只能通过count(*),得到统计值。
有什么更简单的操作,能够不拼接这几千张表,但可以得到他们的统计值?
参考了这位兄弟的文章,
https://blog.csdn.net/weixin_43655401/article/details/93973023
这个统计,包括这几个步骤,
(1) 找到所有包含isdel字段且isdel='0'的表。
(2) 依次执行count(*),统计每张表的记录数。
(3) 将(2)中得到的表名和记录数,存储到另外一张表中,作为检索用途。
我们按照倒序,依次操作下,
1. 创建一张统计表,除了id、insert_time外,tablename存储表名称,total存储该表总量,
create table table_count(
id int auto_increment,
tablename varchar(255),
total int,
insert_time timestamp,
primary key(id)
);
2. 创建存储过程getDataByTableName,
(1) 入参是个表名,拼接的SQL是select count(*),加上这个入参,再加上条件where isdel = '0',将SQL执行结果,就是count(*)存入变量num。
(2) 如果(1)的num>0,则将表名、记录数、插入时间,存入table_count表。
delimiter $$
create procedure getDataByTableName(in tblName VARCHAR(255))
BEGIN
DECLARE num INT;
SET @STMT =CONCAT("SELECT COUNT(*) FROM ", tblName ," WHERE isdel = '0' INTO @num;");
PREPARE STMT FROM @STMT;
EXECUTE STMT;
IF(@num>0) THEN
INSERT INTO table_count(tablename, total, insert_time) VALUES (tblName, @num, now());
END IF;
end$$
delimiter ;
3. 创建存储过程process,
(1) 通过检索information_schema的columns视图,找到数据库test下存在列名叫isdel的表名,放入游标。
(2) 遍历游标,依次调用步骤2创建的存储过程。
delimiter $$
create procedure process()
BEGIN
DECLARE done BOOLEAN DEFAULT 0;
DECLARE tname VARCHAR (255);
DECLARE cur CURSOR
FOR
SELECT TABLE_NAME FROM information_schema.COLUMNS
WHERE COLUMN_NAME = 'isdel' AND TABLE_SCHEMA='test';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
OPEN cur;
repeat FETCH cur INTO tname;
call getDataByTableName(tname);
UNTIL done END repeat;
CLOSE cur;
end$$
delimiter ;
4. 执行存储过程process,
call process();
5. 检索table_count,此时表中记录,就是所有isdel='0',且count(*)>0的表名和对应的记录数了,
select * from table_count;
其实整个过程,就是我们惯性思维能考虑到的,只是要通过存储过程等技术进行封装,MySQL存储过程的语法,确实不太熟悉,借此学习一下。
小白学习MySQL,
《小白学习MySQL - TIMESTAMP类型字段非空和默认值属性的影响》
《小白学习MySQL - InnoDB支持optimize table?》
《小白学习MySQL - table_open_cache的作用》
《小白学习MySQL - only_full_group_by的校验规则》
《小白学习MySQL - max_allowed_packet》
《小白学习MySQL - mysqldump保证数据一致性的参数差异》
相关文章
- 从本体论开始说起——运营商关系图谱的构建及应用
- 如何成为一名数据科学家?
- 从未见过的堂兄杀了人,你的DNA是关键证据
- 20个安全可靠的免费数据源,各领域数据任你挑
- 20个安全可靠的免费数据源,各领域数据任你挑
- 阿里云李飞飞:All in Cloud时代,云原生数据库优势明显
- 基于Hadoop生态系统的一高性能数据存储格式CarbonData(性能篇)
- 大数据告诉你:10年漫威,到底有多少角色
- TigerGraph:实时图数据库助力金融风控升级
- Splunk利用Splunk Connected Experiences和Splunk Business Flow 扩大数据访问
- 大数据开发常见的9种数据分析手段
- 以免在景区看人,我爬了5W条全国景点门票数据...
- 【实战解析】基于HBase的大数据存储在京东的应用场景
- 数据科学家告诉你哪些计算机科学书籍是你应该看的
- Kafka作为大数据的核心技术,你了解多少?
- Spring Boot 整合 Redis 实现缓存操作
- 大数据学习必须掌握的五大核心技术有哪些?
- 基于Antlr在Apache Flink中实现监控规则DSL化的探索实践
- 甲骨文再次被Gartner评为分析型数据管理解决方案魔力象限领导者
- 爬取吴亦凡微博102118条转发数据,扒一扒流量的真假