在使用 MySQL 数据库的过程中，你可能会遇到这样的问题：明明已经删除了表中的大量数据，可数据库文件的大小却丝毫没有变化。这就好像你把家里的垃圾扔出去了，可屋子的空间却没变大一样，让人头疼。今天，咱们就来聊聊如何解决这个问题，也就是 MySQL 数据库的空间回收与碎片整理方法。
一、问题产生的原因
在 MySQL 里，当你删除表中的数据时，MySQL 并不会马上把这些被删除数据占用的空间释放出来，而是把这些空间标记为可复用。就好比你把家里的旧家具搬走了，但是房子里的空间并没有真正空出来，只是可以用来放新家具了。这就导致了即使你删除了很多数据，数据库文件的大小还是不变。
举个例子，我们有一个简单的用户表 users：
-- MySQL 技术栈
-- 创建 users 表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
-- 插入一些数据
INSERT INTO users (name, age) VALUES ('Alice', 25), ('Bob', 30), ('Charlie', 35);
-- 删除一条数据
DELETE FROM users WHERE name = 'Bob';
在执行完上面的代码后，虽然我们删除了 Bob 这条记录，但是 users 表对应的文件大小并不会马上变小。
二、空间回收与碎片整理方法
1. OPTIMIZE TABLE 命令
这个命令就像是给数据库做一次大扫除，它会重新组织表的数据和索引，把那些被标记为可复用的空间真正释放出来。
还是以 users 表为例：
-- MySQL 技术栈
-- 对 users 表进行优化
OPTIMIZE TABLE users;
执行这个命令后，MySQL 会把 users 表中的碎片整理好，释放出被删除数据占用的空间，这样数据库文件的大小就会相应减小。
优点：使用简单，只需要一条命令就能完成空间回收和碎片整理。
缺点：执行这个命令时，会对表加锁，在数据量较大的情况下，执行时间可能会比较长，影响数据库的正常使用。
注意事项：在使用 OPTIMIZE TABLE 命令之前，最好先备份数据，以防万一。同时，尽量在数据库访问量较小的时候执行这个命令，减少对业务的影响。
2. ALTER TABLE 命令重建表
这种方法就像是把房子拆了重新建一遍，通过重建表来达到空间回收和碎片整理的目的。
示例代码如下：
-- MySQL 技术栈
-- 重建 users 表
ALTER TABLE users ENGINE = InnoDB;
这里我们把 users 表的存储引擎重新设置为 InnoDB，实际上就是对表进行了一次重建。在重建的过程中，MySQL 会把数据重新组织，释放出被删除数据占用的空间。
优点：可以有效解决表的碎片问题，提高数据库的性能。
缺点：和 OPTIMIZE TABLE 命令一样，执行时间可能会比较长，并且在重建表的过程中会对表加锁。
注意事项：同样需要在数据库访问量较小的时候执行这个命令，并且在执行之前备份好数据。
3. 分区表的空间回收
如果你的表使用了分区，那么可以通过删除分区的方式来回收空间。
假设我们有一个按年份分区的订单表 orders：
-- MySQL 技术栈
-- 创建按年份分区的 orders 表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATE,
amount DECIMAL(10, 2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)
);
-- 插入一些数据
INSERT INTO orders (order_date, amount) VALUES ('2020-01-01', 100.00), ('2021-05-10', 200.00), ('2022-11-20', 300.00);
-- 删除 2020 年的分区
ALTER TABLE orders DROP PARTITION p2020;
通过删除 p2020 分区，我们可以直接回收这个分区占用的空间。
优点：对于分区表来说，这种方法可以快速回收大量的空间，并且对其他分区没有影响。 缺点：只适用于分区表，如果你的表没有使用分区，就无法使用这种方法。 注意事项：在删除分区之前，要确保你真的不需要这个分区的数据了，因为删除分区后数据将无法恢复。
三、应用场景
1. 数据定期清理的场景
很多企业会定期清理一些历史数据，比如删除一年前的订单记录。在这种情况下，删除数据后使用空间回收和碎片整理方法可以及时释放数据库空间，避免数据库文件过大。
2. 数据库性能优化的场景
当数据库表的碎片过多时，会影响数据库的查询性能。通过空间回收和碎片整理，可以重新组织数据和索引，提高数据库的查询速度。
3. 磁盘空间紧张的场景
如果数据库所在的磁盘空间有限，那么及时回收被删除数据占用的空间就显得尤为重要。通过空间回收和碎片整理，可以让数据库在有限的磁盘空间内继续正常运行。
四、注意事项
1. 备份数据
在进行空间回收和碎片整理操作之前，一定要备份好数据库中的重要数据。因为这些操作可能会出现意外情况，导致数据丢失。
2. 选择合适的时间
由于空间回收和碎片整理操作可能会对数据库的性能产生影响，所以最好在数据库访问量较小的时候执行这些操作，比如在深夜或者周末。
3. 监控操作过程
在执行空间回收和碎片整理操作的过程中，要密切监控数据库的状态，确保操作正常进行。如果出现异常情况，要及时采取措施进行处理。
五、文章总结
在使用 MySQL 数据库时，表数据删除后文件大小不变是一个常见的问题，主要是因为 MySQL 不会马上释放被删除数据占用的空间。我们可以通过 OPTIMIZE TABLE 命令、ALTER TABLE 命令重建表以及分区表的空间回收等方法来解决这个问题。不同的方法有不同的优缺点和适用场景，在使用时要根据实际情况进行选择。同时，在进行空间回收和碎片整理操作时，要注意备份数据、选择合适的时间和监控操作过程，以确保数据库的安全和稳定运行。
评论