本篇文章给大家带来了关于mysql的相关知识,其中主要整理了碎片整理方案的相关问题,也即解决delete大量数据后空间不释放的问题,下面一起来看一下,希望对大家有帮助。
推荐学习:mysql视频教程
MySQL 中 insert 与 update 都可能导致页分裂,这样就存在碎片。
对于大量的UPDATE,也会产生文件碎片化 , Innodb的最小物理存储分配单位是页(page),而UPDATE也可能导致页分裂(page split),频繁的页分裂,页会变得稀疏,并且被不规则的填充,所以最终数据会有碎片。
delete 语句实际上只是给数据打个标记,并且记录到一个链表中,这样就形成了留白空间。
在InnoDB中,删除一些行,这些行只是被标记为“已删除”,而不是真的从索引中物理删除了,因而空间也没有真的被释放回收。InnoDB的Purge线程会异步的来清理这些没用的索引键和行。
当执行插入操作时,MySQL会尝试使用空白空间,但如果某个空白空间一直没有被大小合适的数据占用,仍然无法将其彻底占用,就形成了碎片;
总结:
表的增删改操作,可能会造成数据空洞的,当对表进行大量的增删改操作后,数据空洞存在的可能性比较大。
MySQL删除数据几种情况以及是否释放磁盘空间:
当MySQL对数据进行扫描时,它扫描的对象实际是列表的容量需求上限,也就是数据被写入的区域中处于峰值位置的部分;
MySQL数据库中的表在进行了多次delete、update和insert后,表空间会出现碎片。定期进行表空间整理,消除碎片可以提高访问表空间的性能。
这种碎片不仅额外增加了存储代价,同时也因为数据碎片化降低了表的扫描效率。
碎片若不整理,那么可能会长期占据磁盘空间,导致磁盘使用率越来越高。
修复问题的前提是要先找到问题,这样才能对症下药。
查看数据库中每个存在碎片的表
mysql> select concat('optimize table ',table_schema,'.',table_name,';'),data_free,engine from information_schema.tables where data_free>0 and engine !='MEMORY'; +-----------------------------------------------------------+-----------+--------+ | concat('optimize table ',table_schema,'.',table_name,';') | DATA_FREE | ENGINE | +-----------------------------------------------------------+-----------+--------+ | optimize table abc.t_user_answer; | 2097152 | InnoDB | | optimize table mysql.time_zone_transition; | 4194304 | InnoDB | | optimize table mysql.time_zone_transition_type; | 4194304 | InnoDB | | optimize table mysql.user; | 4194304 | InnoDB | 。。。。
查看指定表的碎片情况
mysql> show table status like 't_user'\G *************************** 1. row *************************** Name: t_user Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 4333 Avg_row_length: 589 Data_length: 2555904 Max_data_length: 0 Index_length: 2719744 Data_free: 4194304 Auto_increment: NULL Create_time: 2021-11-19 10:13:31 Update_time: 2022-04-20 14:28:42 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: Comment: 1 row in set (0.00 sec)
Data_free: 4194304 就代表碎片的byte数。如果经常删改数据表,会造成大量的Data_free 频繁 删除记录 或修改有可变长度字段的表。
找到碎片化最严重的表
SELECT table_schema, TABLE_NAME, concat(data_free/1024/1024, 'M') as data_free FROM `information_schema`.tables WHERE data_free > 3 * 1024 * 1024 AND ENGINE = 'innodb' ORDER BY data_free DESC
官方文档参考
这其实是一个NULL操作,表面上看什么也不做,实际上重新整理碎片了.当执行优化操作时,实际执行的是一个空的 ALTER 命令,但是这个命令也会起到优化的作用,它会重建整个表,删掉未使用的空白空间.
Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 15.11.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.
MySQL5.6 开始采用 Inplace 方式重建表,Alter 期间,支持 DML 查询和更新操作,语句为 alter table t engine=innodb, ALGORITHM=inplace;之所以支持 DML 更新操作,是因为数据拷贝期间会将 DML 更新操作记录到 Row log 中。 重建过程中最耗时的就是拷贝数据的过程,这个过程中支持 DML 查询和更新操作,对于整个 DDL 来说,锁时间很短,就可以近似认为是 Online DDL。 执行过程: 1、获取 MDL(Meta Data Lock)写锁,innodb 内部创建与原表结构相同的临时文件 2、拷贝数据之前,MDL 写锁退化成 MDL 读锁,支持 DML 更新操作 3、根据主键递增顺序,将一行一行的数据读出并写入到临时文件,直至全部写入完成。并且,会将拷贝期间的 DML 更新操作记录到 Row log 中 4、上锁,再将 Row log 中的数据应用到临时文件 5、互换原表和临时表表名 6、删除临时表
OPTIMIZE TABLE语句可以重新组织表、索引的物理存储,减少存储空间,提高访问的I/O效率。类似于碎片整理功能。
MySQL可以通过optimize table
语句释放表空间,重组表数据和索引的物理页,减少表所占空间和优化读写性能
使用语法
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_n说ame [, tbl_name] …
注意:
需要有足够的空间才能进行OPTIMIZE TABLE。 (剩余空间必须 > 被 OPTIMIZE 的表的大小)
OPTIMIZE 只对独立表空间(innodb_file_per_table=1)才有用,对共享表空间不起作用。
对于共享表空间,如果需要瘦身: 必须将数据导出,删除ibdata1,然后将 innodb_file_per_table 设置为独立表空间, 然后将数据导入进来。
对于InnoDB的表,OPTIMIZE TABLE 的工作原理如下
对于InnoDB表, OPTIMIZE TABLE映射到ALTER TABLE … FORCE(或者这样翻译:在InnoDB表中等价 ALTER TABLE … FORCE),它重建表以更新索引统计信息并释放聚簇索引中未使用的空间。
当您在InnoDB表上运行时,它会显示在OPTIMIZE TABLE的输出中,如下所示: mysql> OPTIMIZE TABLE foo; +----------+----------+----------+---------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------+----------+----------+---------------------------------------+ | test.foo | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.foo | optimize | status | OK | +----------+----------+----------+---------------------------------------+ # 但这个提示语可以忽略,从严格的意义讲,说InnoDB不支持optimize table,其实不太准确。 因为 MYSQL的文档说明了,当INNODB 的表,MYSQL会以 ALTER TABLE force + analyze 去执行这个命令(相当于做了recreate和analyze)。 所以最终还是会看到 OK 的状态。 # https://stackoverflow.com/questions/30635603/what-does-table-does-not-support-optimize-doing-recreate-analyze-instead-me
对于MYISAM表,OPTIMIZE TABLE 的工作原理:
1. 如果表已删除或分隔行,就修复该表。
2. 如果索引页没有排序,就排序它们。
3. 如果表的统计信息不是最新的(而且修复不能通过对索引进行排序),就更新它们。
**执行时也可以发现报错: Temporary file write failure. **
建议参考这片文章:
Mysql optimize table 时报错 Temporary file write failure. 的解决方案
optimize 语句的官网介绍
如果您已经删除了表的一大部分,或者如果您已经对含有可变长度行的表(含有VARCHAR, BLOB或TEXT列的表)进行了很多更改,则应使用 OPTIMIZE TABLE。
被删除的记录被保持在链接清单中,后续的INSERT操作会重新使用旧的记录位置。您可以使用OPTIMIZE TABLE来重新利用未使用的空间,并整理数据文件的碎片。
在多数的设置中,您根本不需要运行OPTIMIZE TABLE。即使您对可变长度的行进行了大量的更新,您也不需要经常运行,每周一次或每月一次 即可,只对特定的表运行。
Mysql 5.6 之前 在OPTIMIZE TABLE运行过程中,MySQL会锁定表,5.6之后有了 Online DDL 则大大减少了锁表时间。
alter table tb_test engine = innodb;
analyze table tb_test ;
optimize table tb_test ;
MySQL官方建议不要经常(每小时或每天)进行碎片整理,一般根据实际情况,只需要每周或者每月整理一次即可(我们现在是每月凌晨4点清理mysql所有实例下的表碎片)
推荐学习:mysql视频教程
以上是MySQL的几种碎片整理方案的详细内容。更多信息请关注PHP中文网其他相关文章!