删除大量数据并优化MySQL空间占用

域名工具箱的域名历史查询功能，因为查询时光机储存了大量的html代码，导致数据库体积越来越大，没办法紧急调整了代码逻辑。

自此不再储存html代码，全部是计算后仅保留必要信息，节省磁盘空间，若用户需要查看html代码，均是根据点击通过代理直接查询指定URL的html代码并返回，虽然慢了1、2秒影响不大。

现在大家依然可以根据工具显示的长度判断域名历史的真实性，也可以点击查看源码，功能上依然一致无影响。

本文主要记录一下，在删除了一百多GB的html代码后，对数据库的优化记录：

1. 关闭了二进制日志的记录功能。
2. InnoDB引擎，需要手动重建表进行了优化，释放空间。

主要命令如下：

显示表信息：（主要关注Data_free大小，单位字节，越大碎片越多，可优化）

SHOW TABLE STATUS;

重建表命令：（表明无引号）

ALTER TABLE 表名 ENGINE=InnoDB;

清理二进制日志文件：

-- 清理 2024-01-05 15:00:00 之前的所有二进制日志
PURGE BINARY LOGS BEFORE '2024-01-05 15:00:00';

验证清理效果：

SHOW BINARY LOGS; -- 查看剩余日志文件列表

执行步骤记录：

步骤1：确认表空间配置

SHOW VARIABLES LIKE 'innodb_file_per_table';
/* 必须返回 ON：
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+
*/

步骤2：处理外键约束

-- 临时禁用外键检查（避免重建表时被阻塞）
SET FOREIGN_KEY_CHECKS = 0;

步骤3：重建表释放空间

-- 方法1：使用官方优化命令（自动处理外键）
OPTIMIZE TABLE your_table_name;

-- 方法2：手动重建表（更快且可控）
ALTER TABLE your_table_name ENGINE=InnoDB;

步骤4：恢复外键约束

SET FOREIGN_KEY_CHECKS = 1;

删除html字段的内容耗时2个小时左右，准备工作搞了好久，虽然使用了AI期间依然出现各种问题，备份一下使用过的删除字段代码以备不时之需。

DELIMITER $$
CREATE PROCEDURE update_html_code_to_null(IN p_batch_size INT)
proc_label: BEGIN -- 添加标签到BEGIN块
DECLARE v_min_id BIGINT;
DECLARE v_max_id BIGINT;
DECLARE v_rows_affected INT;
DECLARE v_done BOOLEAN DEFAULT FALSE;
DECLARE v_total_updated BIGINT DEFAULT 0;

-- 获取表的ID范围
SELECT MIN(id), MAX(id) INTO v_min_id, v_max_id FROM web_api.archives_result;

-- 如果没有数据则直接返回
IF v_min_id IS NULL OR v_max_id IS NULL THEN
SELECT 'No data to update' AS message;
LEAVE proc_label; -- 现在标签已定义，可以正确跳出
END IF;

-- 循环更新
update_loop: WHILE NOT v_done DO
START TRANSACTION;

-- 更新一批数据
UPDATE web_api.archives_result
SET html_code = NULL
WHERE id BETWEEN v_min_id AND v_min_id + p_batch_size - 1
AND html_code IS NOT NULL;

-- 记录影响的行数
SET v_rows_affected = ROW_COUNT();
SET v_total_updated = v_total_updated + v_rows_affected;

-- 提交事务
COMMIT;

-- 输出当前批次进度
SELECT CONCAT('Batch updated: ', v_rows_affected, ' rows (IDs ', v_min_id, ' to ', LEAST(v_min_id + p_batch_size - 1, v_max_id), ')') AS batch_log;

-- 更新下一批的起始id
SET v_min_id = v_min_id + p_batch_size;

-- 检查是否完成
IF v_min_id > v_max_id OR v_rows_affected = 0 THEN
SET v_done = TRUE;
END IF;

-- 暂停1秒（根据负载调整）
DO SLEEP(1);
END WHILE update_loop;

-- 最终输出
SELECT CONCAT('Update completed. Total rows updated: ', v_total_updated) AS message;
END
$$
DELIMITER ;

以上代码保存为update_html_code_to_null.sql文件，上传到服务器上，然后 mysql -u root -p 回车输入密码，进入mysql命令行，执行以下代码：

source /update_html_code_to_null.sql

CALL update_html_code_to_null(1000); -- 每次处理1000条记录