一、当批量更新成为刚需

上周三凌晨两点，我接到做电商的朋友老王的紧急电话。他们的商品价格表在促销活动开始前需要更新30万条记录，结果常规的逐条更新操作跑了半小时还没完成。这种典型的批量更新场景，相信很多开发者都经历过。

类似的情况还有：

  • 用户积分批量清零
  • 日志表状态字段全局更新
  • 订单历史数据迁移
  • 实时竞价系统的价格同步

这些场景的共同特点是数据量大、时效性要求高，传统的逐条更新就像用吸管给游泳池换水——效率低得令人发指。

二、性能优化的方案

2.1 基础必杀技：批量UPDATE语句

-- 更新用户等级（MySQL 8.0）
UPDATE user_levels 
SET discount_rate = CASE 
    WHEN total_consumption > 10000 THEN 0.8
    WHEN total_consumption > 5000 THEN 0.85
    ELSE 0.9
END
WHERE campaign_id = 2023;

这是最直接的批量更新方式，但要注意WHERE条件的索引覆盖。当更新量超过表记录的20%时，全表扫描可能更高效。

2.2 组合拳：临时表联查

-- 创建临时价格表
CREATE TEMPORARY TABLE temp_prices (
    product_id INT PRIMARY KEY,
    new_price DECIMAL(10,2)
) ENGINE=Memory;

-- 批量插入待更新数据（假设有10万条）
INSERT INTO temp_prices VALUES 
(1001, 299.00),
(1002, 399.00),
...;

-- 关联更新
UPDATE products p
JOIN temp_prices t ON p.id = t.product_id
SET p.price = t.new_price,
    p.updated_at = NOW();

这种方式适合数据来源复杂的情况，内存表的写入速度是普通表的3-5倍。注意事务大小控制在1万条左右。

2.3 存储过程大法

DELIMITER $$
CREATE PROCEDURE batch_update_order_status()
BEGIN
    DECLARE batch_size INT DEFAULT 5000;
    DECLARE affected_rows INT DEFAULT 1;
    
    WHILE affected_rows > 0 DO
        UPDATE orders 
        SET status = 'archived'
        WHERE create_time < '2022-01-01'
        LIMIT batch_size;
        
        SET affected_rows = ROW_COUNT();
        DO SLEEP(0.1); -- 防止锁竞争
    END WHILE;
END$$
DELIMITER ;

这种分批次更新对线上业务影响最小，通过SLEEP参数可以调节系统压力。实测在5000条/批时，总耗时仅比单次全量更新多15%。

2.4 终极奥义：LOAD DATA大法

-- 生成CSV文件
SELECT id, 
       FLOOR(price * 0.9) AS new_price 
FROM products 
WHERE category = 'electronics'
INTO OUTFILE '/tmp/price_update.csv'
FIELDS TERMINATED BY ',';

-- 创建临时表
CREATE TABLE price_update LIKE products;

-- 导入数据
LOAD DATA INFILE '/tmp/price_update.csv'
INTO TABLE price_update
FIELDS TERMINATED BY ','
(id, new_price);

-- 执行更新
UPDATE products p
JOIN price_update t ON p.id = t.id
SET p.price = t.new_price;

这种方法在处理百万级数据时优势明显，比常规UPDATE快10倍以上。但要注意文件权限和服务器负载。

三、技术选型五维

我们从五个维度对比上述方案：

  1. 执行速度：LOAD DATA > 临时表 > 存储过程 > 批量UPDATE
  2. 开发成本：批量UPDATE < 临时表 < 存储过程 < LOAD DATA
  3. 系统影响：存储过程 < LOAD DATA < 临时表 < 批量UPDATE
  4. 数据安全：存储过程 > 临时表 > LOAD DATA > 批量UPDATE
  5. 适用场景：高频小批（存储过程）、低频大批（LOAD DATA）、中等规模（临时表）

四、避坑指南：血泪经验总结

4.1 索引的生死抉择

在批量更新时，更新字段如果建有索引，会导致索引树重建。曾经有个案例：给2亿用户表加了个普通索引，结果更新速度从每分钟5万条骤降到8千条。

解决方案：

  1. 先DROP索引再重建
  2. 使用Online DDL（ALGORITHM=INPLACE）
  3. 对非必要索引进行清理

4.2 事务的微妙平衡

大事务会导致undo日志膨胀。某次金融系统更新200万账户余额，因为没控制事务大小，导致磁盘空间爆满。

最佳实践：

START TRANSACTION;
-- 每次更新1万条
UPDATE ... LIMIT 10000;
COMMIT;

START TRANSACTION;
UPDATE ... LIMIT 10000;
COMMIT;

这种分段提交事务的方式，既能保证效率，又避免长事务风险。

4.3 锁机制的暗流涌动

某电商平台在促销时更新库存，由于没加SKU条件，导致全表锁死。教训练习：

-- 危险操作（锁定所有记录）
UPDATE inventory SET stock = stock - 1;

-- 安全操作（使用索引条件）
UPDATE inventory SET stock = stock - 1
WHERE sku_id IN (1001,1002,...);

尽量使用行级锁（InnoDB），避免表锁（MyISAM），必要时添加FOR UPDATE跳过锁等待。

五、未来战场：新特性展望

5.1 并行查询

（MySQL 8.0+）

SET SESSION innodb_parallel_update_threads = 4;
UPDATE big_table 
SET status = 'processed'
WHERE create_time < '2023-01-01';

通过多线程加速，实测8核机器上速度提升300%。但要注意线程数不要超过CPU核心数。

5.2 即时DDL（TiDB特性）

在分布式数据库中的实践：

ALTER TABLE user_logs 
ADD INDEX idx_status (status) LOCK=NONE;

这种无锁变更对在线业务零影响，适合7x24小时运行的系统。

六、总结与升华

经过多个项目的验证，我们得出黄金公式：

最佳方案 = 数据量/10000 × 业务容忍时间 + 系统资源余量

当数据量<10万时，优先使用临时表联查；10-100万考虑存储过程分批；超过百万级建议LOAD DATA方案。记得在测试环境用真实数据验证，避免「本地没问题」的尴尬。