MySQL如何統計一個資料庫所有表的資料量

2022-04-15 19:01:28

場景：

mysql統計一個資料庫裡所有表的資料量，最近在做統計想查詢一個資料庫裡基本所有的表資料量，資料量少的通過select count再加起來也是可以的，不過表的資料有點多，不可能一個一個地查

記得在Navicat裡，選擇一個資料量，點選表，如圖：

是可以看到所有表具體的資料行的

然後可以通過sql實現？在mysql裡是可以查詢information_schema.tables這張表的

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

要統計的，加上sum函數就可以

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

OK，本來還以為已經沒問題了，然後還是被反饋統計不對，後來去找了資料

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

官網的解釋：

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才是正確的統計資料，但是對於InnoDB引擎的，可能與實際值相差 40% 到 50%，所以只是一個大概的統計

所以針對這種情況，要更改儲存引擎，肯定是不太合適，因為InnoDB是預設的儲存引擎，能支援事務外健，並行情況效能也比較好

所以，根據網上的做法，重新analyze 對應表，在mysql8.0版本是不管用的，發現查詢資料還是不對，估計是mysql版本太高，mysql5版本沒驗證過

analyze table [table_name]

繼續找資料，在Navicat工具->命令列頁面，設定全域性或者回話的information_schema_stats_expiry為0，表示自動更新，設定全域性的不知道會不會影響效能，所以不知道還是設定對談的就可以

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

查詢設定的information_schema_stats_expiry值

show variables like '%information_schema_stats%';

MySQL 8.0為了提高information_schema的查詢效率，會將檢視tables和statistics裡面的統計資訊快取起來，快取過期時間由引數information_schema_stats_expiry決定

補充：查詢表大小

我需要查詢的庫名為：kite

因此sql語句為：

select
table_schema as '資料庫',
table_name as '表名',
table_rows as '記錄數',
truncate(data_length/1024/1024, 2) as '資料容量(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;

結果如下：

總結

到此這篇關於MySQL如何統計一個資料庫所有表資料量的文章就介紹到這了,更多相關MySQL統計所有表資料量內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL如何統計一個資料庫所有表的資料量

目錄

場景：

官網的解釋：

補充：查詢表大小

總結

熱門文章