数据的准备

数据库表

CREATE TABLE xcache1 (path STRING PRIMARY KEY UNIQUE NOT NULL,status INT NOT NULL,filetype INT);

上面的sql语句创建了一张表，主键为path。其它两个字段在本例中不会使用。

写入记录

下面使用Python生成500万条记录(insert.py)

#-*- coding:utf-8 -*-
import sqlite3
import random
#连接数据库
conn = sqlite3.connect("test.db")
#生成500万个整数
l = range(5000000)
#将500万个整数的顺序打乱
random.shuffle(l)
n = 0
#列举500万个无序整数
for i in l:
    #将整数转换为字符串，作为path的值
    sql = "insert or replace into xcache1 values(\"/%s\",0)"%(str(i))
    conn.execute(sql)
    n = n+1
    #每10万次插入提交一次
    if n%100000 == 0:
        conn.commit()
    print n
#提交剩余的数据
conn.commit()
#关闭数据库链接
conn.close()

数据查询

使用Python做10万次查询(select.py)

#-*- coding:utf-8 -*-
import sqlite3
import random
import time
conn = sqlite3.connect('test.db')
def select_test():
    #取0到100万间的一个随机数
    n = random.randint(0,10000000)
    #生成查询语句
    sql = "select * from xcache1 where path=\'/%s\'"%(str(n),)
    #执行查询并返回结果
    result = conn.execute(sql).fetchall()
    #print result

i = 0
start = time.time()
#执行10万次查询
while i < 100000:
    select_test()
    i = i+1
stop = time.time()
#得出花费的时间
print stop-start
conn.close()

10万次查询所花的时间是：

10.5310001373

创建索引后查询

创建索引

create index index_path on xcache1(path);

查询结果

执行10万次的查询，所花的时间：

10.4659998417

可以看出，此时有没有索引并没有对查询的效率产生任何的影响！

Why？

原因是xcache1中path是主键，sqlite3会在内部将它进行默认排序，用户无需对其创建索引。

如果path是非主键呢？

创建数据表

CREATE TABLE xcache2 (path STRING NOT NULL,filetype INT);

然后稍微修改一下insert.py，往xcache2中写入500万条记录。

未建索引时查询

修改一下select.py，查询的次数由10万次改为100次，同时注意查询的是xcache2表。
执行100次查询所花的时间为：

134.044999838

我想10万次就不必试了。

创建索引后查询

执行100次查询所花的时间为：

0.0120000839233

执行10万次查询所花的时间为：

10.1730000973

可见当path为非主键时，创建索引能大大提升查询的效率！
另外，path为非主键时执行10万次查询所花的时间与path为主键时执行同样次数查询所花的时间一样，正好说明了sqlite3对主键进行了默认排序。

关于查询的优化

查询的优化可以参考这里
这里举一个例子：
在xcache1表中（path为主键，500万条记录），执行如下查询：

sql = "select * from xcache1 where path like\'/100%\'"

需要花费2秒以上。但是如果换成如下查询：

sql = "select * from xcache where path >=\'/100\' and path<\'/101\'"

只需约0.2秒就可以完成。
原因是like操作并不使用索引。类似的，between,or操作也不使用索引。在作查询时需要特别注意。