跳到主要内容

批量操作优化

问题​

如何高效地进行批量插入、批量更新和批量删除？大表的 DML 操作有哪些注意事项？

答案​

批量插入优化​

多值 INSERT​

-- ❌ 逐条插入：每条都是一个事务，频繁 IO
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
INSERT INTO users (name, age) VALUES ('Charlie', 28);

-- ✅ 合并为一条 INSERT：减少网络往返和事务开销
INSERT INTO users (name, age) VALUES 
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 28);

每批大小建议：一般建议每批 500-1000 行，避免单条 SQL 过大（超过 max_allowed_packet）。

手动控制事务​

-- ✅ 手动开启事务，批量提交
START TRANSACTION;
INSERT INTO users (name, age) VALUES ('Alice', 25);
INSERT INTO users (name, age) VALUES ('Bob', 30);
-- ... 每 1000 条提交一次
COMMIT;

START TRANSACTION;
-- ... 下一批
COMMIT;

LOAD DATA INFILE​

对于大量数据导入，LOAD DATA INFILE 是最快的方式（比 INSERT 快 20 倍以上）：

LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES  -- 跳过表头
(name, age, email);

批量插入优化参数​

-- 临时关闭唯一索引检查（确保数据无重复的前提下）
SET unique_checks = 0;

-- 临时关闭外键检查
SET foreign_key_checks = 0;

-- 调大 bulk_insert_buffer_size
SET bulk_insert_buffer_size = 256 * 1024 * 1024;  -- 256MB

-- 执行批量插入...

-- 恢复
SET unique_checks = 1;
SET foreign_key_checks = 1;

批量更新优化​

CASE WHEN 批量更新​

-- ❌ 逐条更新
UPDATE products SET price = 10.0 WHERE id = 1;
UPDATE products SET price = 20.0 WHERE id = 2;
UPDATE products SET price = 30.0 WHERE id = 3;

-- ✅ CASE WHEN 合并为一条
UPDATE products SET price = CASE id
    WHEN 1 THEN 10.0
    WHEN 2 THEN 20.0
    WHEN 3 THEN 30.0
END
WHERE id IN (1, 2, 3);

INSERT ON DUPLICATE KEY UPDATE​

-- 存在则更新，不存在则插入
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES 
    (1, 1, NOW()),
    (2, 1, NOW()),
    (3, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login = NOW();

分批更新大表​

-- ❌ 一次更新所有行：长时间锁表
UPDATE orders SET status = 'archived' WHERE create_time < '2023-01-01';

-- ✅ 分批更新，每批处理一定量
-- 方法：循环更新直到影响行数为 0
UPDATE orders SET status = 'archived' 
WHERE create_time < '2023-01-01' AND status != 'archived'
LIMIT 1000;
-- 重复执行直到 affected_rows = 0

批量删除优化​

大表删除策略​

-- ❌ 直接删除大量数据：长时间锁定、大量 undo log、binlog 膨胀
DELETE FROM logs WHERE create_time < '2023-01-01';

-- ✅ 分批删除
REPEAT
    DELETE FROM logs 
    WHERE create_time < '2023-01-01' 
    LIMIT 1000;
    -- 控制每批删除量，避免长事务
    -- 建议每批之间 sleep 100ms，给其他事务执行的机会
UNTIL ROW_COUNT() = 0

大表清理的最佳实践​

方案 1：分批删除 + 延时

# Python 示例
import time
import pymysql

conn = pymysql.connect(...)
cursor = conn.cursor()

while True:
    cursor.execute("""
        DELETE FROM logs 
        WHERE create_time < '2023-01-01' 
        LIMIT 1000
    """)
    conn.commit()
    
    if cursor.rowcount == 0:
        break
    
    time.sleep(0.1)  # 每批间隔 100ms

方案 2：交换表名（适用于保留少量数据）

-- 如果要清理的数据远多于保留的数据
-- 1. 创建新表
CREATE TABLE logs_new LIKE logs;

-- 2. 将需要保留的数据插入新表
INSERT INTO logs_new SELECT * FROM logs WHERE create_time >= '2023-01-01';

-- 3. 交换表名
RENAME TABLE logs TO logs_old, logs_new TO logs;

-- 4. 确认无误后删除旧表
DROP TABLE logs_old;

方案 3：分区表按分区清理

-- 如果表按时间分区，直接删除整个分区（瞬间完成）
ALTER TABLE logs DROP PARTITION p202301;

大表 DDL 操作​

大表添加索引、修改列等 DDL 操作也需要注意：

-- MySQL 5.6+ 支持 Online DDL
-- ALGORITHM=INPLACE：尽量就地修改（不重建表）
-- LOCK=NONE：不锁表
ALTER TABLE orders ADD INDEX idx_status(status), ALGORITHM=INPLACE, LOCK=NONE;

-- 大表添加列（8.0 Instant DDL，秒级完成）
ALTER TABLE orders ADD COLUMN remark VARCHAR(200) DEFAULT NULL, ALGORITHM=INSTANT;
pt-online-schema-change

对于不支持 Online DDL 的操作，使用 Percona 的 pt-osc 工具：

pt-online-schema-change \
    --alter "ADD INDEX idx_status(status)" \
    --execute D=mydb,t=orders

它通过创建影子表、同步数据、交换表名来实现不停机 DDL。

常见面试问题​

Q1: 为什么不建议一次删除大量数据？​

答案：

  1. 长时间锁定：大事务持有行锁/间隙锁时间长，阻塞其他操作
  2. undo log 膨胀：删除操作产生大量 undo log，占用回滚段空间
  3. binlog 膨胀：一条大 DELETE 产生巨大的 binlog 事件，影响主从同步
  4. 回滚风险：中途失败，回滚可能比执行还慢

正确做法：分批删除，每批 1000-5000 行，批次间留间隔。

Q2: LOAD DATA INFILE 为什么比 INSERT 快？​

答案：

  1. 减少 SQL 解析：只解析一次 SQL
  2. 批量写入：一次 IO 写入大量数据
  3. 减少索引维护：可以先禁用索引，导入后再重建
  4. 减少日志：binlog 记录更紧凑
  5. 跳过事务开销：减少事务提交次数

通常比逐条 INSERT 快 20-50 倍。

Q3: 如何在不停机的情况下给大表添加索引？​

答案：

  1. MySQL 5.6+ Online DDL：

    ALTER TABLE t ADD INDEX idx(col), ALGORITHM=INPLACE, LOCK=NONE;

    支持在不阻塞 DML 的情况下添加索引。

  2. pt-online-schema-change：创建影子表，同步数据，原子交换表名。适用于 MySQL 5.5 或不支持 Online DDL 的操作。

  3. gh-ost（GitHub 开源）：类似 pt-osc，但使用 binlog 同步而非触发器，对主库影响更小。

Q4: INSERT ON DUPLICATE KEY UPDATE 和 REPLACE INTO 的区别？​

答案：

对比INSERT ... ON DUPLICATE KEY UPDATEREPLACE INTO
冲突处理更新已有行删除旧行 + 插入新行
自增 ID不变新 ID（旧行被删，自增 +1）
触发器触发 INSERT 或 UPDATE触发 DELETE + INSERT
外键不影响可能触发级联删除

推荐用 ON DUPLICATE KEY UPDATE，避免 REPLACE INTO 的副作用。

相关链接​