数据库表空间优化和碎片整理

最新推荐文章于 2024-09-03 16:47:47 发布

wybsw520

最新推荐文章于 2024-09-03 16:47:47 发布

阅读量688

点赞数

文章标签：数据库

本文链接：https://blog.csdn.net/wybsw520/article/details/134123962

版权

一、使用场景

1.1 大量删除数据

大量删除数据以后，delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些碎片空间可以复用，但是不整理碎片，之后插入数据是随机插入的，就可能造成索引的数据页分裂。对后续的数据查询会产生性能问题。

1.2 经常删除和插入操作的数据库表，建议定期整理

1.3 数据库维护时，发现表碎片过大

-- 查看表状态
show table status from tableName;
-- 结果中’Data_free’列的值就是碎片大小

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

二、解决方案

-- innodb表执行 alter table tablename ENGINE=InnoDB相当于执行 optimize，但是阿里云客服说RDS不适合，实际测试还行
alter table tabname ENGINE=InnoDB;
-- 整理表空间和碎片
optimize table tabname;
-- 收集统计信息
ANALYZE table tabname;

三、碎片产生原因

3.1 删除记录会留空

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

3.2 插入操作会跳过小碎片

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

3.3 释放表空间，降低磁盘空间使用率

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

四、执行好处

4.1 降低访问表时的IO

4.2 提高mysql性能

4.3 释放表空间，降低磁盘空间使用率

五、OPTIMIZE注意事项

5.1 执行周期

MySQL官方建议不要经常(每小时或每天)进行碎片整理，一般根据实际情况，只需要每周或者每月整理一次即可。

5.2 适用引擎

OPTIMIZE TABLE只对MyISAM，BDB和InnoDB表起作用，尤其是MyISAM表的作用最为明显。此外，并不是所有表都需要进行碎片整理，一般只需要对包含上述可变长度的文本数据类型的表进行整理即可。

5.3 执行锁表

在OPTIMIZE TABLE 运行过程中，MySQL会锁定表。OPTIMIZE 操作会锁住表,而且数据量越大,耗费的时间也越长。

5.4 InnoDB引擎注意事项

默认情况下，直接对InnoDB引擎的数据表使用OPTIMIZE TABLE，可能会显示「 Table does not support optimize, doing recreate + analyze instead」的提示信息。这个时候，我们可以用mysqld --skip-new或者mysqld --safe-mode命令来重启MySQL，以便于让其他引擎支持OPTIMIZE TABLE。