mysql多表批量更新语句 mysql大批量更新数据

转载

mob6454cc61981e 2024-06-12 08:39:45

文章标签 mysql多表批量更新语句 mysql 数据库 hive python 文章分类 MySQL 数据库

大批量插入数据到MySQL时，为了提高效率并减少对数据库的负担，可以采用以下六种策略：

1. 批量插入（Batch Insert）

使用单个 INSERT INTO 语句插入多行数据，而不是为每一行数据单独执行一次 INSERT。这样可以显著减少网络往返时间和数据库处理单个请求的开销。

INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1_1, value2_1, value3_1),
    (value1_2, value2_2, value3_2),
    -- 更多行...
    (value1_n, value2_n, value3_n);

注：虽然批量插入可以显著提升效率，但也要注意单个 INSERT 语句的大小不要过大，以免超出数据库的最大允许包大小或造成内存压力。通常，批量大小可以根据实际情况调整，在数百到数千行之间较为常见。

2. LOAD DATA INFILE

对于非常大的数据集，使用 LOAD DATA INFILE 命令直接从文件中快速加载数据到表中，通常比使用 INSERT 语句更为高效。这种方法适用于数据已经存在于本地文件（如CSV、TXT等格式）的情况。

LOAD DATA INFILE '/path/to/datafile.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS; -- 如果第一行是列名，忽略

根据实际文件格式调整字段分隔符、行分隔符、是否忽略首行（如果首行为列名）等参数。

3. 事务处理

如果数据需要按批次插入，并且数据库支持事务（如InnoDB引擎），可以将一批数据的插入操作放在一个事务中。这有助于确保数据一致性，并减少日志写入次数，进一步提升性能。

START TRANSACTION;
-- 批量插入语句或 LOAD DATA INFILE 命令
COMMIT;

4. 关闭索引更新

在插入大量数据之前，暂时禁用非唯一索引的更新（对于InnoDB，可以使用 ALTER TABLE ... DISABLE KEYS），待数据插入完毕后再重新启用（ALTER TABLE ... ENABLE KEYS）。这样可以避免在插入过程中频繁更新索引，显著加快插入速度，但请注意，这会暂时影响查询性能。