首先，建立数据库
rc = sqlite3_exec(db, "create table if not exists testinfo (id integer primary key, age integer, height text, weight text)", NULL, NULL, &zErr);插入篇：
1、sqlite3_exec()
通常，我们使用sqlite3_exec()函数来处理数据的插入操作，该函数直接调用sql语句对数据进行插入，所以使用起来很方便，插入100w条数据
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "insert into testinfo values(%d, %d, '%d', '%s')", i, i*2, i*10, buf);
sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}该函数每调用一次，都会隐式地开启一次事务，对于大批量的操作，如果不加修饰地直接多次调用该函数，会导致插入效率极低
执行5次平均耗时：1721.272秒，极其慢
2、显式调用事务
利用事务的互斥性，如果在批量的插入操作前显式地开启一次事务，在插入操作结束后，提交事务，那么所有的操作将只执行一次事务，大大地提高IO效率
sqlite3_exec(db, "BEGIN;", 0, 0, NULL);
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "insert into testinfo values(%d, %d, '%d', '%s')", i, i*2, i*10, buf);
sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}
sqlite3_exec(db, "COMMIT;", 0, 0, NULL);执行5次平均耗时：15.559秒 有了很大改善
3、执行准备
sqlite3_exec()函数直接调用sql语句字符串，每执行一次该函数，都要进行一此“词法分析”和“语法分析”为此sqlite引入了“执行准备”这一功能，即事先把sql语句编译成系统能够理解的语言，然后一步一步执行，这样大大地提高了效率，同样是插入100w条数据：
sqlite3_exec(db, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT;", 0, 0, 0);执行5次平均耗时5.298秒
4、关闭写同步
如果有定期备份的机制，而且少量数据丢失可接受，可将同步方式设置为OFF，默认为FULL。
sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);full写入速度最慢，但保证数据是安全的，不受断电、系统崩溃等影响，而off可以加速数据库的一些操作，但如果系统崩溃或断电，则数据库可能会损毁，所以不是很推荐这种做法
执行5次平均耗时5.468秒（不是很理想）
5、使用WAL模式
WAL:Write Ahead Logging,他是数据库中用于实现原子事务的一种机制，从3.7.0版本后引入
WAL模式主要有两个优点：
1、读写可以完全并发进行，不会互相阻塞（但是写之间仍然不能并发）
2、WAL在大多情况下，拥有更好的性能（因为无需每次写入时都要写两个文件）
Rollback journal机制原理：在修改数据库文件中的数据前，先将修改所在分页中的数据备份在另一个地方，然后再将修改写入到数据中；如果事务失败，则将备份数据拷贝回来，撤销修改；如果事务成功，则删除备份，提交修改。
WAL机制原理：修改并不直接写入到数据库文件中，而是写入到另外一个称为WAL的文件中，如果事务失败，wal中的文件会被忽略，撤销修改；如果事务成功，它将在随后的某个时间被写回到数据库文件中，提交修改。
性能差异主要源于每次事务提交，wal只需要将更新的日志写入磁盘，而delete模式首先要将原始数据拷贝到日志文件中，并进行fsync，然后将修改页写入磁盘，同时也需要fsync，确保数据落盘，并且还要清除日志文件。因此写事务在WAL模式下，只需要一次fsync，并且是顺序写，而在delete模式下需要至少两次fsync（日志，数据），并且更新的数据离散分布在多个page中，因此可能需要多个fsync。
WAL使用共享内存技术，因此所有读写进程必须在同一个机器上
开启WAL模式的方法：sqlite3_exec(db, "PRAGMA journal_mode=WAL; ", 0,0,0); 6、内存数据库
另外，如果数据无需长时间保存，可以使用sqlite的内存数据库替代文件数据库
开启sqlite内存数据库的方式：
sqlite3* db = NULL;
rc = sqlite3_open(":memory", &db);执行5次平均耗时：4.052秒
但是内存数据库存在如下缺点：
1.断电或者程序崩溃后数据库就会消失
2.在内存中的数据库不能被别的进程访问
3.不支持像在硬盘上的读写互斥处理，需要自己加锁
查询篇：
1、sqlite3_get_table()
通常使用sqlite3_get_table()函数来执行查询
for( i = 0; i < 300000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "select * from testinfo where id = %d", i);
sqlite3_get_table(db, sqlcmd, &pRecord, &rows, &cols, &zErr);
}
执行5次平均耗时229.438秒
和sqlite3_exec()一样，大批量的调用该函数会导致效率极其低下，所以还是使用事务的方式来提高效率
sqlite3_exec(db, "BEGIN", 0, 0, NULL);
for( i = 0; i < 300000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "select * from testinfo where id = %d", i);
sqlite3_get_table(db, sqlcmd, &pRecord, &rows, &cols, &zErr);
}
sqlite3_exec(db, "COMMIT", 0, 0, NULL);
执行5次平均耗时23.177秒
3、使用执行准备：
sqlite3_exec(db, "BEGIN", 0, 0, NULL);
char *sql = "select * from testinfo where id = ?";
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
rc = sqlite3_step(stmt);
while(rc == SQLITE_ROW)
{
n1 = sqlite3_column_int(stmt, 0);
n2 = sqlite3_column_int(stmt, 1);
ch1 = sqlite3_column_text(stmt, 2);
ch2 = sqlite3_column_text(stmt, 3);
rc = rc = sqlite3_step(stmt);
}
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT", 0, 0, NULL);
4、查询内存数据库
执行5次平均耗时3.235秒
附上完整代码：
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<sys/time.h>
#include"sqlite3.h"
int main(int argc, char* argv[])
{
int rc = 0;
int i = 0;
int j = 0;
int rows, cols;
int n1, n2;
sqlite3* db = NULL;
sqlite3* dbMem = NULL;
char* zErr = NULL;
char **pRecord = NULL;
sqlite3_stmt *stmt = NULL;
char *buf = "CJcEEAAYASCgExEFAaATEqATEy";
struct timeval tmv1;
struct timeval tmv2;
float tmcost;
char tmpstr[32] = {0};
char sqlcmd[2048] = {0};
const char* ch1;
const char* ch2;
rc = sqlite3_open("kaf.db", &db);
if (rc)
{
fprintf(stderr, "Can't open database:%s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, "create table if not exists testinfo (id integer primary key, age integer, height text, weight text)", NULL, NULL, &zErr);
if (SQLITE_OK != rc) {
fprintf(stderr, "create sql failed:%s\n", zErr);
sqlite3_close(db);
exit(1);
}
//sqlite3_exec()逐条插入
if (atoi(argv[1]) == 1)
{
gettimeofday(&tmv1, NULL);
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "insert into testinfo values(%d, %d, '%d', '%s')", i, i*2, i*10, buf);
sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 1 operation costs %f\n", tmcost);
}
//开启事务
else if(atoi(argv[1]) == 2)
{
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN;", 0, 0, NULL);
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "insert into testinfo values(%d, %d, '%d', '%s')", i, i*2, i*10, buf);
sqlite3_exec(db, sqlcmd, NULL, NULL, &zErr);
}
sqlite3_exec(db, "COMMIT;", 0, 0, NULL);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 2 operation costs %f\n", tmcost);
}
//执行准备
else if(atoi(argv[1]) == 3)
{
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 4 operation costs %f\n", tmcost);
}
//关闭写同步
else if(atoi(argv[1]) == 4)
{
sqlite3_exec(db, "PRAGMA synchronous = OFF; ", 0,0,0);
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 5 operation costs %f\n", tmcost);
}
//使用WAL模式
else if(atoi(argv[1]) == 5)
{
sqlite3_exec(db, "PRAGMA journal_mode=WAL; ", 0,0,0);
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT;", 0, 0, 0);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 5 operation costs %f\n", tmcost);
}
//内存数据库
else if(atoi(argv[1]) == 6)
{
rc = sqlite3_open(":memory:", &dbMem);
rc = sqlite3_exec(dbMem, "create table if not exists testinfo (id integer primary key, age integer, height text, weight text)", NULL, NULL, &zErr);
sqlite3_exec(dbMem, "PRAGMA synchronous = OFF; ", 0,0,0);
gettimeofday(&tmv1, NULL);
sqlite3_exec(dbMem, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(dbMem, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(dbMem, "COMMIT;", 0, 0, 0);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 6 operation costs %f\n", tmcost);
}
//sqlite3_get_table()逐条查询
else if(atoi(argv[1]) == 7)
{
gettimeofday(&tmv1, NULL);
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "select * from testinfo where id = %d", i);
sqlite3_get_table(db, sqlcmd, &pRecord, &rows, &cols, &zErr);
}
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 7 operation costs %f\n", tmcost);
}
//开启事务
else if(atoi(argv[1]) == 8)
{
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN", 0, 0, NULL);
for( i = 0; i < 1000000; i++)
{
snprintf(sqlcmd, sizeof(sqlcmd), "select * from testinfo where id = %d", i);
sqlite3_get_table(db, sqlcmd, &pRecord, &rows, &cols, &zErr);
}
sqlite3_exec(db, "COMMIT", 0, 0, NULL);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 8 operation costs %f\n", tmcost);
}
//执行准备
else if(atoi(argv[1]) == 9)
{
char *sql = "select * from testinfo where id = ?";
gettimeofday(&tmv1, NULL);
sqlite3_exec(db, "BEGIN", 0, 0, NULL);
sqlite3_prepare_v2(db, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
rc = sqlite3_step(stmt);
while(rc == SQLITE_ROW)
{
n1 = sqlite3_column_int(stmt, 0);
n2 = sqlite3_column_int(stmt, 1);
ch1 = sqlite3_column_text(stmt, 2);
ch2 = sqlite3_column_text(stmt, 3);
rc = rc = sqlite3_step(stmt);
}
}
sqlite3_finalize(stmt);
sqlite3_exec(db, "COMMIT", 0, 0, NULL);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 9 operation costs %f\n", tmcost);
}
//内存数据库
else if(atoi(argv[1]) == 10)
{
rc = sqlite3_open(":memory:", &dbMem);
rc = sqlite3_exec(dbMem, "create table if not exists testinfo (id integer primary key, age integer, height text, weight text)", NULL, NULL, &zErr);
sqlite3_exec(dbMem, "PRAGMA synchronous = OFF; ", 0,0,0);
gettimeofday(&tmv1, NULL);
sqlite3_exec(dbMem, "BEGIN;", 0, 0, 0);
const char* sql = "insert into testinfo values(?,?,?,?)";
sqlite3_prepare_v2(dbMem, sql, strlen(sql), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sprintf(tmpstr, "%d", i*10);
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
sqlite3_bind_int(stmt, 2, i*2);
sqlite3_bind_text(stmt, 3, tmpstr, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 4, buf, -1, SQLITE_STATIC);
sqlite3_step(stmt);
}
sqlite3_finalize(stmt);
sqlite3_exec(dbMem, "COMMIT;", 0, 0, 0);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 6 operation costs %f\n", tmcost);
char *sql2 = "select * from testinfo where id = ?";
gettimeofday(&tmv1, NULL);
sqlite3_exec(dbMem, "BEGIN", 0, 0, NULL);
sqlite3_prepare_v2(dbMem, sql2, strlen(sql2), &stmt, 0);
for(i = 0; i < 1000000; i++)
{
sqlite3_reset(stmt);
sqlite3_bind_int(stmt, 1, i);
rc = sqlite3_step(stmt);
while(rc == SQLITE_ROW)
{
n1 = sqlite3_column_int(stmt, 0);
n2 = sqlite3_column_int(stmt, 1);
ch1 = sqlite3_column_text(stmt, 2);
ch2 = sqlite3_column_text(stmt, 3);
rc = rc = sqlite3_step(stmt);
}
}
sqlite3_finalize(stmt);
sqlite3_exec(dbMem, "COMMIT", 0, 0, NULL);
gettimeofday(&tmv2, NULL);
tmcost = (float)(tmv2.tv_sec*1000*1000+tmv2.tv_usec - tmv1.tv_sec*1000*1000+tmv1.tv_usec)/1000000;
printf("the 9 operation costs %f\n", tmcost);
}
sqlite3_close(db);
return 1;
}
编译方法：
将sqlite3.c sqlite3.h放在同级目录
gcc test.c sqlite3.c -o test -lpthread -ldl
扫一扫
8807
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
