本文主要演示sqlite开源项目中的数据库方法类索引操作使用说明(包括新建索引、查询数据、检查索引是否存在等)。本项目旨在分享知识和经验,欢迎广大网友一起加入到开源项目中一起分享贡献知识和经验。
话不多说,先上测试代码
/******************************************************************************
> File Name : samples_add_index.cpp
> Author : Wseldom
> Mail : 3235459847@qq.com
> Created Time : Mon 27 Nov 2023 03:36:28 PM CST
******************************************************************************/
#include "./samples_add_index.hpp"
#include "sqlite/sqlite.h"
#include <stdlib.h>
#include<time.h>
#include <sys/time.h>
int samples_add_index (int argc, char** argv)
{
if (argc < 3) {
printf ("请输入正确的格式,如:./exec 1 test.db\n");
return -1;
}
sql::sqlite db(argv[2]);
db.exec ("create table if not exists employee("
"ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"NAME TEXT NOT NULL, "
"AGE INT NOT NULL, "
"ADDRESS CHAR(50), "
"SALARY REAL"
");");
switch (atoi (argv[1])) {
case 0:
{// 添加数据
char buff[1024];
int pos = snprintf (buff, sizeof (buff), "insert into employee(ID,NAME,AGE,ADDRESS,SALARY)values(NULL,'员工");
srand((unsigned)time(NULL));
for (int i = 0; i < 3000000; i++) {
snprintf (buff + pos, sizeof (buff) - pos, "%d',%d,'城市%d',%d);", i, rand() % 60, i % 30, rand () % 30000);
db.exec (buff);
printf ("加载%%%f......\n", i / 3000000.0 * 100);
}
break;
}
case 1:
{// 带索引查询
struct timeval start,end;
long long us_count;
long long s_count;
db.exec (SQLCMD_CREATE_IDX ("employee", "city", "ADDRESS"));
sql::query* query = NULL;
gettimeofday (&start, NULL);
query = db.query ("select * from employee where ADDRESS='城市28';");
gettimeofday (&end, NULL);
if (end.tv_usec >= start.tv_usec) {
us_count = end.tv_usec - start.tv_usec;
s_count = end.tv_sec - start.tv_sec;
} else {
us_count = 1000000 + end.tv_usec - start.tv_usec;
s_count = end.tv_sec - start.tv_sec - 1;
}
printf ("耗时:%lld s %lld ms %lld us\n"
, s_count
, (us_count / 1000), us_count % 1000);
if (query) delete query;
break;
}
case 2:
{// 不带索引查询
struct timeval start,end;
long long us_count;
long long s_count;
sql::query* query = NULL;
db.exec (SQLCMD_DROP_IDX ("city"));
gettimeofday (&start, NULL);
query = db.query ("select * from employee where ADDRESS='城市28';");
gettimeofday (&end, NULL);
if (end.tv_usec >= start.tv_usec) {
us_count = end.tv_usec - start.tv_usec;
s_count = end.tv_sec - start.tv_sec;
} else {
us_count = 1000000 + end.tv_usec - start.tv_usec;
s_count = end.tv_sec - start.tv_sec - 1;
}
printf ("耗时:%lld s %lld ms %lld us\n"
, s_count
, (us_count / 1000), us_count % 1000);
if (query) delete query;
break;
}
case 3:
{
if (db.record_count (SQLCMD_IDX_EXIST ("city"))) {
db.exec (SQLCMD_DROP_IDX ("city"));
printf ("删除索引city\n");
} else {
printf ("创建索引city\n");
db.exec (SQLCMD_CREATE_IDX ("employee", "city", "ADDRESS"));
}
break;
}
}
return 0;
}
插入3000000条记录
先执行make type=4编译上面的测试代码。
然后执行如下命令插入记录
./exec 0 test.db
插入数据量大,需要比较长的实践,可以先泡杯咖啡慢慢等。
不使用索引查询
现在先不使用索引查询一次看看需要多长时间。
添加索引查询
然后新建索引进行查询看看需要多长时间。
从实验结果看,新建了索引后查询速度快了约一倍。
检查索引是否存在
检查索引是否存在,如存在则删除索引,如不存在则新建索引。