前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >mysql表占用多少磁盘空间以及清理表空间

mysql表占用多少磁盘空间以及清理表空间

原创
作者头像
用户4584874
修改2020-08-04 09:57:00
8.6K0
修改2020-08-04 09:57:00
举报
文章被收录于专栏:小七技术路小七技术路

前因

早上上班,发现监控数据中,好几张表的所占数据空间突增,有的突增甚至达到了8G,仔细检测数据库之后,没有发现数据异常,那么问题出在哪里?

问题排查

通过对代码review,数据统计逻辑分析,mysql占用空间sql的排查,发现问题出在统计sql上。下面是我使用的统计sql

代码语言:txt
复制
select data_length, table_name from tables where table_schema='database';

在统计表占用的空间的时候,如果使用上述语句,会导致表空间突增,那么为什么会突增呢?

如何获取表大小

表的大小,是存储在INFORMATION_SCHEMA.TABLES中吗?mysql的官方文档说:

  • 对于MyISAMDATA_LENGTH是数据文件的长度,以字节为单位。
  • 对于InnoDBDATA_LENGTH是为聚簇索引分配的 memory 的近似值,以字节为单位。具体来说,它是聚集索引大小(以页为单位)乘以InnoDB页大小。

这就意味着,使用InnoDB时,我们所获取的表空间大小,是不准确的。通过使用sysbench对mysql进行基准测试,批量数据插入数据库,可以发现,从 INFORMATION_SCHEMA.TABLES 获取的 data_lengthindex_length 所定义的表大小并不是实时更新的,如下图:

此图表明,mysql没有实时维护data_length以及index_length的大小,而是不规则的刷新它们对应的值。问题的原因找到了,tables表虽然可以获取到表空间的大小,但是该数据不是实时的。

如何实时获取表空间大小

如果想要通过information_schema来查看表空间的实际大小,需要做两件事:

  • 禁用 innodb_stats_persistent
  • 开启innodb_stats_on_metadata

禁用持久性统计信息意味着每次服务器启动时 InnoDB 都必须刷新统计信息,这可能会在重新启动之间产生不稳定的查询计划。

如果需要实时获取表空间大小,可以使用INNODB_SYS_TABLESPACES来获取。示例如下:

代码语言:txt
复制
  select \* from INFORMATION\_SCHEMA.INNODB\_SYS\_TABLESPACES where name='test/mytest' \G

  **\*\*\*** 1. row **\*\*\***
   SPACE: 24
    NAME: test/mytest
    FLAG: 33
 FILE\_FORMAT: Barracuda
 ROW\_FORMAT: Dynamic
 PAGE\_SIZE: 16384
 ZIP\_PAGE\_SIZE: 0
 SPACE\_TYPE: Single
 FS\_BLOCK\_SIZE: 4096
 FILE\_SIZE: 98304
 ALLOCATED\_SIZE: 98304
  1 row in set (0.02 sec)

该表可以实时查看表空间的大小,file_size(磁盘上的逻辑文件大小)、allocated_size(为此文件分配的空间)。

如何通过清理表数据,降低表空间

清理表数据的时候,发现我的表空间并没有跟着降低,这是为什么?下面是我清理表数据的sql:

代码语言:txt
复制
  delete from table\_name where create\_time < '20200101';

上面这句sql是非常常见的,因为数据库中的数据过期,需要进行清理。但是在清理完数据之后,我们的表占用空间没有得到降低,这是为什么?

当我们使用innodb的时候,已删除的数据只是被标记为已删除,并不是真正的释放空间,这就导致了为什么我们在删除表中的数据,但是表占用的空间确实不断增长的。那么我们该如何清理表空间?

如果使用的是innodb那么应该先查看innodb_file_per_table(是否独享表空间)。

代码语言:txt
复制
  show variables like 'innodb\_file\_per\_table';

  +-----------------------+-------+

  | Variable\_name         | Value |

  +-----------------------+-------+

  | innodb\_file\_per\_table | ON    |

  +-----------------------+-------+

  1 row in set (0.03 sec)

ON代表共享表空间打开,OFF代表开启共享表空间没有打开,即采用的是默认的共享表空间。那么我们先看下我们的数据库存放咋物理磁盘位置。

代码语言:txt
复制
  show global variables like "%datadir%";

  +---------------+-----------------+

  | Variable\_name | Value           |

  +---------------+-----------------+

  | datadir       | /var/lib/mysql/ |

  +---------------+-----------------+

  1 row in set (0.00 sec)

因为是共享表空间,所以这里是一个非常大的文件ibdata1,这个文件中存放了所有innodb表的数据以及索引。执行以下命令:

代码语言:txt
复制
optimize table table\_name;

+-----------------+----------+----------+-------------------------------------------------------------------+

| Table           | Op       | Msg\_type | Msg\_text                                                          |

+-----------------+----------+----------+-------------------------------------------------------------------+

| test.table\_name | optimize | note     | Table does not support optimize, doing recreate + analyze instead |

| test.table\_name | optimize | status   | OK                                                                |

+-----------------+----------+----------+-------------------------------------------------------------------+

2 rows in set (0.21 sec)

表示该表不支持optimize应该使用recreate以及analyze进行代替。

代码语言:txt
复制
alter table table\_name  ENGINE = 'InnoDB';

analyze table table\_name;

+-----------------+---------+----------+----------+

| Table           | Op      | Msg\_type | Msg\_text |

+-----------------+---------+----------+----------+

| test.table\_name | analyze | status   | OK       |

+-----------------+---------+----------+----------+

1 row in set (0.01 sec)
  • 独享表空间,每张表都有ibdfile,这时候如果删除大量的行,索引会重组,并且释放相应的空间,不需要进行优化。
  • 共享表空间,所有的数据和索引都会放在ibddata1中,随着数据的增加会导致该文件越来越大,超过10g之后,查询速度会变的非常慢,因此最好开启独享表空间。或者定期清理表空间。
    • 方案1:先逻辑备份数据库,将配置文件中innodb_file_per_table参数=1,再将备份导入
    • 方案2:只要修改innodb_file_per_table参数,然后将需要修改的所有innodb的表都运行一遍 alter table table_name engine=innodb;即可使用第二种方式修改后,原来库中的表中的数据会继续存放于ibdata1中,新建的表才会使用独立表空间
总结

上述方法,实在工作遇到的一点知识点的总结。如果雷同,非常正常。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题排查
  • 如何获取表大小
  • 如何实时获取表空间大小
  • 如何通过清理表数据,降低表空间
  • 总结
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档