• 周五. 10月 7th, 2022

5G编程聚合网

5G时代下一个聚合的编程学习网

热门标签

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

admin

11月 28, 2021

一、表碎片清理

存储结构分析

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),dataa中还会产生report_site_day.ibd文件(存储数据信息和索引信息)

         D:javamysql5.6dataipvacloudreport_site_day.frm 和

         D:javamysql5.6dataipvacloudeport_site_day.ibd

         b如果采用共享存储模式(5.5),数据信息和索引信息都存储在ibdata1中
          (其D:javamysql5.6data目录下没有.ibd文件,只有frm文件)

          D:javamysql5.5dataipvacloudreport_site_day.frm

碎片产生的原因 

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

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

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

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

清理碎片

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

mysql表清理碎片后table_name.ibd文件磁盘空间减小,降低访问表时的IO,提高mysql性能,释放表空间降低磁盘空间使用率。

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

对myisam表有用  对innodb也有用,系统会自动把它转成 ALTER TABLE   [tablename] ENGINE = Innodb; 这是因为optimize table的本质,就是alter table,所以不管myisam引擎还是innodb引擎都可以使用OPTIMIZE TABLE回收表空间。

备注:
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话,就表示有碎片

--列出所有已经产生碎片的表 ('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;

--产生碎片比例大的表
SELECT table_schema,table_name,table_rows,data_free FROM information_schema.tables
WHERE ((data_free/1024)/((data_length+index_length+data_free)/1024)) > 0.20
and table_schema not in ('information_schema', 'mysql')
order by table_schema,table_rows desc

--清理多个个表的碎片(逗号隔开即可)

OPTIMIZE TABLE ipvacloud.article,ipvacloud.aspnet_users_viewway;  

字段解释:

  1. Data_length : 数据的大小。

  2. Index_length: 索引的大小。

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

  – data_free碎片空间  TABLE_SCHEMA后等于表名   (data_length+index_length)数据和数据索引的之和的空间  data_free/data_length+index_length>0 的表认为是需要清理碎片的表

简单的碎片清理脚本
#!/bin/bash
date=`date +"%Y-%m-%d %H:%M:%S"`
echo $date >>/root/qingli.log
tables=$(/usr/local/mysql/bin/mysql -u root -p"tina" 2>/dev/null -e "select concat(table_schema,'.',table_name) from information_schema.tables where data_free>0 and engine !='MEMORY';" |grep -v "concat" |grep -v "tinatest" |grep -v "information_schema" |grep -v "mysql")
for table in $tables
do
  /usr/local/mysql/bin/mysql -u root-p"tina" 2>/dev/null -e "optimize no_write_to_binlog table $table;" >>/root/qingli.log
done
添加no_write_to_binlog,是为了保证只在这个主库上执行,这个命令不用传到从库,以免从库执行过久,导致同步延迟  

参考:

https://blog.51cto.com/xiaocao13140/2127856

https://www.cnblogs.com/pc-boke/articles/10247068.html

发表回复

您的电子邮箱地址不会被公开。