mysql的表空间怎么看_如何查看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表的真实文件大小。

参考