Mysql表空间碎片的处理

[本文(Mysql表空间碎片的处理)原始地址]http://xcoder.me/2017-09/tuning/Mysql表空间碎片的处理/

MySQL具有相当多不同种类的存储引擎来实现列表中的数据存储功能。
每当MySQL从你的列表中删除了一行内容,该段空间就会被留空。
而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大。

产生原因

表空间碎片产生的常见原因:

  • 记录被Delete,且原空间无法复用;
    一般空间碎片大的都是表中记录被大面积删除。

  • 记录被Update(通常出现在变长字段中),原空间无法复用;
    一般是对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改

  • 记录插入导致页分裂,页的填充率降低;

影响

如果表空间比较大,且表空间碎片较大,可能带来的负面影响:

  • 浪费磁盘空间;

  • 可能导致查询扫描的IO成本提升,效率降低;

如果表空间较小或者碎片率较小,用户无需关注,也不建议执行回收空间碎片操作。

回收表空间碎片的方法

  • 在多数的设置中,您根本不需要运行OPTIMIZE TABLE。
    即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每月一次即可,只对特定的表运行。
  • OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。
  • 注意,在OPTIMIZE TABLE运行过程中,MySQL会锁定表

  • 也可以考虑alter table tableName Engine='InnoDB';这种方式来整理

  • 回收空间虽然对大表查询有一定程序的优化,能够降低IO成本,提高效率,但是这个动作耗性能且锁表,请在业务低峰期做。

总结

之所以会产生这种问题,很多场景都是这样的:比如你有一个日志表,随着时间的积累,数据量很快上千万条了,但是这些数据也有冷热之分,所以完全可以按照时间或者其他纬度来拆分表以达到数据量减小以提高查询效率的目的。

比如每月拆一回,这样的操作会导致原来的空间大小还在一定程序上保留,导致碎片率高,可能一个普通的查询就会全表扫描。

我们可以通过回收表空间碎片的方法来处理,其实也可以逆向来处理,比如把冷数据留下(即从原表把热数据导入到新表,然后把原表重命名再删除热数据,把新表重命名为原表),新增加的表用来存放热数据,这样对热数据的访问就没有碎片率的问题,还不会影响业务的正常使用。

小英雄雨来 wechat
扫码二维码或搜索"架构演进之旅"订阅微信公众号
enjoy?donate!