首页 > 问答 > 关键词  > mysql最新资讯  > 正文

MySQL统计库表大小都是多少

2020-06-30 15:25 · 稿源:数据库干货铺

统计每个库每个表的大小是数据治理的其中最简单的一个要求,本文将从抽样统计结果及精确统计结果两方面来统计MySQL的每个库每个表的数据量情况。

1、统计预估数据量

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

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

SELECTtable_schema,table_name,table_rows,data_length+index_length+data_freedata_sizeFROMinformation_schema.`TABLES`WHEREtable_schemaIN('db1','db2');

其中data_size单位为B

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

2、统计实际数据量

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

创建路径

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

mkdir-p/usr/local/data_size

创建统计库及表

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

SQL>createdatabasebak_db;

创建统计的存储过程

SQL>usebak_db;
SQL>CREATEPROCEDURE`p_db_size`()BEGINDECLAREv_idINT;DECLAREv_maxidINT;DECLAREv_tbnameVARCHAR(50);DECLAREv_dbnameVARCHAR(50);DECLAREv_sql_updVARCHAR(200);SETv_id=(SELECTMIN(id)FROMbak_db.tb_size);SETv_maxid=(SELECTMAX(id)FROMbak_db.tb_size);WHILEv_id<=v_maxid
DOSETv_tbname=(SELECTtbnameFROMbak_db.tb_sizeWHEREid=v_id);SETv_dbname=(SELECTdbnameFROMbak_db.tb_sizeWHEREid=v_id);SETv_sql_upd=CONCAT('updatebak_db.tb_sizesettb_rows=(selectcount(*)from',v_dbname,".",v_tbname,")whereid=",v_id);SET@v_sql_upd:=v_sql_upd;PREPAREstmtFROM@v_sql_upd;EXECUTEstmt;DEALLOCATEPREPAREstmt;SETv_id=v_id+1;ENDWHILE;END;

创建脚本

vimdata.sh/*插入如下内容*/#!/bin/bash
cd/usr/local/data_sizedu-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"insertintobak_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'-h192.168.28.132-e"droptableifexistsbak_db.tb_size;CREATETABLEIFNOTEXISTSbak_db.tb_size(idINT(11)NOTNULLPRIMARYKEYAUTO_INCREMENT,sizeINT,tb_routeVARCHAR(200),tbnameVARCHAR(50),dbnameVARCHAR(50),tb_rowsINT(11));"

#导入数据
/usr/local/mysql5.7/bin/mysql-uroot-p'Test#123456'-h192.168.28.132-e"usebak_db;truncatetablebak_db.tb_size;source/usr/local/data_size/data_size.sql;"

#生成库名及表名,当然该步骤也可以从数据字段中获取
/usr/local/mysql5.7/bin/mysql-uroot-p'Test#123456'-h192.168.28.132-e"usebak_db;UPDATEbak_db.tb_sizeSETtbname=REPLACE(SUBSTRING_INDEX(tb_route,'/',-1),'.ibd','');"
/usr/local/mysql5.7/bin/mysql-uroot-p'Test#123456'-h192.168.28.132-e"UPDATEbak_db.tb_sizeSETdbname=LEFT(SUBSTRING_INDEX(tb_route,'/',-2),INSTR(SUBSTRING_INDEX(tb_route,'/',-2),'/')-1);"
sleep10#如果之前的步骤在主库金学习学习,则建议暂停一段时间以免后面统计的时候无法获得表及内容,如果前面的步骤都在从库,则可以省略该步骤
echo'startcallprocedure'
#调用存储过程统计每个表的记录条数
/usr/local/mysql5.7/bin/mysql-uroot-p'Test#123456'-h192.168.28.132-e"usebak_db;callbak_db.p_db_size();"

#把表及数据导出
/usr/local/mysql5.7/bin/mysqldump-uroot-p'Test#123456'-h192.168.28.132--single-transactionbak_dbtb_size>/usr/local/data_size/tb_size.sql

#将表及结果导入主库(从库相当于删除在重建了一次)
/usr/local/mysql5.7/bin/mysql-uroot-p'Test#123456'-h192.168.28.128-e"usebak_db;source/usr/local/data_size/tb_size.sql;"

结果如下:

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

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

本文转载自微信公众号【数据库干货铺】。

举报

  • 相关推荐
  • 大家在看

今日大家都在搜的词: