MySQL删除数据后空间没变小|极客笔记

MySQL删除数据后空间没变小

简介

MySQL是一种常用的关系型数据库管理系统，拥有广泛的应用场景。在使用MySQL时，我们经常需要进行数据的删除操作。然而，有时候我们会发现，当我们删除了一些数据之后，数据库的磁盘空间并没有立即减小，这可能导致数据库占用过多的存储空间。

本文将详细解释MySQL删除数据后空间没有变小的原因，并提供解决方法。

原因分析

当我们在MySQL中删除数据时，数据并不是真正从磁盘上移除的，而只是从MySQL的逻辑上被标记为已删除。这是因为MySQL使用了一种称为“MVCC（多版本并发控制）”的机制，用于支持数据库的并发读写操作。

MVCC机制通过在每行数据的后面添加一列来实现，用于维护行的删除和插入时间戳，以及事务ID。当进行数据删除操作时，MySQL会将该行标记为“已删除”，并且在需要释放空间时进行重用。

然而，这种重用空间的过程并不是实时进行的。在MySQL中，空间的重用是由InnoDB存储引擎负责管理的。InnoDB使用一种称为“自适应哈希索引重建”来重用空间。简单来说，InnoDB会在后台定期进行空间重用操作，但并不会立即释放磁盘空间。

此外，InnoDB还使用一种称为“doublewrite buffer”的机制，用于提高写入性能和保证数据完整性。这也会导致数据库空间不立即减小的现象。

解决方法

尽管MySQL删除数据后空间没有立即减小是MySQL的正常行为，但我们可以采取一些措施来回收磁盘空间并减小数据库的存储空间。

方法一：优化表

通过对表进行优化操作，可以释放一些未被使用的空间。优化表会重新构建表，生成新的物理文件，从而消除空洞并减小实际占用的磁盘空间。

OPTIMIZE TABLE 表名;

运行结果：

+----------------+----------+----------+----------+
| Table          | Op       | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| test.table_name  | optimize | status   | OK       |
+----------------+----------+----------+----------+

方法二：压缩表

通过对表进行压缩操作，可以进一步减小占用的存储空间。压缩表会对表中的数据进行重新组织和压缩，以减少数据占用的磁盘空间。

ALTER TABLE 表名 ENGINE=InnoDB;

运行结果：

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

方法三：重新导出导入数据

将数据重新导出为SQL文件，然后重新导入到新的数据库中。这样可以排除已删除数据对存储空间的影响，并提供一次机会来压缩和优化数据库。

# 导出数据
mysqldump -u 用户名 -p 数据库名 > 数据库名.sql

# 创建新数据库
mysql -u 用户名 -p -e "CREATE DATABASE 新数据库名;"

# 导入数据
mysql -u 用户名 -p 新数据库名 < 数据库名.sql

运行结果：

// 导出数据
Dump completed on 2021-01-01 00:00:00

// 创建新数据库
Query OK, 1 row affected (0.00 sec)

// 导入数据
Query OK, 0 rows affected (0.00 sec)

方法四：重建表

重建表是一种彻底消除已删除数据的方法。它会创建一个全新的表，并将原表中的数据重新插入到新表中，从而达到减小存储空间的目的。

CREATE TABLE 新表名 LIKE 原表名;
INSERT INTO 新表名 SELECT * FROM 原表名;
DROP TABLE 原表名;
RENAME TABLE 新表名 TO 原表名;

运行结果：

Query OK, 0 rows affected (0.00 sec)

Query OK, 1000 rows affected (0.00 sec)
Records: 1000  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

方法五：重启数据库

重启数据库是一种极端的解决方法，但也是一种有效的方式。当数据库重启时，所有缓存和未提交的事务都会被清除，从而完全释放已删除数据占用的空间。

# 重启数据库服务
sudo service mysql restart

运行结果：

Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for tables which need an upgrade, are corrupt or were not closed cleanly.