在sqlite中使用索引

时间:2022-11-26 05:34:27

出处: 网络

 

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'

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

3)对于between,or,like,都无法使用索引。
如 ...WHERE myfield BETWEEN 10 and 20;
这时就应该将其转换成:
    ...WHERE myfield >= 10 AND myfield <= 20;
再如LIKE:...mytable WHERE myfield LIKE 'sql%';;
此时应该将它转换成:
...WHERE myfield >= 'sql' AND myfield < 'sqm';
    再如OR:...WHERE myfield = 'abc' OR myfield = 'xyz';
此时应该将它转换成:
...WHERE myfield IN ('abc', 'xyz');


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

最后在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倍多。