Sqlite索引

简介

索引（Index）有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。索引是一种特殊的查找表，索引是一个指向表中数据的指针。用来加快数据检索。

除了索引，对查询性能的影响因素还有很多，影响数据库操作的整体性能就需要考虑更多因素，这是一个很大的学问。 

聚集索引 

 表中的行的物理顺序与键值的逻辑（索引）顺序相同。数据的物理顺序只能有一种，所以一张表只能有一个聚集索引。

非聚集索引 

表中行的物理顺序与索引顺序无关。

影响

查询

在聚集索引中，找到页节点即找到了数据行，而在非聚集索引中，还需要再去读取数据页。

插入 

聚集索引的插入操作比较复杂，是非常耗费资源的操作。最简单的情况，插入操作会找到对于的数据页，然后为新数据腾出空间，执行插入操作。如果该数据页已经没有空间，那就需要拆分数据页。

仅有非聚集索引的表，插入只需在表的末尾插入即可。

删除 

删除行后下方的数据会向上移动以填补空缺。如果删除的数据是该数据页的最后一行，那么这个数据页会被回收，它的前后一页的指针会被改变，被回收的数据页，会在特定的情况被重新使用。

聚集索引，如果索引页只剩一条记录，那么该记录可能会移动到邻近的索引表中，原来的索引页会被回收。

非聚集索引，没办法做到这一点，就会导致出现多个数据页，都只有少量数据的情况。

索引的优缺点

优点：

1. 加快数据的检索速度，创建索引的主要原因 。
2. 加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义 。
3. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

缺点： 

1. 当增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度，这个是比较大的问题。
2. 创建索引需要耗费一定的时间，但是问题不大，一般索引只要build一次 。
3. 索引需要占用物理空间，特别是聚集索引，需要较大的空间。 

索引的使用

应当建立索引的情形

在经常搜索的列上建立索引。

经常同时查询多列，且每列都含有重复值，可以建立组合索引，组合索引的前导列一定要是使用最频繁的列。

索引也可以是唯一的，与 UNIQUE 约束类似，在列上或列组合上防止重复条目。

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

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

不应该创建索引的情形

使用索引需要平衡投入与产出，找到一个产出最好的点。

索引不应该使用在较小的表上。
索引不应该使用在有频繁的大批量的更新或插入操作的表上。
索引不应该使用在含有大量的 NULL 值的列上。
索引不应该使用在频繁操作的列上。

1、很少在查询中使用的列
2、含有很多重复数据的列，比如只有0，1，这时候扫描整表通常会更有效。
3、TEXT，IMAGE的列不应该创建索引。这些字段长度不固定，或许很长，或许为空。 
4、更新操作远大于查询操作时，不建立索引。
5、在大规模的更新操作前drop索引，之后再重新创建索引，不过这就需要把创建索引对资源的消耗考虑在内。

组合索引失效的情形

在表中创建了组合索引却没有生效，与前导列有关。所谓前导列，就是在创建组合索引的第一列或靠前的连续多列。比如：

CREATE INDEX index_name  ON table_name(a, b, c)创建索引，那么a，ab，abc都是前导列，而bc，b，c就不是。

这里以SQLite为标准。在WHERE子句中，前导列必须使用等于或者IN操作，最右边的列可以使用不等式，这样索引才可以完全生效。

用如下语句创建索引： 
CREATE INDEX index_name   ON ex1(a,b,c,d,e); 

1、查询语句： 
...WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello' 
这显然对于abcd四列都是有效的，因为只有等于和IN操作，并且是前导列。 

2、查询语句： 
... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello' 
那这里只有a，b和c的索引会是有效的，d列的索引会失效，因为它在c列的右边，而c列使用了不等式，根据使用不等式的限制，c列已经属于最右边。 

3、查询语句： 
... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello' 
索引将不会被使用，因为没有使用前导列，这个查询会是一个全表查询。

between，or，like，无法使用索引

BETWEEN : ...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'); 

1.最左前缀匹配原则，索引会从左向右匹配，直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引，就都可以用到，a,b,d的顺序可以任意调整。

2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

3.尽量选择区分度高的列作为索引，唯一的列区分度越大，而状态、性别字段在大数据面前区分度越小。

4.索引列不能参与计算，保持列“干净”，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，成本太大。

5.修改索引而不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引

例子

创建如下表和索引： 
create table if not exists t1(a,b)
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 

索引有助于加快 SELECT 查询和 WHERE 子句，但它会减慢使用 UPDATE 和 INSERT 语句时的数据输入。索引可以创建或删除，但不会影响数据。

加上索引后插入速度会变慢，前几十万数据的时候还可以，后来插入1万条要几十秒。
但是没有索引，插入一直都很快  。

 

对与插入操作，索引是个负担。同时，索引让db的大小增加了2倍多。 

可以看到显著提升了查询的速度，稍稍减慢了插入速度，还稍稍提升了更新数据和删除数据的速度。

如果把更新和删除中的where子句中的列换成b，速度就和没有索引一样了，因为索引失效。

所以索引能大幅度提升查询速度，而对于删除和更新操作，如果where子句中的列使用了索引，即使重新build索引，有可能速度还是比不使用索引要快的。

已经存在20万数据的表中，插入新的数据，在插入前用select语句判断是否存在该数据，不存在就插入，新的1000条数据都是新数据的情况下，

无索引的情况下，插入1000条数据
单位 ms
6250 ，8843 ，9266 ，5516 ，6656 ，  5172  ，

建立索引后时间缩减一半 ：3766 ，2578 ，2532  。这是在插入需要调整索引的情况后，仍然可以加快整体的速度。

如果插入的数据是已经存在的，无需真正插入，则更快只需627ms  172ms。

单位 ms

57891 ， 67547 ，63094  。

如果是已经存在的用到select 也需要 
47953ms。    

创建索引的命令

单列索引
CREATE   INDEX   index_name  ON     table_name (column1);

组合索引
组合索引是基于一个表的两个或多个列上创建的索引。

CREATE      INDEX      index_name       ON     table_name (column1, column2);


创建一个单列索引还是组合索引，要考虑查询过滤条件的 WHERE 子句中使用非常频繁的列。

如果值使用到一个列，则选择使用单列索引。如果在作为过滤的 WHERE 子句中有两个或多个列经常使用，则选择使用组合索引。

唯一索引
唯一索引不允许任何重复的值插入到表中。为了维护数据的完整性。

CREATE UNIQUE INDEX     index_name      ON     table_name (column_name);

隐式索引
隐式索引是在创建对象时，由数据库服务器自动创建的索引。索引自动创建为主键约束和唯一约束。

查看表结构索引

SELECT * FROM sqlite_master
您可以列出数据库范围的所有索引
sqlite> SELECT * FROM sqlite_master WHERE type = 'index';

删除索引

DROP INDEX 命令
可以使用 SQLite 的 DROP 命令删除索引。

DROP INDEX index_name;


 

SQLite 索引 | 菜鸟教程

Android数据库Sqlite中索引的使用_移动开发笔记-CSDN博客_android sqlite 索引