小白学习MySQL - “投机取巧”统计表的记录数

bisal

发布于 2021-11-29 11:06:55

4.3K0

发布于 2021-11-29 11:06:55

同事提了个统计需求，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，