MySQL 清除表空间碎片

碎片产生的原因

(1)表的存储会出现碎片化,每当删除了一行内容,该段空间就会变为空白、被留空,而在一段时间内的大量删除操作,会使这种留空的空间变得比存储列表内容所使用的空间更大;

(2)当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;

(3)当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;

例如:

一个表有1万行,每行10字节,会占用10万字节存储空间,执行删除操作,只留一行,实际内容只剩下10字节,但MySQL在读取时,仍看做是10万字节的表进行处理,所以,碎片越多,就会越来越影响查询性能。

查看表碎片大小

(1)查看某个表的碎片大小

mysql> SHOW TABLE STATUS LIKE '表名';

结果中’Data_free’列的值就是碎片大小

(2)列出所有已经产生碎片的表

mysql> select table_schema db, table_name, data_free, engine     
from information_schema.tables 
where table_schema not in ('information_schema', 'mysql')  and data_free > 0;

清除表碎片

(1)MyISAM表

mysql> optimize table 表名

(2)InnoDB表

mysql> alter table 表名 engine=InnoDB

Engine不同,OPTIMIZE 的操作也不一样的,MyISAM 因为索引和数据是分开的,所以 OPTIMIZE 可以整理数据文件,并重排索引.

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.所以把 Optimize 命令放在程序中是不妥当的,不管设置的命中率多低,当访问量增大的时候,整体命中率也会上升,这样肯定会对程序的运行效率造成很大影响.比较好的方式就是做个shell,定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片

建议

清除碎片操作会暂时锁表,数据量越大,耗费的时间越长,可以做个脚本,定期在访问低谷时间执行,例如每周三凌晨,检查DATA_FREE字段,大于自己认为的警戒值的话,就清理一次。

原文发布于微信公众号 - Java后端技术(JavaITWork)

原文发表时间:2016-11-06

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Java帮帮-微信公众号-技术文章全总结

Oracle应用实战三——表+序列

创建表空间 表空间? ORACLE数据库的逻辑单元。 数据库---表空间 一个表空间可以与多个数据文件(物理结构)关联 一个数据库下可以建立多个表空间...

32040
来自专栏Java后端技术栈

在一个千万级的数据库查寻中,如何提高查询效率?

A. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

13330
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之事务(r5笔记第4天)

MySQL中的存储引擎很是丰富,常用的有InnoDB,MyISAM等,也查看了不少的资料,基本也有所了解,从一些参考书中看MySQL中的sql部分也是一扫而过,...

48480
来自专栏Java进阶干货

MySql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

12110
来自专栏沈唁志

谈谈在SQL语句中的优化技巧

19640
来自专栏用户2442861的专栏

聚簇索引和非聚簇索引区别的应用

http://www.cnblogs.com/wuxiaoqian726/articles/2016095.html

1.1K30
来自专栏程序猿

从0学习MySQL系列(三)概念篇

概要 ---- 在篇文章中提过:概念:数据库管理系统(Database Management System)一些语法的汇总点。 ...

32250
来自专栏java达人

百万级数据查询优化(数据库)

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 n...

28290
来自专栏精讲JAVA

百万数据查询优化技巧三十则

对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。 应尽量避免在 where 子句中对字段进行 null ...

21590
来自专栏jouypub

MySQL的语句执行顺序

MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是FROM操作,最后执行的是LIMIT操作。其中每一个操作都会产生一张虚拟的表,这个虚拟的表作为...

5610

扫码关注云+社区

领取腾讯云代金券