sqlite 索引

索引是一种用来在某种条件下加速查询的结构

索引的种类

1）聚集索引：表中行的物理顺序与键值的逻辑（索引）顺序相同。因为数据的物理顺序只能有一种，所以一张表只能有一个聚集索引。如果一张表没有聚集索引，那么这张表就没有顺序的概念，所有的新行都会插入到表的末尾。对于聚集索引，叶节点即存储了数据行，不再有单独的数据页。就比如说我小时候查字典从来不看目录，我觉得字典本身就是一个目录，比如查裴字，只需要翻到p字母开头的，再按顺序找到e。通过这个方法我每次都能最快的查到老师说的那个字，得到老师的表扬。

2）非聚集索引：表中行的物理顺序与索引顺序无关。对于非聚集索引，叶节点存储了索引字段值以及指向相应数据页的指针。叶节点紧邻在数据之上，对数据页的每一行都有相应的索引行与之对应。有时候查字典，我并不知道这个字读什么，那我就不得不通过字典目录的“部首”来查找了。这时候我会发现，目录中的排序和实际正文的排序是不一样的，这对我来说很苦恼，因为我不能比别人快了，我需要先再目录中找到这个字，再根据页数去找到正文中的字。

索引与数据的查询，插入与删除

1）查询。查询操作就和查字典是一样的。当我们去查找指定记录时，数据库会先查找根节点，将待查数据与根节点的数据进行比较，再通过根节点的指针查询下一个记录，直到找到这个记录。这是一个简单的平衡树的二分搜索的过程，我就不赘述了。在聚集索引中，找到页节点即找到了数据行，而在非聚集索引中，我们还需要再去读取数据页。

2）插入。聚集索引的插入操作比较复杂，最简单的情况，插入操作会找到对于的数据页，然后为新数据腾出空间，执行插入操作。如果该数据页已经没有空间，那就需要拆分数据页，这是一个非常耗费资源的操作。对于仅有非聚集索引的表，插入只需在表的末尾插入即可。如果也包含了聚集索引，那么也会执行聚集索引需要的插入操作。

3）删除。删除行后下方的数据会向上移动以填补空缺。如果删除的数据是该数据页的最后一行，那么这个数据页会被回收，它的前后一页的指针会被改变，被回收的数据页也会在特定的情况被重新使用。与此同时，对于聚集索引，如果索引页只剩一条记录，那么该记录可能会移动到邻近的索引表中，原来的索引页也会被回收。而非聚集索引没办法做到这一点，这就会导致出现多个数据页都只有少量数据的情况。

索引的优缺点

优点：
1）大大加快数据的检索速度，这也是创建索引的最主要的原因

2）加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

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

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

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

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

索引创建的原则

一般我们需要在这些列上建立索引：

1）在经常需要搜索的列上，这是毋庸置疑的；

2）经常同时对多列进行查询，且每列都含有重复值可以建立组合索引，组合索引尽量要使常用查询形成索引覆盖（查询中包含的所需字段皆包含于一个索引中，我们只需要搜索索引页即可完成查询）。 同时，该组合索引的前导列一定要是使用最频繁的列。对于前导列的问题，在后面sqlite的索引使用介绍中还会做讨论。

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度，连接条件要充分考虑带有索引的表。；

4）在经常需要对范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的，同样，在经常需要排序的列上最好也创建索引。

6）在经常放到where子句中的列上面创建索引，加快条件的判断速度。要注意的是where字句中对列的任何操作（如计算表达式，函数）都需要对表进行整表搜索，而没有使用该列的索引。所以查询时尽量把操作移到等号右边。

对于以下的列我们不应该创建索引：

1）很少在查询中使用的列

2）对只有少数值的列不建索引

3）含有很少非重复数据值的列，比如只有0，1，这时候扫描整表通常会更有效

4）对于定义为TEXT，IMAGE的数据不应该创建索引。这些字段长度不固定，或许很长，或许为空。

1.创建索引：

creat index [unique] index_name on table_name[columns]

可以对字段进行约束，如collate nocase ,unique等

2.使用索引：

在单字段索引的情况下，对于下面的where子句中出现的表达式，SQLite将使用索引：

column {=|<|>|<=|>=} expression

expression {=|<|>|<=|>=} column

column in (expression_list)

column in (subquery) //子查询

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

1.

在查询select XXXXXXXXX where a=1 and b=2 and d=3;只有前两个条件使用索引，因为没有有效条件来缩小c到d的d 差距

所以，多字段索引时，查询时从左往右使用字段索引的，直到where子句无法找出有效条件来继续进行索引。

2.

…WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d=’hello’

这显然对于abcd四列都是有效的，因为只有等于和in操作，并且是前导列。

3.

… WHERE a=5 AND b IN (1,2,3) AND c>12 AND d=’hello’

那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用了不等式，根据使用不等式的限制，c列已经属于最右边。

4.
… WHERE b IN (1,2,3) AND c NOT NULL AND d=’hello’

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

5.对于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’);