Sqlite索引

最新推荐文章于 2024-07-26 17:12:57 发布

边缘计算机

最新推荐文章于 2024-07-26 17:12:57 发布

阅读量4.1k

点赞数 3

文章标签： sqlite 数据库 sql

本文链接：https://blog.csdn.net/panjunnn/article/details/120344165

版权

简介

索引（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