Sqlite优化总结

最新推荐文章于 2024-03-11 12:08:56 发布

ZWaruler

最新推荐文章于 2024-03-11 12:08:56 发布

阅读量2k

点赞数 2

分类专栏： Android Java

本文链接：https://blog.csdn.net/u010483016/article/details/83275571

版权

Android 同时被 2 个专栏收录

24 篇文章 0 订阅

订阅专栏

Java

15 篇文章 0 订阅

订阅专栏

一.使用索引

创建索引的基本语法如下:   
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. 在条件参数中使用计算的时候,无法使用占位符,不然计算的结果不正确.

ZWaruler

关注

2
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
Sqlite优化总结

一.使用索引创建索引的基本语法如下: CREATE INDEX index_name ON table_name (column_name,...)间接索引:定义主键约束或者唯一性约束,可以间接创建索引,主键默认为唯一约束.所有不需要再创建索引,以免造成浪费.注意事项: 1.建立索引会增加数据库的大小. 2.对于insert,update,delete操作来说,使用索引会变...
复制链接

扫一扫