sqlite 索引总结

 索引的种类：

1.聚集索引。表中行的物理顺序与键值的逻辑（索引）顺序相同。因为数据的物理顺序只能有一种，所以一张表只能有一个聚集索引。如果一张表没有聚集索引，那么这张表就没有顺序的概念，所有的新行都会插入到表的末尾。对于聚集索引，叶节点即存储了数据行，不再有单独的数据页。

2.非聚集索引。表中行的物理顺序与索引顺序无关。对于非聚集索引，叶节点存储了索引字段值以及指向相应数据页的指针。叶节点紧邻在数据之上，对数据页的每一行都有相应的索引行与之对应。

3.               聚集索引                                                                  非聚集索引

   查询      找到页节点即找到了数据行                                                需要再去读取数据页

   插入      比较复杂，该数据页已经没有空间，那就需要拆分数据页                       只需在表的末尾插入

   删除      索引页只剩一条记录，那么该记录可能会移动到邻近的索引表中，              会导致出现多个数据页都只有少量数据

               原来的索引页也会被回收

4.索引的使用

  4.1  在经常需要搜索的列上

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

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

sqlite中使用索引的情况

1.Sqlite不支持聚集索引。不要再在对于声明为：INTEGER PRIMARY KEY的主键上创建索引。

2.对于where子句中出现的列要想索引生效，会有一些限制，这就和前导列有关。

   CREATE INDEX comp_ind ON table1(x, y, z)创建索引，那么x,xy,xyz都是前导列，而yz，y，z这样的就不是。

3.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'

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

对于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');

sqlite索引 使用实例

建立三个表：
create table t1 
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t2 
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null);
create table t3 
(id integer primary key,
num integer not null,
word1 text not null,
word2 text not null); 

建立若干索引：
t1表：在num，word1，word2上有复合索引
t2表：在num，word1，word2上各有一个索引
t3表：在word1上有一个索引
create index idxT1 on t1(num,word1,word2);
create index idxT2Num on t2(num);
create index idxT2Word1 on t2(word1);
create index idxT2Word2 on t2(word2);
create index idxT3Word1 on t2(word1); 

向三个表中各插入10000行数据，其中num项为随机数字，word1和word2中是英文单词，三个表中对应的num，word1和word2列中都包含有部分相同值，但是它们在表中出现的顺序不同。

速度测试结果：
1) select count(*) from t1,t3 where t1.word2=t3.word2; 
很慢（t3.word2上没有索引）
2) select count(*) from t3,t1 where t1.word2=t3.word2; 
很慢（t1.word2上没有独立索引）
3) select count(*) from t1,t2 where t1.word2=t2.word2;
很快（t2.word2上有索引）
4) select count(*) from t2,t1 where t1.word2=t2.word2; 
很慢（t1.word2上没有独立索引）
5) select count(*) from t1,t2 where t1.num=t2.num;
很快（t2.num上有索引）
6) select count(*) from t2,t1 where t1.num=t2.num; 
很快（t1的复合索引中，第一个列是num）
7) select count(*) from t1,t3 where t1.num=t3.num; 
很慢（t3.num上没有索引）
8) select count(*) from t3,t1 where t1.num=t3.num; 
很快（t1的复合索引中，第一个列是num）