统计每个库每个表的大小是数据治理的其中最简单的一个要求, 本文将从抽样统计结果及精确统计结果两方面来统计 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`()
- BEGIN
- DECLARE 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_maxid
- DO
- SET 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/bash
- cd /usr/local/data_size
- du -s /data/MySQL/mysql3306/data/db1/* |grep -v ".frm" |grep -v ".opt">/usr/local/data_size/data_size
- du -s /data/MySQL/mysql3306/data/db2/* |grep -v ".frm" |grep -v ".opt">>/usr/local/data_size/data_size
- # 后面 4 步是拼接成 sql
- awk '{print"insert into bak_db.tb_size(size,tb_route)values("""$0}' /usr/local/data_size/data_size>/usr/local/data_size/data_size1
- awk '{print $0";"}' /usr/local/data_size/data_size1>/usr/local/data_size/data_size.sql
- sed -i "s#\t#,'#g" /usr/local/data_size/data_size.sql
- sed -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: 本文精确统计的脚本还有许多优化空间, 写的比较仓促, 大家可以按需自行调整, 水平有限, 欢迎斧正. 如有问题, 欢迎与我沟通.
来源: https://www.cnblogs.com/gjc592/p/13209367.html