sqlite的索引优化

一直比较喜欢文本型数据库，简单、方便，容易保存。最近将一个网站的mysql 数据库转换成了 2.5G 的 sqlite数据库。悲剧发生了，非常慢，网站经常超出30秒的运行时间,所以一直用缓存扛着，10.1 假期正好有空，决定彻底解决下这个慢的问题。

首先是首页慢，认真的分析了首页调用的函数，发现卡死经常在一个 collect 表上，打开collect 表，12万条数据，保守估计，应该至少占了1.5 G以上的空间，因为内容数据基本都在这个表上。怪不得慢，就算是物理机的高速硬盘，估计也扛不住这样的数据吧。

查看collect 表的索引，发现只有 vtype 和 class 2个整数型索引，将函数的sql语句拿出：

select id,title from collect where vtype=6 and class=0 limit 20

执行竟然要 40多秒！！！悲剧~~~~

马上建立class和vtype 2个字段的复合索引，建立索引用了将近1分钟。

然后查询，速度马上从40秒下降到 100ms 以内，第2次查询，速度在20ms以内，第3次，第4次和第2次是一个数量级的。

PS: 为什么第1次慢？按理说sqlite这种文本型数据库应该是没有缓存的，和mysql 、sqlserver这种服务型数据库来对比，没有缓存应该不会快的啊？  个人认为应该是操作系统的IO缓存所引起。以前操作系统复制文件的时候，复制之后删除，第2次复制，速度块了很多，很有可能是这个原因。

经过上面的优化，首页可以在300多ms打开了，将页面缓存设置为86400秒（也就是一天更新一次），第一次300多ms响应，以后基本上是10ms以内输出缓存，优化成功！

 

第2个需要优化的页面为列表页，12万条数据分页显示，代码很简单：

select id ,title from collect where vtype>=5 and class=0 limit 20

sql语句很快，可是页面很慢。。一直找不到原因。

后来发现是分页类的原因，分页类要求出count总数，有一条：

select count(*) from collect where vtype>=5 and class=0 

my god ， 悲剧发生了，2分钟，整整卡了2分钟才刷出一个38049 这个整数。。。为什么？明明做了复合索引了，怎么还这么慢？

于是又尝试了：select count(*) from collect , select count(*) from collect where vtype>5 , select count(*) from collect where class=0

这样的语句，都非常快，几毫秒求出结果，为什么上面的那么慢？？？证明，上面的where 条件根本没走索引。百度，google 关于sqlite复合索引的介绍都很少，只有一篇多表查询告诉你如何建索引的文章。看来只有自己尝试了。

首先怀疑是不是 >= 出了问题？ 于是将 语句改为：

select count(*) from collect where class=0 and vtype>4    （还是很慢）

select count(*) from collect where class=0 and vtype=5      (很快，20ms查询完)

select count(*) from collect where class=0 and vtype in (5,6)    （很慢)

select count(*) from collect where class=0 and (vtype=5 or vtype=6)    (很慢)

select count(*) from collect where (class=0 and vtype=5) or (class=0 and vtype=6)    （很快，20ms查询完)

select count(*) from collect where (vtype=5 and class=0) or (class=0 and vtype=6)    (很快)

结论：从上面可以看出，不能对vtype 不能有超过2种可能与 class做 and 操作，就会快，class和 vtyp 的顺序对查询结果没有影响；至于上面的是走了哪个索引，还没实验。

 

由于sqlite的explain语句没有mysql好用，无法知道具体的走索引细节，所以只能在此大胆推测+测试得出一些结论。

首先有1个问题： select id from collect where class=0 and vtype>4 limit 20 很快可以查到，证明走了复合索引。但select count(*) from collect where class=0 and vtype> 4   却很慢。 你说他没走索引嘛，至少where 后面的绝对走了索引。但你说他走了索引嘛，count(*) 确实在遍历，也就是索引里没记录总数。

为了验证为什么count那么慢，而limit很快，我做了一个实验

select id from collect where class=0 and vtype>4 limit 20       (8ms)

select id from collect where class=0 and vtype>4 limit 200     (16ms)

select id from collect where class=0 and vtype>4 limit 2000     (268ms)

select id from collect where class=0 and vtype>4  （不敢做下去，肯定很卡，基本上会超时）

20条很快，数量越多，越慢，但确实走了索引，没走索引之前，同样的语句慢得吓人，基本上是几分钟都出不来。

看下面的语句：

select id from collect where class=0 and vtype=5 limit 20   (0ms)

select id from collect where class=0 and vtype=5 limit 200  (2ms)

select id from collect where class=0 and vtype=5 limit 2000 (8ms)

select id from collect where class=0 and vtype=5                  (243ms,3.8万条记录)

上面可以看出，非常快，基本上是上面的10倍以上。和输出记录数基本上没很大差别。

以上2种语句的对比发现：对于复合索引，

1）  >, = 确实都会走索引，做简单的limit查询速度都很快；

2)   对于大数据输出，> 非常耗时，尤其是对于count ，在数据量较大时， 比较符号(<,> ) 将会直接卡死。

3）= 号速度最快，不管是count还是大数据输出，速度都可以接受，在平时的编程中，尽量多用 = 运算符来处理复合索引。

而对于单索引，则没有影响，不管>, = 速度都非常快。count函数也没有影响。

 

 

 

 

1.从网站下载了sqlite3 ，解压后放在D:\sqlite.里面有三个文件，分别是：sqlite3.def,sqlite3.dll,sqlite3.exe。

1).sqlite3.def: 用于编译生成相应的LIB文件

2).sqlite3.dll:提供sqlite所需的动态链接库文件。

3).sqlit3e.exe:执行文件，用于执行数据库操作

2. 示例：

1）。打开一个cmd程序，进入sqlite目录，执行：

d:\sqlite>sqlite3.exe my.db   -->创建一个数据库文件my.db

sqlite3>.help 　　 -->

sqlite3>.width [column1_max_width] [column2_max_width] .....

sqlite3>.output [filename] --->output the result to the special file

sqlite3>..tables，可以看数据库中的所有表； 
sqlite3>.indices，可以列出一个指定表的所有索引； 
sqlite3>.schema，创建表和索引时候的create语句，也可以在后面指明表名； 
sqlite3>.databases,查看当前打开的所有数据库，通常是一个main一个tem

sqlite3>.dump；  导出的SQL语句，可以放到ORACLE中去执行；

sqlite3>.explain:  就是查询一条SQL语句最终解析出来的执行计划,对SQL语句调优很有用；

sqlite3>.timeout: 默认超时时间是0，查询一张表或索引，发现表或索引被锁定，就立刻返回，不等待；

3.局限性：

1）sqlite只适合对单一文件进行读并发操作，不适合对其进行并发写事务。事务的局限性，使其应用受限，通常用于处理小型的事务。

2）不支持外键约束； 
3）对触发器的支持不是很完整； 
4）对ALTER TABLE的支持不是很完整； 
5）不支持嵌入式事务； 
6）不支持RIGHT和FULL OUTER JOIN. 
7）不支持视图的写更新操作； 
8）不支持GRANT和REVOKE；

9）不支持存储过程

10）文件锁：sqlite对事务的并发控制是通过本地文件锁低级原语来进行的。若有部分数据残存于网络分区中，文件锁将失效。