`

数据库批量数据插入问题分析

 
阅读更多

在数据库的相关开发中，经常会遇到数据的批量插入问题。本文主要是通过实验的方式探讨批量数据插入的瓶颈，以及优化建议。


以10w条记录的插入作为实验对象，采用下面的几种方法插入：

1.       普通插入：普通的一条条插入

2.       普通插入+手动提交：setAutoCommit(false)、commit（）

3.       普通插入+手动提交+ prepareStatement方式

4.       批量插入：addBatch、executeBatch

5.       批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,

6.       多线程插入。

7.       InnoDB引擎和MyISAM引擎的比较。


实验环境：

数据库：MySQL 5.0

机器硬件：

内存 3G 

CPU AMD双核4400+ 2.3G

首先建立一个简单的user表：

CREATE TABLE `user` (                                         

          `id` varchar(50) NOT NULL,                                  

          `seqid` bigint(20) NOT NULL auto_increment,                 

          `name` varchar(50) NOT NULL,                                 

          PRIMARY KEY (`seqid`)

        ) ENGINE=MyISAM DEFAULT CHARSET=utf8

一、普通插入
代码：

 1Connection conn=source.getConnection();
 2Statement s=conn.createStatement();
 3String sql="";
 4long start=System.currentTimeMillis();
 5for(int i=0;i<100000;i++)
 6{
 7    sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
 8    s.execute(sql);
 9}
10s.close();
11conn.close();
12long end=System.currentTimeMillis();
13System.out.println("commonInsert()执行时间为："+(end-start)+"ms");
输出结果：
commonInsert()执行时间为：13828ms

二、普通插入+手动提交：setAutoCommit(false)、commit()
代码：
 1Connection conn=source.getConnection();
 2conn.setAutoCommit(false);
 3Statement s=conn.createStatement();
 4String sql="";
 5long start=System.currentTimeMillis();
 6for(int i=0;i<100000;i++)
 7{
 8   sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
 9   s.execute(sql);
10}
11conn.commit();
12s.close();
13conn.close();
14long end=System.currentTimeMillis();
15System.out.println("commonInsert()执行时间为："+(end-start)+"ms");
输出结果：
commonInsert()执行时间为：13813ms
对比分析：
可以看出，仅仅是这种方式的设置，对性能的影响并不大。

三、普通插入+手动提交+ prepareStatement方式
代码：
 1Connection conn=source.getConnection();
 2        conn.setAutoCommit(false);
 3        PreparedStatement ps=conn.prepareStatement("insert into user(id,name) value(?,?)");
 4        long start=System.currentTimeMillis();
 5        for(int i=0;i<100000;i++)
 6        {
 7            ps.setString(1,(i+1)+"");
 8            ps.setString(2,"chenxinhan");
 9            ps.execute();
10        }
11        conn.commit();
12        ps.close();
13        conn.close();
14        long end=System.currentTimeMillis();
15        System.out.println("prepareStatementInsert()执行时间为："+(end-start)+"ms");
输出结果：
prepareStatementInsert()执行时间为：12797ms
对比分析：
采用prepareStatement的方式确实可以提高一点性能，因为减少了数据库引擎解析优化SQL语句的时间，但是由于现在的插入语句太简单，所以性能提升不明显。

四、批量插入：addBatch、executeBatch
代码：
 1        Connection conn=source.getConnection();
 2        conn.setAutoCommit(false);
 3        Statement s=conn.createStatement();
 4        String sql="";
 5        long start=System.currentTimeMillis();
 6        for(int j=0;j<100;j++)
 7        {
 8            for(int i=0;i<1000;i++)
 9            {
10                sql="insert into user(id,name) value('"+(i+1)+"','chenxinhan')";
11                s.addBatch(sql);
12            }
13            s.executeBatch();
14            conn.commit();
15            s.clearBatch();
16        }
17        s.close();
18        conn.close();
19        long end=System.currentTimeMillis();
20        System.out.println("batchInsert()执行时间为："+(end-start)+"ms");
输出结果：
batchInsert()执行时间为：13625ms
对比分析：
按道理，这种批处理的方式是要快些的，但是测试结果却不尽人意，有点不解，请高人拍砖。

五、批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,
代码：
 1Connection conn=source.getConnection();
 2        conn.setAutoCommit(false);
 3        Statement s=conn.createStatement();
 4        StringBuilder sql=new StringBuilder("");
 5        long start=System.currentTimeMillis();
 6        for(int j=0;j<100;j++)
 7        {
 8            sql=new StringBuilder("");
 9            sql.append("insert into user(id,name) values ");
10            for(int i=0;i<1000;i++)
11            {
12                if(i==999)
13                    sql.append("('").append(i+1).append("',").append("'cxh')");
14                else
15                    sql.append("('").append(i+1).append("',").append("'cxh'),");
16            }
17            s.execute(sql.toString());
18            conn.commit();
19        }
20        s.close();
21        conn.close();
22        long end=System.currentTimeMillis();
23        System.out.println("manyInsert()执行时间为："+(end-start)+"ms");
输出结果：
manyInsert()执行时间为：937ms
对比分析：
发现采用这种方式的批量插入性能提升最明显，有10倍以上的性能提升。所以这种方式是我推荐的批量插入方式！

六、多线程插入
在第五种方式的基础上采用多线程插入。
代码：
 1final Connection conn=source.getConnection();
 2        for(int j=0;j<3;j++)
 3        {
 4            Thread t=new Thread(){
 5                @Override
 6                public void run() {
 7                    try
 8                    {
 9                        long start=System.currentTimeMillis();
10                        Statement s=conn.createStatement();
11                        StringBuilder sql=new StringBuilder("");
12                        for(int j=0;j<100;j++)
13                        {
14                            conn.setAutoCommit(false);
15                            sql=new StringBuilder("");
16                            sql.append("insert into user (id,name) values ");
17                            for(int i=0;i<1000;i++)
18                            {
19                                if(i==999)
20                                    sql.append("('").append(i+1).append("',").append("'cxh')");
21                                else
22                                    sql.append("('").append(i+1).append("',").append("'cxh'),");
23                            }
24                            s.execute(sql.toString());
25                            conn.commit();
26                        }
27                        s.close();
28                        long end=System.currentTimeMillis();
29                        System.out.println("multiThreadBatchInsert()执行时间为："+(end-start)+"ms");
30                    }
31                    catch(Exception e)
32                    {
33                        e.printStackTrace();
34                    }
35                }
36            };
37            t.start();
38            //t.join();
39        }
输出结果：
multiThreadBatchInsert()执行时间为：2437ms
multiThreadBatchInsert()执行时间为：2625ms
multiThreadBatchInsert()执行时间为：2703ms

注意：上面我采用的是三个线程插入30w条数据。
取最大时间为2703ms，较上面的937ms，基本还是三倍的时间。
所以发现此时多线程也解决不了批量数据插入问题。原因就是，这时候的性能瓶颈不是CPU，而是数据库！

七、InnoDB引擎和MyISAM引擎的比较
最后，分析一下，这两个引擎对批量数据插入的影响。
先建立user2数据表：

CREATE TABLE `user2` (                                         

          `id` varchar(50) NOT NULL,                                  

          `seqid` bigint(20) NOT NULL auto_increment,                 

          `name` varchar(50) NOT NULL,                                 

          PRIMARY KEY (`seqid`)

        ) ENGINE=InnoDB DEFAULT CHARSET=utf8

代码：
 1Connection conn=source.getConnection();
 2        conn.setAutoCommit(false);
 3        Statement s=conn.createStatement();
 4        StringBuilder sql=new StringBuilder("");
 5        long start=System.currentTimeMillis();
 6        for(int j=0;j<100;j++)
 7        {
 8            sql=new StringBuilder("");
 9            sql.append("insert into user2 (id,name) values ");
10            for(int i=0;i<1000;i++)
11            {
12                if(i==999)
13                    sql.append("('").append(i+1).append("',").append("'cxh')");
14                else
15                    sql.append("('").append(i+1).append("',").append("'cxh'),");
16            }
17            s.execute(sql.toString());
18            conn.commit();
19        }
20        s.close();
21        conn.close();
22        long end=System.currentTimeMillis();
23        System.out.println("manyInsert2()执行时间为："+(end-start)+"ms");
输出结果：
manyInsert2()执行时间为：3484ms
注意：第七项的代码和第五是一样的，除了数据表名称不同（user、user2）
但是，
InnoDB ：3484ms
MyISAM：937ms
所以，MyISAM引擎对大数据量的插入性能较好。

总结：
对于大数据量的插入，建议使用insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,的方式，引擎建议使用MyISAM引擎。
友情提醒：本博文章欢迎转载，但请注明出处：陈新汉


分享到：
| 开博说明
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    odbc 数据库批量插入操作

    ODBC（Open Database Connectivity）是微软提供的一种...总的来说，掌握ODBC批量插入技巧是提高数据库操作效率的重要手段，尤其在处理大数据量时。正确使用ODBC和优化批量操作策略，能有效提升系统性能，降低资源消耗。

    C#.NET中如何批量插入大量数据到数据库中

    在C#.NET中批量插入大量数据到数据库是一个常见的任务，特别是在处理诸如从Excel文件导入数据等场景时。这里，我们将探讨如何使用C#.NET高效地完成这个任务，并提供一个简单的示例来说明整个过程。 首先，我们需要...

    java代码oracle数据库批量插入

    这段Java代码通过JDBC实现了向Oracle数据库批量插入数据的功能。关键步骤包括：建立数据库连接、准备SQL语句、批量添加SQL语句到批处理队列、执行批处理以及事务提交。这种方式相比于逐条插入数据，可以显著提升插入...

    Delphi批量插入数据库数据..rar

    本压缩包“Delphi批量插入数据库数据”可能包含了一个或多个示例代码，用于演示如何在Delphi中高效地向数据库批量插入数据。 批量插入数据是数据库操作中的常见需求，尤其是在处理大量数据时，为了提高效率和减少...

    批量插入数据库方法

    在执行批量插入过程中，可能会遇到数据异常、连接问题等，应设置合适的错误处理机制，如捕获异常并记录日志，以便分析和修复问题。 通过以上方法，你可以使用C#实现高效、可靠的数据库批量插入操作。在实际项目中...

    oracle数据库中批量插入问题

    3. **事务管理**：对于大规模数据插入，应使用事务来确保数据的一致性和完整性，避免部分数据插入成功导致的业务逻辑错误。 总之，Oracle数据库中的批量插入技术是数据导入、更新等操作中不可或缺的一部分，掌握这...

    批量插入数据到Oracle数据库Demo

    10. **工具辅助**：除了手动编写PL/SQL脚本，还可以使用工具如SQL*Loader、Oracle SQL Developer等来自动化批量数据导入。 在"InsertData"文件中，可能包含了实现上述操作的示例代码或脚本，通过这些示例，你可以...

    数据库批量数据拷贝

    数据库批量数据拷贝是一项在IT领域中常见的任务，特别是在数据迁移、备份或分析时。它涉及到从一个数据库中选择性地或整体地复制数据到另一个数据库。以下是对这个主题的详细解释： 首先，批量数据拷贝的核心是有效...

    批量插入数据库

    批量插入数据库

    C#大数据批量插入Access程序

    - **批量操作间隔**：在大量数据插入过程中，适当加入延迟，防止对数据库造成过大压力。 这个"C#大数据批量插入Access程序"的源代码会展示这些概念和技术的实际应用，为开发者提供了一个高效处理大数据的模板。通过...

    JDBC连接MySQL数据库批量插入数据过程详解

    "JDBC连接MySQL数据库批量插入数据过程详解" 本文主要介绍了使用JDBC连接MySQL数据库批量插入数据的过程详解，通过示例代码详细介绍了批量插入数据的步骤，对大家的学习或者工作具有一定的参考学习价值。 一、JDBC...

    h2数据库批量导出

    《H2数据库批量导出详解》 H2数据库是一款开源、高性能、轻量级的Java关系型数据库，广泛应用于测试、开发以及嵌入式环境。它支持多种数据库模式，包括MySQL、Oracle等，并且提供了丰富的命令行工具和API，使得...

    一个工具类搞定批量插入增加数据到Mysql数据库

    只要学我一样编写这么一个工具类便可以实现批量插入多条数据，百条，千条，万条，后期还会继续优化增加数据时的速度！有点代码基础的保证能看懂，此项目已经有了前端界面你可以直接的导入然后运行测试既可以了，表...

    往数据库插入数据，相同的不插入

    在Java开发中，常常会遇到需要将一系列数据插入数据库的需求。但在实际操作过程中，为了避免重复数据的插入，我们需要实现一种机制来判断待插入的数据是否已存在于数据库中。这不仅可以提高数据的准确性，还能有效...

    数据库复制批量插入数据命令.doc

    这种操作可以提高数据插入的效率和速度。 SQL Server 在 SQL Server 中，可以使用以下语句来复制表结构和表数据： 1. 复制表结构：新建表 student2，并且结构同表 syn_xj_student 一致。 ```sql select * into ...

    Oracle数据库批量数据无损迁移技术.pdf

    Oracle 数据库批量数据无损迁移技术 Oracle 数据库是一种高效、可靠的关系数据库管理系统，广泛应用于各大、中、小企业中。但是，在数据库迁移过程中，选择批量数据无损迁移技术，优化迁移方案，能够使得停库时间被...

    ACCESS和SQL批量插入数据工具

    这款工具专为数据库管理员和开发人员设计，用于快速、高效地向ACCESS和SQL Server数据库中批量插入数据，尤其在进行负荷测试时，它能显著提高工作效率。 首先，我们来了解一下ACCESS。ACCESS是Microsoft Office套件...

    java批量插入Oracle数据方法

    在IT领域，特别是...通过合理设计代码结构，利用`PreparedStatement`的批处理功能，可以显著提升数据插入的速度和系统的整体性能。在实践中，还需注意细节如事务管理和异常处理，以确保数据完整性和系统的健壮性。

    excel批量导入数据库

    首先，Excel文件（通常是.XLSX或.XLS格式）中的数据被解析，然后根据预定义的映射规则转换成数据库可识别的格式，最后这些数据被插入到合适的数据库表中。这一过程通常需要编程知识，如SQL和Python等，但也有许多...

    C# 批量插入和更新数据

    本案例展示了如何使用C#结合ADO.NET进行批量数据插入的操作。具体步骤如下： 1. **初始化参数**：首先定义了一个`DateTime`类型的变量`begin`用于记录开始时间，并通过`connectionString`连接数据库。 2. **创建...

Magicbox
Global site tag (gtag.js) - Google Analytics