怎样批量更新数据而不锁表？

wen IT资讯 2026-06-03 238

本文目录导读：

怎样批量更新数据而不锁表？

📌 目录导读
问题背景
为什么批量更新会锁表？
不锁表的更新方案一览
方案一：分批更新 + 小事务
方案二：基于主键的游标遍历
方案三：使用 pt-archiver 或 gh-ost
方案四：临时表 + 重命名
方案五：应用层限流 + 异步队列
常见问题（FAQ）
总结与最佳实践

📌 目录导读

问题背景
为什么批量更新会锁表？
不锁表的更新方案一览
分批更新 + 小事务
基于主键的游标遍历
使用 pt-archiver 或 gh-ost 工具
临时表 + 重命名
应用层限流 + 异步队列
常见问题（FAQ）
总结与最佳实践

问题背景

在实际业务中，我们经常需要批量更新几万甚至上百万条记录，给用户表统一标记等级、给订单表修改状态、给文章表更新阅读量。

但直接执行 UPDATE table SET status = 1 WHERE condition 会导致：

锁表: MyISAM 引擎直接锁表，InnoDB 虽行锁但大量行锁升级为表锁
长事务: 占用大量 undo log，拖慢其他查询
主从延迟: 大事务导致从库无法及时写入 relay log
业务中断: 用户查询被阻塞，导致页面超时甚至雪崩

“怎样批量更新而不锁表” 成为高并发场景下的核心痛点。

为什么批量更新会锁表？

1 加锁机制

InnoDB 行锁：默认在索引上锁，WHERE 条件没有索引或扫描行数过多，会升级为 临键锁（Next-Key Lock） 甚至表锁。
MyISAM 表锁：任何写操作都会锁整表。

2 事务隔离级别

在 REPEATABLE READ 级别，MySQL 通过 MVCC 实现快照读，但 UPDATE 属于当前读,会加行锁。
当更新语句扫描的行数占全表比例过高，MySQL 优化器判断行锁成本高于表锁,会自动降级为表锁。

3 典型锁表现象

-- 假设 users 表 100 万行，以下语句会锁大量行甚至表
UPDATE users SET status = 'vip' WHERE created_at < '2023-01-01';

SHOW PROCESSLIST; 会看到 Waiting for table metadata lock。

不锁表的更新方案一览

方案	适用场景	是否锁表	复杂度
分批更新 + 小事务	简单条件更新	否（单次锁少量行）	低
游标遍历更新	需要逐条处理逻辑	否	中
pt-archiver	归档删除场景	否（工具自带限流）	低
临时表 + 重命名	重新生成数据	极短锁（毫秒级）	中
异步队列 + 限流	更新频率高	否	高

方案一：分批更新 + 小事务

1 实现方式

将一个大 UPDATE 拆成多个小批次，每次更新 100~500 行，再 COMMIT。

-- 每次更新 200 行，循环执行
UPDATE users SET status = 'vip'
WHERE id > 0 AND status != 'vip'
LIMIT 200;

2 配合主键范围

# Python 伪代码
min_id = 0
batch_size = 200
while True:
    rows = db.execute(f"""
        SELECT id FROM users
        WHERE id > {min_id} AND status != 'vip'
        ORDER BY id ASC LIMIT {batch_size}
    """)
    if not rows:
        break
    max_id = rows[-1]['id']
    db.execute(f"""
        UPDATE users SET status = 'vip'
        WHERE id BETWEEN {min_id+1} AND {max_id}
    """)
    db.commit()
    min_id = max_id

3 优点与缺点

✅ 不锁大表，每批仅锁几行
❌ 需要循环，复杂度略高
❌ 不能保证原子性（某批次失败需重试）

方案二：基于主键的游标遍历

1 核心逻辑

使用 SELECT ... FOR UPDATE 逐行读取并更新,每次只锁一行。

-- 存储过程示意
DECLARE done INT DEFAULT FALSE;
DECLARE cur_id INT;
DECLARE cur CURSOR FOR 
    SELECT id FROM users WHERE status != 'vip' LIMIT 1000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
    FETCH cur INTO cur_id;
    IF done THEN
        LEAVE read_loop;
    END IF;
    UPDATE users SET status = 'vip' WHERE id = cur_id;
    COMMIT;  -- 每行提交一次（性能较低）
END LOOP;
CLOSE cur;

2 优化：批量 FETCH + 逐行提交

实际应用中不要每行都提交，而是每 100 行提交一次,平衡锁粒度与性能。

3 适用场景

更新逻辑复杂（如需要调用外部 API）
无法使用简单 WHERE 条件批量更新

方案三：使用 pt-archiver 或 gh-ost

1 pt-archiver

Percona Toolkit 中的归档工具，支持 --limit 和 --sleep 控制速度,常用于删除过期数据。

pt-archiver --source h=localhost,D=test,t=users \
  --where "created_at < '2023-01-01'" \
  --limit 200 --sleep 0.1 --purge

2 gh-ost（GitHub Online Schema Migration）

虽主要用于 DDL，但也可用于 UPDATE 操作，通过 binlog 同步实现无锁更新。

核心原理：创建影子表，将原表数据拷贝到影子表，同时实时同步 binlog 变更，RENAME TABLE 切换。

3 优点

✅ 成熟开源，经过生产验证
✅ 自带限流、暂停、监控

方案四：临时表 + 重命名

1 适用场景

当需要更新几乎所有行时（如给所有用户加一个字段），直接 UPDATE 会锁表,不如重建表。

2 操作步骤

-- 1. 创建新表（结构相同）
CREATE TABLE users_new LIKE users;
-- 2. 插入更新后的数据
INSERT INTO users_new SELECT *, 'vip' AS status FROM users;
-- 3. 原子重命名（锁表时间极短）
RENAME TABLE users TO users_old, users_new TO users;

3 注意

步骤 2 期间 users 表仍在被读写，新插入的数据会丢失,需额外处理。
可以通过 pt-online-schema-change 自动处理这部分增量。

方案五：应用层限流 + 异步队列

1 架构

请求 → 异步队列（RabbitMQ / Redis） → 消费者分批更新 → 限流

2 优势

完全解耦，对主库无直接压力
失败可重试，不影响其他业务

3 实现示例

// Node.js 伪代码
const queue = [];
let batch = [];
setInterval(() => {
  if (batch.length >= 200) {
    db.query(`UPDATE users SET ... WHERE id IN (${batch.join(',')})`);
    batch = [];
  }
}, 100);

常见问题（FAQ）

Q1：分批更新时，中间有新的数据写入怎么办？

答：如果新数据也符合条件，会被后续批次更新；如果要求严格一致性，可在 WHERE 中加上 status != 'vip' 避免重复更新。

Q2：使用 `LIMIT` 分批更新会重复更新吗？

答：WHERE 条件没有唯一索引，确实可能重复，建议用主键范围 + ORDER BY 唯一保证。

Q3：更新 1000 万行数据，哪种方案最快？

答：临时表 + 重命名最快（毫秒级锁表），但需要处理增量数据；如果允许慢更新，pt-archiver 最安全。

Q4：`UPDATE` 语句不带 `WHERE` 会怎样？

答：一定锁表（InnoDB 行锁升级为表锁），且无法回滚,生产环境严禁执行。

总结与最佳实践

推荐程度	方案	适用业务量
分批更新 + 主键范围	中等数据量（百万级）
pt-archiver	归档、删除、清洗
临时表 + 重命名	全量替换（千万级）
gh-ost	数据库表结构变更
异步队列	高频增量更新