SQLite查询优化的例子
2009-03-07 15:19 竹 石 阅读(3555) 评论(1) 编辑 收藏 举报上次讲到了SQLite的查询优化代码中的具体实现，现在来看一下它的几个实例：
1 #include "stdio.h"
2 #include "sqlite3.h"
3 #include <windows.h>
4 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql);
5
6 int main(int argc, char **argv)
7 {
8 sqlite3 *db;
9 char *zErr;
10 int rc;
11 char *sql;
12 sqlite3_stmt *stmt=0;
13 rc = sqlite3_open("memory.db", &db);
14 if(rc) {
15 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
16 sqlite3_close(db);
17 }
18
19 //下面是所建的各个表的结构
20 sql="CREATE TABLE t1 (num int,word TEXT NOT NULL)";
21 //sql="CREATE TABLE t4 (num INTEGER NOT NULL,word TEXT NOT NULL)";
22 //sql="CREATE TABLE t3 (num INTEGER NOT NULL,word TEXT NOT NULL)";
23 rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
24 if(rc != SQLITE_OK) {
25 if (zErr != NULL) {
26 fprintf(stderr, "SQL error: %s\n", zErr);
27 sqlite3_free(zErr);
28 }
29 }
30
31 //下面是对所以插入进行手动提交，这样可以加快插入速度
32 //sqlite3_exec(db,"BEGIN",NULL,NULL,&zErr);
33 //插入1000000条记录
34 //for (int i=0;i<1000000;i++)
35 //{
36 // sql = sqlite3_mprintf("insert into t1 values(%d,'%s')",i,"goodc");
37 // rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
38 //}
39 //sqlite3_exec(db,"COMMIT",NULL,NULL,&zErr);
40
41 sql="create index t1nwindex on t1(num)";
42 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
43 if(rc != SQLITE_OK) {
44 if (zErr != NULL) {
45 fprintf(stderr, "SQL error: %s\n", zErr);
46 sqlite3_free(zErr);
47 }
48 }
49
50 //sql="drop index t1nwindex";
51 //sql="drop index t3index";
52 //sql="delete from t2";
53 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
54 if(rc != SQLITE_OK) {
55 if (zErr != NULL) {
56 fprintf(stderr, "SQL error: %s\n", zErr);
57 sqlite3_free(zErr);
58 }
59 }
60
61 printf("查询结果是：\n");
62 //sql="select * from t1 where num=3000 or num=2000";//有INTEGER PRIMARY KEY，快
63 //sql="select * from t2 where num=3000 or num=2000";//没有索引，慢
64 //sql="select * from t3 where num=3000 or num=2000";//有索引，快
65
66 //这里交换位置了，但是结果用的时间想差比较大的原因是，t1是用索引存储的，但是它不是由create index
67 //而创建的，所以系统还不会把它作为索引处理，所以这两个表就只是无索引的表，在内部优化计算代价只是对它
68 //进行估计，因为源代码中没有捕获到下面的查询条件，所以都是系统最大值（源代码中有），所以就嵌套顺序没
69 //变，所以出现下面的差异。
70 //sql="SELECT count(*) FROM t3, t1 WHERE t1.num = t3.num";//比下面的快，由于内层少
71 //sql="SELECT count(*) FROM t1, t3 WHERE t1.num = t3.num";//比上面的慢，由于内层多
72
73 //下面这个已经内部实现优化，所以所用时间是相同的
74 //sql="SELECT * FROM t2, t3 WHERE t2.num = t3.num";//有索引，稍快
75 //sql="SELECT * FROM t3, t2 WHERE t2.num = t3.num";//同上，内部已经优化
76
77 //sql="select * from t3 where num=8000";//有索引，快
78 //sql="select * from t1 where num%2=0";//有索引,但不能用，很慢
79 //sql="select * from t1 where num=8000";//没有索引，慢
80
81 //BETWEEN的转换优化---内部已经实现优化,如果有索引的话快一点
82 //sql="select count(*) from t2 where word between 'goodl' and 'goodm'";//BETWEEN
83 //sql="select count(*) from t2 where word >='goodl' and word<'goodm'";//BETWEEN的转换
84
85 //LIKE的转换优化---内部已经实现优化
86 //sql="select count(*) from t2 where word like 'goodl%'";//有索引不起作用
87 //sql="select count(*) from t2 where word >='goodl' and word <'goodm'";//如果有索引会更快
88
89 //IN的转换优化---内部没有实现优化,但此时如果可以用索引的话就会很好
90 //如果不用索引则在这里体现不出IN比OR优，而如果有索引则差别很明显
91 //sql="select count(*) from t2 where word in('goodllll','goodkkkk','goodaaaa')";
92 //sql="select count(*) from t2 where word ='goodllll' or word ='goodkkkk' or word='goodaaaa'";
93
94 int start=GetTickCount();
95 query(db,stmt,sql);
96 printf("the time has pass:%dms\n",GetTickCount()-start);
97 sqlite3_close(db);
98 return 0;
99 }
100
101 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql){
102 int rc,ncols,i;
103 const char *tail;
104 rc = sqlite3_prepare(db, sql, -1, &stmt, &tail);
105 if(rc != SQLITE_OK) {
106 fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
107 }
108 rc = sqlite3_step(stmt);
109 ncols = sqlite3_column_count(stmt);
110 while(rc == SQLITE_ROW) {
111 for(i=0; i < ncols; i++) {
112 fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
113 }
114 fprintf(stderr, "\n");
115 rc = sqlite3_step(stmt);
116 }
117 }
2 #include "sqlite3.h"
3 #include <windows.h>
4 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql);
5
6 int main(int argc, char **argv)
7 {
8 sqlite3 *db;
9 char *zErr;
10 int rc;
11 char *sql;
12 sqlite3_stmt *stmt=0;
13 rc = sqlite3_open("memory.db", &db);
14 if(rc) {
15 fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
16 sqlite3_close(db);
17 }
18
19 //下面是所建的各个表的结构
20 sql="CREATE TABLE t1 (num int,word TEXT NOT NULL)";
21 //sql="CREATE TABLE t4 (num INTEGER NOT NULL,word TEXT NOT NULL)";
22 //sql="CREATE TABLE t3 (num INTEGER NOT NULL,word TEXT NOT NULL)";
23 rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
24 if(rc != SQLITE_OK) {
25 if (zErr != NULL) {
26 fprintf(stderr, "SQL error: %s\n", zErr);
27 sqlite3_free(zErr);
28 }
29 }
30
31 //下面是对所以插入进行手动提交，这样可以加快插入速度
32 //sqlite3_exec(db,"BEGIN",NULL,NULL,&zErr);
33 //插入1000000条记录
34 //for (int i=0;i<1000000;i++)
35 //{
36 // sql = sqlite3_mprintf("insert into t1 values(%d,'%s')",i,"goodc");
37 // rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
38 //}
39 //sqlite3_exec(db,"COMMIT",NULL,NULL,&zErr);
40
41 sql="create index t1nwindex on t1(num)";
42 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
43 if(rc != SQLITE_OK) {
44 if (zErr != NULL) {
45 fprintf(stderr, "SQL error: %s\n", zErr);
46 sqlite3_free(zErr);
47 }
48 }
49
50 //sql="drop index t1nwindex";
51 //sql="drop index t3index";
52 //sql="delete from t2";
53 rc=sqlite3_exec(db, sql, NULL, NULL, &zErr);
54 if(rc != SQLITE_OK) {
55 if (zErr != NULL) {
56 fprintf(stderr, "SQL error: %s\n", zErr);
57 sqlite3_free(zErr);
58 }
59 }
60
61 printf("查询结果是：\n");
62 //sql="select * from t1 where num=3000 or num=2000";//有INTEGER PRIMARY KEY，快
63 //sql="select * from t2 where num=3000 or num=2000";//没有索引，慢
64 //sql="select * from t3 where num=3000 or num=2000";//有索引，快
65
66 //这里交换位置了，但是结果用的时间想差比较大的原因是，t1是用索引存储的，但是它不是由create index
67 //而创建的，所以系统还不会把它作为索引处理，所以这两个表就只是无索引的表，在内部优化计算代价只是对它
68 //进行估计，因为源代码中没有捕获到下面的查询条件，所以都是系统最大值（源代码中有），所以就嵌套顺序没
69 //变，所以出现下面的差异。
70 //sql="SELECT count(*) FROM t3, t1 WHERE t1.num = t3.num";//比下面的快，由于内层少
71 //sql="SELECT count(*) FROM t1, t3 WHERE t1.num = t3.num";//比上面的慢，由于内层多
72
73 //下面这个已经内部实现优化，所以所用时间是相同的
74 //sql="SELECT * FROM t2, t3 WHERE t2.num = t3.num";//有索引，稍快
75 //sql="SELECT * FROM t3, t2 WHERE t2.num = t3.num";//同上，内部已经优化
76
77 //sql="select * from t3 where num=8000";//有索引，快
78 //sql="select * from t1 where num%2=0";//有索引,但不能用，很慢
79 //sql="select * from t1 where num=8000";//没有索引，慢
80
81 //BETWEEN的转换优化---内部已经实现优化,如果有索引的话快一点
82 //sql="select count(*) from t2 where word between 'goodl' and 'goodm'";//BETWEEN
83 //sql="select count(*) from t2 where word >='goodl' and word<'goodm'";//BETWEEN的转换
84
85 //LIKE的转换优化---内部已经实现优化
86 //sql="select count(*) from t2 where word like 'goodl%'";//有索引不起作用
87 //sql="select count(*) from t2 where word >='goodl' and word <'goodm'";//如果有索引会更快
88
89 //IN的转换优化---内部没有实现优化,但此时如果可以用索引的话就会很好
90 //如果不用索引则在这里体现不出IN比OR优，而如果有索引则差别很明显
91 //sql="select count(*) from t2 where word in('goodllll','goodkkkk','goodaaaa')";
92 //sql="select count(*) from t2 where word ='goodllll' or word ='goodkkkk' or word='goodaaaa'";
93
94 int start=GetTickCount();
95 query(db,stmt,sql);
96 printf("the time has pass:%dms\n",GetTickCount()-start);
97 sqlite3_close(db);
98 return 0;
99 }
100
101 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql){
102 int rc,ncols,i;
103 const char *tail;
104 rc = sqlite3_prepare(db, sql, -1, &stmt, &tail);
105 if(rc != SQLITE_OK) {
106 fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(db));
107 }
108 rc = sqlite3_step(stmt);
109 ncols = sqlite3_column_count(stmt);
110 while(rc == SQLITE_ROW) {
111 for(i=0; i < ncols; i++) {
112 fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i));
113 }
114 fprintf(stderr, "\n");
115 rc = sqlite3_step(stmt);
116 }
117 }
