怎樣在磁碟上查詢MySQL表的大小?這裡有答案
我想知道 MySQL 表在磁碟上佔用多少空間,但看起來很瑣碎。不應該在 INFORMATION_SCHEMA.TABLES 中提供這些資訊嗎?沒那麼簡單! |
我想知道 MySQL 表在磁碟上佔用多少空間,但看起來很瑣碎。不應該在 INFORMATION_SCHEMA.TABLES 中提供這些資訊嗎?沒那麼簡單!
這個看似簡單的問題實際上在 MySQL 中非常複雜。MySQL 支援許多儲存引擎(其中一些根本不在磁碟上儲存資料), 不同的儲存資料格式。例如,InnoDB 儲存引擎為 MySQL 5.7 提供了三種“基本”格式,其中包含 row_formats 和兩種可壓縮的種類。
簡化一下:我們如何在磁碟上查詢儲存在其自己的表空間中的 InnoDB 表的表大小(前提是 innodb_file_per_table=1 )。
在我們得到答案之前,先展示透過 sysbench 執行預先獲得的圖表(批次資料插入表):
在磁碟上查詢 MySQL 表的大小
此圖顯示了從 INFORMATION_SCHEMA.TABLES 獲取的 data_length 和 index_length 所定義的表大小。可以預期,隨著資料的增多,表格會跳躍增長(有時會增加 10GB 或更多)。
該圖表與磁碟上資料的變化方式不匹配,它逐漸增長(如預期):
-rw-r----- 1 mysql mysql 220293234688 Jan 25 17:03 sbtest1.ibd -rw-r----- 1 mysql mysql 220310011904 Jan 25 17:03 sbtest1.ibd -rw-r----- 1 mysql mysql 222499438592 Jan 25 17:07 sbtest1.ibd
正如我們從這個實驗中看到的那樣,MySQL 並沒有真正的實時維護 data_length 和 index_length 的值,而是定期重新整理它們 - 而且不規則地重新整理它們。圖表的後半部分一些資料重新整理變得更加規律。這與圖表的第一部分不同,後者似乎每次有 10% 的行更改時,就更新一次統計資訊。table_rows, data_free 或 update_time ,它們也是實時更新的。
要在 MySQL 5.7獲取 information_schema 獲取到更準確的實時資訊,需要做兩件事:
禁用 innodb_stats_persistent
啟用 innodb_stats_on_metadata
這兩者都會帶來嚴重的代價。
禁用永續性統計資訊意味著每次伺服器啟動時 InnoDB 都必須重新整理統計資訊,這代價很大,並且可能會在重新啟動之間產生不穩定的查詢計劃。那有沒有更好的辦法呢?事實證明有。
可以透過 INNODB_SYS_TABLESPACES 檢視錶空間資訊表以檢視實際檔案大小。與 index_length 和 data_length 不同, INNODB_SYS_TABLESPACES 實時更新,無需特殊配置:
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1' \G *************************** 1. row *************************** SPACE: 42 NAME: sbinnodb/sbtest1 FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 245937209344 ALLOCATED_SIZE: 245937266688 1 row in set (0.00 sec)
使用這個表的好處是,它還處理新功能 “InnoDB 頁壓縮”,正確顯示了 file_size (磁碟上的邏輯檔案大小)和 allocated_size(為此檔案分配的空間,並且可以顯著縮小)之間的區別。
最後,讓我們看一下不同的 InnoDB 壓縮方式如何影響 information_schema 中提供的資訊。
mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G *************************** 1. row *************************** SPACE: 48 NAME: sbinnodb/testcomp FLAG: 33 FILE_FORMAT: Barracuda ROW_FORMAT: Dynamic PAGE_SIZE: 16384 ZIP_PAGE_SIZE: 0 SPACE_TYPE: Single FS_BLOCK_SIZE: 4096 FILE_SIZE: 285212672 ALLOCATED_SIZE: 113004544 1 row in set (0.00 sec)
如果您使用舊的 InnoDB 壓縮(InnoDB 表壓縮),您將看到 data_length 和 index_length 中顯示的壓縮資料大小作為結果。例如, avg_row_length 將遠低於您的預期。
如果在 MySQL 5.7 中使用新的 InnoDB 壓縮(InnoDB 頁壓縮),您將看到與檔案大小相對應的值,而不是如 information_schema 中所示的分配大小。
回答一個微不足道的問題“這個表在磁碟上佔用了多少空間?” 在 MySQL 中真的不是一個簡單的問題 - 顯而易見的資料,可能會得到錯誤的答案。
檢視 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 以獲取 InnoDB 表的實際檔案大小值。
原文連結:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31559985/viewspace-2657871/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL 庫大小、表大小、索引大小查詢命令MySql索引
- 查詢MySQL資料庫,MySQL表的大小MySql資料庫
- 查詢表的大小
- 【sqlserver】查詢 表的大小SQLServer
- Oracle查詢表佔磁碟空間大小及移動表空間Oracle
- 關於MySQL 查詢表資料大小的總結MySql
- 表空間大小查詢
- MYSQL中怎麼查詢LONGBLOB型別資料的大小MySql型別
- 小白如何入門Mybatis?這裡有答案MyBatis
- 查詢oracle 表的大小和表的建立時間Oracle
- MYSQL查詢和插入資料的流程是怎樣的MySql
- 將主查詢條件寫到子查詢裡執行效果會怎樣?
- mysql字串之大小寫匹配查詢MySql字串
- 怎樣查詢一個表的最後更新時間?
- MySQL單表查詢MySql
- mysql鎖表查詢MySql
- MySQL 單表查詢MySql
- 查詢表的大小及表空間的使用情況
- impala查詢不到hive裡建立的表Hive
- 怎樣在 Ubuntu Linux 上安裝 MySQLUbuntuLinuxMySql
- 關於在javabean裡封裝,大家來看看這樣做怎樣?JavaBean封裝
- 使MySQL查詢區分大小寫(轉)MySql
- Python和人工智慧有到底什麼關係?答案在這裡!Python人工智慧
- Sqlserver查詢出所有表的大小使用情況SQLServer
- 查詢表空間的大小和使用率
- 如何使用Finder在Mac上檢視資料夾大小?這樣做Mac
- MySql 鎖表 查詢 命令MySql
- mysql表碎片的查詢自己回收MySql
- Oracle 查詢表大小以及表空間使用率Oracle
- SQL裡3個表的連線查詢SQL
- MySQL模糊查詢(like)時區分大小寫MySql
- 查詢資料庫每個表佔用的大小資料庫
- 查詢資料量的大小
- [MySQL] - 聯表查詢,查詢一個不在另一個表的記錄MySql
- 報表的查詢皮膚怎麼顯示在左側?
- jQuery查詢帶有class樣式的元素jQuery
- 在Oracle中進行大小寫不敏感的查詢Oracle
- mysql 三表關聯查詢MySql