在日常開(kāi)發(fā)中，我們經(jīng)常需要將大量數(shù)據(jù)批量插入到 MySQL 數(shù)據(jù)庫(kù)中。然而，逐行插入（單條執(zhí)行 INSERT INTO）的方式效率較低，尤其在處理大規(guī)模數(shù)據(jù)時(shí)，會(huì)導(dǎo)致性能瓶頸。為了解決這個(gè)問(wèn)題，我們可以使用批量插入技術(shù)，顯著提升數(shù)據(jù)插入效率。本文將介紹批量插入的原理、實(shí)現(xiàn)方法，并結(jié)合 Python 和 PyMySQL 庫(kù)提供詳細(xì)的實(shí)戰(zhàn)示例。

一、批量插入的優(yōu)勢(shì)

批量插入數(shù)據(jù)有以下幾個(gè)優(yōu)點(diǎn)：

1. 減少網(wǎng)絡(luò)交互：批量插入一次性傳輸多條記錄，減少客戶(hù)端與數(shù)據(jù)庫(kù)之間的網(wǎng)絡(luò)通信次數(shù)。
2. 提高事務(wù)效率：批量插入可以減少事務(wù)的提交次數(shù)，從而降低事務(wù)管理的開(kāi)銷(xiāo)。
3. 提高插入性能：批量插入可以有效地降低數(shù)據(jù)庫(kù)的鎖定資源時(shí)間，使插入操作更高效。

二、MySQL 表的創(chuàng)建示例

我們以學(xué)生信息表為例，假設(shè)有如下的表結(jié)構(gòu)：

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    age INT,
    gender ENUM('M', 'F'),
    grade VARCHAR(10)
);

表 students 用于存儲(chǔ)學(xué)生的基本信息，包括 id（主鍵），name（姓名），age（年齡），gender（性別），以及 grade（成績(jī)）。

三、Python 實(shí)現(xiàn)批量插入

接下來(lái)，我們使用 Python 的 PyMySQL 庫(kù)來(lái)連接 MySQL，并實(shí)現(xiàn)批量插入數(shù)據(jù)。

1. 安裝 PyMySQL 和 Faker 庫(kù)

首先，確保已經(jīng)安裝了 PyMySQL 和 Faker 庫(kù)。如果尚未安裝，可以使用以下命令進(jìn)行安裝：

pip install pymysql faker

2. 生成 1 萬(wàn)條隨機(jī)的學(xué)生數(shù)據(jù)

使用 Faker 庫(kù)生成隨機(jī)的學(xué)生信息數(shù)據(jù)，包括姓名、年齡、性別和成績(jī)。以下是生成數(shù)據(jù)的代碼：

import random
from faker import Faker
# 初始化 Faker
fake = Faker()
# 隨機(jī)生成學(xué)生數(shù)據(jù)
def generate_random_students(num_records=10000):
    students_data = []
    for _ in range(num_records):
        name = fake.name()
        age = random.randint(18, 25)  # 隨機(jī)年齡在 18 到 25 歲之間
        gender = random.choice(['M', 'F'])  # 隨機(jī)選擇性別
        grade = random.choice(['A', 'B', 'C', 'D', 'F'])  # 隨機(jī)選擇成績(jī)
        students_data.append((name, age, gender, grade))
    return students_data
# 生成 1 萬(wàn)條學(xué)生數(shù)據(jù)
students_data = generate_random_students(10000)
# 輸出前 5 條數(shù)據(jù)查看
for student in students_data[:5]:
    print(student)

3. 批量插入數(shù)據(jù)到 MySQL

批量插入的核心思路是將數(shù)據(jù)分成若干批次，使用 executemany 方法執(zhí)行批量插入操作。下面是批量插入的完整代碼：

import pymysql
from tqdm import tqdm
# 創(chuàng)建數(shù)據(jù)庫(kù)連接
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)
            # 當(dāng)批次達(dá)到 BATCH_SIZE 時(shí)執(zhí)行批量插入
            if len(batch) >= BATCH_SIZE:
                sql = """
                INSERT INTO students (name, age, gender, grade)
                VALUES (%s, %s, %s, %s)
                """
                cursor.executemany(sql, batch)
                batch = []  # 清空批次
        # 插入剩余的未滿(mǎn)批次的數(shù)據(jù)
        if batch:
            sql = """
            INSERT INTO students (name, age, gender, grade)
            VALUES (%s, %s, %s, %s)
            """
            cursor.executemany(sql, batch)
        # 提交事務(wù)
        connection.commit()
except Exception as e:
    print(f"插入數(shù)據(jù)時(shí)出現(xiàn)錯(cuò)誤: {e}")
    connection.rollback()
finally:
    # 關(guān)閉數(shù)據(jù)庫(kù)連接
    connection.close()

4. 代碼詳解

1. 生成隨機(jī)數(shù)據(jù)：使用 generate_random_students 函數(shù)生成 1 萬(wàn)條隨機(jī)學(xué)生數(shù)據(jù)，并存儲(chǔ)在 students_data 列表中。
2. 數(shù)據(jù)庫(kù)連接：使用 PyMySQL 連接到 MySQL 數(shù)據(jù)庫(kù)，并禁用自動(dòng)提交模式，以便手動(dòng)管理事務(wù)。
3. 批量插入：
   • 將數(shù)據(jù)分成大小為 BATCH_SIZE 的批次進(jìn)行插入操作。
   • 使用 cursor.executemany 方法批量插入每個(gè)批次的數(shù)據(jù)，這樣可以減少 SQL 執(zhí)行次數(shù)，提高效率。
4. 處理剩余數(shù)據(jù)：如果數(shù)據(jù)量不足一個(gè)批次，最后將剩余數(shù)據(jù)插入。
5. 事務(wù)管理：在插入成功后調(diào)用 connection.commit() 提交事務(wù)，如果發(fā)生錯(cuò)誤則進(jìn)行回滾。
6. 關(guān)閉連接：無(wú)論操作是否成功，都需要關(guān)閉數(shù)據(jù)庫(kù)連接。

四、性能優(yōu)化建議

1. 調(diào)整批次大小：可以根據(jù)具體的硬件和數(shù)據(jù)量情況，適當(dāng)調(diào)整批次大小（BATCH_SIZE），通常 500 到 1000 條為一個(gè)批次較為合適。
2. 禁用自動(dòng)提交：將自動(dòng)提交模式禁用（connection.autocommit(False)），可以提高插入效率。
3. 刪除或禁用索引：在大量數(shù)據(jù)插入時(shí)，可以暫時(shí)禁用或刪除表上的索引，插入完成后再重新建立索引。
4. 批量插入語(yǔ)句優(yōu)化：可以將 INSERT INTO 語(yǔ)句改為 INSERT IGNORE 或 INSERT ON DUPLICATE KEY UPDATE 來(lái)處理主鍵沖突的情況。
5. unique: 盡量少用unique。當(dāng)表的數(shù)據(jù)量很大時(shí)，每插入一個(gè)數(shù)據(jù)都會(huì)判斷該值是否唯一，會(huì)導(dǎo)致數(shù)據(jù)插入數(shù)據(jù)越來(lái)越慢。

五、總結(jié)

批量插入是提高 MySQL 數(shù)據(jù)插入性能的重要手段。通過(guò)使用批量插入技術(shù)，可以顯著減少 SQL 執(zhí)行次數(shù)，提高數(shù)據(jù)導(dǎo)入的效率。本文通過(guò)一個(gè)學(xué)生信息表的實(shí)戰(zhàn)示例，詳細(xì)介紹了批量插入的實(shí)現(xiàn)方法，并提供了性能優(yōu)化的建議。希望這篇文章對(duì)您在處理大規(guī)模數(shù)據(jù)時(shí)有所幫助。

如果有更復(fù)雜的數(shù)據(jù)處理需求，您還可以考慮使用 MySQL 的 LOAD DATA 語(yǔ)句或?qū)ｉT(mén)的 ETL 工具來(lái)進(jìn)行數(shù)據(jù)導(dǎo)入操作。

到此這篇關(guān)于MySQL 批量插入的原理和實(shí)戰(zhàn)方法(快速提升大數(shù)據(jù)導(dǎo)入效率)的文章就介紹到這了,更多相關(guān)mysql批量插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論