如何檢視MySQL的表空間

正常方法

通常情況下，我們一般的思路是通過下面的方式擷取表空間：

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = '[NAME_OF_DPA_DATABASE]'
    AND (100*(data_free/(data_length + index_length+data_free))) > [MINIMUM_PERCENT_WASTED]
    AND (data_length + index_length + data_free) > [MINIMUM_FILESIZE_IN_BYTES]
ORDER BY (data_length + index_length + data_free) DESC;           

例如：

SELECT CONCAT( table_schema, '.', table_name ) table_name, 
    CONCAT( ROUND( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length, 
    CONCAT( ROUND( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
    CONCAT( ROUND( data_free / ( 1024 *1024 ) , 2 ) , 'M' ) free_length, 
    CONCAT( ROUND( ROUND( data_length + index_length + data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size, 
    CONCAT( ROUND( ROUND( data_free ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_wasted,
             (100*(data_free/(data_length + index_length + data_free))) percent_wasted
FROM information_schema.TABLES
WHERE table_schema = 'dpa_repo'
    AND (100*(data_free/(data_length + index_length+data_free))) > 75
    AND (data_length + index_length + data_free) > 52428800
ORDER BY (data_length + index_length + data_free) DESC;           

可能沒有注意到的是

對于上述方法中的data_length 或 index_length的值MySQL并不是實時更新的，而是周期性地維護，通過

測試發現當10%的行被改變

時，data_length 或 index_length與正在更新的統計資料一緻。

而table_rows, data_free 或 update_time卻是實時更新的。

那麼有沒有方法在我們查詢information_schema時擷取到data_length和 index_length的值呢？

在MySQL 5.7裡面如果想擷取information_schema精确值，就要disable innodb_stats_persistent 和 enable innodb_stats_on_metadata,這兩種方法都有明顯的副作用。

disable innodb_stats_persistent意味着每次在MySQL啟動時才會重新整理統計資訊，這中代價非常昂貴并且會在重新開機之間生成易失性查詢計劃。

enabling innodb_stats_on_metadata會使每次通路information_schema的時候非常非常慢。

有沒有更好的辦法

可以檢視information.INNODB_SYS_TABLESPACES檢視真實的檔案大小，不像index_length and 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)           

用這個表還有一個更好的事情是：它可以處理MySQL 5.7 新的“Innodb Page Compression”，這不同于file_size（它是在磁盤上的邏輯檔案的大小），也不同于allocated_size（它是檔案已經配置設定的空間，可以小很多）

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)           

結論

查INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES擷取INNODB表的真實檔案大小。

參考

https://www.percona.com/blog/2016/01/26/finding_mysql_table_size_on_disk/ https://support.solarwinds.com/Success_Center/Database_Performance_Analyzer_(DPA)/Knowledgebase_Articles/MySQL_repository_table_optimization_for_DPA https://dev.mysql.com/doc/refman/5.7/en/innodb-persistent-stats.html