关于事务开启与否对数据库插入数据所需时间的影响的讨论

   最近在做sqlite3的二次开发，看到API里面关于事务的时候，萌生了测试一下事务的开启与否对插入数据所需要的时间影响的想法，根据sqlite3的api，在后面测试的时候发现对于sqlite3来说事务的开启与否仅插入1w条数据，时间的差距就已经非常非常明显了,先说下测试环境：ubuntu12.04, sqlite3 3.7.14.1，测试插入1w条数据。
   
   不开启事务时的测试代码如下：
   
 

   intinsert_no_trans()
    {
   
   printf("<--------------------->\n"
          "go intofunction insert_no_trans()\n");

    int j =0;
    for (j = 0;j < 10000; j++)
    {
       sprintf(sql,"INSERT INTO [dev] ([id], [name], [age])\
             values (%d,'%s', %d)", j, "JGood", j);
       if(SQLITE_OK!= sqlite3_exec
             (conn, sql,0, 0, &err_msg))
       {
         fprintf(stderr, "INSERT ERROR: %s\n", err_msg);
         exit(EXIT_FAILURE);
       }
      
    }
   printf("INSERT all succussfully!\n");

   printf("function insert_no_trans() end. \n"
         "<---------------------> \n");

    returnEXIT_SUCCESS;
    }
   

   
   开启事务的测试代码如下：

 

   intinsert_with_trans()
    {
   
   printf("<--------------------->\n"
          "go intofunction insert_with_trans()\n");

   sqlite3_exec(conn, "begin;", 0, 0, 0); //开启事务

    int j =0;
    for (j = 0;j < 10000; j++)
    {
       sprintf(sql,"INSERT INTO [dev] ([id], [name], [age])\
             values (%d,'%s', %d)", j, "JGood", j);
       if(SQLITE_OK!= sqlite3_exec(conn, sql, 0, 0, &err_msg))
       {
          is_succeed =false; //失败之后把标识设为false
         fprintf(stderr, "INSERT ERROR: %s\n", err_msg);
          break;
       }
      
    }

   if(is_succeed)
      sqlite3_exec(conn, "commit;", 0, 0, 0); //提交事务
    else
    {
      sqlite3_exec(conn, "rollback;", 0, 0, 0); //回滚事务
      exit(EXIT_FAILURE);
    }

   printf("INSERT all succussfully!\n");

   printf("function insert_with_trans() end. \n"
         "<---------------------> \n");

    returnEXIT_SUCCESS;
    }

   测试结果大跌眼镜，时间测试我用的是linux的time命令，在开启了事务的情况下插入1w条数据的时间仅为0.4s，而在不开启事务的情况下，由于时间太长，没有等其执行完，根据.db文件的大小和已经使用的时间推测，假设单位时间插入同样多条数据，推算出来的时间高达18分钟。这已经不在一个数量级了，google了一下这个问题，看到网上有人对此是这么解释的，如果未启用事务，sqlite会每插入一条数据，就往磁盘上面写一次，在整个执行过程中我也观察到未开启事务时程序执行期间硬盘灯一直是亮的，这也映证了这一点。而在开启事务的情况下，其应该是在对数据全部处理完之后才需要执行一次IO操作，时间自然非常快。
   
   
   既然sqlite事务开启与否对性能影响如此之大，那么mysql又如何呢？于是继续测试一下mysql在此种情况下的插入时间的差别。先说明环境，ubuntu10.04，openjdk 1.6.0_24，mysql5.1.66。测试是使用java通过jdbc来操作mysql的，关键代码如下。
   
   未开启事务（一条一条插入）时测试代码如下：

    Statementstmt = conn.createStatement();
   conn.setAutoCommit(true);
   stmt.executeUpdate("set autocommit=1");
    for(int i =0; i < 10000; i++)
       stmt.executeUpdate("insert into dev values(1,'java',1);");

   开启事务时测试代码如下：

    Statementstmt = conn.createStatement();
   conn.setAutoCommit(false);
   stmt.executeUpdate("set autocommit=0;");
   stmt.executeUpdate("start transaction;");
    for(int i =0; i < 10000; i++)
       stmt.executeUpdate("insert into dev values(1,'java',1);");
   stmt.executeUpdate("commit;");

   
   这个结果也在意料之中，开启事务之后插入所需要的时间确实比未开启事务要短，但差距不是很明显。插入1w条数据时，不开启事务用时4.4s，开启事务之后用时2.1s，而插入100w条数据时差距就更明显了，不开启事务用时5分44s，开启之后用时2分10s。同时也观察了磁盘的读写情况，无论事务是否开启，都没有出现像sqlite那样磁盘灯一直亮的情况，唯一的差别就是在程序执行过程中在终端查询selectcount(*) ，不开启事务时其值是一直在变化的，开启事务时多次查询值均未变化，这也验证了事务开启与否在操作上的区别。
   
   再对比一下mysql和sqlite在均插入1w条数据时间的差别，sqlite3用时仅0.4s,mysql用时2.2s，虽然从功能和体积上这二者均不是一个级别的，但是至少可以说明一点，sqlite3的性能非常不错，甚至在这次的测试中表现出来的插入所需要时间方面比mysql更好（是否可以这样认为呢？）。

   最后，需要说明的是，这仅仅只是个人对这二者一次非常简单的测试，测试方法或者代码逻辑上如有错误，敬请指正。