数据库索引(Index)

夜雨风云

已于 2024-03-05 10:02:39 修改

阅读量9.1k

点赞数 7

分类专栏：数据库系统概念文章标签： sql 数据库

于 2021-04-25 23:33:22 首次发布

本文链接：https://blog.csdn.net/wangxufa/article/details/116140750

版权

数据库系统概念专栏收录该内容

19 篇文章 4 订阅

订阅专栏

数据库存储数据最终是以文件的形式存储到硬盘的。一般来说，我们在程序中使用的时候肯定要把磁盘文件中的数据读到内存中。而磁盘 IO 是非常高昂的操作。一种有效的解决方案是提供一种稳定的数据结构能够满足只需要查询很少的数据就可定位到期望的数据。也即每次查询数据仅需要进行少部分的磁盘 IO 操作。这种数据结构就是索引。
索引（Index）是帮助数据库高效获取数据的数据结构。如果将数据库比作书，那么索引就相当于目录。

索引的优缺点

索引的优点：
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的查询性能。
索引的缺点：
时间方面：创建索引和维护索引要耗费时间，具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，会降低增/改/删的执行效率；
空间方面：索引需要占物理空间。

索引使用场景

索引在sql中的使用

where子句

查询引擎会根据where子句中涉及的字段优先选择索引查询数据。

order by子句

当使用order by将查询结果按照某个字段排序时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序（将数据从硬盘分批读取到内存使用内部排序，最后合并排序结果），这个操作是很影响性能的，因为需要将查询涉及到的所有数据从磁盘中读到内存（如果单条数据过大或者数据量过多都会降低效率），更无论读到内存之后的排序了。
但是如果我们对该字段建立索引后，那么由于索引本身是有序的，因此直接按照索引的顺序和映射关系逐条取出数据即可。而且如果是分页的，那么只用取出索引表某个范围内的索引对应的数据，而不用取出所有数据进行排序再返回某个范围内的数据。（从磁盘取数据是最影响性能的）

join子句

对join语句匹配关系（on）涉及的字段建立索引能够提高效率

select子句

在执行select子句中，如果select中的查询字段存在于覆盖索引中，那么无需读取记录即可返回。
所以在编写select子句时，尽可能的在select后只写必要的查询字段，以增加索引覆盖的几率。

索引分类

按照索引的实现结构，基本的索引类型有两种：
顺序索引：基于值的顺序排序。
散列索引：基于将值平均分布到若干个散列桶中。一个值所属的散列桶由散列函数决定。
对于索引的实现的选择，没有银弹。没有哪一种技术是最好的，只能说某种技术对特定数据库应用是最合适的。对索引技术实现的评价，必须基于下面这些因素。
访问类型（access type）：能有效支持的访问类型。访问类型可以包括找到具有特定属性值的记录，以及找到属性值落在某个特定范围内的记录。
访问时间(access time)：在查询中使用该技术找到一个特定数据项或数据项集所需的时间。
插入时间(insertion time)：插入一个新数据项所需的时间。该值包括找到插入这个新数据项的正确位置所需的时间，以及更新索引结构所需的时间。
删除时间(deletion time)：删除一个数据项所需的时间。该值包括找到待删除项所需的时间，以及更新索引结构所需的时间。
空间开销(space time)：索引结构所占用的额外存储空间。如果存储索引的额外空间大小适度，通常牺牲一定的空间代价来换取性能的提交是值得的。
用于在数据库文件中查找记录的属性或属性集称为搜索码(search key)。注意，这里的码与主码、候选码、超码的定义不同。

顺序索引

顺序索引按顺序存储搜索码的值，并将每个搜索码与包含该搜索码的记录关联起来。

聚集索引和非聚集索引

被索引文件中的记录自身也可以按照某种排列顺序顺序存储。一个文件可以有多个索引，分别基于不同的搜索码。如果包含记录的文件(数据库文件)按照某个搜索码指定的顺序排序，那么该搜索码对应的索引称为聚集索引(clustering index)。聚集索引也称为主索引（primary index）。也有将其称为聚簇索引。
搜索码指定的顺序与文件中记录的物理顺序不同的索引称为非聚集索引(noclustering index)或辅助索引(secondary index)。也称为非聚簇索引。

稠密索引和稀疏索引

索引项（index entry）或索引记录（index record）由一个搜索码值和指向具有该搜索码值的一条或多条记录的指针组成。指向记录的指针包括磁盘块的标识和标识磁盘块内记录的块内偏移量（哪个磁盘块上的哪个位置）。
根据是否为所有的搜索码值建立索引项，可将顺序索引细分为两类：稠密索引和稀疏索引。
稠密索引(dense index)：在稠密索引中，数据库文件中的每个搜索码值都有一个索引项。根据是否聚集，稠密索引可细分为稠密聚集索引和稠密非聚集索引。在稠密聚集索引中，索引项包括索引码值以及指向具有该索引码值的第一条数据记录的指针。具有相同索引码值的其余记录顺序地存储在第一条数据记录之后。在稠密非聚集索引中，索引必须存储指向所有具有相同搜索码值的记录的指针列表。
稀疏索引(sparse index)：在稀疏索引中，只为搜索码的某些值建立索引项。只有当关系按照搜索码排列顺序存储时，才能使用稀疏索引。换句话说，只存在稀疏聚集索引，不存在稀疏非聚集索引。对于稀疏聚集索引，每个索引项也包括一个搜索码值和指向具有该搜索码值的第一条记录的指针。为了定位一条记录，我们找到其最大索引值小于或等于所查找记录的搜索码值的索引项。然后从该索引项指向的记录开始，沿着文件中的指针查找，直到找到所需记录为止。
利用稠密索引通常可以比稀疏索引更快地定位一条记录。但是稀疏索引也有比稠密索引优越的地方：它所占用空间较小，并且插入和删除时所需的维护开销也较小。系统设计者必须在存取时间和空间开销之间进行权衡。尽管有关这一权衡的决定依赖于具体的引用，但为每个块建一个索引项的稀疏是一个较好的折中。原因在于，处理数据库查询的开销主要由把块从磁盘读到内存的时间决定。一旦将块放入主存，扫描整个块的时间就是可以忽略的。使用这样的稀疏索引，可以定位包含所要查找记录的块。

多级索引

如果索引小到可以放在主存中，搜索一个索引项的时间就会很短。但是，如果索引过大而不能放在主存中，那么当需要时，就必须从磁盘中取索引块。于是搜索一个索引项就醒酒药多次读取磁盘块。（这不是我们想看到的，也不符合使用索引的初衷）
可以在索引文件上使用二分查找算法来定位索引项，但是搜索的开销依旧很大。如果索引占据b个磁盘块，二分查找算法需要读取 $log_2(b)$ 个磁盘块。因此，搜索一个大的索引仍可能是一个相当耗时的过程。
为了处理这个问题，可以像对待其他任何顺序文件那样对待索引文件，并在原始的内存索引上构造一个稀疏的外层索引。注意，索引项总是有序的，这使得外层索引可以是稀疏的。这样，在定位一条记录时，首先在外层索引上使用二分法搜索找到其最大搜索码值小于或等于所需搜索码值的记录。然后指针指向一个内存索引块。扫描这个索引块，直到找到其最大索引值小于或等于所需搜索码值的记录。
如果文件及其庞大，甚至外层索引页可能大到无法装入内存。在这种情况下，可以创建另一级索引。事实上，可以根据需要多次重复此过程。具有两级及以上的索引称为多级(multilevel)索引。

索引的更新

无论采用何种形式的索引，每当文件中有记录插入或删除时，索引都需要更新。此外，如果文件中的记录更新，任何搜索码属性受影响的索引也必须更新。对记录的更新也可设计为对旧记录的删除以及随后对新纪录的插入。因此，我们只需考虑索引的插入和删除，并不需要明确地考虑更新。

辅助索引

辅助索引必须是稠密索引，对每个搜索码值都有一个索引项，而且对文件中的每条记录都有一个指针。如果辅助索引是稀疏索引，那么两个有索引项的索引码值之间的索引码值对应的记录可能存在于文件中的任何地方（因为是辅助索引，不需顺序存储），那么通常只能通过扫描整个文件才能找到它们。

多码上的索引

一般来说，一个搜索码可以由多个属性。一个包含多个属性的搜索码称为复合搜索码（composite search key）。这个索引的结构和任何其他索引一样，唯一不同的是搜索码不是单个属性，而是一个属性列表。

B+树索引文件

索引顺序文件组织最大的缺点在于，随着文件的增大，索引查找性能和数据顺序扫描性能都会下降。虽然这种性能下降可以通过对文件进行重新组织来弥补，但我们不希望频繁地进行重组。
B+树索引结构在数据插入和删除的情况下仍能保持其执行效率的最广泛的索引结构之一。

B+树结构

B+树索引是一种多级索引，但其结构不同于多级索引顺序文件。
对于B+树，叶结点指向具体搜索码值。对于非叶结点形成叶结点上的一个多级（稀疏）索引。

B+树扩展

B+树文件组织

索引顺序文件组织最大的缺点是文件增大时性能下降：随着文件的增大，增加的索引记录所占百分比和实际记录之间变得不协调，不得不存储在溢出块中。我们通过在文件中使用B+树索引来解决索引查找时性能下降的问题。
更进一步，可以通过B+树的叶结点来组织存放实际记录的磁盘块，这样B+树不仅作为索引使用，而且也是一个文件中的记录的组织者，即B+树文件组织。
B+树文件组织可以用于存储大型数据对象，如clob类型或blob类型。

B树文件索引

B树索引和B+树索引相似。两种方法的主要区别在于B树去除了搜索码值存储中的冗余。在B+树中，搜索码值可以在某些叶结点中重复出现，有的还在非叶节点中重复出现。然而，在B树中，由于出现在非叶结点中的搜索码值不会出现在其他地方，因此我们将不得不在非叶结点中为每个搜索码增加一个指针域。附加的这些指针指向文件记录或相应搜索码所对应的桶。
注意，许多数据库操作手册、行业文献及行业专家使用术语B树来指代B+树结构。事实上，在当前的用法中，这样的定义是合理的，因为术语B树和B+树是同义词。

多码访问

对于某些类型的查询来说，如果存在多个索引则使用多个索引，或使用建立在多属性搜索码上的索引，这样比较有利。

使用多个单码索引

多码索引

多码索引是指在复合的搜索码上建立和使用索引。也常称为”联合索引“。
多个单列索引在多条件查询时只会生效第一个索引！所以多条件联合查询时最好建联合索引！

最左前缀匹配原则

在建立多码索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从多码索引的最左边开始匹配，多码索引的第一个字段必须出现在查询组句中，这个索引才会被用到，示例：
对列col1、列col2和列col3建一个多码索引：

KEY test_col1_col2_col3 on test(col1,col2,col3);

多码索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
对于查询：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

覆盖索引

覆盖索引(covering index)存储一些记录的属性（但不是搜索码属性）的值以及指向记录的指针。存储附加的属性值对于辅助索引时非常有用的，因为它们使索引能够回答一些查询，甚至不需要找到实际的记录。(索引中存储记录的属性，查找时，可以直接使用索引上的值)

静态散列

顺序文件组织的一个缺点是必须访问索引结构来定位数据或必须使用二分法搜索，这将导致过多的磁盘IO操作。基于散列技术的文件组织使我们能够避免访问索引结构。同时，散列也提供一种构造索引的方法。
在对散列的描述中，使用术语桶(bucket)来表示能存储一条或多条记录的一个存储单元。通常一个桶就是一个磁盘块，但也也可能小于或大于一个磁盘块。
正规地说，令K表示所有搜索码值的集合，令B表示所有桶地址的集合，散列函数h是一个从K到B的函数。
散列可以用于两个不同的目的。在散列文件组织(hash file organization)中，通过计算所需记录搜索码值上的一个函数直接获得包含该记录的磁盘块地址。在散列索引组织(hash index organization)中，把搜索码以及与它们相关联的指针组织成一个散列文件结构。

桶溢出处理

如果桶中没有足够的空间，就会发生桶溢出(bucket overflow)。桶溢出的发生可能有以下几个原因：
桶不足：桶数目(用 $n_B$ 表示)的选择使 $n_B>n_r/f_r$ ，其中 $n_r$ 表示将要存储的记录总数， $f_r$ 表示一个桶中能存放的记录数目。当然，这种表示是以在选择散列函数时记录总数已知为前提的。
偏斜：某些桶分配到的记录比其他桶多，所以即使其他桶仍有空间，某个桶也仍可能溢出。这种偏斜称为桶偏斜(skew)。偏斜发生的原因有两个：
(1) 多条记录可能具有相同的搜索码。
(2) 所选的散列函数可能会造成搜索码的分布不均。
为了减少桶溢出的可能性，桶的数目选为 $n_r/f_r*(1+d)$ ，其中d是避让因子，其典型值约为0.2。尽管有一定的空间浪费，但好处是减少了溢出的可能性。
尽管分配的桶比所需的桶多一些，但桶溢出还是可能发生。用溢出桶(overflow bucket)来处理桶溢出问题。如果一条记录插入桶中，但该桶已满，系统会为该桶提供一个溢出桶，并将记录插入到溢出桶中。如果溢出桶也满了，系统会提供另一个溢出桶，如此继续下去。一个给定桶的所有溢出桶用一个链表关联起来。这个链表称为溢出链(overflow chaining)。

散列索引

散列不仅可以用于文件的组织，还可以用于索引结构的创建。散列索引(散列索引)将搜索码及其相应的指针组织成散列文件结构。按照如下方法构建散列索引。将散列函数作用于搜索码已确定对应的桶，然后将此搜索码以及相应的指针存入此桶（或溢出桶）中。

动态散列

静态散列技术要求固定桶地址集合B，这带来一个严重的问题。大多数数据库都会随时间变大。对数据库使用静态散列，有三种选择：
(1) 根据当前文件大小选择散列函数。这种选择会使性能随数据库增大而下降。
(2) 根据将来某个时刻文件的预计大小选择散列函数。尽管这样可以避免性能下降，但初始时会造成相当大的空间浪费。
(3) 随着文件的增大，周期性地对散列结构进行重组。这种重组涉及一系列问题，包括新散列函数的选择，在文件中每条记录上重新计算散列函数，以及分配新的桶。重组是一个规模大、耗时的操作。而且重组期间必须禁止对文件的访问。
几种动态散列(dynamic hashing)技术允许散列函数动态改变，以适应数据库增大或缩小的需要。这里介绍一种称为可扩充散列(extendable hashing)的动态散列技术。

静态散列和动态散列比较

可扩充散列最主要的优点是其性能不随着文件的增加而下降。此外，其空间开销是最小的。尽管桶地址表带来了额外的开销，但该表为当前前缀长度的每个散列值存放一个指针，因此该表较小。可扩充散列与其他散列形式相比，主要的空间节省是不必为将来的增长保留桶；桶的分配是动态的。
可扩充散列的一个缺点在于查找涉及附加的间接层，因为系统在访问桶本身前必须先访问桶地址表。这一额外的访问只对性能有一个微小的影响。

顺序索引和散列的比较

大多数数据库系统支持B+树索引，并且有可能还额外支持某种形式的散列文件组织或散列索引。
要对文件组织和索引技术做出明智的选择，实现者或数据库设计者必须考虑以下问题：
(1)索引或散列组织的周期性重组代价是否可以接受
(2)插入和删除的相对频率如何
(3)是否愿意以增加最坏情况下的访问时间为代价优化平均访问时间
用户可能提出哪些类型的查询
对于特定值查找，散列索引方案更可取。
对于值范围的查找，顺序索引更可取。
通常设计者会使用顺序索引，除非他预先知道将来不会频繁使用范围查询。在这种情况下使用散列索引。

位图索引

位图索引是一种为多码上的简单查询设计的特殊索引，尽管每个位图索引都是建立在一个码之上的。
为了使用位图索引，关系中的记录必须按顺序编号，比如从0开始。对于给定的一个n值，必须能很简单地检索到编号为n的记录。然后该记录号就可以很简单地转化为一个块编号和一个指出块内记录的记录号。

位图索引结构

位图(bitmap)就是位的一个简单数组（每一位(bit)代表一个记录）。在其最简单的形式中，关系r的属性A上的位图索引(bitmap index)是由A能取到的每个值建立的位图构成的。每个位图都和关系中的记录数相等数目的位。如果编号为i的记录在属性A上的值为 $v_j$ ，则值为 $v_j$ 的位图中的第i个位置为1。