Sqlite事务模型、性能优化Tips、常见误区(4)

2.4.1 结论

• 多线程设置是决定DDL、DML、WAL(包括SHM)操作是否线程安全的设置
• 多线程设置与读写(连接)分离没有任何关系，并不是实现读写(连接)分离的必要条件(很多人对这一点有误解)

3，性能优化tips

3.1 合理使用事务

由#2.2的分析可知，写操作会在RESERVED状态下将数据更改、b-tree的更改、日志等写入page cache，并最终flush到数据库文件中;使用事务的话，只需要一次对DB文件的flush操作，同时也不会对其他连接的读写操作阻塞;对比以下两种数据写入方式(这里以统一存储提供的API为例)，实测耗时有十几倍的差距(当然对于频繁的读操作，使用事务可以减事务状态的切换，也会有一点点性能提升)：

// batch insert in transaction with 1000000 records 
// 
AliDBExecResult* execResult = NULL; 
_database->InTransaction([&]() -> bool {    // in transaction 
  auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)"); 
  for (auto record : records) {    // bind 1000000 records 
    // bind record 
    ... 
    ... 
    statement->AddBatch(); 
  } 
  auto result = statement->ExecuteUpdate(); 
  return result->is_success_; 
}); 
 
 
// batch insert with 1000000 records, no transaction 
// 
auto statement = _database->PrepareStatement("INSERT INTO table VALUES(?, ?)"); 
for (auto record : records) {    // bind 1000000 records 
  // bind record 
  ... 
  ... 
  statement->ExecuteUpdate(); 
} 

3.2 启用WAL + 读写(连接)分离

启用WAL之后，数据库大部分写操作变成了串行写(对WAL文件的串行操作)，对写入性能提升有非常大的帮助;同时读写操作可以互相完全不阻塞(如#2.3所述)。上述两点比较好的解释了启用WAL带来的提升;同时推荐一个写连接 + 多个读连接的模型，如下图所示：

3.2.1 读写连接分离的细节

• 读操作使用不同的连接并发执行，可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁
• 所有的写操作、显式事务操作都使用同一个连接，且所有的写操作、显式事务操作都串行执行

可以完全避免由于显式事务、写操作之间的锁竞争带来的死锁，如#2.2.1提到的死锁的例子

并发写并不能有效的提高写入效率，参考如下伪代码，哪段执行更快?

// two transactions:  
void Transaction_1() { 
        connection_->Exec("BEGIN"); 
      connection_->Exec("insert into table(value) values('xxxx')"); 
      connection_->Exec("COMMIT"); 
} 
 
void Transaction_2() { 
        connection_->Exec("BEGIN"); 
      connection_->Exec("insert into table(value) values('xxxx')"); 
      connection_->Exec("COMMIT"); 
} 
 
// code fragment 1: concurrent transaction 
thread1.RunBlock([]() -> void { 
      for (int i=0; i< 100000; i++) { 
            Transaction_1(); 
    } 
}); 
 
thread2.RunBlock([]() -> void { 
      for (int i=0; i< 100000; i++) { 
            Transaction_2(); 
    } 
}); 
 
thread1.Join(); thread2.join(); 
 
// code fragment 2: serial transaction 
for (int i=0; i< 100000; i++) { 
  Transaction_1(); 
} 
for (int i=0; i< 100000; i++) { 
  Transaction_2(); 
} 

3.3 针对具体业务场景，设置合适的WAL SIZE

如#2.3提到，过大的WAL文件，会让查找操作从B-Tree查找退化成线性查找(WAL中page连续存储);但大的WAL文件对写操作较友好。对于大记录的写入操作，较大的wal size会有效提高写入效率，同时不会影响查询效率

3.4 针对业务场景分库分表

分库分表可以有效提高数据操作的并发度;但同时过多的表会影响数据库文件的加载速度。现在数据库方向的很多研究包括Auto sharding, paxos consensus, 存储和计算的分离等;Auto

application-awared optimization，Auto hardware-awared optimization，machine

learning based optimization也是不错的方向。

3.5 其他

包括WAL checkpoint策略、WAL size优化、page size优化等，均需要根据具体的业务场景设置。

4，常见问题 & 误区

4.1 线程安全设置及误区

sqlites configuration options: https://sqlite.org/c3ref/c_config_getmalloc.html

按照sqlite文档，sqlite线程安全模式有以下三种：

SQLITE_CONFIG_SINGLETHREAD(单线程模式)

This option sets the threading mode to Single-thread. In other words, it disables all mutexing and puts SQLite into a mode where it can only be used by a single thread.

SQLITE_CONFIG_MULTITHREAD(多线程模式)

（编辑：ASP站长网）