大表删除字段后，如何进行空间释放和整理？

在不同的数据库系统中，大表删除字段后进行空间释放和整理的方法有所不同，下面分别介绍常见数据库的处理方式。

MySQL

1. OPTIMIZE TABLE 语句

OPTIMIZE TABLE 语句可用于对表进行碎片整理，释放删除字段后产生的未使用空间。不过该语句仅适用于 MyISAM、BDB 和 InnoDB 存储引擎。

OPTIMIZE TABLE your_table_name;

• 原理：OPTIMIZE TABLE 会创建一个临时表，将原表的数据逐行复制到临时表中，在此过程中会跳过已删除的字段，然后删除原表，将临时表重命名为原表名。这样可以有效消除碎片，释放空间。
• 注意事项：执行该操作时会对表加锁，可能会影响数据库的正常使用，因此建议在业务低峰期进行。

2. ALTER TABLE 重建表

可以使用 ALTER TABLE 语句重建表来释放空间。

 

ALTER TABLE your_table_name ENGINE = InnoDB;

• 原理：该操作会创建一个新的表结构，然后将原表的数据复制到新表中，最后删除原表。与 OPTIMIZE TABLE 类似，通过重建表来消除碎片，释放删除字段后多余的空间。
• 注意事项：同样会对表加锁，且在数据量较大时可能会消耗较多的时间和系统资源。

Oracle

1. 在线重定义表

对于大表，可使用在线重定义功能来释放空间。

 

-- 1. 创建中间表
CREATE TABLE temp_table AS SELECT * FROM your_table_name;

-- 2. 删除原表
DROP TABLE your_table_name;

-- 3. 重命名中间表为原表名
ALTER TABLE temp_table RENAME TO your_table_name;

• 原理：通过创建一个与原表结构相同的中间表，将原表的数据复制到中间表，然后删除原表，最后将中间表重命名为原表名，从而实现空间的释放和表的整理。
• 注意事项：在复制数据过程中需要足够的磁盘空间，并且操作过程中会对表加锁，可能影响业务。

2. 收缩表空间

可以使用 ALTER TABLE 语句结合 SHRINK SPACE 选项来收缩表空间。

 

ALTER TABLE your_table_name SHRINK SPACE CASCADE;

• 原理：该语句会尝试将表中的空闲空间返还给表空间，同时可以使用 CASCADE 选项级联收缩相关的索引空间。
• 注意事项：表需要启用行移动功能（ALTER TABLE your_table_name ENABLE ROW MOVEMENT;），并且操作可能会消耗较多时间。

PostgreSQL

1. VACUUM 和 ANALYZE 命令

VACUUM 命令用于回收已删除行所占用的磁盘空间，ANALYZE 命令用于更新表的统计信息。

-- 简单的 VACUUM 操作
VACUUM your_table_name;

-- 更彻底的 FULL VACUUM 操作
VACUUM FULL your_table_name;

-- 更新统计信息
ANALYZE your_table_name;

• 原理：VACUUM 会标记已删除的行，使其占用的空间可以被后续的插入操作复用；VACUUM FULL 会对表进行更彻底的整理，将数据重新排列，释放未使用的空间，但操作会对表加排他锁，阻塞其他操作。ANALYZE 则会更新表的统计信息，帮助查询优化器生成更优的执行计划。
• 注意事项：VACUUM FULL 操作会对表加锁，可能会影响数据库的正常使用，建议在业务低峰期进行。

2. 重建表

可以通过创建新表，将原表数据复制到新表，然后删除原表并将新表重命名为原表名的方式来重建表。

 

-- 创建新表
CREATE TABLE new_table AS SELECT * FROM your_table_name;

-- 删除原表
DROP TABLE your_table_name;

-- 重命名新表
ALTER TABLE new_table RENAME TO your_table_name;

• 原理：通过重建表消除碎片，释放删除字段后多余的空间。
• 注意事项：需要足够的磁盘空间来存储新表的数据，并且操作过程中会对表加锁，影响业务。