SQLite优化

1. 建表优化

SQLite的数据库本质文件读写操作，频繁操作打开和关闭是很耗时和浪费资源的；

优化方法事务机制：

这里要注意一点：事务的开启是要锁定DB的，其他对DB的写入操作都是无法成功的。

db.beginTransaction(); //手动设置开始事务

try {

       //这里写你数据操作

       db.setTransactionSuccessful();//设置事务处理成功，不设置会自动回滚不提交

} catch (Exception e) {

} finaly {

       db.endTransaction();//提交

}

项目中不会把项目上万条数据存SQL里的，尽管android有SQLite。

那样处理起来非常慢，而且程序经常出现ANR。

打个比方：有200个城市，每个城市500条城市信息，你怎么创建表

A：我创建一张表存10000条数据。

B：200张表，每张存500条数据。

C：我创建两张表，

      一张存city，其实这张表只有1条数据；

      Id(编号)，

      Version（这200个城市更新版本用）

      CityDate（200个城市xml格式字符串数据）。

      另一张表存城市信息表：200条数据，每个城市一条数据

      id（CityDate解析出城市对象对应的id）

      Version（这500条城市信息更新版本用）

      CityMessage（500条城市信息xml格式字符串数据）。

首先你给用户展示200城市（你只取了一条数据，200个城市xml格式字符串数据进行解析）

用户点击一个城市你显示500条记录（通过城市解析ID，取出城市信息表中对应500数据xml格式字符串数据进行解析）

这样做的好处：

（1）统一数据接口，无论你从网络上直接去数据，还是读本地缓存统一数据接口，xml

（2）数据进行排序，内存操作要快一些；

（3）其实这和自己写文件没什么区别，为什么还要用数据库那，这么做有利于程序版本更新升级，数据维护！

2. 其他技巧

1) 相对于封装过的ContentProvider而言，使用原始SQL语句执行效率高，比如使用方法rawQuery、execSQL的执行效率比较高。

2) 对于需要一次性修改多个数据时，可以考虑使用SQLite的事务方式批量处理，我们定义SQLiteDatabase db对象，执行的顺序为：

db.beginTransaction();//这里处理数据添加，删除或修改的SQL语句

db.setTransactionSuccessful();//这里设置处理成功

db.endTransaction();//这句很重要，告诉数据库处理完成了，这时SQLite的底层会执行具体的数据操作。

3) 打好SQL语句的基础，对于查询，以及分配表的结构都十分重要

3. 其他

1) 影响查询性能的因素：

Ø 对表中行的检索数目，越小越好

Ø 排序与否。

Ø 是否要对一个索引。

Ø 查询语句的形式

2) 几个查询优化的转换

Ø 对于单个表的单个列而言，如果都有形如T.C=expr这样的子句，并且都是用OR操作符连接起来，形如：x = expr1 OR expr2 = x OR x = expr3 此时由于对于OR，在SQLite中不能利用索引来优化，所以可以将它转换成带有IN操作符的子句：x IN(expr1,expr2,expr3)这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。

Ø 如果一个子句的操作符是BETWEEN，在SQLite中同样不能用索引进行优化，所以也要进行相应的等价转换：如：a BETWEEN b AND c可以转换成：(a BETWEEN bAND c) AND (a>=b) AND (a<=c)。在上面这个子句中，(a>=b)AND (a<=c)将被设为dynamic且是(aBETWEEN b AND c)的子句，那么如果BETWEEN语句已经编码，那么子句就忽略不计，如果存在可利用的index使得子句已经满足条件，那么父句则被忽略。

Ø 如果一个单元的操作符是LIKE，那么将做下面的转换：x LIKE ‘abc%’，转换成：x>=‘abc’AND x<‘abd’。因为在SQLite中的LIKE是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也还是比不上转换后的效率的。

3) 几种查询语句的处理（复合查询）

查询语句为：<SelectA><operator> <selectB>ORDER BY <orderbylist> ORDER BY <orderbylist> 的执行方法：is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 这个语句的执行过程是先将selectA和selectB执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程：

outA: 将selectA的结果的一行放到最终结果集中

outB: 将selectA的结果的一行放到最终结果集中(只有UNION操作和UNION ALL操作，其它操作都不放入最终结果集中)

AltB: 当selectA的当前记录小于selectB的当前记录

AeqB: 当selectA的当前记录等于selectB的当前记录

AgtB: 当selectA的当前记录大于selectB的当前记录

EofA: 当selectA的结果遍历完

EofB: 当selectB的结果遍历完

4. 子查询扁平化

例子：SELECT a FROM (SELECT x+y AS aFROM t1 WHERE z<100) WHERE a>5

对这个SQL语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了，如果对上面的SQL进行处理后可以得到如下SQL语句：SELECTx+y AS a FROM t1 WHERE z<100 AND a>5，这个结果显然和上面的一样，但此时只需要对数据进行查询一次就够了，另外如果在表t1上有索引的话就避免了遍历整个表。

运用flatten方法优化SQL的条件：

a) 子查询和外查询没有都用集函数

b) 子查询没有用集函数或者外查询不是个表的连接

c) 子查询不是一个左外连接的右操作数

d) 子查询没有用DISTINCT或者外查询不是个表的连接

e) 子查询没有用DISTINCT或者外查询没有用集函数

f) 子查询没有用集函数或者外查询没有用关键字DISTINCT

g) 子查询有一个FROM语句

h) 子查询没有用LIMIT或者外查询不是表的连接

i)  子查询没有用LIMIT或者外查询没有用集函数

j)  子查询没有用集函数或者外查询没用LIMIT

k) 子查询和外查询不是同时是ORDER BY子句

l)  子查询和外查询没有都用LIMIT

m) 子查询没有用OFFSET

n) 外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT

o) 外查询没有用集函数子查询不包含ORDER BY

复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNIONALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。

SELECT a+1 FROM (SELECT x FROM tab UNION ALL SELECT yFROM tab UNION ALL SELECT abs(z*2) FROM tab2) WHERE a!=5 ORDER BY 1

转换为：

SELECT x+1 FROM tab WHERE x+1!=5 UNION ALL SELECT y+1FROM tab WHERE y+1!=5 UNION ALL SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5ORDER BY 1

5. 连接查询

在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到FROM最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。

优化的方法如下：

a) 对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）：

b) 如果没有索引，则找有没有在这个表上对rowid的查询条件：如果有Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计。如果没有Rowid=EXPR 但有rowid IN (...)，而IN是一个列表，那么记录返回记录数为IN列表中元素的个数，估计代价为NlogN,如果IN不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为100，代价为200。如果对rowid是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。如果这个查询还要求排序，则再另外加上排序的代价NlogN如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

如果WHERE子句中存在OR操作符，那么要把这些OR连接的所有子句分开再进行分析。

如果有子句是由AND连接符构成，那么再把由AND连接的子句再分别分析。如果连接的子句的形式是X<op><expr>，那么就再分析这个子句。接下来就是把整个对OR操作的总代价计算出来。如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

c) 如果有索引，则统计每个表的索引信息，对于每个索引：

先找到这个索引对应的列号，再找到对应的能用到（操作符必须为=或者是IN（…））这个索引的WHERE子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的操作符是什么，如果是=，那么没有附加的代价，如果是IN（sub-select），那么估计它附加代价inMultiplier为25，如果是IN（list），那么附加代价就是N（N为list的列数）。再计算总的代价和总的查询结果记录数和代价。nRow =pProbe->aiRowEst * inMultiplier;/*计算行数*/cost = nRow* estLog(inMultiplier);/*统计代价*/如果找不到操作符为=或者是IN（…）的子句，而是范围的查询，那么同样只好估计查询结果记录数为nRow/3，估计代价为cost/3。同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。

d) 通过上面的优化过程，可以得到对一个表查询的总代价

再对第二个表进行同样的操作，这样如此直到把FROM子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。

e) 所以循环的嵌套顺序不一定是与FROM子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。

6. 索引

在SQLite中，有以下几种索引：

Ø  单列索引

Ø  多列索引

Ø  唯一性索引

对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。

运用索引的注意事项：

1) 对于一个很小的表来说没必要建立索引

2) 在一个表上如果经常做的是插入更新操作，那么就要节制使用索引

3) 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引。

索引的运用时机：

1) 操作符：=、>、<、IN等

2) 操作符BETWEEN、LIKE、OR不能用索引，如BETWEEN：SELECT* FROM mytable WHERE myfield BETWEEN 10 and 20;这时就应该将其转换成：SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20;此时如果在myfield上有索引的话就可以用了，大大提高速度再如LIKE：SELECT * FROM mytable WHERE myfield LIKE 'sql%';此时应该将它转换成：SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield <'sqm';此时如果在myfield上有索引的话就可以用了，大大提高速度。再如OR：SELECT * FROM mytable WHERE myfield ='abc' OR myfield = 'xyz';此时应该将它转换成：SELECT * FROMmytable WHERE myfield IN ('abc', 'xyz');此时如果在myfield上有索引的话就可以用了，大大提高速度

3) 有些时候索引都是不能用的，这时就应该遍历全表

 

转自：http://www.cnblogs.com/devinzhang/archive/2012/01/16/2323949.html