sqlite性能优化1

创建索引的基本语法如下:
CREATE INDEX index_name ON table_name (column_name,...)

间接索引:
定义主键约束或者唯一性约束,可以间接创建索引,主键默认为唯一约束.所 有不需要再创建索引,以免造成浪费.

注意事项:
1.建立索引会增加数据库的大小.
2.对于insert,update,delete操作来说,使用索引会变慢,因为同时需要维护索引的变化.
3.为数据量较小的表建立索引,往往会事倍功半.

使用索引需要根据实际情况权衡利弊,对于查询操作量级较大,查询要求较高的推荐使用索引

使用场景
a. 当某字段数据更新频率较低，查询频率较高，经常有范围查询(>, <, =, >=, <=)或order by、group by发生时建议使用索引。并且选择度越大，建索引越有优势，这里选择度指一个字段中唯一值的数量/总的数量。
b. 经常同时存取多列，且每列都含有重复值可考虑建立复合索引

索引使用规则

a.对于复合索引，把使用最频繁的列做为前导列(索引中第一个字段)。如果查询时前导列不在查询条件中则该复合索引不会被使用。
如create unique index PK_GRADE_CLASS on student (grade, class)
select * from student where class = 2未使用到索引
select * from dept where grade = 3使用到了索引

b.避免对索引列进行计算，对where子句列的任何计算如果不能被编译优化，都会导致查询时索引失效
select * from student where tochar(grade)=’2′
c.比较值避免使用NULL
d.多表查询时要注意是选择合适的表做为内表。连接条件要充份考虑带有索引的表、行数多的表，内外表的选择可由公式：
外层表中的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。实际多表操作在被实际执行前，查询优化器
会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。
e. 查询列与索引列次序一致
f. 用多表连接代替EXISTS子句
g. 把过滤记录数最多的条件放在最前面


索引的运用时机：

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 * FROM mytable WHERE myfield IN ('abc', 'xyz');
此时如果在myfield上有索引的话就可以用了，大大提高速度

SQLite想要执行操作，需要将程序中的sql语句编译成对应的SQLiteStatement，比如select * from record这一句，被执行100次就需要编译100次。对于批量处理插入或者更新的操作，我们可以使用显式编译来做到重用SQLiteStatement。

想要做到重用SQLiteStatement也比较简单，基本如下：
1.编译sql语句获得SQLiteStatement对象，参数使用?代替
2.在循环中对SQLiteStatement对象进行具体数据绑定，bind方法中的index 从1开始，不是0

请参考如下简单的使用代码:
private void insertWithPreCompiledStatement(SQLiteDatabase db) {
String sql = "INSERT INTO " + TableDefine.TABLE_RECORD + "( " + TableDefine.COLUMN_INSERT_TIME + ") VALUES(?)";
SQLiteStatement statement = db.compileStatement(sql);
int count = 0;
while (count < 100) {
count++;
statement.clearBindings();
statement.bindLong(1, System.currentTimeMillis());
statement.executeInsert();
}
}

在Android中,每次数据库插入,更新操作都开启了事务,确保操作的原子性.事务的实现需要借助rollback journal文件,因此每次数据库操作都会对文件进行打开读写关闭操作.
在批量的进行操作时,就会反复的对文件进行打开读写关闭,降低执行的效率.此时,我们可以显式的使用事务,将批量操作导致的文件操作次数变为一次.

具体的实现代码如下：
private void insertWithTransaction(SQLiteDatabase db) {
int count = 0;
ContentValues values = new ContentValues();
try {
db.beginTransaction();
while (count++ < 100) {
values.put(TableDefine.COLUMN_INSERT_TIME, System.currentTimeMillis());
db.insert(TableDefine.TABLE_RECORD, null, values);
}
db.setTransactionSuccessful();
} catch (Exception e) {
e.printStackTrace();
} finally {
db.endTransaction();
}
}

影响查询性能的因素：

1． 对表中行的检索数目，越小越好
2． 排序与否。
3． 是否要对一个索引。
4． 查询语句的形式

几个查询优化的转换

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

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

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

子查询扁平化

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

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

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

1.子查询和外查询没有都用集函数
2.子查询没有用集函数或者外查询不是个表的连接
3.子查询不是一个左外连接的右操作数
4.子查询没有用DISTINCT或者外查询不是个表的连接
5.子查询没有用DISTINCT或者外查询没有用集函数
6.子查询没有用集函数或者外查询没有用关键字DISTINCT
7.子查询有一个FROM语句
8.子查询没有用LIMIT或者外查询不是表的连接
9.子查询没有用LIMIT或者外查询没有用集函数
10.子查询没有用集函数或者外查询没用LIMIT
11.子查询和外查询不是同时是ORDER BY子句
12.子查询和外查询没有都用LIMIT
13.子查询没有用OFFSET
14.外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT
15.外查询没有用集函数子查询不包含ORDER BY
16.复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNION ALL复合查询，但他是都由若干个非集函数的查询构 成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。

SELECT a+1 FROM (
SELECT x FROM tab
UNION ALL
SELECT y FROM 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+1 FROM tab WHERE y+1!=5
UNION ALL
SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5
ORDER BY 1