快捷導(dǎo)航

mysql數(shù)據(jù)庫空間統(tǒng)計sql代碼示例

更新時間：2024年08月09日 09:10:14 作者：小Tomkk

在mysql中有一個information_schema數(shù)據(jù)庫,這個數(shù)據(jù)庫中裝的是mysql的元數(shù)據(jù),包括數(shù)據(jù)庫信息、數(shù)據(jù)庫中表的信息等,這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫空間統(tǒng)計sql的相關(guān)資料,需要的朋友可以參考下

說明

INFORMATION_SCHEMA Table Reference 表參考

information_schema是‌MySQL中的一個特殊數(shù)據(jù)庫，它存儲了關(guān)于所有其他數(shù)據(jù)庫的元數(shù)據(jù)信息。這些元數(shù)據(jù)包括數(shù)據(jù)庫名、表名、列的數(shù)據(jù)類型、訪問權(quán)限等。通過查詢information_schema，用戶可以獲取到關(guān)于數(shù)據(jù)庫結(jié)構(gòu)的詳細(xì)信息，這對于數(shù)據(jù)庫管理和優(yōu)化非常有幫助。例如，可以通過查詢information_schema來查看表的索引信息、視圖定義、存儲過程和函數(shù)的信息等。此外，由于information_schema中的表都是只讀的，它們實際上可以被視為視圖，因此用戶無法直接修改這些數(shù)據(jù)，保證了元數(shù)據(jù)的完整性。‌

一、數(shù)據(jù)庫存儲代碼

請注意

如果啟用了innodb_read_only系統(tǒng)變量，ANALYZE TABLE可能會失敗，因為它無法更新使用InnoDB的數(shù)據(jù)字典中的統(tǒng)計表。對于更新鍵分布的ANALYZE TABLE操作，即使操作更新表本身(例如，如果它是一個MyISAM表)，也可能發(fā)生失敗。要獲取更新的分布統(tǒng)計信息，可以設(shè)置information_schema_stats_expiry=0。

代碼如下（GB）（示例）：以下是GB的統(tǒng)計
查詢一個實例的所有庫的數(shù)據(jù)的大小總和

select coalesce(table_schema, '合計') as table_schema ,
concat(round(sum(data_length/1024/1024/1024),2),'GB') as data_length_GB, 
concat(round(sum(index_length/1024/1024/1024),2),'GB') as index_length_GB  ,
concat(round(sum(index_length/1024/1024/1024),2)+round(sum(data_length/1024/1024/1024),2),'GB')  as tal_GB 
from information_schema.tables t where table_Type='BASE TABLE'
and table_schema not in ('document','mysql','performance_schema','sys')
group by table_schema

代碼如下（MB）（示例）： MB
查詢一個實例的所有庫的數(shù)據(jù)的大小總和

select coalesce(table_schema, '合計') as table_schema,
concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB, 
concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB  ,
concat(round(sum(index_length/1024/1024),2)+round(sum(data_length/1024/1024),2),'MB')  as tal_MB
from information_schema.tables t where table_Type='BASE TABLE'
and table_schema not in ('document','mysql','performance_schema','sys')
group by table_schema WITH ROLLUP order by round(sum(data_length/1024/1024),2) desc

二、查詢某個數(shù)據(jù)庫的所有表的代碼

SELECT
    TABLE_NAME, ENGINE, VERSION, ROW_FORMAT, TABLE_ROWS, AVG_ROW_LENGTH,
    DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT,
    CREATE_TIME, UPDATE_TIME, CHECK_TIME, TABLE_COLLATION, CHECKSUM,
    CREATE_OPTIONS, TABLE_COMMENT
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW TABLE STATUS
  FROM db_name
  [LIKE 'wild']

The following statements are equivalent:

SELECT
  TABLE_NAME, TABLE_TYPE
  FROM INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'db_name'
  [AND table_name LIKE 'wild']

SHOW FULL TABLES
  FROM db_name
  [LIKE 'wild']

三、列出所有已經(jīng)產(chǎn)生碎片的表

-- 列出所有已經(jīng)產(chǎn)生碎片的表 ('information_schema', 'mysql'這兩個庫是mysql自帶的庫)
select 
table_schema db, 
table_name,
 data_free, 
 engine,
 table_rows,
 data_length+index_length length 
from
information_schema.tables   
where 
table_schema not in ('information_schema', 'mysql') and data_free > 0
ORDER BY data_free desc

處理表碎片

alter table gd_channel_app_retention engine=innodb;

note：這個語句處理碎片空間其實是先復(fù)制現(xiàn)有數(shù)據(jù)表然后刪除舊的數(shù)據(jù)表。如果這個表占用空間巨大，還是直接遷移數(shù)據(jù)吧。

具體可以查看 mysql 幫助

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

總結(jié)

information_schema用于存儲數(shù)據(jù)庫元數(shù)據(jù)，本文sql 主要是 MySQL系統(tǒng)庫之information_schema的實現(xiàn)，

查詢數(shù)據(jù)庫結(jié)構(gòu)：information_schema 可用于查詢數(shù)據(jù)庫、表、列、索引、外鍵、觸發(fā)器等對象的結(jié)構(gòu)信息。
權(quán)限管理：可以使用 information_schema 查詢用戶和權(quán)限信息，以確保正確的訪問控制和權(quán)限設(shè)置。
性能優(yōu)化：information_schema 提供有關(guān)索引、表大小、表引擎等性能相關(guān)信息，這對于性能優(yōu)化很有幫助。
查詢執(zhí)行計劃：可以查詢 information_schema 獲取查詢執(zhí)行計劃，以了解查詢?nèi)绾伪粓?zhí)行。

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

您可能感興趣的文章: