谈数据库索引和Sqlite中索引的使用(二)
根据上文的分析，我们大致对什么时候使用索引有了自己的想法（如果你没有，回头再看一遍。。。）。一般我们需要在这些列上建立索引：
1）在经常需要搜索的列上，这是毋庸置疑的；
2）经常同时对多列进行查询，且每列都含有重复值可以建立组合索引，组合索引尽量要使常用查询形成索引覆盖（查询中包含的所需字段皆包含于一个索引中，我们只需要搜索索引页即可完成查询）。 同时，该组合索引的前导列一定要是使用最频繁的列。对于前导列的问题，在后面sqlite的索引使用介绍中还会做讨论。
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度，连接条件要充分考虑带有索引的表。；
4）在经常需要对范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的，同样，在经常需要排序的列上最好也创建索引。
6）在经常放到where子句中的列上面创建索引，加快条件的判断速度。要注意的是where字句中对列的任何操作（如计算表达式，函数）都需要对表进行整表搜索，而没有使用该列的索引。所以查询时尽量把操作移到等号右边。 www.2cto.com
对于以下的列我们不应该创建索引：
1）很少在查询中使用的列
2）含有很少非重复数据值的列，比如只有0，1，这时候扫描整表通常会更有效
3）对于定义为TEXT，IMAGE的数据不应该创建索引。这些字段长度不固定，或许很长，或许为空。
当然，对于更新操作远大于查询操作时，不建立索引。也可以考虑在大规模的更新操作前drop索引，之后重新创建，不过这就需要把创建索引对资源的消耗考虑在内。总之，使用索引需要平衡投入与产出，找到一个产出最好的点。
7. 在sqlite中使用索引
1）Sqlite不支持聚集索引，android默认需要一个_id字段，这保证了你插入的数据会按“_id”的整数顺序插入，这个integer类型的主键就会扮演和聚集索引一样的角色。所以不要再在对于声明为：INTEGER PRIMARY KEY的主键上创建索引。 www.2cto.com
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列已经属于最右边。
最后再看一条： www.2cto.com
... 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万条数据，分别对表进行如下操作： www.2cto.com
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倍多。 www.2cto.com
还有个要吐槽的是，android中的rawQurey方法，执行完sql语句后返回一个cursor，其实并没有完成一个查询操作，我在rawquery之前和之后计算查询时间，永远是1ms...这让我无比苦闷。看了下
源码，在对cursor调用moveToNext这些移动游标方法时，都会最终先调用getCount方法，而getCount方法才会调用native方法调用真正的查询操作。这种设计显然更加合理。