如何在磁盘上查找 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 或更多)。

该图表与磁盘上数据的变化方式不匹配，它逐渐增长(如预期)：

 
 
 
 
      
  
  
  
  1. -rw-r----- 1 mysql mysql 220293234688 Jan 25 17:03 sbtest1.ibd 
    2.   
  
  
  
  2. -rw-r----- 1 mysql mysql 220310011904 Jan 25 17:03 sbtest1.ibd 
    3.   
  
  
  
  3. -rw-r----- 1 mysql mysql 222499438592 Jan 25 17:07 sbtest1.ibd 
    4.

正如我们从这个实验中看到的那样，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 实时更新，无需特殊配置：

 
 
 
 
      
  
  
  
  1. mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/sbtest1' \G 
    2.   
  
  
  
  2. *************************** 1. row *************************** 
    3.   
  
  
  
  3.  SPACE: 42 
    4.   
  
  
  
  4.  NAME: sbinnodb/sbtest1 
    5.   
  
  
  
  5.  FLAG: 33 
    6.   
  
  
  
  6.  FILE_FORMAT: Barracuda 
    7.   
  
  
  
  7.  ROW_FORMAT: Dynamic 
    8.   
  
  
  
  8.  PAGE_SIZE: 16384 
    9.   
  
  
  
  9. ZIP_PAGE_SIZE: 0 
    10.   
  
  
  
  10.  SPACE_TYPE: Single 
    11.   
  
  
  
  11. FS_BLOCK_SIZE: 4096 
    12.   
  
  
  
  12.  FILE_SIZE: 245937209344 
    13.   
  
  
  
  13. ALLOCATED_SIZE: 245937266688 
    14.   
  
  
  
  14. 1 row in set (0.00 sec) 
    15.

使用这个表的好处是，它还处理新功能 “InnoDB 页压缩”，正确显示了 file_size (磁盘上的逻辑文件大小)和 allocated_size(为此文件分配的空间，并且可以显着缩小)之间的区别。

最后，让我们看一下不同的 InnoDB 压缩方式如何影响 information_schema 中提供的信息。

 
 
 
 
      
  
  
  
  1. mysql> select * from INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES where name='sbinnodb/testcomp' G 
    2.   
  
  
  
  2. *************************** 1. row *************************** 
    3.   
  
  
  
  3.  SPACE: 48 
    4.   
  
  
  
  4.  NAME: sbinnodb/testcomp 
    5.   
  
  
  
  5.  FLAG: 33 
    6.   
  
  
  
  6.  FILE_FORMAT: Barracuda 
    7.   
  
  
  
  7.  ROW_FORMAT: Dynamic 
    8.   
  
  
  
  8.  PAGE_SIZE: 16384 
    9.   
  
  
  
  9. ZIP_PAGE_SIZE: 0 
    10.   
  
  
  
  10.  SPACE_TYPE: Single 
    11.   
  
  
  
  11. FS_BLOCK_SIZE: 4096 
    12.   
  
  
  
  12.  FILE_SIZE: 285212672 
    13.   
  
  
  
  13. ALLOCATED_SIZE: 113004544 
    14.   
  
  
  
  14. 1 row in set (0.00 sec) 
    15.

如果您使用旧的 InnoDB 压缩(InnoDB 表压缩)，您将看到 data_length 和 index_length 中显示的压缩数据大小作为结果。例如， avg_row_length 将远低于您的预期。

如果在 MySQL 5.7 中使用新的 InnoDB 压缩(InnoDB 页压缩)，您将看到与文件大小相对应的值，而不是如 information_schema 中所示的分配大小。

结论

回答一个微不足道的问题“这个表在磁盘上占用了多少空间?” 在 MySQL 中真的不是一个简单的问题 - 显而易见的数据，可能会得到错误的答案。

查看 INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES 以获取 InnoDB 表的实际文件大小值。

分享标题：如何在磁盘上查找 MySQL 表的大小
网页URL：http://www.shufengxianlan.com/qtweb/news28/355328.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

广告

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源： 创新互联

猜你还喜欢下面的内容

标签优化知识

同城分类信息

网站定制　　 　四川电信机房托管　　 　成都网站排名　　 　成都二枢电信服务器托管　　 　重庆建站　　 　温江网站建设　　 　成都网站制作　　 　门户网站建设方案　　 　江油网站建设　　 　等级保护测评　　 　成都名片印刷　　 　泽君科技　　 　免备案虚拟主机　　 　标志设计　　 　成都网站建设　　 　子晨包装　　 　软文营销　　 　眉山网站建设　　 　手机网站建设　　 　成都保温材料