【MySQL 高级】批量插入百万级数据量

基础概念与作用说明

批量插入，顾名思义，是指一次性向数据库中插入多条记录的操作。相较于单条记录的插入，批量插入能显著减少网络传输次数和事务处理开销，从而大幅度提升数据写入效率。在数据仓库构建、数据迁移、数据初始化等场景中，批量插入技术的应用尤为关键。

准备工作与环境搭建

在开始批量插入操作前，确保你的MySQL服务器和客户端工具已准备就绪，且有足够的磁盘空间和内存资源。此外，优化服务器配置，如调整innodb_buffer_pool_size、innodb_log_file_size等参数，以适应大数据量的写入需求。

批量插入策略与技巧

示例一：使用LOAD DATA INFILE

LOAD DATA INFILE是MySQL提供的一种高效数据加载工具，它可以从文件中批量读取数据并直接插入数据库，跳过了客户端到服务器的网络传输过程，特别适合大数据量的导入。

-- 假设有一个CSV文件data.csv，包含id, name, age三列
LOAD DATA INFILE '/path/to/data.csv' INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

示例二：批量插入语句

当数据量适中，且数据已存在于内存中时，可以使用单条SQL语句批量插入多条记录。

INSERT INTO users (id, name, age) VALUES
(1, 'John Doe', 28),
(2, 'Jane Smith', 32),
...,
(N, 'User N', 22);

示例三：使用事务批量插入

通过开启事务，将多条插入操作封装在一起，可以减少每次插入时的事务处理开销，提升整体性能。

START TRANSACTION;
-- 执行批量插入语句
INSERT INTO users (id, name, age) VALUES
(1, 'John Doe', 28),
(2, 'Jane Smith', 32),
...,
(N, 'User N', 22);
COMMIT;

示例四：利用Python脚本进行批量插入

Python等脚本语言可以轻松处理大量数据，并利用其连接库（如mysql-connector-python）高效地执行批量插入操作。

import mysql.connector

db = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="yourdatabase"
)

cursor = db.cursor()
values = [(i, f'User {i}', i*2) for i in range(1, 1000001)]
query = "INSERT INTO users (id, name, age) VALUES (%s, %s, %s)"

# 批量执行插入
cursor.executemany(query, values)
db.commit()

性能优化与注意事项

• 禁用索引和外键检查：在批量插入前，可以暂时禁用索引和外键检查，待数据导入完成后重新启用，以减少插入过程中的I/O操作。
• 调整innodb参数：根据数据量和服务器配置，适当调整innodb_buffer_pool_size、innodb_log_buffer_size等参数，以提高InnoDB存储引擎的性能。
• 分批插入：对于极大数据量的插入，可以考虑分批进行，避免一次性加载过多数据导致内存溢出或数据库锁死。