设为首页 收藏本站
查看: 653|回复: 0

[经验分享] mysql清除磁盘碎片

[复制链接]
累计签到:2 天
连续签到:1 天
发表于 2018-10-6 12:15:20 | 显示全部楼层 |阅读模式
任务背景
  接到金山云报警短信,说某数据库的容量已经达到了90%的水位线,于是登陆控制台查看详细情况。
  在控制台首先发现,每一天的磁盘容量的确有所波动,那么就证明开发人员写的“资源回收”模块是在正常运行的,如图:
DSC0000.jpg

  那么就说明没有什么数据是可以删的,既然删不掉多余的数据又不想多掏钱扩磁盘容量,只能从“磁盘碎片”下手了。而InnoDB引擎清理磁盘碎片的命令就是OPTIMIZE。

具体操作
  首先我先查询一下所有的“磁盘碎片情况”,使用语句如下:
  

    select CONCAT(TABLE_SCHEMA,'.',TABLE_NAME) as 数据表名,concat(truncate(sum(DATA_LENGTH+DATA_FREE+INDEX_LENGTH)/1024/1024,2),' MB') as total_size, concat(truncate(sum(DATA_LENGTH)/1024/1024,2),' MB') as data_size,concat(truncate(sum(DATA_FREE)/1024/1024,2),' MB') as data_free, concat(truncate(sum(INDEX_LENGTH)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_NAME order by data_length desc;   

  或者使用select table_schema, table_name, data_free, engine from information_schema.tables where table_schema not in ('information_schema', 'mysql') and data_free > 0;也可以,这个是查询data_free大于0的所有表。
  然后看到我这个叫history_device_flow_day的表里情况如下:
DSC0001.jpg

  表里的data_free就是磁盘碎片的量,比如我现在要干掉history_device_flow_day里所有的磁盘碎片,是975MB,于是先查询一下这个history_device_flow_day的存储引擎,使用语句如下:
  

    show table status from jsonlinef***ds where name='history_device_flow_day';  

  上面语句里的jsonlinef***ds是对应的数据库,看到的效果如下:
DSC0002.jpg

  存储引擎是InnoDB,那么就可以启动清除碎片的语句了:OPTIMIZE TABLE 数据表表名;,因为OPTIMIZE TABLE只对MyISAM、BDB和InnoDB表起作用。
  再执行了OPTIMIZE TABLE history_device_flow_day;之后,大约9分钟,就会看到“OK”的字样:
DSC0003.jpg

  估计有的朋友会问,那上面不是明明写了“Table does not support optimize, doing recreate + analyze instead”吗?这个其实无妨,实际上磁盘碎片已经被清除掉了。我们可以再用一次查询磁盘碎片的命令看一下,如图:
DSC0004.jpg

  的确释放了900多M。
  或者使用ALTER TABLE 表名 ENGINE = Innodb;(只是InnoDB的表可以这么做)来达到清理磁盘碎片的目的,这个命令表面上看什么也不做,实际上是重新整理碎片了。当执行优化操作时,实际执行的是一个空的ALTER命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间。

补充
  为什么会产生磁盘碎片?那是因为某一个表如果经常插入数据和删除数据,必然会产生很多未使用的空白空间,这些空白空间就是不连续的碎片,这样久而久之,这个表就会占用很大空间,但实际上表里面的记录数却很少,这样不但会浪费空间,并且查询速度也更慢。
  注意!OPTIMIZE操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作。所以把OPTIMIZE命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响。比较好的方式就是做个shell,定期检查mysql中 information_schema.TABLES字段,查看DATA_FREE字段,大于0的话,就表示有碎片,然后启动脚本。

参考资料
  http://pengbotao.cn/mysql-suipian-youhua.html
  http://irfen.me/mysql-data-fragmentation-appear-and-optimization/
  最后的最后,如果您觉得本文对您升职加薪有帮助,那么请不吝赞助之手,刷一下下面的二维码,赞助本人继续写更多的博文!
DSC0005.jpg




运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-613786-1-1.html 上篇帖子: jdbc链接mysql 下篇帖子: MySQL --2数据操作
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表