快捷導(dǎo)航

實(shí)例講解MySQL統(tǒng)計(jì)庫(kù)表大小

更新時(shí)間：2020年07月04日 11:55:07 作者：懂點(diǎn)IT的耿小廚

這篇文章主要介紹了MySQL統(tǒng)計(jì)庫(kù)表大小的相關(guān)資料，文中示例代碼非常詳細(xì)，幫助大家更好的理解和學(xué)習(xí)，感興趣的朋友可以了解下

統(tǒng)計(jì)每個(gè)庫(kù)每個(gè)表的大小是數(shù)據(jù)治理的其中最簡(jiǎn)單的一個(gè)要求，本文將從抽樣統(tǒng)計(jì)結(jié)果及精確統(tǒng)計(jì)結(jié)果兩方面來統(tǒng)計(jì)MySQL的每個(gè)庫(kù)每個(gè)表的數(shù)據(jù)量情況。

1、統(tǒng)計(jì)預(yù)估數(shù)據(jù)量

mysql數(shù)據(jù)字典庫(kù)information_schema里記錄了統(tǒng)計(jì)的預(yù)估數(shù)據(jù)量（innodb引擎表不準(zhǔn)確，MyISAM引擎表準(zhǔn)確）及數(shù)據(jù)大小、索引大小及表碎片的大小等信息。

如果想了解每個(gè)庫(kù)及表的大概數(shù)據(jù)量級(jí)，可以直接查information_schema.tables進(jìn)行統(tǒng)計(jì)即可。例如：

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

如上文所述，統(tǒng)計(jì)信息里的數(shù)據(jù)條數(shù)及size是根據(jù)部分?jǐn)?shù)據(jù)抽樣統(tǒng)計(jì)的值，與實(shí)際大小存在差異，且表越大，差異越明顯，如果想知道每張表的實(shí)際情況，需用后續(xù)的方法。

2、統(tǒng)計(jì)實(shí)際數(shù)據(jù)量

想要統(tǒng)計(jì)每張表的實(shí)際大小就得去遍歷每個(gè)表算出對(duì)的記錄數(shù)，通過查看表空間大?。總€(gè)表獨(dú)立表空間）查看每個(gè)表的size。通過以下步驟即可達(dá)到精確統(tǒng)計(jì)的目的。

創(chuàng)建路徑

創(chuàng)建一個(gè)工作路徑，保存腳本及臨時(shí)文件等

mkdir -p /usr/local/data_size

創(chuàng)建統(tǒng)計(jì)庫(kù)及表

在需要統(tǒng)計(jì)的數(shù)據(jù)庫(kù)實(shí)例上創(chuàng)建統(tǒng)計(jì)庫(kù)

SQL> create database bak_db;

創(chuàng)建統(tǒng)計(jì)的存儲(chǔ)過程

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;

創(chuàng)建腳本

vim    data.sh
/* 插入如下內(nèi)容*/

#! /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

# 創(chuàng)建統(tǒng)計(jì)表
 /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));" 

# 導(dǎo)入數(shù)據(jù)
 /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;"

# 生成庫(kù)名及表名，當(dāng)然該步驟也可以從數(shù)據(jù)字段中獲取
 /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       # 如果之前的步驟在主庫(kù)金學(xué)習(xí)學(xué)習(xí)，則建議暫停一段時(shí)間 以免后面統(tǒng)計(jì)的時(shí)候無法獲得表及內(nèi)容，如果前面的步驟都在從庫(kù)，則可以省略該步驟
echo 'start call procedure' 
# 調(diào)用存儲(chǔ)過程 統(tǒng)計(jì)每個(gè)表的記錄條數(shù)
/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();"

# 把表及數(shù)據(jù)導(dǎo)出
/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

# 將表及結(jié)果導(dǎo)入主庫(kù)（從庫(kù)相當(dāng)于刪除在重建了一次）
/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;"

結(jié)果如下：