当前位置: 首页 > 技术随笔 > MySQL数据表碎片整理

MySQL数据表碎片整理

在MySQL中,我们经常会使用VARCHARTEXTBLOB等可变长度的文本数据类型。不过,当我们使用这些数据类型之后,我们就不得不做一些额外的工作——MySQL数据表碎片整理。

那么,为什么在使用这些数据类型之后,我们就要对MySQL定期进行碎片整理呢?

现在,我们先来看一个具体的例子。在这里,我们使用如下SQL语句在MySQL自带的TEST数据库中创建名为DEMO的数据表并插入5条测试数据。

--创建DEMO表
CREATE TABLE DEMO(
id int unsigned,
body text
) engine=myisam charset=utf8;

--插入5条测试数据
INSERT INTO DEMO VALUES(1, 'AAAAA');
INSERT INTO DEMO VALUES(2, 'BBBBB');
INSERT INTO DEMO VALUES(3, 'CCCCC');
INSERT INTO DEMO VALUES(4, 'DDDDD');
INSERT INTO DEMO VALUES(5, 'EEEEE');

然后我们以这5条测试数据为基础,使用如下INSERT INTO语句重复执行多次进行复制性插入。

INSERT INTO DEMO SELECT id, body FROM DEMO;

使用INSERT INTO语句多次插入产生总共约262万条数据使用INSERT INTO语句多次插入产生总共约262万条数据

众所周知,MySQL中MyISAM表的数据是以文件形式存储的,我们可以在MySQL存储数据的文件夹中找到数据库test目录下的demo.MYD文件。此时,我们可以看到demo.MYD文件的大小约为50MB。

demo.MYD文件约为50MBdemo.MYD文件约为50MB

此时,假如我们需要删除DEMO表中所有ID列小于3的数据(即1和2),于是我们执行如下SQL语句:

DELETE FROM DEMO WHERE id < 3

此时,我们可以看到DEMO表中的数据量只有原来的3/5:

删除后,只剩下157万条记录删除后,只剩下157万条记录

DEMO表中的现有数据量只有原来的3/5,按理说,这个时候demo.MYD文件的大小也应该只有原来的3/5左右。不过,我们再次查看demo.MYD文件时,却惊奇地发现该文件的大小一点都没有变!

删除数据后,demo.MYD的文件大小没有变化删除数据后,demo.MYD的文件大小没有变化

那么就究竟是怎么一回事呢?原来,在MySQL中,如果我们删除了表中的大量数据,或者我们对含有可变长度文本数据类型(VARCHARTEXTBLOB)的表进行了很多更改,不过被删除的数据记录仍然被保持在MySQL的链接清单中,因此数据存储文件的大小并不会随着数据的删除而减小。

当我们确定数据需要被清除掉时,那么这些数据就已经成了无用的数据,但是按照MySQL的处理方式,这些数据仍然会占用我们的磁盘空间,从而造成了极大的资源浪费。不仅如此,过大的数据文件还会导致MySQL执行相关数据操作时需要耗费更多的性能和时间。因此,对MySQL的某些数据表进行碎片整理是非常有必要的。

对MySQL进行碎片整理的方法非常简单,因为MySQL已经给我们提供了对应的SQL指令,这个SQL指令就是OPTIMIZE TABLE,其完整语法如下:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE table_name1 [, table_name2] ...

从上面的语法描述中,我们可以得知,OPTIMIZE TABLE可以一次性对多个表进行碎片整理,只需要在OPTIMIZE TABLE后面接多个表名,并以英文逗号隔开即可。

此外,OPTIMIZE TABLE语句有两个可选的关键字:LOCALNO_WRITE_TO_BINLOG。在默认情况下,OPTIMIZE TABLE语句将会被记录到二进制日志中,如果我们指定了LOCALNO_WRITE_TO_BINLOG关键字,则不会记录。当然,一般情况下,我们也无需关注这两个关键字。

现在,我们就使用OPTIMIZE TABLE语句对刚才的DEMO表进行碎片整理。

对demo表进行碎片整理对demo表进行碎片整理

然后,我们再来查看demo.MYD文件,此时我们就会发现demo.MYD文件的大小已经减小到约为原来的3/5了。

碎片整理后demo.MYD文件的大小碎片整理后demo.MYD文件的大小

备注
1.MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可。
2.OPTIMIZE TABLE只对MyISAM,BDB和InnoDB表起作用,尤其是MyISAM表的作用最为明显。此外,并不是所有表都需要进行碎片整理,一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。
3.在OPTIMIZE TABLE运行过程中,MySQL会锁定表。
4.默认情况下,直接对InnoDB引擎的数据表使用OPTIMIZE TABLE,可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候,我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL,以便于让其他引擎支持OPTIMIZE TABLE

28 2
我们认为: 用户的主要目的,是为了获取有用的信息,而不是来点击广告的。因此本站将竭力做好内容,并将广告和内容进行分离,确保所有广告不会影响到用户的正常阅读体验。用户仅凭个人意愿和兴趣爱好点击广告。
我们坚信:只有给用户带来价值,用户才会给我们以回报。
CodePlayer技术交流群1CodePlayer技术交流群1

帮朋友打一个硬广告:

P2P网贷系统(Java版本) 新年低价大促销,多年P2P技术积累,系统功能完善(可按需定制,可支持第三方存管、银行存管),架构稳定灵活、性能优异、二次开发快速简单。 另可提供二次开发、安装部署、售后维护、安全培训等一条龙服务。

外行看热闹,内行看门道。可以自信地认为,在系统设计上,比市面上的晓风、迪蒙、方维、绿麻雀、国融信、金和盛等P2P系统要好。
深圳地区支持自带技术人员现场考察源代码、了解主要技术架构,货比三家,再决定是否购买。

也可推荐他人购买,一旦完全成交,推荐人可获得实际售价 10% 的返现。
有意向者,详情请 点击这里 联系,工作时间立即回复。