Sqlite优化总结

一.使用索引

创建索引的基本语法如下:   
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上有索引的话就可以用了,大大提高速度

二.编译SQL语句

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();
			}
		}

四.查询时返回更少的结果集及更少的字段

	查询时,查询列的参数为null时,默认查询所有的列.如果你不需要查询所有的列时,请将具体的列名传入参数中,避免使用null,这样可以提高查询效率.
	如果只需要返回一条数据时,可以添加limit限制,这样找到一条结果后就不会再继续查询了.

五.避免使用Cursor.getColumnIndex()

	Cursor.getColumnIndex()的时间差不多跟Cursor.getInt()相同,因此,避免使用它,尤其是在循环中.
	使用常量来代替Cursor.getColumnIndex(),或者在循环外使用一次Cursor.getColumnIndex()来获取index.

六.设定合适的ContentValues容量

	ContentValues内部采用了HashMap来存储数据,初始容量是8.当添加的数据超过该数值时,会进行扩容.因此可以为ContentValues设置恰当的容量初始值,
	避免内部频繁进行扩容,尤其是在循环中使用的时候.
	在使用其他List,Map等容器的时候,也可以参考此点.

七.及时关闭Cursor

八.查询优化

影响查询性能的因素:

    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

九.表数据拷贝到另外一张表中

	INSERT INTO COMPANY_BKP
	SELECT * FROM COMPANY 
	WHERE ID IN (SELECT ID 
              FROM COMPANY) ;

	可以先将原表重命名,然后创建新的表,在将数据拷贝到新表中.

十.常用函数

SQLite 有许多内置函数用于处理字符串或数字数据。下面列出了一些有用的 SQLite 内置函数,且所有函数都是大小写不敏感,这意味着您可以使用这些函数的小写形式或大写形式或混合形式:

序号	函数 & 描述
1	SQLite COUNT 函数
	SQLite COUNT 聚集函数是用来计算一个数据库表中的行数。
	
2	SQLite MAX 函数
	SQLite MAX 聚合函数允许我们选择某列的最大值。
	
3	SQLite MIN 函数
	SQLite MIN 聚合函数允许我们选择某列的最小值。
	
4	SQLite AVG 函数
	SQLite AVG 聚合函数计算某列的平均值。
	
5	SQLite SUM 函数
	SQLite SUM 聚合函数允许为一个数值列计算总和。
	
6	SQLite RANDOM 函数
	SQLite RANDOM 函数返回一个介于 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数。
	
7	SQLite ABS 函数
	SQLite ABS 函数返回数值参数的绝对值。
	
8	SQLite UPPER 函数
	SQLite UPPER 函数把字符串转换为大写字母。
	
9	SQLite LOWER 函数
	SQLite LOWER 函数把字符串转换为小写字母。
	
10	SQLite LENGTH 函数
	SQLite LENGTH 函数返回字符串的长度。
	
11	SQLite sqlite_version 函数
	SQLite sqlite_version 函数返回 SQLite 库的版本。

十一.使用数学计算时的注意事项

在sqlite语句中,可以直接进行数学运算以及位运算,其中有一些注意事项.
1. 可以在查询中对各列的数值进行运算,也可以在条件中对各列的数值进行限制.
2. 在条件参数中使用计算的时候,无法使用占位符,不然计算的结果不正确.
发布了29 篇原创文章 · 获赞 14 · 访问量 8万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览