谈数据库索引和Sqlite中索引的使用(3)

    3）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

   www.2cto.com  

   再说缺点：

  1）创建索引需要耗费一定的时间，但是问题不大，一般索引只要build一次

  2）索引需要占用物理空间，特别是聚集索引，需要较大的空间

  3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，这个是比较大的问题。

 

6.索引的使用

       根据上文的分析，我们大致对什么时候使用索引有了自己的想法（如果你没有，回头再看一遍。。。）。一般我们需要在这些列上建立索引：

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