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);
}

本文鏈接：https://blog.csdn.net/u011726005/article/details/77756467

智能推薦

Sqlite3交叉編譯

一、準備工作本次使用ubuntu18.04和 "arm-fsl-linux-gnueabi-"交叉編譯器 1、下載Sqlite3 在https://www.sqlite.org/download.html里下載Sqlite3 2、將交叉編譯工具鏈安裝 1、查看交叉編譯器是否在環境變量中加入環境變量的方法： 1、sudo vi /etc/bash.bashrc 2、輸入exp...

學API接口測試，致富植發（七）Sqlite3 數據庫

一、在project的settings.py中修改配置，將數據庫絕對路徑換成相對路徑 Django默認自帶sqllite的數據庫驅動，默認使用sqllite的數據庫二、在app的models.py 中創建類三、執行命令來生成數據表 1、 2、四、查看數據表五、往數據表添加數據六、安裝SQliteExpert...

sqlite3測試打開關閉數據庫的耗時

結果...

VS2015配置Sqlite3

轉載自VS2015中配置SQLite3 VS2015中配置SQLite3 官方下載地址：http://www.sqlite.org/download.html 一、下載 SQLite3.def和SQlite3.dll 文件，并解壓到如 D:\SQLite3\ 目錄下。二、運行 CMD，輸入： "D:\Program Files (x86)\Microsoft Visual Studi...

Python SQLite3 迭代器

需求：遍歷一個指定文件夾，將指定文件夾下的文件名稱寫入到數據庫對應的列中，如果數據庫行中的數據與文件的名字相同，則將對應的文件名寫入匹配行的相對應的列中數據庫如下圖：注意：通過cursor.execute()所獲得的對象是一個可迭代對象*，如果把這個可迭代對象用在for的嵌套循環中的話，會出錯，達不到預期結果*（此處我踩了10多分鐘坑，最后調試才發現問題），必須先創建一個集合，對cursor...

Sqlite3性能測試

智能推薦

猜你喜歡