数据库写入性能优化

忘是亡心i 2022-08-08 06:26 187阅读 0赞

# 数据库写入性能优化 #

标签（空格分隔）：数据库 java sqlserver

--------------------

*  数据库写入性能优化
    
     *  两个实例
        
         *  example1
         *  example2
     *  Statement PreparedStatement
     *  关闭自动提交
     *  感谢

--------------------

## 两个实例 ##

最近因为比赛缘故，频繁使用java操作数据库，由于要写入的数据量比较庞大（大约100w左右），故往数据库里写入数据的性能一直不能令人满意（简直就是慢的飞起）。最近两天一直在研究如何提高数据库的写入性能，初步有点成果。(百度了好长时间一点成果都没有，结果Google分分钟给搞定，看来[梯子][Link 1]没白买)所以写个博客分享一下这几天的优化历程，也许优化还不是很到位，还请不吝赐教。好了，废话不多说，先来看一下我一开始的代码。

### example1 ###

public void example1()
        {
            DatabaseOp.createDatabase("test"); // 建立数据库test
    
            String columLable = "id int, value int";
            DatabaseOp.createTable("example1", columLable, "test"); // 建立表exanple1
    
            DbConntion dc = new DbConntion("test");
            Connection con = dc.getConnection(); // 获取连接
            Statement stmt = null;
            try
            {
                stmt = con.createStatement();
    
                //总共100w数据，分十次，每次10w行插入
                for (int i = 0; i < 10; i++)
                {
                    long startTime = System.currentTimeMillis();
    
                    for (int j = 0; j < 100000; j++)
                    {
                        StringBuffer insertSQL = new StringBuffer(
                                "INSERT INTO example1 VALUES ( ").append(j + 1)
                                .append(",").append(j).append(" );");
                        stmt.executeUpdate(insertSQL.toString());
                    }
    
                    long endTime = System.currentTimeMillis();
    
                    System.out.println("Total Time: " + (endTime - startTime)/1000 + "s");
                }
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }

输出内容：

Total Time: 100s

由于速度太慢，还没有完全执行完我就终止了程序，上面的输出应该只是插入了10w条数据的时间，已经是一分多钟了。

### example2 ###

下面再看另一个例子：

public void example2()
        {
            String columLable = "id int, value int";
            DatabaseOp.createTable("example2", columLable, "test"); //在数据库test中创建表example2
    
            DbConntion dc = new DbConntion("test");
            Connection mcon = dc.getManualCommitConnection();//获取连接 注意 这里与example1中不同
            PreparedStatement pstmt = null;
            try
            {
                pstmt = mcon.prepareStatement("INSERT INTO example2 VALUES (?,?)");
    
                //这里也是共100w条数据，分10次，每次10w条写入数据库
                //这里与example1中方法不同
                for(int i = 0; i < 10; i++)
                {
                    long startTime = System.currentTimeMillis();
    
                    for(int j = 0; j < 100000; j++)
                    {
                        pstmt.setInt(1, j+1);
                        pstmt.setInt(2, j);
                        pstmt.executeUpdate();
                    }
                    mcon.commit();
    
                    long endTime = System.currentTimeMillis();
                    System.out.println("Total Time: " + (endTime - startTime)/1000 + "s");
                }
            }
            catch(SQLException e)
            {
                e.printStackTrace();
            }
        }

这次的输出结果如下:

Total Time: 10s
    Total Time: 7s
    Total Time: 8s
    Total Time: 8s
    Total Time: 7s
    Total Time: 8s
    Total Time: 8s
    Total Time: 8s
    Total Time: 8s
    Total Time: 7s

速度简直是差了简直有10倍，这其中的奥妙便是`Statement`和`PreparedStatement`的差异了。

## Statement && PreparedStatement ##

首先，对于经常跟jdbc打交道的程序猿就应该*总是*使用`PreparedStatement`而不是使用`Statement`,主要是基于以下原因：

1.  `PreparedStatement`作为`Statement`的子类，继承了`Statement`的所有方法，并且还重写了`execute`,`executeUpdate`,`executeQuery`方法，可以不带参数更方便的调用。
2.  `PreparedStatement`比`Statement`的效率更高。因为`PreparedStatement`会先将*SQL*语句预编译，并在数据库服务器中缓存下来，遇到语法相同的*SQL语句*即不用编译直接执行就可以（类似是函数）。而`Statement`由于每次执行时的语句很难相同，很难匹配到相同的，因而需要几乎每次*编译–>执行*,效率降低。
3.  `PreparedStatement`比`Statement`语法上更简洁清晰。比如*example2*中的*SQL语句*

pstmt = mcon.prepareStatement("INSERT INTO example2 VALUES (?,?)");
    pstmt.setInt(1, j+1);
    pstmt.setInt(2, j);
    pstmt.executeUpdate();

`PreparedStatement`使用的*SQL语句*中可以使用`?`充当参数占位符，并提供了`SetXXX()`方法用来填充参数。  
对比一下*example1*中的语法：

StringBuffer insertSQL = new StringBuffer("INSERT INTO example1 VALUES ( ").append(j + 1).append(",").append(j).append(" );");
    stmt.executeUpdate(insertSQL.toString());

这样的语法不仅难看，而且很难看懂，我想谁看这种代码都不会有好心情的。

1.    
    
2.  `PreparedStatement`比`Statement`更安全，这一点我不是很懂。还是直接上引用比较好。

> 即使到目前为止,仍有一些人连基本的恶义SQL语法都不知道.  
> String sql = “select \* from tb\_name where name= ‘”+varname+”’ and passwd=’”+varpasswd+”’”;  
> 如果我们把\[’ or ‘1’ = ‘1\]作为varpasswd传入进来.用户名随意,看看会成为什么?  
>   
> select \* from tb\_name = ‘随意’ and passwd = ” or ‘1’ = ‘1’;  
> 因为’1’=’1’肯定成立,所以可以任何通过验证.更有甚者:  
> 把\[‘;drop table tb\_name;\]作为varpasswd传入进来,则:  
> select \* from tb\_name = ‘随意’ and passwd = ”;drop table tb\_name;有些数据库是不会让你成功的,但也有很多数据库就可以使这些语句得到执行.  
>   
> 而如果你使用预编译语句.你传入的任何内容就不会和原来的语句发生任何匹配的关系.只要全使用预编译语句,你就用不着对传入的数据做任何过虑.而如果使用普通的statement,有可能要对drop,;等做费尽心机的判断和过虑.

## 关闭自动提交 ##

正如看到的那样，*example1*跟*example2*中获取数据库连接的方式并不一样。  
*example1*中的是

Connection con = dc.getConnection(); // 获取连接

而*example2*中的是

Connection mcon = dc.getManualCommitConnection();//获取连接 注意 这里与example1中不同

这其中，`getConnection()`的代码是

public Connection getConnection()
        {
            Connection connection = null;
            try
            {
                Class.forName("net.sourceforge.jtds.jdbc.Driver");
                connection = DriverManager.getConnection(this.getConnectionUrl(),
                        this.userName, this.passWord);
            }
            catch (Exception e)
            {
                e.printStackTrace();
            }
            return connection;
        }

`getManualCommitConnection()`的代码是

public Connection getManualCommitConnection()
        {
            Connection con = this.getConnection();
    
            try
            {
                con.setAutoCommit(false);//关闭自动提交
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
    
            return con;
        }

`getManualCommitConnection()`调用了`getConnection()`并关闭了获取到的连接的自动提交。这意味着当你在一个`Connection`上执行了*SQL语句*后，*SQL语句*并不会立即就被发送到数据库服务器执行，而是需要手动调用`Connection.commit()`方法一次性提交到服务器。不言而喻，这样会减少网络通信流量并且在一定程度上保证操作数据库时的安全性。

## 感谢 ##

作为一个刚接触数据库的新手，能够找到一个合适的方法解决手头的问题得到了别人的帮助自然不言而喻，当然，这篇博文也并非全部是我原创，写作过程中也是参考了他人的博客，在此列出曾经浏览过的博客和网页，感谢他们的分享并希望有相同疑惑的人能够得到更多的帮助。

*  [MSDN][]
 *  [cihaibo的博客][cihaibo]
 *  [StackOverFlow][]

当然，也多亏了买的[梯子][Link 1]能够让我找到*MSDN*和*StackOverFlow*上的问题,才了解了这些东西(百度根本没百度到！)。

当然，作为一个新人，这篇博客写的难免有些深度不够，相信只要坚持下去，我也可以分享给大家有深度有思考的技术。

感谢你能耐心看到这里。如有错误，还请不吝赐教。

[Link 1]: http://honx.in/_VLuuZc6vDwo5iCtb
[MSDN]: https://social.msdn.microsoft.com/forums/sqlserver/en-US/7153ab80-f8f8-4de9-8af5-0dca372a0c50/jdbc-equivalent-of-sqlbulkcopy
[cihaibo]: http://blog.sina.com.cn/s/blog_77eba18f01019csh.html
[StackOverFlow]: http://stackoverflow.com/questions/3746453/whats-the-most-efficient-way-to-bulk-copy-to-sql-server-from-java