MySQL性能优化：高效批量插入数据

MySQL性能优化：高效批量插入数据

引用

CSDN

等

9

来源

1.

https://m.blog.csdn.net/weixin_39914499/article/details/113211465

2.

https://blog.csdn.net/weixin_42540829/article/details/84099486

3.

https://m.blog.csdn.net/weixin_28958733/article/details/113196202

4.

https://blog.csdn.net/yimenglin/article/details/107705997

5.

https://www.cnblogs.com/scarlettxu/p/3405349.html

6.

https://learn.microsoft.com/zh-CN/previous-versions/sql/2014/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-2014

7.

https://m.php.cn/faq/576995.html

8.

https://m.php.cn/faq/339381.html

9.

https://www.cnblogs.com/end/archive/2011/05/18/2049953.html

在现代应用程序开发中，数据库性能优化是一个永恒的话题。特别是在处理大量数据插入操作时，如何提高效率、减少资源消耗成为开发者们关注的重点。本文将分享一些实用的MySQL批量插入数据的优化技巧，帮助你显著提升数据插入速度。

在数据库操作中，每次插入操作都需要经历网络传输、解析SQL语句、执行插入、更新索引等多个步骤。如果每次只插入一条记录，那么这些开销就会被重复执行，导致整体性能下降。而批量插入可以将多条记录一次性发送给数据库，显著减少这些开销。

1. 减少连接开销：每次与数据库建立连接都需要初始化和权限验证等步骤，批量插入可以降低这部分开销。
2. 提升效率：更新多条记录的耗时几乎与单条记录相当，批量处理能显著提高吞吐量。

1. 合理设置批大小

批大小并非越大越好，需要根据具体场景测试找到最优值。通常情况下，50-100条记录为一批较为合适。过大的批大小可能会导致内存消耗过高，而过小则无法充分发挥批量插入的优势。

2. 使用事务管理

关闭自动提交并手动管理事务可以进一步提升性能。在开始插入前执行SET autocommit = 0;，在所有数据插入完成后执行COMMIT;。

3. 优化SQL语句

使用INSERT INTO table VALUES (...),(...),...的格式进行批量插入。避免使用单条INSERT语句，因为每条语句都会产生额外的开销。

4. 引擎特性优化

• MyISAM引擎：可通过禁用索引（ALTER TABLE tb_name DISABLE KEYS;）和唯一性检查（SET UNIQUE_CHECKS=0;）来加速插入。
• InnoDB引擎：除了上述方法外，还可通过调整配置参数（如innodb_buffer_pool_size、bulk_insert_buffer_size等）优化性能。

以下是一个Java示例，展示如何分批插入数据：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;

public class BatchInsertExample {
    public static void main(String args) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "password";

        List<String> dataList = new ArrayList<>();
        for (int i = 0; i < 25; i++) {
            dataList.add("Item " + i);
        }

        int batchSize = 10;

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(false); // 关闭自动提交
            String sql = "INSERT INTO your_table (column_name) VALUES (?)";
            PreparedStatement pstmt = conn.prepareStatement(sql);

            for (int i = 0; i < dataList.size(); i++) {
                pstmt.setString(1, dataList.get(i));
                pstmt.addBatch();

                if ((i + 1) % batchSize == 0 || (i + 1) == dataList.size()) {
                    pstmt.executeBatch();
                    conn.commit(); // 提交事务
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

1. 合理设置批大小：批大小并非越大越好，需根据场景测试找到最优值。例如，在某些情况下，50-100条记录为一批较为合适。
2. 使用事务：关闭自动提交并手动管理事务可以进一步提升性能。
3. 优化SQL语句：
   • 使用INSERT INTO table VALUES (...),(...),...格式进行批量插入。
   • 对于MyISAM引擎，可通过禁用索引和唯一性检查来加速插入。
   • 对于InnoDB引擎，除了上述方法外，还可通过调整配置参数优化性能。

通过以上方法，你可以显著提升MySQL数据库的数据插入性能。无论是处理千万级的数据还是日常开发任务，这些技巧都能让你事半功倍。快来尝试这些优化方法，让你的MySQL数据库飞速运转吧！