当前位置：首页 > ops >正文

MySQL 安全更新大量数据

ops 2025/8/16 16:34:12

1. 使用主键范围分批更新

2. Shell 脚本解决方案

（1）脚本说明

（2）使用说明

假设 t1.id 是主键，t1.copyright != 6 的数据有 1000 万行，copyright 上没有索引，需要执行逻辑如下的数据更新：

update t1 set state = 0 where copyright != 6;

对于大批量数据更新，直接执行这条 SQL 是不可行的：

锁表时间过长，影响对该表的正常访问。
I/O 资源满载，导致卡库。
主库大事务，造成从库复制延迟。

以下是两种高效的分批更新方法。

1. 使用主键范围分批更新

DELIMITER //
CREATE PROCEDURE batch_update_state()
BEGINDECLARE min_id INT;DECLARE max_id INT;DECLARE batch_size INT DEFAULT 10000; -- 每批处理的行数-- 获取最小和最大ID，查询不上锁SELECT MIN(id), MAX(id) INTO min_id, max_id FROM t1 WHERE copyright != 6;-- 分批更新WHILE min_id <= max_id DOUPDATE t1 FORCE INDEX (PRIMARY)SET state = 0 WHERE copyright != 6 AND id BETWEEN min_id AND min_id + batch_size - 1;SET min_id = min_id + batch_size;DO SLEEP(0.5); -- 可选，减少对数据库的压力END WHILE;
END //
DELIMITER ;-- 执行存储过程
CALL batch_update_state();

此方法可以有效减少锁持有时间，避免长时间阻塞其他查询。注意事项：

根据表大小调整 batch_size 值（通常1000-50000之间）。
在低峰期执行大批量更新操作。
考虑添加适当的索引（如 copyright）。
对于生产环境，建议先在测试环境验证。

2. Shell 脚本解决方案

现在再加两个约束条件：当主库活动线程超过 50 个，或者从库延迟超过 10 秒，则等待恢复后再继续执行更新。连接主库无法获取其从库的复制延迟，因此无法通过存储过程实现。使用外部脚本实现会更灵活可靠，特别是需要监控主库负载和从库延迟的场景。以下是完整的 Shell 脚本解决方案，使用 MySQL 命令行工具实现安全的分批更新。

#!/bin/bash# 配置参数
DB_HOST="主库IP"
DB_USER="用户名"
DB_PORT=3306
DB_PASS="密码"
DB_NAME="test"
TABLE_NAME="t1"
BATCH_SIZE=10000
MAX_ACTIVE_THREADS=50
MAX_REPLICA_DELAY=10
REPLICA_HOST="从库IP"  # 用于检查延迟# 获取最小ID和最大ID，一次性查询并赋值
read MIN_ID MAX_ID <<< $(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT $DB_NAME -Nse "SELECT MIN(id), MAX(id) FROM $TABLE_NAME WHERE copyright != 6")CURRENT_ID=$MIN_ID# 分批更新
while [ $CURRENT_ID -le $MAX_ID ]
do# 检查主库活动线程数ACTIVE_THREADS=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT $DB_NAME -Nse "SELECT COUNT(*) FROM information_schema.processlist WHERE COMMAND NOT IN ('Sleep', 'Binlog Dump', 'Daemon')AND USER NOT IN ('system user', 'event_scheduler')")# 检查从库延迟（需要在从库上执行）REPLICA_DELAY=$(mysql -h$REPLICA_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT -Nse "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')# 如果资源紧张则等待if [ $ACTIVE_THREADS -ge $MAX_ACTIVE_THREADS ] || [ "$REPLICA_DELAY" != "NULL" ] && [ $REPLICA_DELAY -ge $MAX_REPLICA_DELAY ]; thenecho "[$(date +'%T')] 等待 - 活动线程: $ACTIVE_THREADS, 从库延迟: ${REPLICA_DELAY:-未知}"sleep 10continuefi# 计算理论结束IDEND_ID=$((CURRENT_ID + BATCH_SIZE - 1))# 限制结束ID不超过最大值END_ID=$((END_ID > MAX_ID ? MAX_ID : END_ID))# 主库执行批量更新echo "[$(date +'%T')] 处理ID: $CURRENT_ID ~ $END_ID"mysql -h$DB_HOST -u$DB_USER -p$DB_PASS -P$DB_PORT $DB_NAME -e "UPDATE $TABLE_NAME FORCE INDEX (PRIMARY)SET state = 0 WHERE copyright != 6 AND id BETWEEN $CURRENT_ID AND $END_ID"# 更新进度REMAINING=$((MAX_ID - END_ID))echo "[$(date +'%T')] 剩余记录: $REMAINING"CURRENT_ID=$((END_ID + 1))sleep 0.5  # 减轻数据库压力
doneecho "批量更新完成！"