小编给大家分享一下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;"
结果如下:
可以看出精确值与统计信息里的值差异还是很大的,且表越大 差异越明显。
看完了这篇文章,相信你对MySQL如何统计库表大小有了一定的了解,想了解更多相关知识,欢迎关注亿速云行业资讯频道,感谢各位的阅读!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。