MySQL 批量插入详解：快速提升大数据导入效率的实战方法

文章目录

- 一、批量插入的优势
- 二、MySQL 表的创建示例
- 三、Python 实现批量插入
- - 1. 安装 PyMySQL 和 Faker 库
  - 2. 生成 1 万条随机的学生数据
  - 3. 批量插入数据到 MySQL
  - 4. 代码详解
- 四、性能优化建议
- 五、总结

在日常开发中，我们经常需要将大量数据批量插入到 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 = 1000try: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。当表的数据量很大时，每插入一个数据都会判断该值是否唯一，会导致数据插入数据越来越慢。