Android 数据库优化 索引

在android开发中会使用到数据库，在频繁的查询以及大量数据中查询的时候速度非常缓慢，容易出现卡顿的情况，可以使用索引进行优化

 

1、索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

2：原理

　索引是对数据库表中一个或多个列（例如，user表的姓名 (name) 列）的值进行排序的结构。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

　例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少

   的多。可见，索引是用来定位的。

 

3、根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

　　唯一索引 

　　唯一索引是不允许其中任何两行具有相同索引值的索引。

　　当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

　　主键索引

　　数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。

　　在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引 的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

　　聚集索引

　　在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。

　　如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。

4、索引的使用
       根据上文的分析，我们大致对什么时候使用索引有了自己的想法（如果你没有，回头再看一遍。。。）。一般我们需要在这些列上建立索引：
　　1）在经常需要搜索的列上，这是毋庸置疑的； 
　　2）经常同时对多列进行查询，且每列都含有重复值可以建立组合索引，组合索引尽量要使常用查询形成索引覆盖（查询中包含的所需字段皆包含于一个索引中，我们只需要搜索索引页即可完成查询）。 同时，该组合索引的前导列一定要是使用最频繁的列。

　　对于前导列的问题，在后面sqlite的索引使用介绍中还会做讨论。
　　3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度，连接条件要充分考虑带有索引的表。； 
　　4）在经常需要对范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的，同样，在经常需要排序的列上最好也创建索引。
　　6）在经常放到where子句中的列上面创建索引，加快条件的判断速度。要注意的是where字句中对列的任何操作（如计算表达式，函数）都需要对表进行整表搜索，而没有使用该列的索引。所以查询时尽量把操作移到等号右边。

　　对于以下的列我们不应该创建索引：
　　1）很少在查询中使用的列
　　2）含有很少非重复数据值的列，比如只有0，1，这时候扫描整表通常会更有效
　　3）对于定义为TEXT，IMAGE的数据不应该创建索引。这些字段长度不固定，或许很长，或许为空。
　　当然，对于更新操作远大于查询操作时，不建立索引。也可以考虑在大规模的更新操作前drop索引，之后重新创建，不过这就需要把创建索引对资源的消耗考虑在内。总之，使用索引需要平衡投入与产出，找到一个产出最好的点。

5、 在sqlite中使用索引 

　　1）Sqlite不支持聚集索引，android默认需要一个_id字段，这保证了你插入的数据会按“_id”的整数顺序插入，这个integer类型的主键就会扮演和聚集索引一样的角色。所以不要再在对于声明为：INTEGER PRIMARY KEY的主键上创建索引。
　　2）很多对索引不熟悉的朋友在表中创建了索引，却发现没有生效，其实这大多数和我接下来讲的有关。对于where子句中出现的列要想索引生效，会有一些限制，这就和前导列有关。所谓前导列，就是在创建复合索引语句的第一列或者连续的多列。

　　比如通过：　　CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。下面讲的这些，对于其他数据库或许会有一些小的差别，这里以sqlite为标准。在where子句中，

　　前导列必须使用等于或者in操作，最右边的列可以使用不等式，这样索引才可以完全生效。同时，where子句中的列不需要全建立了索引，但是必须保证建立索引的列之间没有间隙。举几个例子来看吧：

用如下语句创建索引：
CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);
这里是一个查询语句：
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'
这显然对于abcd四列都是有效的，因为只有等于和in操作，并且是前导列。
再看一个查询语句：
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'
那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用了不等式，根据使用不等式的限制，c列已经属于最右边。
最后再看一条：
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

索引将不会被使用，因为没有使用前导列，这个查询会是一个全表查询。

其实除了索引，对查询性能的影响因素还有很多，比如表的连接，是否排序等。影响数据库操作的整体性能就需要考虑更多因素，使用更对的技巧，不得不说这是一个很大的学问。

最后在android上使用sqlite写一个简单的例子，看下索引对数据库操作的影响。
创建如下表和索引：
   db.execSQL("create table if not exists t1(a,b)");        
   db.execSQL("create index if not exists ia on t1(a,b)");
插入10万条数据，分别对表进行如下操作：
select * from t1 where a='90012'
插入：insert into t1(a,b) values('10008','name1.6982235534984673')
更新：update t1 set b='name1.999999' where a = '887'

删除：delete from t1 where a = '1010'

数据如下（5次不同的操作取平均值）：
操作   无索引    有索引
查询   170ms  5ms
插入   65ms   75ms
更新   240ms  52ms
删除   234ms  78ms

        可以看到显著提升了查询的速度，稍稍减慢了插入速度，还稍稍提升了更新数据和删除数据的速度。如果把更新和删除中的where子句中的列换成b，速度就和没有索引一样了，因为索引失效。所以索引能大幅度提升查询速度，对于删除和更新操作，如果where子句中的列使用了索引，即使需要重新build索引，有可能速度还是比不使用索引要快的。对与插入操作，索引显然是个负担。同时，索引让db的大小增加了2倍多。

       还有个要吐槽的是，android中的rawQurey方法，执行完sql语句后返回一个cursor，其实并没有完成一个查询操作，我在rawquery之前和之后计算查询时间，永远是1ms...这让我无比苦闷。看了下源码，在对cursor调用moveToNext这些移动游标方法时，都会最终先调用getCount方法，而getCount方法才会调用native方法调用真正的查询操作。这种设计显然更加合理。