mysql 表空间收缩_mysql表碎片清理和表空间收缩

mysql表碎片清理和表空间收缩(即清理碎片后report_site_day.ibd文件磁盘空间减小,该方案基于独立表空间存储方式)

OPTIMIZETABLE [tablename],当然这种方式只适用于独立表空间

清除碎片的优点:

降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率

OPTIMIZE TABLE ipvacloud.report_site_day;对myisam表有用  对innodb也有用，系统会自动把它转成 ALTER TABLE  report_site_dayENGINE = Innodb; 这是因为optimize table的本质，就是alter table

所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间

mysql innodb引擎 长时间使用后，数据文件远大于实际数据量(即report_site_day.ibd文件越来越大)，导致空间不足。

就是我的mysql服务器使用了很久之后，发现\data\ipvacloud\report_site_day.ibd  目录的空间占满了我系统的整个空间，马上就要满了。

MySQL5.5默认是共享表空间 ，5.6中默认是独立表空间(表空间管理类型就这2种)

独立表空间 就是采用和MyISAM 相同的方式, 每个表拥有一个独立的数据文件( .idb )

1.每个表都有自已独立的表空间。

2.每个表的数据和索引都会存在自已的表空间中。

3.可以实现单表在不同的数据库中移动(将一个库的表移动到另一个库里,可以正常使用)。

4．drop table自动回收表空间 ，删除大量数据后可以通过alter table XX engine = innodb;回收空间

InnoDB引擎 frm ibd文件说明：

1.frm ：描述表结构文件，字段长度等

2.ibd文件

a如果采用独立表存储模式(5.6)，data\a中还会产生report_site_day.ibd文件(存储数据信息和索引信息)

D:\java\mysql5.6\data\ipvacloudreport_site_day.frm 和

D:\java\mysql5.6\data\ipvacloud\report_site_day.ibd

b如果采用共享存储模式(5.5)，数据信息和索引信息都存储在ibdata1中

(其D:\java\mysql5.6\data\目录下没有.ibd文件,只有frm文件)

D:\java\mysql5.5\data\ipvacloudreport_site_day.frm

查看当前数据库的表空间管理类型(on表示独立表空间开启,5.6默认开启独立)

脚本：show variables like "innodb_file_per_table";

mysql> show variables like "innodb_file_per_table";

一，小结

结合mysql官方网站的信息，个人是这样理解的。当你删除数据时，mysql并不会回收，被已删除数据的占据的存储空间，以及索引位。而是空在那里，而是等待新的数据来弥补这个空缺，这样就有一个缺少，如果一时半会，没有数据来填补这个空缺，那这样就太浪费资源了。所以对于写比较频烦的表，要定期进行optimize，一个月一次，看实际情况而定了。

举个例子来说吧。有100个php程序员辞职了，但是呢只是人走了，php的职位还在那里，这些职位不会撤销，要等新的php程序来填补这些空位。招一个好的程序员，比较难。我想大部分时间会空在那里。哈哈。

当我们使用mysql进行delete数据，delete完以后，发现空间文件ibd并没有减少，这是因为碎片空间的存在，举个例子，一共公司有10号员工，10个座位，被开除了7个员工，但这些座位还是保留的，碎片整理就像，让剩下的3个员工都靠边坐，然后把剩下的7个作为给砸掉，这样就能释放出空间了

好处除了减少表数据与表索引的物理空间，还能降低访问表时的IO，这个比较理解，整理之前，取数据需要跨越很多碎片空间，这时需要时间的，整理后，想要的数据都放在一起了，直接拿就拿到了，效率提高

二，手册中关于OPTIMIZE的一些用法(标红的为应用场景)和描述

OPTIMIZE  TABLE tbl_name [, tbl_name] ...

如果您已经删除了表的一大部分，或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改，则应使用OPTIMIZE TABLE。被删除的记录被保持在链接清单中，后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。

碎片产生的原因

(删除时留白, 插入时尝试使用留白空间 (当删除后并未将所有留空的都插入数据,既可以认为未被立即使用的留空就是碎片)

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

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

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

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

备注：

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。

OPTIMIZE 操作会暂时锁住表,而且数据量越大,耗费的时间也越长,它毕竟不是简单查询操作.

比较好的方式就是做个shell,定期检查mysql中 information_schema.TABLES字段,查看 DATA_FREE 字段,大于0话,就表示有碎片

问题产生: 例如你有1个表格里面有约10000000条，大概10G的数据，但是你手动删除了5000000条数据，即约5G的数据，但是删除后，你会发现系统的空间还是占用了10G，

解决方案: 表空间收缩即D:\java\mysql5.6\data\ipvacloud\report_site_day.ibd文件变小

create database frag_test;

use frag_test;

create table frag_test (c1 varchar(64));

insert into frag_test values ('this is row 1');

insert into frag_test values ('this is row 2');

insert into frag_test values ('this is row 3');

insert into frag_test values ('this is row 4');

insert into frag_test values ('this is row 5');

SELECT * FROM frag_test;

-- 碎片查看(即查看frag_test库下所有表的状态,1条记录是1个表)  frag_test是库名

-- 需要注意的是，“data_free”一栏显示出了我们删除一行后所产生的留空空间  删除前 Data_free: 0字节 删除一条记录后再查看碎片  Data_free: 20字节

-- 如果没有及时插入,那么删除一条记录后,留空的20字节就变成碎片; 现在如果你将两万条记录删到只剩一行，

-- 列表中有用的内容将只占二十字节，但MySQL在读取中会仍然将其视同于一个容量为四十万字节的列表进行处理，并且除二十字节以外，其它空间都被白白浪费了。

-- 现在我们删除一行，并再次检测:

delete from frag_test where c1 = 'this is row 2';

-- 删除一条记录后再查看碎片  Data_free: 20字节 即留空了20字节  data_free 是碎片空间

show table status from frag_test;

字段解释：Data_length : 数据的大小。

Index_length: 索引的大小。

Data_free :数据在使用中的留存空间，如果经常删改数据表，会造成大量的Data_free  频繁 删除记录 或修改有可变长度字段的表

--

data_free碎片空间  TABLE_SCHEMA后等于表名

(data_length+index_length)数据和数据索引的之和的空间

data_free/data_length+index_length>0.30 的表认为是需要清理碎片的表

select

table_schema

db,table_name,engine,table_rows,data_free,data_length+index_length

length from information_schema.tables where TABLE_SCHEMA='frag_test';

-- table_schema db, table_name, data_free, engine依次表示 数据库名称 表名称 碎片所占字节空间  表引擎名称

-- 列出所有已经产生碎片的表 ('information_schema', 'mysql'这两个库是mysql自带的库)

select table_schema db, table_name, data_free, engine,table_rows,data_length+index_length length

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

-- 库名.表名   清理2个表的碎片(逗号隔开即可) OPTIMIZE TABLE ipvacloud.article,ipvacloud.aspnet_users_viewway;

-- 存储过程里的table_schema就是数据库名称 虽然提示 Table does not support optimize, doing recreate + analyze instead  该命令执行完毕后   返回命令,虽然提示不支持optimize，但是已经进行重建和分析，空间已经回收(即碎片得到整理,表空间得到回收)。  原来对于InnoDB 通过该命令还是有用的，OPTIMIZE TABLE ipvacloud.article;

OPTIMIZE TABLE ipvacloud.article;

-- 清除碎片操作会暂时锁表，数据量越大，耗费的时间越长 可以做个脚本，例如每月凌晨3点，检查DATA_FREE字段，

-- 大于自己认为的警戒值(碎片空间占数据和数据索引空间之和的百分比>0.30)的话，就清理一次

/*

清理mysql下实例下表碎片(当碎片字节空间占 数据字节与索引字节空间 之和大于0.30时, 这些表的碎片都需要清理,使用游标遍历清理) 定时任务事件 每月凌晨4点调用此清理表碎片的任务

table_schema是数据库名 OPTIMIZE TABLE ipvacloud.article;

*/

DROP PROCEDURE IF EXISTS `optimize_table`;

DELIMITER ;;

CREATE  PROCEDURE `optimize_table`()

BEGIN

DECLARE tableSchema VARCHAR(100);

DECLARE tableName VARCHAR(100);

DECLARE stopFlag INT DEFAULT 0;

-- 大于30%碎片率的清理

DECLARE

rs CURSOR FOR SELECT table_schema,table_name FROM

information_schema.tables WHERE

((data_free/1024)/((data_length+index_length+data_free)/1024)) >

0.30;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopFlag = 1;

OPEN rs;

WHILE stopFlag <> 1 DO

FETCH NEXT FROM rs INTO tableSchema,tableName;

IF stopFlag<>1 THEN

-- SET @table_optimize = CONCAT('ALTER TABLE `',tableName,'` ENGINE = INNODB');

SET @table_optimize = CONCAT('OPTIMIZE TABLE `',table_schema,'`.`',tableName,'`');

PREPARE sql_optimize FROM @table_optimize;

EXECUTE sql_optimize;

END IF;

END WHILE;

CLOSE rs;

END

;;

DELIMITER ;

/*

此定时任务 事件每月凌晨4点清理mysql实例下的表碎片

*/

DROP EVENT IF EXISTS `event_optimize_table`;

DELIMITER ;;

CREATE

EVENT `event_optimize_table` ON SCHEDULE EVERY 1 MONTH STARTS

'2017-12-15 04:00:00' ON COMPLETION PRESERVE ENABLE DO CALL

optimize_table()

;;

DELIMITER ;

如何缩小共享表空间  optimize table xxx; 对共享表空间不起作用

如果不把数据分开存放的话，这个文件的大小很容易就上了G，甚至几十G。对于某些应用来说，并不是太合适。因此要把此文件缩小。

无法自动收缩，必须数据导出，删除ibdata1，然后数据导入(数据库变为独享表空间)

解决方法：数据文件单独存放(共享表空间如何改为每个表独立的表空间文件)。

本地mysql5.5创建一个ipvacloud库,将其他服务的1张表数据传输到本地的ipvacloud

ibdata1(ibdata1存放数据和索引等)文件从18M增加到178M   其ipvacloud文件下只新增了frm文件 即D:\java\mysql5.5\data\ipvacloud\report_site_day.frm

导出数据(navicat导出或mysqldump)

关闭MySQL服务：

删除ibdat1、ib_log*和应用数据库目录

更改myini文件(在最后一行添加innodb_file_per_table=1)

启动mysql   使用此命令看(ibdata1又回到初始的18M,ipvacoud已是空的) 独立表空间是否开启成功(on表示开启成功)show variables like '%per_table%';

导入数据库 .sql文件(导入数据成功后ibdat1从18M增加到34M, 独立表空间有ibd文件,来存放数据和索引信息)

将表由共享表空间迁移到了独立表空间中，同时也是对共享表空间"瘦身"

>mysqldump -h192.168.2.227 -u root -p ipvacloud site_all_info > d:456.sql