一、问题现象：删了数据却腾不出空间

最近遇到个有意思的情况：小王把生产环境MySQL表中500万条日志记录删了，结果发现服务器磁盘空间根本没减少。更奇怪的是，使用df -h命令查看磁盘使用率仍然显示98%，就像这些数据压根没删除一样。

这种情况常发生在长期运行的数据库实例中。当我们用DELETE FROM log_table WHERE create_time < '2023-01-01'这类语句删除数据后，会发现以下特征：

  • 数据文件（.ibd）体积没有变化
  • SHOW TABLE STATUS显示Data_free有大量未使用空间
  • 新增数据时会优先使用这些"已删除"的空间

二、原理剖析：数据库的存储机制

要理解这个现象，需要先了解MySQL的存储原理。以InnoDB引擎为例，其存储结构就像一本活页笔记本：

-- 创建测试表（MySQL 8.0）
CREATE TABLE space_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;

当执行删除操作时：

  1. 数据页中的记录被标记为"可复用"
  2. 这些空间会被保留供后续插入使用
  3. 磁盘文件不会主动缩小，就像撕掉笔记本中间的页数但本子厚度不变

三、解决方案实战

方案1：OPTIMIZE TABLE（适合中小表）

最直接的解决方法就是整理表空间：

-- 执行表优化（会锁定表）
OPTIMIZE TABLE space_test;

-- 查看优化结果
SHOW TABLE STATUS LIKE 'space_test';

输出结果中的Data_length和Index_length会显著减小，但注意：

  1. 需要两倍原表的磁盘空间
  2. 执行期间全表锁定
  3. 不适合超过10GB的大表

方案2：InnoDB引擎配置调优（长期方案）

调整innodb_file_per_table参数：

-- 查看当前配置
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- 永久生效配置（需要重启）
SET GLOBAL innodb_file_per_table = ON;

当启用独立表空间后，配合以下参数可实现自动空间回收：

# my.cnf配置
innodb_file_per_table = ON
innodb_autoextend_increment = 64
innodb_max_dirty_pages_pct = 50

方案3：表重建法（通用方案）

通过导出导入重建表：

# 导出数据
mysqldump -u root -p dbname space_test > space_test.sql

# 删除原表
mysql -u root -p -e "DROP TABLE dbname.space_test;"

# 重新建表
mysql -u root -p dbname < space_test.sql

这种方法适合需要变更表结构的情况，但要注意：

  • 需要维护时间窗口
  • 确保导出期间没有数据写入
  • 主从架构需要特殊处理

方案4：分批删除策略（海量数据场景）

对于超大型表，建议采用分段删除：

DELIMITER $$
CREATE PROCEDURE batch_delete()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE min_id INT;
    DECLARE max_id INT;
    
    SELECT MIN(id), MAX(id) INTO min_id, max_id FROM space_test;
    
    WHILE min_id <= max_id DO
        DELETE FROM space_test 
        WHERE id BETWEEN min_id AND min_id + 1000;
        
        SET min_id = min_id + 1000;
        DO SLEEP(1);  -- 控制删除速度
    END WHILE;
END$$
DELIMITER ;

方案5：分区表管理（预防性方案）

使用分区表实现物理删除：

-- 创建按月的分区表
CREATE TABLE log_partition (
    id INT AUTO_INCREMENT,
    log_data TEXT,
    created_at DATETIME,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (YEAR(created_at)*100 + MONTH(created_at)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303)
);

-- 删除旧分区
ALTER TABLE log_partition DROP PARTITION p202301;

这种方式可以直接释放磁盘空间，但需要提前规划分区策略。

方案6：使用物理文件收缩（InnoDB特有）

对于独立表空间文件：

-- 进入MySQL数据目录
cd /var/lib/mysql/dbname

-- 查看表空间碎片
ALTER TABLE space_test ENGINE=InnoDB;

-- 强制回收空间（需要双倍磁盘空间）
ALTER TABLE space_test FORCE;

方案7：在线DDL工具（生产环境推荐）

使用pt-online-schema-change工具：

pt-online-schema-change \
--alter="ENGINE=InnoDB" \
D=testdb,t=space_test \
--execute

该工具通过创建影子表的方式，在不停机的情况下完成表重建。

四、关联技术解析

  1. Undo日志机制：长事务会导致旧数据无法被清除
  2. MVCC实现：多版本并发控制会保留旧数据版本
  3. Page合并机制：InnoDB的页合并策略影响空间回收效率
  4. 文件系统特性：EXT4/XFS等文件系统对稀疏文件的支持差异

五、应用场景分析

场景类型 适用方案 执行窗口需求
中小表清理 OPTIMIZE TABLE 维护窗口
海量数据删除 分批删除+分区表 无
生产环境维护 pt-online-schema-change 在线执行
归档数据管理 分区表DROP PARTITION 随时执行

六、技术方案优缺点对比

OPTIMIZE TABLE

  • 👍 操作简单，立即生效
  • 👎 锁表时间长，需要双倍空间

分区表方案

  • 👍 物理删除效率高
  • 👎 需要提前规划分区

在线DDL工具

  • 👍 零停机时间
  • 👎 需要安装额外工具

七、操作注意事项

  1. 备份优先：执行任何空间回收操作前务必全量备份
  2. 监控锁状态：SHOW PROCESSLIST观察查询阻塞情况
  3. 文件系统检查：确保使用支持稀疏文件的XFS/ext4
  4. 版本兼容性：MySQL 8.0与5.7的undo日志管理差异
  5. 复制延迟：主从架构中注意DDL操作传播时间

八、总结建议

经过多个生产环境的实践验证，我们总结出以下最佳实践组合：

  1. 预防阶段：启用innodb_file_per_table+定期清理
  2. 设计阶段：对日志类数据使用分区表
  3. 维护阶段：使用pt-online-schema-change在线维护
  4. 应急处理：结合业务低峰期执行OPTIMIZE TABLE

对于不同的数据规模，可参考以下处理阈值：

  • <10GB：直接OPTIMIZE
  • 10-100GB：使用在线DDL工具

  • 100GB：建议采用分区表方案