8. 探究SQL插入(insert)大量数据时的效率问题

2016-06-16 本文已影响1312人面糊

首先, 解释一下出入数据时的执行过程
- 当SQL调用执行一个语句的时候, 都会开启一个称谓事务: transaction的东西, 并且在执行完毕之后, 提交事务
  - 开启事务: begin transaction
  - 提交事务: commit transaction

首先来测试: 使用普通的sql语句来插入10000条数据

耗时: 37.504418秒(一般来说不应该这么慢的, 不知道这次测试为什么会这样)

如果在你的APP中, 提交一些10000条数据, 需要让用户等待37秒的话, 那么用户肯定是很不乐意的

  // 1. 编写sql语句 insert into t_stu(name, age, score) values ('\(name)', \(age), \(score))
  NSString *sql = [NSString stringWithFormat:@"insert into T_human(name, age, height) values('%@', %li, %f)", self.name, self.age, self.height];
  
  // 2. 执行SQL
  [[SQLiteTool shareInstance] excuteSQL:sql];

使用参数绑定, 来插入数据

耗时: 耗时13.305384秒, 相比上面的方法有很大的提升

准备语句会在一定程度上优化数据的插入, 他不会因为反复的编译准备语句而导致性能的降低

  // 插入数据
  - (void)insertHumanBind {
      
      // 1. 留给准备语句用的语句
      NSString *sql = @"insert into T_human(name, age, height) values(?, ?, ?)";
      
      // 2. 创建准备语句
      sqlite3 *db = [SQLiteTool shareInstance].db;
      sqlite3_stmt *stmt = nil; // 准备语句的引用指针
      if (sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil) != SQLITE_OK) {
          NSLog(@"编译失败");
          return;
      }
  
      // 3. 绑定参数
          sqlite3_bind_text(stmt, 1, @"lilchunjiang".UTF8String, -1, SQLITE_TRANSIENT);
          sqlite3_bind_int(stmt, 2, 16);
          sqlite3_bind_double(stmt, 3, 99);
          
          // 4. 执行, 这里要判断执行是否完成
          if (sqlite3_step(stmt) != SQLITE_DONE) {
              NSLog(@"执行失败");
              return;
          }
          
          // 5. 重置, 将绑定的值清空
          sqlite3_reset(stmt);
      
      // 6. 销毁
      sqlite3_finalize(stmt);
  }

尽管经过上面的优化, 我们仍然达不到预期的效果, 这主要是由于

如果执行10000次SQL语句, 那么就会经历10000次开启事务 -> 执行语句 -> 提交事务, 这样会严重影响存储的性能
因此我们的解决方案为: 手动开启事务, 然后重复10000次的执行, 最后手动提交事务

这时我们得到的耗时为: 0.040238秒, 这绝对是惊人的效率提升, 这主要是由于我们避免了重复的开启/提交事务以及处理语句这些执行的切换, 而是专注于语句的执行

  // 开启事务
  - (void)beginTransaction {
      NSString *sql = @"begin transaction";
      [self excuteSQL:sql];
  }
  
  // 提交事务
  - (void)commitTransaction {
      NSString *sql = @"commit transaction";
      [self excuteSQL:sql];
  }   
  
  // 插入1w条数据
  - (void)insertBind10000 {
      
      // 1. 语句
      NSString *sql = @"insert into T_human(name, age, height) values(?, ?, ?)";
      
      // 2. 创建准备语句
      sqlite3 *db = [SQLiteTool shareInstance].db;
      sqlite3_stmt *stmt = nil;
      if (sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil) != SQLITE_OK) {
          NSLog(@"编译失败");
          return;
      }
      
      // 3. 开启事务
      [[SQLiteTool shareInstance] beginTransaction];
      
      for (int i = 0; i < 10000; i++) {
          // 4. 绑定参数
          sqlite3_bind_text(stmt, 1, @"lilchunjiang".UTF8String, -1, SQLITE_TRANSIENT);
          sqlite3_bind_int(stmt, 2, 16);
          sqlite3_bind_double(stmt, 3, 99);
          
          // 5. 执行
          if (sqlite3_step(stmt) != SQLITE_DONE) {
              NSLog(@"执行失败");
              return;
          }
          
          // 6. 重置, 将绑定的值清空
          sqlite3_reset(stmt);
      }
      
      // 7. 提交事务
      [[SQLiteTool shareInstance] commitTransaction];
      
      // 8. 销毁准备语句
      sqlite3_finalize(stmt);
  }

小结
- 如果我们只需要插入几条语句的话, 那么就直接使用单条语句即可, 因为这样很简单
- 但是如果需要数据的大批量插入, 就一定要使用最后一种方法, 这样会让数据存储有极大的性能提升

8. 探究SQL插入(insert)大量数据时的效率问题

猜你喜欢

热点阅读