mysql表碎片清理和表空间收缩(即清理碎片后report_site_day.ibd文件磁盘空间减小,该方案基于独立表空间存储方式)
OPTIMIZETABLE [tablename],当然这种方式只适用于独立表空间
清除碎片的优点:
降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率
OPTIMIZE TABLE ipvacloud.report_site_day;对myisam表有用 对innodb也有用,系统会自动把它转成 ALTER TABLE report_site_day ENGINE = 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
转载于:https://blog.51cto.com/xiaocao13140/2127856