Sqlite3性能测试

参考:http://blog.csdn.net/majiakun1/article/details/46607163,感谢作者分享。


Sqlite3最简单的提升读写性能的方法有:

1. 关闭写同步,也就是设置synchronous。Sqlite3是一个文件数据库,所谓的设置写同步就是设置每次写完数据之后刷新IO缓存的频率,如果写同步设置为Full,那么每次写都会刷新缓存,这样保证数据总能写到文件里,十分安全的做法,防止机器掉电等意外,但是不断地刷新缓存效率低下,一般使用没有这么高的安全要求。关闭写同步就是不去手动刷新缓存,这样效率会极大地提升。关闭写同步只要执行"PRAGMA synchronous = OFF"即可。如Slite3提供的C接口代码,

  const char* kTurnOffSynchronous = "PRAGMA synchronous = OFF;";

  sqlite3_exec(db, kTurnOffSynchronous, NULL, NULL, NULL);

2. 执行SQL语句时,编译一次多次使用,而不是每执行一次都进行编译执行等操作。如Sqlite3提供的C接口代码:

  const char* kInsertSql = "INSERT INTO PERFORMANCE_TEST VALUES(?,?,?,?);";

  sqlite3_stmt* stmt = NULL;
  sqlite3_prepare_v2(db, kInsertSql, strlen(kInsertSql), &stmt, NULL);
  for (int i = 0; i < data_count; ++i) {
    sqlite3_reset(stmt);

    sqlite3_bind_int(stmt, 1, i);
    sqlite3_bind_int(stmt, 2, i);
    sqlite3_bind_int(stmt, 3, i);
    sqlite3_bind_int(stmt, 4, i);
    sqlite3_step(stmt);
  }

  if (turn_on_transaction) {
    sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);
  }

  sqlite3_finalize(stmt);

3. 显式开启事务。Sqlite3的语句执行操作隐式都开启了事务,比如写十万条数据,就会进行十万次事务,极大地限制了效率,解决方法就是显式开启事务,在写数据之前开启事务,写十万条数据之后,进行提交事务。这样写十万条数据只开启了一次事务。直接执行语句“begin”和“commit”即可开启和提交事务。


简单的测试结果如下(固态硬盘,只能简单地作为参考):


测试完整代码如下:

#include <iostream>
#include <string>
#include <sstream>
#include <vector>
#include <list>
#include <cstdio>

#include "sqlite3.h"
#include "common.h"

// 参考:http://blog.csdn.net/majiakun1/article/details/46607163

static const char* kDatabaseName = "test.db";

//--------------------------------------------------------------

static void TestTimer();

//--------------------------------------------------------------

static bool PrepareDB(sqlite3** db, bool create_table);

static void CreateTable();

static void ClearTable();

static void TestExec(bool turn_off_synchronous = false);

static void TestNoSynchronous();

static void TestTransactionExec();

static void TestStep(bool turn_on_transaction = true);

static void PerformanceTest();

//--------------------------------------------------------------

int main() {
  //TestTimer();
  //ClearTable();
  CreateTable();

  PerformanceTest();

  return 0;
}

//--------------------------------------------------------------

static void PerformanceTest() {
  TestExec(false);
  TestExec(true);
  TestNoSynchronous();
  TestTransactionExec();
  TestStep(true);
  TestStep(false);
}

// 1.直接执行sqlite3_exec。
static void TestExec(bool turn_off_synchronous) {
  sqlite3* db = NULL;
  if (!PrepareDB(&db, false)) {
    return;
  }

  if (turn_off_synchronous) {
    std::cout << "1.关闭写同步执行sqlite3_exec : " << std::endl;
  } else {
    std::cout << "2.直接执行sqlite3_exec : " << std::endl;
  }

  utility::Timer timer;
  std::stringstream sstream(std::stringstream::out);

  const int kDataCount = 1000;
  const char* kTurnOffSynchronous = "PRAGMA synchronous = OFF;";

  if (turn_off_synchronous) {
    sqlite3_exec(db, kTurnOffSynchronous, NULL, NULL, NULL);
  }

  // Insert.
  timer.Start();
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "INSERT INTO PERFORMANCE_TEST VALUES("
            << i << "," << i << "," << i << "," << i << ");";

    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }

  double rate = kDataCount / timer.GetSeconds();
  std::cout << "插入数据: " << rate <<"条/秒" << std::endl;

  // Delete.
  timer.Start();
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "DELETE FROM PERFORMANCE_TEST WHERE ID1 = " << i;
    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }

  rate = kDataCount / timer.GetSeconds();
  std::cout << "删除数据: " << rate << "条/秒\n" << std::endl;

  sqlite3_close(db);
}

// 2.显式开启事务,执行sqlite3_exec。
// (A)所谓”事务“就是指一组SQL命令,这些命令要么一起执行,要么都不被执行。
// (B)在SQLite中,每调用一次sqlite3_exec()函数,就会隐式地开启了一个事务,如果插入一条数据,就调用该函数一次,事务就会被反复地开启、关闭,会增大IO量。
// (C)如果在插入数据前显式开启事务,插入后再一起提交,则会大大提高IO效率,进而加数据快插入速度。
static void TestTransactionExec() {
  sqlite3* db = NULL;
  if (!PrepareDB(&db, false)) {
    return;
  }

  std::cout << "3.显式开启事务执行sqlite3_exec : " << std::endl;

  utility::Timer timer;
  std::stringstream sstream(std::stringstream::out);

  const int kDataCount = 100000;
  const char* kBeginTransaction = "begin";
  const char* kCommitTransaction = "commit";

  // Insert.
  timer.Start();
  sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "INSERT INTO PERFORMANCE_TEST VALUES("
      << i << "," << i << "," << i << "," << i << ");";

    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }
  sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);

  double rate = kDataCount / timer.GetSeconds();
  std::cout << "插入数据: " << rate << "条/秒" << std::endl;

  // Delete.
  timer.Start();
  sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "DELETE FROM PERFORMANCE_TEST WHERE ID1 = " << i;
    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }
  sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);

  rate = kDataCount / timer.GetSeconds();
  std::cout << "删除数据: " << rate << "条/秒\n" << std::endl;

  sqlite3_close(db);
}

// 3.关闭写同步且显式开启事务执行sqlite3_exec。
// (A)在SQLite中,数据库配置的参数都由编译指示(pragma)来实现的。
// (B)synchronous选项有三种可选状态,分别是full、normal、off。
// 当synchronous设置为FULL,SQLite数据库引擎在紧急时刻会暂停以确定数据已经写入磁盘。这使系统崩溃或电源出问题时能确保数据库在重起后不会损坏。FULL synchronous很安全但很慢。
// 当synchronous设置为NORMAL, SQLite数据库引擎在大部分紧急时刻会暂停,但不像FULL模式下那么频繁。 NORMAL模式下有很小的几率(但不是不存在)发生电源故障导致数据库损坏的情况。
// 但实际上,在这种情况 下很可能你的硬盘已经不能使用,或者发生了其他的不可恢复的硬件错误。
// 当为synchronous OFF时,SQLite在传递数据给系统以后直接继续而不暂停。若运行SQLite的应用程序崩溃, 数据不会损伤,但在系统崩溃或写入数据时意外断电。
// (C)SQLite3中,该选项的默认值就是full,如果我们再插入数据前将其改为off,则会提高效率。
static void TestNoSynchronous() {
  sqlite3* db = NULL;
  if (!PrepareDB(&db, false)) {
    return;
  }

  std::cout << "4.关闭写同步且显式开启事务执行sqlite3_exec : " << std::endl;

  utility::Timer timer;
  std::stringstream sstream(std::stringstream::out);

  const int kDataCount = 100000;
  const char* kTurnOffSynchronous = "PRAGMA synchronous = OFF;";
  const char* kBeginTransaction = "begin";
  const char* kCommitTransaction = "commit";

  sqlite3_exec(db, kTurnOffSynchronous, NULL, NULL, NULL);

  // Insert.
  timer.Start();
  sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "INSERT INTO PERFORMANCE_TEST VALUES("
      << i << "," << i << "," << i << "," << i << ");";

    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }
  sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);

  double rate = kDataCount / timer.GetSeconds();
  std::cout << "插入数据: " << rate << "条/秒" << std::endl;

  // Delete.
  timer.Start();
  sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  for (int i = 0; i < kDataCount; ++i) {
    sstream << "DELETE FROM PERFORMANCE_TEST WHERE ID1 = " << i;
    sqlite3_exec(db, sstream.str().c_str(), NULL, NULL, NULL);
    sstream.str("");
  }
  sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);

  rate = kDataCount / timer.GetSeconds();
  std::cout << "删除数据: " << rate << "条/秒\n" << std::endl;

  sqlite3_close(db);
}

// 4. 使用sqlite3_step执行。
// (A)SQLite执行SQL语句的时候,有两种方式:一种是使用前文提到的函数sqlite3_exec(),该函数直接调用包含SQL语句的字符串;
// 另一种方法就是“执行准备”(类似于存储过程)操作,即先将SQL语句编译好,然后再一步一步(或一行一行)地执行。
// (B)如果采用前者的话,就算开起了事务,SQLite仍然要对循环中每一句SQL语句进行“词法分析”和“语法分析”。
// (C)“执行准备”主要分为三大步骤:并且声明一个指向sqlite3_stmt对象的指针,该函数对参数化的SQL语句zSql进行编译,将编译后的状态存入ppStmt中。
// 调用函数 sqlite3_step() ,这个函数就是执行一步(本例中就是插入一行),如果函数返回的是SQLite_ROW则说明仍在继续执行,否则则说明已经执行完所有操作。
// 调用函数 sqlite3_finalize(),关闭语句。
// (D)综上所述啊,SQLite插入数据效率最快的方式就是:事务+关闭写同步+执行准备(存储过程),如果对数据库安全性有要求的话,就开启写同步。

static void TestStep(bool turn_on_transaction) {
  sqlite3* db = NULL;
  if (!PrepareDB(&db, false)) {
    return;
  }

  std::string title = "5.直接执行sqlite3_step : ";
  if (turn_on_transaction) {
    title = "6.显式开启事务执行sqlite3_step : ";
  }

  std::cout << title << std::endl;

  utility::Timer timer;

  int data_count = 100;
  if (turn_on_transaction) {
    data_count = 10000;
  }

  const char* kBeginTransaction = "begin";
  const char* kCommitTransaction = "commit";

  // Insert.
  timer.Start();
  if (turn_on_transaction) {
    sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  }

  const char* kInsertSql = "INSERT INTO PERFORMANCE_TEST VALUES(?,?,?,?);";

  sqlite3_stmt* stmt = NULL;
  sqlite3_prepare_v2(db, kInsertSql, strlen(kInsertSql), &stmt, NULL);
  for (int i = 0; i < data_count; ++i) {
    sqlite3_reset(stmt);

    sqlite3_bind_int(stmt, 1, i);
    sqlite3_bind_int(stmt, 2, i);
    sqlite3_bind_int(stmt, 3, i);
    sqlite3_bind_int(stmt, 4, i);
    sqlite3_step(stmt);
  }

  if (turn_on_transaction) {
    sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);
  }

  sqlite3_finalize(stmt);

  double rate = data_count / timer.GetSeconds();
  std::cout << "插入数据: " << rate << "条/秒" << std::endl;

  // Delete.
  timer.Start();
  if (turn_on_transaction) {
    sqlite3_exec(db, kBeginTransaction, NULL, NULL, NULL);
  }

  const char* kDeleteSql = "DELETE FROM PERFORMANCE_TEST WHERE ID1 = ?;";
  sqlite3_prepare_v2(db, kDeleteSql, strlen(kDeleteSql), &stmt, NULL);
  for (int i = 0; i < data_count; ++i) {
    sqlite3_reset(stmt);

    sqlite3_bind_int(stmt, 1, i);
    sqlite3_step(stmt);
  }

  if (turn_on_transaction) {
    sqlite3_exec(db, kCommitTransaction, NULL, NULL, NULL);
  }

  sqlite3_finalize(stmt);

  rate = data_count / timer.GetSeconds();
  std::cout << "删除数据: " << rate << "条/秒\n" << std::endl;

  sqlite3_close(db);
}

static bool PrepareDB(sqlite3** db, bool create_table) {
  int rc = sqlite3_open(kDatabaseName, db);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to open " << kDatabaseName << std::endl;
    std::cout << "Error msg: " << sqlite3_errmsg(*db) << std::endl;
    return false;
  }

  if (!create_table) {
    return true;
  }

  const char* kCreateTableSql = "CREATE TABLE PERFORMANCE_TEST(\
                                                                 ID1 INT, ID2 INT, ID3 INT, ID4 INT\
                                                                                                  );";
  char* error_msg = NULL;
  rc = sqlite3_exec(*db, kCreateTableSql, NULL, NULL, &error_msg);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to create table PERFORMANCE_TEST." << std::endl;
    std::cout << "Error msg: " << error_msg << std::endl;
    sqlite3_free(error_msg);
    return false;
  }

  return true;
}

static void CreateTable() {
  sqlite3* db = NULL;
  PrepareDB(&db, true);
  sqlite3_close(db);
}

static void ClearTable() {
  sqlite3* db = NULL;
  PrepareDB(&db, false);

  const char* kClrearTableSql = "DELETE FROM PERFORMANCE_TEST;";
  char* error_msg = NULL;

  int rc = sqlite3_exec(db, kClrearTableSql, NULL, NULL, &error_msg);
  if (rc != SQLITE_OK) {
    std::cout << "Failed to clear table!" << std::endl;
    std::cout << "Error msg: " << error_msg << std::endl;
    sqlite3_free(error_msg);
  }

  sqlite3_close(db);
}






已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页