sqlite的索引优化
转载于这个博客（对这位朋友表示感谢）：
http://hi.baidu.com/3g13/blog/item/f12dc5131313aa1e5baf5301.html
一直比较喜欢文本型数据库，简单、方便，容易保存。最近将一个网站的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对事务的并发控制是通过本地文件锁低级原语来进行的。若有部分数据残存于网络分区中，文件锁将失效。
