統計每個庫每個表的大小是數據治理的其中最簡單的一個要求，本文將從抽樣統計結果及精確統計結果兩方面來統計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_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 '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.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： 本文精確統計的腳本還有許多優化空間，寫的比較倉促，大家可以按需自行調整，水平有限，歡迎斧正。如有問題，歡迎與我溝通。

以上就是實例講解MySQL統計庫表大小的詳細內容，更多關于MySQL統計庫表大小的資料請關注好吧啦網其它相關文章！