場景：

mysql統(tǒng)計(jì)一個(gè)數(shù)據(jù)庫里所有表的數(shù)據(jù)量，最近在做統(tǒng)計(jì)想查找一個(gè)數(shù)據(jù)庫里基本所有的表數(shù)據(jù)量，數(shù)據(jù)量少的通過select count再加起來也是可以的，不過表的數(shù)據(jù)有點(diǎn)多，不可能一個(gè)一個(gè)地查

記得在Navicat里，選擇一個(gè)數(shù)據(jù)量，點(diǎn)擊表，如圖：

是可以看到所有表具體的數(shù)據(jù)行的

然后可以通過sql實(shí)現(xiàn)？在mysql里是可以查詢information_schema.tables這張表的

SELECT table_rows,table_name FROM information_schema.tables  
 WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名稱' 
 and table_name not in ('不查詢的表名稱') 
 ORDER BY table_rows DESC;

要統(tǒng)計(jì)的，加上sum函數(shù)就可以

SELECT sum(table_rows) FROM information_schema.tables  
 WHERE TABLE_SCHEMA = '數(shù)據(jù)庫名稱' 
 and table_name not in ('不查詢的表名稱') 
 ORDER BY table_rows DESC;

OK，本來還以為已經(jīng)沒問題了，然后還是被反饋統(tǒng)計(jì)不對，后來去找了資料

https://dev.mysql.com/doc/refman/8.0/en/information-schema-tables-table.html

官網(wǎng)的解釋：

TABLE_ROWS
The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40% to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.
TABLE_ROWS is NULL for INFORMATION_SCHEMA tables.
For InnoDB tables, the row count is only a rough estimate used in SQL optimization. (This is also true if the InnoDB table is partitioned.)

大概意思是對于MyISAM才是正確的統(tǒng)計(jì)數(shù)據(jù)，但是對于InnoDB引擎的，可能與實(shí)際值相差 40% 到 50%，所以只是一個(gè)大概的統(tǒng)計(jì)

所以針對這種情況，要更改存儲(chǔ)引擎，肯定是不太合適，因?yàn)镮nnoDB是默認(rèn)的存儲(chǔ)引擎，能支持事務(wù)外健，并發(fā)情況性能也比較好

所以，根據(jù)網(wǎng)上的做法，重新analyze 對應(yīng)表，在mysql8.0版本是不管用的，發(fā)現(xiàn)查詢數(shù)據(jù)還是不對，估計(jì)是mysql版本太高，mysql5版本沒驗(yàn)證過

analyze table [table_name]

繼續(xù)找資料，在Navicat工具->命令行頁面，設(shè)置全局或者回話的information_schema_stats_expiry為0，表示自動(dòng)更新，設(shè)置全局的不知道會(huì)不會(huì)影響性能，所以不知道還是設(shè)置會(huì)話的就可以

SET SESSION information_schema_stats_expiry=0;
SET @@SESSION.information_schema_stats_expiry=0;

查詢設(shè)置的information_schema_stats_expiry值

show variables like '%information_schema_stats%';

MySQL 8.0為了提高information_schema的查詢效率，會(huì)將視圖tables和statistics里面的統(tǒng)計(jì)信息緩存起來，緩存過期時(shí)間由參數(shù)information_schema_stats_expiry決定

補(bǔ)充：查詢表大小

我需要查詢的庫名為：kite

因此sql語句為：

select
table_schema as '數(shù)據(jù)庫',
table_name as '表名',
table_rows as '記錄數(shù)',
truncate(data_length/1024/1024, 2) as '數(shù)據(jù)容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='kite'
order by table_rows desc, index_length desc;

結(jié)果如下：

總結(jié) 

到此這篇關(guān)于MySQL如何統(tǒng)計(jì)一個(gè)數(shù)據(jù)庫所有表數(shù)據(jù)量的文章就介紹到這了,更多相關(guān)MySQL統(tǒng)計(jì)所有表數(shù)據(jù)量內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論