索引在Sqlite数据库中的使用 索引的优缺点

类型：互联网电视大小：31.6米语言：中文评分：9.2标签:立即下载要利用索引优化数据库数据的操作，必须明确几个问题：1。什么是指数2。指数3的原理。指数4的优缺点。什么时候使用索引，如何使用，探讨索引在数据库操作中的作用。

1.数据库索引入门回想小时候查字典的步骤，索引和字典目录的概念是一致的。字典目录让我们不用翻整本字典就能找到需要的内容页，然后翻到那一页。索引也是如此，它是根据多个字段对记录进行排序的表示。对表中的字段进行索引会创建另一个存储字段值的数据结构，并且每个值还包括指向其相关记录的指针。这样就不需要查询整个数据库，自然提高了查询效率。同时对索引的数据结构进行排序，可以对其进行二分搜索法运算，速度更快。

B树和索引大多数数据库使用B树或B树作为存储结构，B树索引也是最常见的索引。对B树的简单介绍可以增强对索引的理解。b树是为磁盘设计的多分支平衡树。B-树最准确的定义是一个平衡的多分支搜索树，带有t(t=2)个关键字。M阶的B树满足以下条件：1)每个节点最多有M个子节点；2)除根节点和叶节点外，其他节点至少有M/2个子节点；3)根节点至少有两个子节点(除非树只包含一个节点)；4)所有叶节点都在同一层，叶节点不包含任何关键词信息，可视为外部节点；5)K个关键字的非叶节点只包含K 1个子节点；B树中的每个节点可以根据实际情况包含大量的关键字信息和分支(当然不能超过磁盘块的大小，根据不同的磁盘驱动器，块的大小一般在1k~4k左右)；这样就减少了树的深度，也就是说在寻找一个元素时，只需要从外部存储盘读入几个节点到内存中，就可以快速访问到要搜索的数据。b树操作时间通常由两部分组成：磁盘访问时间和CPU计算时间。与磁盘的io速度相比，cpu的计算时间可以忽略不计，因此B树的意义出现，树的深度减小，决定了io的读写次数。b树索引是一种典型的树结构，其主要组成部分有：1)叶节点：包含的条目直接指向表中的数据行。2)分支节点：包含的条目指向索引中的其他分支节点或叶节点。3)根节点：B树索引只有一个根节点，实际上是位于树顶端的分支节点。如下图所示：

每个索引包含两部分，一部分是索引本身的值，另一部分是指向数据页或另一个索引的指针。每个节点都是一个包含多个索引的索引页。为空表创建索引时，数据库将分配一个空的索引页，它代表根节点。在插入数据之前，索引页是空的。每当您插入数据时，数据库都会在根节点创建一个索引条目。当根节点已满并且再次插入数据时，根节点将会拆分。例如，图中所示的数据被插入到根节点中。(如果有四个以上，就会分裂成两个节点。将G移动到新的根节点，并将H和N放在新的右子节点中。如图：

根节点填充有4个节点

插入h进行分割。

一般拆分步骤如下：1)创建两个子节点；2)将原始节点中的数据分成两半，写入两个新的子节点。3)将指向页面节点的指针放在以下节点中。当您继续向表中插入数据时，指向根节点中叶节点的指针会被填满。当叶需要拆分时，根节点没有空间来创建指向新叶节点的指针。然后数据库创建一个分支节点。随着叶节点的拆分，根节点中的指针都指向这些分支节点。随着数据的不断插入，索引会增加更多的分支节点，使得树结构成为这样的多级结构。

3.索引的类型

1)聚集索引：表中行的物理顺序与键值的逻辑(索引)顺序相同。因为数据只能有一个物理顺序，所以一个表只能有一个聚集索引。如果一个表没有聚集索引，那么这个表就没有顺序的概念，所有新行都将被插入到表的末尾。对于聚集索引，叶节点存储数据行，没有单独的数据页。比如小时候从来不查目录。我认为字典本身就是一个目录。比如我查裴字，只需要翻到字母P的开头，然后按顺序找到E。通过这种方法，我可以尽快找到老师说的单词，并得到老师的表扬。

2)非聚集索引：表中行的物理顺序与索引顺序无关。对于非聚集索引，叶节点存储索引字段值和指向相应数据页的指针。叶节点位于数据的正上方，数据页的每一行都有一个对应的索引行。有时候查字典的时候，不知道这个字该怎么读，只好通过字典目录里的“偏旁部首”来查。这时我会发现目录中的排序和实际文本中的排序不一样，这让我很苦恼，因为我不能比别人快。我需要先在目录中找到这个单词，然后根据页数在文本中找到这个单词。

4.查询、插入和删除索引和数据

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

2)插入。聚集索引的插入操作很复杂。在最简单的情况下，插入操作将找到的数据页，然后为新数据腾出空间来执行插入操作。如果数据页没有空间，就需要拆分数据页，这是一个非常耗费资源的操作。对于只有非聚集索引的表，只在表的末尾插入。如果还包括聚集索引，聚集索引所需的插入操作也将被执行。

3)删除。删除一行后，底部的数据将向上移动以填充空白。如果删除的数据是数据页的最后一行，那么数据页将被回收，上一页的指针将被更改，回收的数据页将在特定情况下被重用。同时，对于聚集索引，如果索引页中只剩下一条记录，该记录可能会移动到相邻的索引表中，原始索引页将被回收。非聚集索引做不到这一点，导致多个数据页只有少量数据的情况。

5.其实通过前面的介绍，指数的优缺点一目了然。首先优点是：1)数据检索速度大大加快，这是创建索引的主要原因；2)加速了表与表之间的连接，特别是在实现数据的引用完整性方面。

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

除此之外，缺点如下：1)创建索引需要一定的时间，但问题不大。通常，一个索引只需要构建一次。2)索引需要占用物理空间，尤其是聚集索引，需要很大的空间

3)在增加、删除、修改表中的数据时，需要动态维护索引，降低了数据维护的速度，这是一个很大的问题。

6.索引的使用根据上面的分析，我们对何时使用索引有自己的想法(如果没有，再回头看。)。一般我们需要在这些列上建立索引：1)在需要频繁搜索的列上，这是毋庸置疑的；2)多列同时频繁查询，每列包含重复值，可以建立组合索引。组合索引应该使常用的查询尽可能形成索引覆盖(查询中包含的所有必填字段都包含在一个索引中，所以我们只需要搜索索引页面就可以完成查询)。同时，复合索引的前导列必须是最常用的列。前导列的问题将在后面介绍sqlite的索引用法时讨论。3)在经常用于连接的列中，这些列主要是外键，可以加快连接速度，连接条件要充分考虑有索引的表。

4)在经常需要搜索范围的列上创建索引，因为索引已经排序，并且指定的范围是连续的。同样，最好在经常需要排序的列上创建索引。

6)对经常放在where子句中的列创建索引，以加快条件的判断。需要注意的是，在where语句中对一列的任何操作(比如计算表达式和函数)都需要搜索整个表，而不是使用该列的索引。所以查询的时候尽量把操作移到等号右边。

我们不应该为以下列创建索引：1)查询中很少使用的列；2)在扫描整个表时，非重复数据值很少的列，如只有0，1，通常更有效；3)不应为定义为文本和图像的数据创建索引。这些字段的长度不固定，可能很长，也可能是空的。当然，当更新操作比查询操作大得多时，不会建立索引。您也可以考虑在大规模更新操作之前删除索引，然后重新创建，但需要考虑创建索引的资源消耗。总之，使用索引需要平衡输入和输出，找到最佳的输出点。

7.在sqlite中使用索引

1)Sqlite不支持聚集索引，android默认需要一个_id字段，保证你插入的数据会按照“_id”的整数顺序插入，整数类型的主键会起到和聚集索引一样的作用。所以不要在声明为：INTEGER PRIMARY KEY的主键上创建索引。

2)很多不熟悉索引的朋友在表中创建了索引，但是发现效果并不好。其实大部分都和我接下来要讲的内容有关。对于出现在where子句中的列，如果索引要生效，将会有一些与前导列相关的限制。所谓前导列就是复合索引语句中的第一列或连续多列。例如，如果通过在表1 (x，y，z)上创建index comp _ ind来创建索引，则x，xy，xyz是前导列，但yz，y，z不是。其他数据库之间可能有一些小的差异。sqlite是这里的标准。在where子句中，前导列必须使用等式或In运算，最右边的列可以使用不等式，这样索引才能完全有效。同时，where子句中的所有列都不需要索引，但是索引列之间必须没有间隙。举几个例子看看：

使用以下语句创建索引：在ex1上创建索引idx _ ex1，b，c，d，e，y，z)；下面是一个查询语句：其中a=5，b在(1，2，3)中，c为null，d=' hello '，这显然对abcd四列有效，因为只执行equals和in运算，它们是前导列。看看另一个查询语句：其中a=5和b在(1，2，3)和c12和d=' hello '，那么这里只有a，b和c的索引会有效，d列的索引会无效，因为它在c列的右边，这就使用了不等式。根据使用不等式的限制，c列已经属于极右翼。最后，再看一项：其中(1，2，3)中的b和c不为空，d=' hello '

将不使用索引，因为不使用前导列，并且查询将是全表查询。

3)对于介于之间的，或类似的，不能使用索引。例如，WHERE myfield介于10和20之间；此时，应该将其转换为：其中myfield=10，myfield=20另一个例子是：我的表，其中我的字段像“SQL %”；此时，应该将其转换为：其中myfield=' SQL '和myfield ' sqm或者：其中我的字段=' ABC '或我的字段=' XYZ '；此时，应该将其转换为：其中myfield IN ('abc '，' XYZ ')；

事实上，除了索引之外，影响查询性能的因素还有很多，比如表的连接以及是否排序。影响数据库操作的整体性能，需要考虑更多的因素，使用更多正确的技巧，这是一门很棒的学问。

最后，用android上的sqlite写一个简单的例子，看看索引对数据库操作的影响。创建了以下表和索引：db.execsql('如果t1 (a，b)不存在，则创建表')；db.execSQL('如果t1(a，b)上不存在ia，则创建索引')；插入100，000条数据并分别对表执行以下操作：从t 1中选择*其中a='90012 '插入：插入t1 (a，b)值(' 10008 '，' name 1.6982235534984673 ')更新：更新t1集合b=' name 1.99999 '

删除：从t1中删除，其中a='1010 '

数据如下(5个不同操作的平均值):无索引操作索引查询170毫秒5毫秒插入65毫秒75毫秒更新240毫秒52毫秒删除234毫秒78毫秒

可以看到查询速度明显提高，插入速度略有减慢，更新删除数据的速度略有提高。如果更新和删除时where子句中的列被B替换，速度和没有索引一样，因为索引是无效的。因此，索引可以大大提高查询速度。对于删除和更新操作，如果where子句中的列使用索引，即使需要构建索引，也可能比不使用索引更快。对于和插入操作，索引显然是一个负担。同时，索引将数据库的大小增加了2倍以上。

另一件让人吐槽的事情是，android中的rawQurey方法在执行完sql语句后返回了一个游标，但实际上它并没有完成一个查询操作。我计算了rawquery前后的查询时间，总是1毫秒.这让我非常沮丧。阅读完源代码后，在调用moveToNext时，会先调用getCount方法，然后getCount方法会调用原生方法来调用真正的查询操作。这个设计显然更合理。