MySQL 批量插入的原理和实战方法(快速提升大数据导入效率)_Mysql

在日常开发中，我们经常需要将大量数据批量插入到 mysql 数据库中。然而，逐行插入（单条执行 insert into）的方式效率较低，尤其在处理大规模数据时，会导致性能瓶颈。为了解决这个问题，我们可以使用批量插入技术，显著提升数据插入效率。本文将介绍批量插入的原理、实现方法，并结合 python 和 pymysql 库提供详细的实战示例。

一、批量插入的优势

批量插入数据有以下几个优点：

减少网络交互：批量插入一次性传输多条记录，减少客户端与数据库之间的网络通信次数。
提高事务效率：批量插入可以减少事务的提交次数，从而降低事务管理的开销。
提高插入性能：批量插入可以有效地降低数据库的锁定资源时间，使插入操作更高效。

二、mysql 表的创建示例

我们以学生信息表为例，假设有如下的表结构：

create table students (
    id int primary key auto_increment,
    name varchar(100),
    age int,
    gender enum('m', 'f'),
    grade varchar(10)
);

表 students 用于存储学生的基本信息，包括 id（主键），name（姓名），age（年龄），gender（性别），以及 grade（成绩）。

三、python 实现批量插入

接下来，我们使用 python 的 pymysql 库来连接 mysql，并实现批量插入数据。

1. 安装 pymysql 和 faker 库

首先，确保已经安装了 pymysql 和 faker 库。如果尚未安装，可以使用以下命令进行安装：

pip install pymysql faker

2. 生成 1 万条随机的学生数据

使用 faker 库生成随机的学生信息数据，包括姓名、年龄、性别和成绩。以下是生成数据的代码：

import random
from faker import faker
# 初始化 faker
fake = faker()
# 随机生成学生数据
def generate_random_students(num_records=10000):
    students_data = []
    for _ in range(num_records):
        name = fake.name()
        age = random.randint(18, 25)  # 随机年龄在 18 到 25 岁之间
        gender = random.choice(['m', 'f'])  # 随机选择性别
        grade = random.choice(['a', 'b', 'c', 'd', 'f'])  # 随机选择成绩
        students_data.append((name, age, gender, grade))
    return students_data
# 生成 1 万条学生数据
students_data = generate_random_students(10000)
# 输出前 5 条数据查看
for student in students_data[:5]:
    print(student)

3. 批量插入数据到 mysql

批量插入的核心思路是将数据分成若干批次，使用 executemany 方法执行批量插入操作。下面是批量插入的完整代码：

import pymysql
from tqdm import tqdm
# 创建数据库连接
connection = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.dictcursor
)
# 批量插入的批次大小
batch_size = 1000
try:
    with connection.cursor() as cursor:
        batch = []
        for student in tqdm(students_data, total=len(students_data)):
            batch.append(student)
            # 当批次达到 batch_size 时执行批量插入
            if len(batch) >= batch_size:
                sql = """
                insert into students (name, age, gender, grade)
                values (%s, %s, %s, %s)
                """
                cursor.executemany(sql, batch)
                batch = []  # 清空批次
        # 插入剩余的未满批次的数据
        if batch:
            sql = """
            insert into students (name, age, gender, grade)
            values (%s, %s, %s, %s)
            """
            cursor.executemany(sql, batch)
        # 提交事务
        connection.commit()
except exception as e:
    print(f"插入数据时出现错误: {e}")
    connection.rollback()
finally:
    # 关闭数据库连接
    connection.close()

4. 代码详解

生成随机数据：使用 generate_random_students 函数生成 1 万条随机学生数据，并存储在 students_data 列表中。
数据库连接：使用 pymysql 连接到 mysql 数据库，并禁用自动提交模式，以便手动管理事务。
批量插入：
- 将数据分成大小为 batch_size 的批次进行插入操作。
- 使用 cursor.executemany 方法批量插入每个批次的数据，这样可以减少 sql 执行次数，提高效率。
处理剩余数据：如果数据量不足一个批次，最后将剩余数据插入。
事务管理：在插入成功后调用 connection.commit() 提交事务，如果发生错误则进行回滚。
关闭连接：无论操作是否成功，都需要关闭数据库连接。

四、性能优化建议

调整批次大小：可以根据具体的硬件和数据量情况，适当调整批次大小（batch_size），通常 500 到 1000 条为一个批次较为合适。
禁用自动提交：将自动提交模式禁用（connection.autocommit(false)），可以提高插入效率。
删除或禁用索引：在大量数据插入时，可以暂时禁用或删除表上的索引，插入完成后再重新建立索引。
批量插入语句优化：可以将 insert into 语句改为 insert ignore 或 insert on duplicate key update 来处理主键冲突的情况。
unique: 尽量少用unique。当表的数据量很大时，每插入一个数据都会判断该值是否唯一，会导致数据插入数据越来越慢。