插入 3 萬條數(shù)據(jù)要 20 多秒，確實(shí)太慢了——這通常是因?yàn)?strong>每次插入單獨(dú)提交事務(wù)、沒有使用批量插入、索引維護(hù)開銷大、配置未優(yōu)化等原因?qū)е碌摹?/p>

本文將從代碼層面、配置層面、架構(gòu)層面三個(gè)維度，給出一套可落地的快速插入優(yōu)化方案，幫你把插入速度從 20 秒提升到毫秒級(jí)，甚至更快。

前置核心認(rèn)知：為什么你的插入這么慢

在開始優(yōu)化之前，先搞清楚插入慢的核心原因，90%的慢插入都源于以下幾點(diǎn)：

一、代碼層面優(yōu)化：成本最低、效果最明顯（優(yōu)先做）

代碼層面的優(yōu)化不需要修改配置、不需要調(diào)整架構(gòu)，只需改幾行代碼，就能帶來 10-100 倍的性能提升，是第一優(yōu)先級(jí)的優(yōu)化方案。

1.1 核心優(yōu)化 1：關(guān)閉 autocommit，手動(dòng)提交事務(wù)

MySQL 默認(rèn)開啟 autocommit（自動(dòng)提交），每執(zhí)行一條 INSERT 都會(huì)自動(dòng)開啟一個(gè)事務(wù)并提交，事務(wù)提交時(shí)需要刷盤寫 redo log，開銷極大。

優(yōu)化方案：關(guān)閉 autocommit，批量插入后手動(dòng)提交事務(wù)，把 3 萬次事務(wù)提交合并成 1 次。

示例 1：Java JDBC 批量插入 + 手動(dòng)提交

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class FastInsertDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/idea_demo?serverTimezone=Asia/Shanghai&useSSL=false&rewriteBatchedStatements=true";
        String user = "idea_user";
        String password = "IdeaDemo@2026";
        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            // 1. 關(guān)閉自動(dòng)提交
            conn.setAutoCommit(false);
            String sql = "INSERT INTO user_info (username, phone, age) VALUES (?, ?, ?)";
            try (PreparedStatement ps = conn.prepareStatement(sql)) {
                // 2. 批量添加參數(shù)
                for (int i = 1; i <= 30000; i++) {
                    ps.setString(1, "用戶" + i);
                    ps.setString(2, "13800" + String.format("%06d", i));
                    ps.setInt(3, 20 + (i % 30));
                    ps.addBatch(); // 添加到批量
                    // 每 1000 條提交一次，避免大事務(wù)
                    if (i % 1000 == 0) {
                        ps.executeBatch();
                        conn.commit();
                        ps.clearBatch(); // 清空批量
                    }
                }
                // 3. 提交剩余的數(shù)據(jù)
                ps.executeBatch();
                conn.commit();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

關(guān)鍵注意：

• JDBC 連接 URL 必須添加 rewriteBatchedStatements=true，否則批量插入不會(huì)真正生效；
• 不要一次性提交 3 萬條，建議每 1000-5000 條提交一次，避免大事務(wù)導(dǎo)致鎖等待、回滾日志過大。

示例 2：MyBatis 批量插入

<!-- UserMapper.xml -->
<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO user_info (username, phone, age)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.phone}, #{item.age})
    </foreach>
</insert>

// UserService.java
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    @Transactional(rollbackFor = Exception.class)
    public void batchInsert(List<User> userList) {
        // 每 1000 條插入一次
        int batchSize = 1000;
        for (int i = 0; i < userList.size(); i += batchSize) {
            int end = Math.min(i + batchSize, userList.size());
            userMapper.batchInsert(userList.subList(i, end));
        }
    }
}

示例 3：Python pymysql 批量插入

import pymysql
def fast_insert():
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='idea_user',
        password='IdeaDemo@2026',
        database='idea_demo',
        charset='utf8mb4'
    )
    try:
        with conn.cursor() as cursor:
            # 1. 關(guān)閉自動(dòng)提交
            conn.autocommit(False)
            sql = "INSERT INTO user_info (username, phone, age) VALUES (%s, %s, %s)"
            data = []
            for i in range(1, 30001):
                data.append((f"用戶{i}", f"13800{i:06d}", 20 + (i % 30)))
                # 每 1000 條提交一次
                if i % 1000 == 0:
                    cursor.executemany(sql, data)
                    conn.commit()
                    data = []
            # 提交剩余數(shù)據(jù)
            if data:
                cursor.executemany(sql, data)
                conn.commit()
    finally:
        conn.close()
if __name__ == '__main__':
    fast_insert()

1.2 核心優(yōu)化 2：使用 LOAD DATA INFILE（最快的插入方式）

如果數(shù)據(jù)量特別大（比如 100 萬條以上），LOAD DATA INFILE 是 MySQL 最快的插入方式，它直接從文件讀取數(shù)據(jù)，跳過 SQL 解析、網(wǎng)絡(luò)往返等開銷，速度是批量 INSERT 的 10-100 倍。

步驟 1：準(zhǔn)備數(shù)據(jù)文件

先把要插入的數(shù)據(jù)保存為文本文件（CSV 格式），比如 user_data.csv：

用戶1,13800000001,25
用戶2,13800000002,30
用戶3,13800000003,28
...
用戶30000,13800030000,22

步驟 2：執(zhí)行 LOAD DATA INFILE

-- 本地文件導(dǎo)入（需要開啟 local_infile）
LOAD DATA LOCAL INFILE '/path/to/user_data.csv'
INTO TABLE user_info
FIELDS TERMINATED BY ','  -- 字段分隔符
ENCLOSED BY '"'           -- 字段包裹符（可選）
LINES TERMINATED BY '\n'  -- 行分隔符
IGNORE 1 LINES            -- 忽略第一行（如果有表頭）
(username, phone, age);   -- 對(duì)應(yīng)字段

前置配置

如果執(zhí)行報(bào)錯(cuò) The used command is not allowed with this MySQL version，需要開啟 local_infile：

-- 臨時(shí)開啟
SET GLOBAL local_infile = 1;
-- 永久開啟（修改 my.cnf/my.ini）
[mysqld]
local_infile = 1

1.3 輔助優(yōu)化：臨時(shí)禁用非唯一索引

插入大量數(shù)據(jù)時(shí)，每插入一條數(shù)據(jù)都要更新所有索引的 B+ 樹，開銷極大?？梢栽诓迦肭?strong>臨時(shí)禁用非唯一索引，插入完再重建，能大幅提升插入速度。

步驟 1：禁用非唯一索引

-- 禁用表的非唯一索引
ALTER TABLE user_info DISABLE KEYS;

步驟 2：插入數(shù)據(jù)

執(zhí)行批量插入或 LOAD DATA INFILE。

步驟 3：重建索引

-- 重建非唯一索引
ALTER TABLE user_info ENABLE KEYS;

注意：

• DISABLE KEYS 只對(duì)非唯一索引有效，唯一索引（主鍵、唯一鍵）無法禁用；
• 如果表的唯一索引太多，這個(gè)優(yōu)化效果有限。

二、配置層面優(yōu)化：進(jìn)一步提升性能

代碼層面優(yōu)化后，如果還想更快，可以調(diào)整 MySQL 配置，減少磁盤 IO 開銷，提升插入速度。

2.1 InnoDB 核心配置優(yōu)化

生產(chǎn)環(huán)境 99% 使用 InnoDB 存儲(chǔ)引擎，以下是 InnoDB 的核心優(yōu)化配置：

配置 1：innodb_buffer_pool_size（最重要）

Buffer Pool 是 InnoDB 的內(nèi)存緩存，用于緩存表數(shù)據(jù)和索引，越大越好，能大幅減少磁盤 IO。

建議值：設(shè)置為服務(wù)器內(nèi)存的 50%-75%（如果服務(wù)器只跑 MySQL）。

[mysqld]
# 比如服務(wù)器內(nèi)存 16G，設(shè)置為 10G
innodb_buffer_pool_size = 10G

配置 2：innodb_log_file_size 和 innodb_log_buffer_size

Redo Log 是 InnoDB 的事務(wù)日志，增大日志文件大小和緩沖區(qū)，能減少日志刷盤次數(shù)。

建議值：

[mysqld]
# Redo Log 文件大小，建議 1G-4G
innodb_log_file_size = 2G
# Redo Log 緩沖區(qū)，建議 16M-64M
innodb_log_buffer_size = 64M

配置 3：innodb_flush_log_at_trx_commit（權(quán)衡一致性與性能）

控制 Redo Log 的刷盤策略，對(duì)插入速度影響極大：

• 1（默認(rèn)）：每次事務(wù)提交都刷盤，最安全，性能最差；
• 0：每秒刷盤一次，性能最好，但崩潰可能丟失 1 秒數(shù)據(jù)；
• 2：寫入操作系統(tǒng)緩存，每秒刷盤一次，性能較好，崩潰可能丟失數(shù)據(jù)。

建議值：

• 生產(chǎn)環(huán)境（金融、支付等強(qiáng)一致性場(chǎng)景）：保持 1；
• 非生產(chǎn)環(huán)境（日志、統(tǒng)計(jì)等）：可以改成 0 或 2，性能提升 5-10 倍。

[mysqld]
innodb_flush_log_at_trx_commit = 2

配置 4：innodb_flush_method

控制數(shù)據(jù)和日志的刷盤方式，建議設(shè)置為 O_DIRECT，繞過操作系統(tǒng)緩存，減少雙寫開銷。

[mysqld]
innodb_flush_method = O_DIRECT

配置 5：max_allowed_packet

控制 MySQL 接收的最大數(shù)據(jù)包大小，批量插入時(shí)如果數(shù)據(jù)包太大會(huì)報(bào)錯(cuò)，建議設(shè)置為 64M-1G。

[mysqld]
max_allowed_packet = 64M

2.2 其他通用配置優(yōu)化

[mysqld]
# 關(guān)閉查詢緩存（MySQL 8.0 已移除，5.7 建議關(guān)閉）
query_cache_type = 0
query_cache_size = 0
# 臨時(shí)表大小
tmp_table_size = 64M
max_heap_table_size = 64M
# 連接數(shù)
max_connections = 1000

三、架構(gòu)層面優(yōu)化：應(yīng)對(duì)海量數(shù)據(jù)

如果數(shù)據(jù)量特別大（比如 1000 萬條以上），代碼和配置優(yōu)化后還是慢，可以考慮架構(gòu)層面的優(yōu)化。

3.1 分庫(kù)分表：分散寫入壓力

單表數(shù)據(jù)量超過 1000 萬條時(shí)，插入速度會(huì)明顯下降，可以用 ShardingSphere、MyCat 等分庫(kù)分表中間件，把數(shù)據(jù)分散到多個(gè)庫(kù)、多個(gè)表中，分散寫入壓力。

3.2 消息隊(duì)列異步寫入：減少前端等待時(shí)間

如果是 Web 應(yīng)用，用戶提交數(shù)據(jù)后不需要立即寫入數(shù)據(jù)庫(kù)，可以先把數(shù)據(jù)寫入 Kafka、RabbitMQ 等消息隊(duì)列，后臺(tái)異步批量插入數(shù)據(jù)庫(kù)，減少前端等待時(shí)間，提升用戶體驗(yàn)。

3.3 讀寫分離：分散數(shù)據(jù)庫(kù)壓力

使用主從復(fù)制，寫入主庫(kù)，讀取從庫(kù)，分散數(shù)據(jù)庫(kù)壓力，主庫(kù)可以專注于寫入，提升寫入速度。

四、避坑指南：90%的人都踩過的坑

坑1：批量插入的數(shù)據(jù)包超過 max_allowed_packet

現(xiàn)象：批量插入時(shí)報(bào)錯(cuò) Packet for query is too large。

解決方案：

• 增大 max_allowed_packet 配置（見本文 2.2 節(jié)）；
• 減小批量插入的大小，比如從每 5000 條提交一次改成每 1000 條提交一次。

坑2：InnoDB 大事務(wù)導(dǎo)致鎖等待

現(xiàn)象：批量插入時(shí)，其他查詢/更新被阻塞。

解決方案：不要一次性提交所有數(shù)據(jù)，建議每 1000-5000 條提交一次，避免大事務(wù)。

坑3：唯一索引太多，插入速度慢

現(xiàn)象：表有 5 個(gè)以上唯一索引，插入速度極慢。

解決方案：

• 評(píng)估是否真的需要這么多唯一索引，刪除不必要的；
• 如果必須保留，考慮用消息隊(duì)列異步寫入，或分庫(kù)分表。

坑4：使用 MyISAM 存儲(chǔ)引擎

現(xiàn)象：插入時(shí)表鎖，其他操作被阻塞。

解決方案：生產(chǎn)環(huán)境必須使用 InnoDB 存儲(chǔ)引擎，MyISAM 不支持事務(wù)、表鎖，不適合高并發(fā)場(chǎng)景。

五、優(yōu)化效果對(duì)比

我們用 3 萬條數(shù)據(jù)做測(cè)試，對(duì)比不同優(yōu)化方案的插入時(shí)間：

總結(jié)

MySQL 快速插入的核心優(yōu)化思路是：減少事務(wù)提交次數(shù)、減少網(wǎng)絡(luò)往返、減少索引維護(hù)開銷、減少磁盤 IO。

優(yōu)化優(yōu)先級(jí)：

• 第一優(yōu)先級(jí)：代碼層面優(yōu)化——批量插入 + 手動(dòng)提交事務(wù)，成本最低，效果最明顯；
• 第二優(yōu)先級(jí)：使用 LOAD DATA INFILE，適合從文件導(dǎo)入大量數(shù)據(jù)；
• 第三優(yōu)先級(jí)：配置層面優(yōu)化——調(diào)整 buffer pool、日志大小、刷盤策略；
• 第四優(yōu)先級(jí)：架構(gòu)層面優(yōu)化——分庫(kù)分表、消息隊(duì)列、讀寫分離，適合海量數(shù)據(jù)場(chǎng)景。

到此這篇關(guān)于MySQL中實(shí)現(xiàn)大數(shù)據(jù)快速插入的全攻略的文章就介紹到這了,更多相關(guān)MySQL大數(shù)據(jù)插入內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論