Java程序导入1万条数据到MySQL的性能提升170倍！

于 2024-08-25 19:07:48 发布
阅读量492 收藏 3
点赞数 7
分类专栏： java工具类 文章标签： java mysql 开发语言
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_40697623/article/details/141533020
版权

8月22号写了一篇txt到mysql的程序，有个小老弟私信我说太慢了，今天对这段程序做了一个优化在处理大规模数据导入时，性能优化是一个至关重要的方面。本文将分享如何通过简单而有效的方式，将一个原本需要337秒导入1万条数据的Java程序优化到仅需2秒的经验

首先来看一下老程序

package practise.utils;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;

public class TxtToDatabase {

    /**
     * 将TXT文件导入到MySQL数据库中的通用方法
     *
     * @param jdbcURL  MySQL连接URL
     * @param dbUser   数据库用户名
     * @param dbPassword 数据库密码
     * @param tableName 表名
     * @param columns   列名（以逗号分隔的字符串）
     * @param filePath  TXT文件路径
     * @param delimiter TXT文件中的分隔符
     */
    public static void importTxtToDatabase(String jdbcURL, String dbUser, String dbPassword,
                                           String tableName, String columns, String filePath, String delimiter) {
        // 构造SQL插入语句
        String sql = generateInsertSQL(tableName, columns);
        
        try (Connection connection = DriverManager.getConnection(jdbcURL, dbUser, dbPassword);
             BufferedReader reader = new BufferedReader(new FileReader(filePath));
             PreparedStatement statement = connection.prepareStatement(sql)) {

            String line;
            while ((line = reader.readLine()) != null) {
                String[] data = line.split(delimiter);

                // 为PreparedStatement设置参数
                for (int i = 0; i < data.length; i++) {
                    statement.setString(i + 1, data[i]);
                }

                // 执行SQL插入命令
                statement.executeUpdate();
            }

            System.out.println("Data has been inserted successfully into table " + tableName);

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成INSERT SQL语句
     *
     * @param tableName 表名
     * @param columns   列名
     * @return 生成的SQL语句
     */
    private static String generateInsertSQL(String tableName, String columns) {
        String[] columnArray = columns.split(",");
        StringBuilder placeholders = new StringBuilder();

        for (int i = 0; i < columnArray.length; i++) {
            placeholders.append("?");
            if (i < columnArray.length - 1) {
                placeholders.append(",");
            }
        }

        return "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders.toString() + ")";
    }

    public static void main(String[] args) {
        // 示例参数
        String jdbcURL = "jdbc:mysql://localhost:3306/zhenbutong";
        String dbUser = "root";
        String dbPassword = "root";
        String tableName = "user";
        String columns = "id,username,password";
        String filePath = "D:\\tmp\\user.txt";
        String delimiter = "\\|";

        LocalDateTime startTime = LocalDateTime.now();
        System.out.println("开始执行：" + startTime);
        
        // 调用通用方法导入数据
        importTxtToDatabase(jdbcURL, dbUser, dbPassword, tableName, columns, filePath, delimiter);
        
        LocalDateTime endTime = LocalDateTime.now();
        System.out.println("执行结束：" + endTime);
        
        // 计算执行时长（以秒为单位）
        Duration duration = Duration.between(startTime, endTime);
        long seconds = duration.getSeconds();
        System.out.println("总共执行了：" + seconds + " 秒");

    }
}

这段程序执行的结果是337秒

初始方案与问题分析

原始代码逐条将TXT文件中的数据插入到MySQL数据库。这种方法简单直接，但当数据量大时，性能问题凸显。逐条插入不仅增加了数据库的网络开销，还导致事务处理效率低下。

以下是原始代码的核心逻辑：

while ((line = reader.readLine()) != null) {
    String[] data = line.split(delimiter);

    for (int i = 0; i < data.length; i++) {
        statement.setString(i + 1, data[i]);
    }

    statement.executeUpdate();
}

问题：每插入一行数据，程序都会向数据库发送一次请求并执行一次事务提交，这样的频繁交互导致了极大的性能损耗。

优化策略

要解决这个问题，我们采用了以下策略：

  1. 批量插入（Batch Insert）：将多条SQL语句放入批处理中，然后一次性执行。这减少了数据库交互的次数，大幅提高了插入效率。

  2. 关闭自动提交：将数据库连接的自动提交功能关闭，手动管理事务提交。这样做可以减少数据库在处理每条记录时的事务开销。

  3. 调整批处理大小：在我们的示例中，选择了每500条记录执行一次批处理，这个数字根据实际环境可以调整。

优化后的代码实现

以下是优化后的代码实现：

package practise.utils;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.Duration;
import java.time.LocalDateTime;

public class TxtToDatabaseBatch {

    /**
     * 将TXT文件导入到MySQL数据库中的通用方法
     *
     * @param jdbcURL  MySQL连接URL
     * @param dbUser   数据库用户名
     * @param dbPassword 数据库密码
     * @param tableName 表名
     * @param columns   列名（以逗号分隔的字符串）
     * @param filePath  TXT文件路径
     * @param delimiter TXT文件中的分隔符
     */
    public static void importTxtToDatabase(String jdbcURL, String dbUser, String dbPassword,
                                           String tableName, String columns, String filePath, String delimiter) {
        // 构造SQL插入语句
        String sql = generateInsertSQL(tableName, columns);
        
        try (Connection connection = DriverManager.getConnection(jdbcURL, dbUser, dbPassword);
             BufferedReader reader = new BufferedReader(new FileReader(filePath));
             PreparedStatement statement = connection.prepareStatement(sql)) {

            connection.setAutoCommit(false); // 关闭自动提交，手动管理事务

            String line;
            int batchSize = 500; // 批处理大小
            int count = 0;

            while ((line = reader.readLine()) != null) {
                String[] data = line.split(delimiter);

                // 为PreparedStatement设置参数
                for (int i = 0; i < data.length; i++) {
                    statement.setString(i + 1, data[i]);
                }

                statement.addBatch(); // 添加到批处理中
                count++;

                if (count % batchSize == 0) {
                    statement.executeBatch(); // 批量执行
                    connection.commit(); // 提交事务
                }
            }

            // 处理剩余的数据
            statement.executeBatch();
            connection.commit();

            System.out.println("Data has been inserted successfully into table " + tableName);

        } catch (SQLException | IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成INSERT SQL语句
     *
     * @param tableName 表名
     * @param columns   列名
     * @return 生成的SQL语句
     */
    private static String generateInsertSQL(String tableName, String columns) {
        String[] columnArray = columns.split(",");
        StringBuilder placeholders = new StringBuilder();

        for (int i = 0; i < columnArray.length; i++) {
            placeholders.append("?");
            if (i < columnArray.length - 1) {
                placeholders.append(",");
            }
        }

        return "INSERT INTO " + tableName + " (" + columns + ") VALUES (" + placeholders.toString() + ")";
    }

    public static void main(String[] args) {
        // 示例参数
        String jdbcURL = "jdbc:mysql://localhost:3306/zhenbutong";
        String dbUser = "root";
        String dbPassword = "root";
        String tableName = "user";
        String columns = "id,username,password";
        String filePath = "D:\\tmp\\user1.txt";
        String delimiter = "\\|";

        LocalDateTime startTime = LocalDateTime.now();
        System.out.println("开始执行：" + startTime);
        
        // 调用通用方法导入数据
        importTxtToDatabase(jdbcURL, dbUser, dbPassword, tableName, columns, filePath, delimiter);
        
        LocalDateTime endTime = LocalDateTime.now();
        System.out.println("执行结束：" + endTime);
        
        // 计算执行时长（以秒为单位）
        Duration duration = Duration.between(startTime, endTime);
        long seconds = duration.getSeconds();
        System.out.println("总共执行了：" + seconds + " 秒");

    }
}

优化后的执行结果

优化效果

在应用以上优化后，程序的执行时间从337秒锐减至2秒，性能提升近170倍。这一提升不仅显著减少了处理时间，还提高了程序的可扩展性，使其能够更高效地处理更大规模的数据集。

性能优化的思考

  1. 批量处理与批次大小：批量处理的核心在于减少数据库的交互次数。根据系统资源和数据库性能，合理设置批处理大小能够避免内存溢出，并最大化性能。

  2. 事务管理：合理的事务管理可以减少数据库在处理每次插入时的开销。在大规模插入操作中，批量提交事务比每条数据都提交要高效得多。

  3. 数据库配置与硬件资源：在实际应用中，还可以通过调整数据库的配置（如innodb_buffer_pool_size、innodb_log_file_size等）和优化硬件资源（如内存、磁盘I/O等），进一步提升性能。

总结

通过批量插入和事务管理，本文展示了如何有效提升Java程序在大规模数据导入时的性能。虽然本文案例以MySQL为例，但这些优化策略同样适用于其他关系型数据库。在日常开发中，我们应始终关注性能瓶颈，并通过合理的优化手段来提升程序效率。希望这些经验能为您在项目中的性能优化带来启发。

项目笔记与工具库
关注
专栏目录
Java如何快速向MySQL插入百万条数据
mirage_zy的博客
04-25 1555
1.使用批量提交数据，url一定要设置允许重写批量提交【rewriteBatchedStatements=true】，以及此时的sql语句一定不能有分号，否则有【BatchUpdateException】异常，2.其他的就正常使用PreparedStatement ps;的以下三个方法即可。
Java千万数据导入mysql_java之5分钟插入千万条数据
weixin_42565755的博客
02-16 1535
虽说不一定5分钟就插入完毕，因为取决去所插入的字段，如果字段过多会稍微慢点，但不至于太慢。10分钟内基本能看到结果。之前我尝试用多线程来实现数据插入(百万条数据)，半个多小时才二十多万条数据。线程池数据插入核心代码:ExecutorService executorService = Executors.newFixedThreadPool();executorService.submit(new ...
Java 实现从Excel批量导入数据到MySQL
03-14
实现从Excel批量导入数据到MySQL 数据库中，可以同时导入多张Excel表，使用时在excel.table 中配置需要导入的表及可！
快速插入大量数据到MySQL
Mr朱鹏的博客
03-08 475
快速插入大量数据到MySQL解决办法：load data local infile
java使用程序实现插入数据库
最新发布
weixin_42084448的博客
08-17 108
我整理的一些关于【数据】的项目学习资料（附讲解～～）和大家一起分享、学习一下：https://d.51cto.com/eDOcp1Java程序实现插入数据库 在现代软件开发中，数据库的使用是必不可少的。Java作为一种广泛使用的编程语言，其强大的库支持使得与数据库进行交互变得非常简单。本文将介绍如何在Java中实现数据插...
Java中大数据多线程导入MySQL性能测试
鸟人的博客
07-12 846
多线程可以有效地提高业务效率，下面模拟向MySQL分批次写入1000万条记录，看看使用多线程和不使用多线程，耗费时间的差距CPU 4核8线程内存 16G 2400MHz使用MyBatis向MySQL数据插入数据，线程池初始大小20，分10轮写入，每轮写入10万条数据，总计100万，由于MyBatis无法承载太多数据，所以每轮10万条数据分200批次写入MySQL数据库，每批次500条记录 100万数据性能测试 多线程 耗时74s耗时204s使用多线程性能几乎提升3倍.....................
java连接mysql，一次插入百万数据的优化方式
wcx2019110035的博客
12-10 862
1.本地连接的mysql，数据是随便生成的，这点找gpt就能做2.100w条数据写入速度有显著提升：90s-11s（本机）
教你如何6秒钟往MySQL插入100万条数据的实现
09-08
综上所述，通过使用`PreparedStatement`和开启`rewriteBatchedStatements`，以及合理的数据库配置和优化策略，可以有效地实现高效的大规模数据插入，如6秒钟内插入100万条数据到MySQL数据库。这样的实践对于大数据...
88秒插入1000万条数据到MySQL数据库表的操作方法
09-09
在本教程中，我们将探讨如何在88秒内将1000万条数据高效地插入到MySQL数据库表中，使用的数据库版本为5.7。首先，遇到的问题是`PacketTooBigException`，这是由于单个数据包大小超过了MySQL服务器允许的最大值（默认...
java往mysql中插入10万条数据或者100万条数据
aaaaaaddddsssss的博客
06-19 2719
插入数据sql代码，java代码转载自----》》https://blog.csdn.net/jason_m_ho/article/details/82941297，本人亲测运行有效 mysql代码，先创建一个testdb数据库在运行下面sql语句创建tb_data表，需要mysql-connector-java-5.1.21.jar连接数据库的jar包 CREATE TABLE `tb_data` ( `id` INT(11) DEFAULT NULL, `user_name` VARCHAR(1
JAVA使用JDBC进行insert操作添加信息到数据库
08-25
JAVA使用JDBC进行insert操作添加信息到数据库
Mysql 数据库插入 1w 条数据用了多久？
dj1540225203的博客
09-07 6084
最近做个批量获取客户公众号粉丝的功能，但是客户粉丝量过万的时候，需要做批量插入处理，但微信每次返回1万。 微信接口文档：https://developers.weixin.qq.com/doc/offiaccount/User_Management/Getting_a_User_List.html public function actionGetOpenidList() { set_time_limit(0); date_default_timezone_s
java大数据量插入数据库批量提交
qq_41060647的博客
07-26 634
1、TestVo.java(测试实体类)2、Test.java(测试类)
java批量提交_JAVA批量插入数据操作+事务提交
weixin_33171271的博客
02-15 925
packagecom.eg.egsc.egc.mapanalysisapp;importjava.sql.Connection;importjava.sql.Date;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.SQLException;importjava.util.Random;/**...
10亿数据高效插入MySQL最佳方案
竹林幽深
01-17 541
我们直接引入本文要探讨的问题，现在要求用最快的速度把10亿条左右的数据存到数据库中，对应的该实现有着以下几个要求:每条数据转为数据库数据大约1k。数据都存在txt文档中，需要进行解析后才能存到数据库中。所有数据对应的文档都存在Hdfs或S3分布式文件存储里。数据被分到100个文件中，通过文件的后缀确定这些数据的批次。要求保证有序导入，数据尽可能不重复。存储的数据库是MySQL。
【JDBC】-- Java向数据库批量插入数据操作
热门推荐
张修宇的博客
07-22 1万+
在Java项目中可能会出现大量向数据库中插入的情况，下面有四种方式实现批量插入，四种方式效率由低到高、循序渐进。下面使用idea+Mysql8。
Java课程设计：连接MySQL与数据插入性能分析
"该资源是一个Java课程设计项目，旨在实现与MySQL数据库的连接，并进行数据的插入操作。项目包括创建数据库、表格，以及通过三种方式（一次插入一条、一次插入多条、批处理）生成并输入随机数据。此外，还记录了不同...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值