如安在磁盘上查找 MySQL 表的巨细

我想知道 MySQL 表在磁盘上占用几多空间，但看起来很噜苏。不该该在 INFORMATION_SCHEMA.TABLES 中提供这些信息吗?没那么简朴!

这个看似简朴的题目现实上在 MySQL 中很是伟大。MySQL 支持很多存储引擎(个中一些基础不在磁盘上存储数据)， 差异的存储数据名目。譬喻，InnoDB 存储引擎为 MySQL 5.7 提供了三种“根基”名目，个中包括 row_formats 和两种可压缩的种类。

简化一下：我们如安在磁盘上查找存储在其本身的表空间中的 InnoDB 表的表巨细(条件是 innodb_file_per_table=1 )。

在我们获得谜底之前，先展示通过 sysbench 运行预先得到的图表(批量数据插入表)：

此图表现了从 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 表的现实文件巨细值。

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!