统计所有库表的大小

2020-06-29 00:00:00 创建数据统计步骤大小

统计每个库每个表的大小是数据治理工作的基本内容，本文将从抽样统计结果及统计结果两方面来统计MySQL的每个库每个表的数据量情况。

1、统计预估数据量

mysql数据字典库information_schema里记录了统计的预估数据量（innodb引擎表不准确，MyISAM引擎表准确）及数据大小、索引大小及表碎片的大小等信息。

如果想了解每个库及表的大概数据量级，可以直接查information_schema.tables进行统计即可。例如：

SELECT  table_schema,table_name,table_rows,data_length+index_length+ data_free data_size
FROM information_schema.`TABLES` WHERE table_schema IN ('db1','db2')；

其中data_size单位为B

如上文所述，统计信息里的数据条数及size是根据部分数据抽样统计的值，与实际大小存在差异，且表越大，差异越明显，如果想知道每张表的实际情况，需用后续的方法。

2、统计实际数据量

想要统计每张表的实际大小就得去遍历每个表算出对的记录数，通过查看表空间大小（每个表独立表空间）查看每个表的size。通过以下步骤即可达到统计的目的。

创建路径

创建一个工作路径，保存脚本及临时文件等

mkdir -p  /usr/local/data_size

创建统计库及表

在需要统计的数据库实例上创建统计库

SQL>  create  database bak_db;

创建统计的存储过程

SQL> use bak_db;SQL>CREATE  PROCEDURE `p_db_size`()BEGINDECLARE v_id INT;DECLARE v_maxid INT;DECLARE v_tbname VARCHAR(50);DECLARE  v_dbname VARCHAR(50);DECLARE v_sql_upd VARCHAR(200);SET v_id =(SELECT MIN(id) FROM bak_db.tb_size);SET v_maxid =(SELECT MAX(id) FROM bak_db.tb_size);WHILE v_id <=v_maxidDOSET v_tbname = (SELECT tbname FROM  bak_db.tb_size WHERE  id=v_id);SET v_dbname = (SELECT dbname FROM  bak_db.tb_size WHERE  id=v_id);SET  v_sql_upd = CONCAT('update bak_db.tb_size  set tb_rows=(select count(*) from  ',v_dbname,".",v_tbname,") where id=",v_id);    SET  @v_sql_upd := v_sql_upd;    PREPARE stmt FROM @v_sql_upd;    EXECUTE stmt ;    DEALLOCATE PREPARE stmt;    SET v_id = v_id +1;END WHILE;    END;

创建脚本

vim       data.sh/*  插入如下内容*/
#! /bin/bashcd /usr/local/data_size
du -s  /data/mysql/mysql3306/data/db1/* |grep -v  ".frm" |grep -v ".opt" >/usr/local/data_size/data_sizedu -s  /data/mysql/mysql3306/data/db2/* |grep -v  ".frm" |grep -v ".opt">>/usr/local/data_size/data_size
# 后面4步是拼接成sqlawk '{print "insert into bak_db.tb_size(size,tb_route)values("""$0}' /usr/local/data_size/data_size >/usr/local/data_size/data_size1awk '{print $0";"}' /usr/local/data_size/data_size1 >/usr/local/data_size/data_size.sqlsed  -i "s#\t#,'#g"  /usr/local/data_size/data_size.sqlsed  -i "s#;#');#g"  /usr/local/data_size/data_size.sql
# 创建统计表 /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "drop table if exists bak_db.tb_size;CREATE TABLE IF NOT EXISTS bak_db.tb_size (  id INT(11) NOT NULL PRIMARY KEY  AUTO_INCREMENT ,size INT,tb_route VARCHAR(200),tbname VARCHAR(50),dbname VARCHAR(50),tb_rows INT(11));"
# 导入数据 /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use  bak_db;truncate table bak_db.tb_size;source /usr/local/data_size/data_size.sql;"
# 生成库名及表名，当然该步骤也可以从数据字段中获取 /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use  bak_db;UPDATE  bak_db.tb_size  SET  tbname=REPLACE(SUBSTRING_INDEX(tb_route,'/',-1),'.ibd','');" /usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "UPDATE  bak_db.tb_size  SET  dbname=LEFT (SUBSTRING_INDEX(tb_route,'/',-2),INSTR(SUBSTRING_INDEX(tb_route,'/',-2),'/')-1);"sleep 10              # 如果之前的步骤在主库金学习学习，则建议暂停一段时间 以免后面统计的时候无法获得表及内容，如果前面的步骤都在从库，则可以省略该步骤echo 'start call  procedure'# 调用存储过程 统计每个表的记录条数/usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.132 -e "use  bak_db;call bak_db.p_db_size();"
# 把表及数据导出/usr/local/mysql5.7/bin/mysqldump -uroot -p'Test#123456' -h 192.168.28.132 --single-transaction bak_db tb_size >/usr/local/data_size/tb_size.sql
# 将表及结果导入主库（从库相当于删除在重建了一次）/usr/local/mysql5.7/bin/mysql -uroot -p'Test#123456' -h 192.168.28.128 -e "use  bak_db;source /usr/local/data_size/tb_size.sql;"

结果如下：

可以看出值与统计信息里的值差异还是很大的，且表越大差异越明显。

TIPS：本文统计的脚本还有许多优化空间，写的比较仓促，大家可以按需自行调整，水平有限，欢迎斧正。如有问题，欢迎与我沟通。

想了解更多内容或参与技术交流可以关注微信公众号【数据库干货铺】或进技术交流群沟通。

>>>>

近期活动

2020年，Gdevops全球敏捷运维峰会开启了传播前沿技术、助力技术人成长进阶、促进跨界交流合作的第五个年头。依托迄今成功举办的17场大会在分享议题上的精心打磨、在技术圈子里的口碑传播，Gdevops在展开新一年技术巡演中邀请到更的嘉宾阵容，将带来更重磅的科技成果与实践。点击链接了解更多情况

2020 Gdevops全球敏捷运维峰会

往期精彩回顾

1. MySQL高可用之MHA集群部署

2. mysql8.0新增用户及加密规则修改的那些事

3. 比hive快10倍的大数据查询利器-- presto

4. 监控利器出鞘：Prometheus+Grafana监控MySQL、Redis数据库

5. PostgreSQL主从复制--物理复制

6. MySQL传统点位复制在线转为GTID模式复制

7. MySQL敏感数据加密及解密

8. MySQL数据备份及还原（一）

9. MySQL数据备份及还原（二）

扫

码

关

注

相关文章