表和索引结构

夏末蝉未鸣01

已于 2024-09-04 17:21:14 修改

阅读量581

点赞数 29

分类专栏：数据库文章标签：数据库

于 2024-05-21 16:16:12 首次发布

本文链接：https://blog.csdn.net/m0_62410482/article/details/139093047

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

索引及表页、索引及表行、缓冲池，以及磁盘缓存的结构和使用

一些辅助式随机读和顺序读：跳跃式顺序读、列表预读及数据块预读

页和表聚簇、索引行、索引组织表，以及页邻接

索引及表页、索引及表行、缓冲池，以及磁盘缓存的结构和使用

表页和索引页

表和索引行都存储在页中。页的大小一般为4KB，这是一个可以满足大多数需求的大小，也可以使用其他大小。页的大小仅决定了一个页可以存储多少个索引行、表行，以及一共需要多少个页来存储表、索引。当表和索引被加载或重组时，每个页都会预留出一定的空闲空间，以满足向其添加新的表行和索引行的需求。

缓冲池和I/O活动都是基于页的，一次将一个完整的页读取进缓冲池，一次I/O可以读入多个页到缓冲池中。

索引行

索引行在评估访问路径的时候是一个非常有用的概念。

对于一个唯一索引，一个索引行等同于叶子页的一个索引条目。字段的值从表中复制到索引上，并加上一个指向表中记录的指针。通常，表页的编号也是这个指针的组成部分。

对于一个非唯一索引，一个特定的索引值所对应的索引行应该被想象成独立的索引条目，每一个都含有相同的字段值，但却有不同的指针。大多数情况下，非唯一索引的实际存储方式是一个字段值后带着多个指针。

索引结构

非叶子页通常包含着一个（可能被截断的）键值，以及一个指向下一层级页的指针，该键值是下一层级页中的最大键值。多个索引层级按照这一方式逐层建立，直到只剩下一个页——根页，它位于索引结构的最上层。这种组织方式的索引被称为B树索引，通过这种索引来查找任何一条索引记录都需要访问相同数量的非叶子页。

表行

每一个索引行都指向表中相对应的一行记录，指针通常标识了记录所在的页及它在页中的位置。表行除了存储行的字段之外，还包含了一些控制信息用于定义行并帮助DBMS处理插入或删除操作。

当加载表或向表中插入记录的时候，表中记录的顺序可以被定义成和它的某一个索引记录相同的顺序。在这种情况下，当索引行被按顺序处理时，对应的表行也将依照相同的顺序被逐个处理。索引和表都按相同的顺序被访问，这是一个效率很高的处理过程。

显然，表中记录的顺序只能按照表中某一个索引的顺序组织。如果通过表上其他索引来访问这张表，那么表中相应的记录将不会按照与索引条目相同的顺序存储。如此一来，虽然索引的顺序依然是顺序且高效的，但是表的处理却是随机且低效的。

缓冲池

关系型数据库管理系统最重要的一个目标是确保表或者索引中的数据是随时可用的。为了尽可能实现这个目标，使用内存中的缓冲池来最小化磁盘活动。每个DBMS都会根据对象类型及页的大小拥有多个缓冲池。缓冲池管理器将尽力确保经常使用的数据被保存在池中，以避免一些不必要的磁盘读。索引或表页在或不在缓冲池中，访问的成本是不同的。

从DBMS缓冲池进行的读取

如果一个索引或者表页在缓冲池中被找到，那么唯一的成本就是去处理这些索引或者表中的记录。

随机和顺序磁盘I/O的特性

从磁盘驱动器进行的随机I/O

随机读取（random read）是指访问的页不是连续的，需要磁盘的磁头不断移动。

一个页包含了多条记录，我们可能需要该页上所有的行，或者其中一部分行，又或者只需要其中某一行，但所花的成本都是相同的，约10ms。如果磁盘驱动器被重度使用，那么这一速度将大幅降低，因为需要等待磁盘空闲下来。

在计算领域中，10ms是一个很长的时间，我们并不一定需要知道这10ms是怎么得出的，只要知道这一粗略但合理的数字即可。

从磁盘服务器缓存进行的读取

幸运的是，现今的磁盘服务器都提供自己的内存（或缓冲区）以降低响应时间上的巨大成本。若DBMS所需的页不在数据库缓冲池中，继而会向磁盘服务器发起读请求，磁盘服务器会判断该页是否在服务器缓冲区中，只有当它发现页不在缓冲区中时才从磁盘驱动器读取该页。如果该页在磁盘服务器的读缓冲区中，那么所花费的时间将从10ms大幅降至1ms。

从磁盘驱动器进行的顺序读取

在许多实际场景中，我们需要将很多个页读取到缓冲池中，并按顺序处理它们。DBMS会意识到多个索引或者表页需要被顺序的读取，且能识别出那些不在缓冲池的页。随后，它将发出多页I/O请求，每次请求的页的数量由DBMS决定。只有那些不在缓冲池的页会被从磁盘服务器上读取，因为那些已经在缓冲池中的页可能包含了尚未被写入磁盘的更新数据。

顺序地读取页有两个非常重要的优势：

同时读取页意味着平均读取每个页的时间将会减少。在当前的磁盘服务器条件下，对于4kb大小的页而言，这一值可能会低至0.1ms（40MB/s）；
由于DBMS事先知道需要读取哪些页，所以可以在页被真正请求之前就提前将其读取进来，我们称其为预读。

一些辅助式随机读和顺序读：跳跃式顺序读、列表预读及数据块预读

辅助式随机读

我们已经看到了缓冲池和磁盘缓冲区是如何将随机读取的高成本降至最低的。还有其它一些场景也能降低这些成本，有时是自然发生的，有时是优化器有意为之的，本书将它们统称为辅助式随机读（这个术语并未被任何一个DBMS使用）。

自动跳跃式顺序读

从定义上看，如果一系列不连续的行按照同一方向扫描，那么访问模式将会是跳跃式顺序的。于是，每行的平均I/O自然比随机访问时间短，跳跃的距离越短，则节省的时间越多。比如，当表行是通过一个聚簇索引读取时，访问模式即为跳跃式顺序的。跳跃式顺序读的好处在两种情况下被放大：

磁盘服务器可能注意到对某一驱动器的访问是顺序的（或者几乎是顺序的），于是服务器开始向前提前读取多个页；
DBMS可能注意到SELECT语句正以顺序或几乎顺序的方式访问索引或表页，于是DBMS便开始向前提前读取多个页，这在DB2 for z/OS中被称为动态预读。

列表预读

在上面的例子中，由于表和索引行被访问的顺序是一致的，所以很方便地获得了成本降低收益。事实上DB2 for z/OS能够在表和索引行顺序不一致的情况下主动创造跳跃式顺序访问。为了做到这一点，它需要访问所有满足条件的索引行，然后按照表页对其排序后再访问表行。图中数字代表了操作的顺序。

数据块预读

当表行和索引行访问顺序不一致时，Oracle会使用数据块预读这一特性。在这种方式下，DBMS先从索引片上搜集指针，然后再进行多重随机I/O来并行读取表行。如果第4、5、6不所代表的表行分别位于三个不同的磁盘驱动器上，那么这三次随机I/O将被并行执行。

一个索引也许能够提供正确的顺序，但上述所讨论的机制也许会在访问表行之前就破坏了这一顺序，因此，也就需要对结果集进行一次排序了。

辅助式顺序读

当要扫描一张大表时，优化器可能会选择开启并行机制。例如，它可能会将一个游标拆分为多个用范围谓词限定的游标，每一个游标扫描一个索引片。当有多个处理器和磁盘驱动器可用时，所花费的时间将相应的减少。辅助式顺序读这一术语同样也未被任何一个DBMS使用过。

同步和异步I/O的意义

术语同步I/O是指在进行I/O操作时，DBMS不能继续进行其他操作，它必须等待，直到I/O操作完成。

异步读是指当前一步的页尚在处理时就提前发起了，这一处理时间和I/O时间之间可能有很大一部分重叠。理想情况下，在这些页被实际处理前，异步I/O就已经完成了。每一组页都以这种方式被预读然后再处理。注意，整个预读过程从一次同步读开始，然后才开始预读过程，以此来最小化首次读取的等待时间。

当DBMS请求一个页时，磁盘系统可能也会将接下来一组页加载至磁盘缓冲区中（它预测这些页可能很快就会请求到）。这一组页可能是条带中的剩余部分，可能是磁轨的剩余部分，也可能是多个条带。我们将这种机制称为磁盘前读。

大部分数据库都是异步的，以达到对性能的影响最小化。这一方式导致的最主要影响是加重了磁盘环境的负载，而这反过来会影响读I/O的性能。

硬件特性

在本书写作时，数据库服务器所使用的磁盘驱动器在性能表现上相差无几。它们的转速为10000~15000转/min，平均寻道时间为3~4ms，本书所建议的从磁盘驱动器随机读取的平均时间估算值（10ms）对于当前的所有磁盘系统都是适用的。

顺序读取的时间则由于配置的不同而差别很大。它不仅取决于连接（和最后争用）的带宽，还取决于并行度。RAID条带为一个单线程提供了潜在的并行能力。

除了I/O时间估值以外，磁盘空间和内存的成本也会对索引设计造成影响。

本地磁盘只能提供物理数据存储能力，无法提供磁盘服务器所能提供的其他额外功能（比如容错、读写缓冲区、条带等）。

磁盘服务器是具备了多个处理器和大量内存的计算机。最先进的磁盘服务器有容错能力：所有必要的组件都有一份冗余，并且软件能够支持将操作快速转移至另一个备用单元上。

本次磁盘和磁盘服务器用的都是标准的磁盘驱动器。磁盘驱动器越大，平均每千兆的成本越低。例如，一个145GB的驱动器比8个18GB的驱动器更便宜，然而，在相同的访问密度下，也需要更久的排队时间。

每一个索引设计者都应该弄清自己系统的值。

页和表聚簇、索引行、索引组织表，以及页邻接

页

表页的大小限制了表行的最大长度。通常，一个表行必须能够存入一个表页中，一个索引片叶必须能够存入一个叶子页中。如果一张表的平均行长大于表页大小的三分之一，那么空间利用率将会很糟。例如，一个4kb大小的页只能存储一个长度为2100字节的行。无法利用的空间问题对于索引更为严重。由于新的索引行必须根据索引键的值放在相应的某个叶子页中，因此在加载和重组后，许多索引的叶子页都应该有能够容纳多个索引行的空闲空间。因此，如果索引行的长度大于叶子页大小的20%，就有可能造成糟糕的空间利用率和频繁的叶子页分裂。

在当前的磁盘条件下，磁盘的一次旋转需要4ms（15000rpm）或6ms（10000rpm）。由于一个磁轨的容量通常大于100千字节（kb），因此2kb、4kb和8kb大小的页的随机读取时间大致是相等的。不过，RAID磁盘的条带大小大到足以访问一个页，否则，为了读取一个页就可能需要访问不止一个磁盘驱动器。

在如今大部分环境下，顺序处理过程中一次I/O操作会同时将多个页加载至缓冲池中，多个页会在一次磁盘转动中被传输进来，所以，页的大小对于顺序读的性能影响并不大。

表聚簇

通常情况下，一个表页只包含一张表中的数据。Oracle提供了一个选项以支持在一个表页中交错存储多个相关表的数据。这与存储多种不同段类型的分层IMS数据库记录相似。例如，一份保单可能在5张表中存有相关的数据。单据号可能是其中一张表的主键和其余4张表的外键。当把所有与这个保单相关的数据交错存储在一张表中时，这些数据可能都能够保存在同一页中。于是读取某个表单相关的所有数据可能就只需要一次I/O操作而非5次。而另一方面，交错存储多张表的数据可能会在其他方面引起一些问题。

索引行

在一个索引上，当前DBMS所能支持的列是有上限的。

在一些数据库产品中，索引变成的列会有一些限制。如果只支持定长的索引行，那么DBMS会将变长列扩展至最大长度，然后再存储在索引中。由于变长列变得越来越普遍，对于变长索引列（及索引行）的支持在新的数据库版本中已经很常见了。

通常，索引键由所有被复制到索引上的列组成，它决定了索引条目的顺序。在唯一索引中，索引条目等同于索引行。在非唯一索引中，对于每一个唯一的索引键值，都存在一个索引条目，以及指向每一个满足该索引键的表行的指针。除此之外，每一个索引条目还需要一些特定数量的控制信息，用以将其按键值串联起来。为了计算每页所能存储的索引行数量，本书假设这一控制信息所占用的空间约为10字节。

系统组织表

如果一个表的表行不是特别长，可以考虑将所有列复制到索引上，以加快SELECT的执行速度。如此一来，表就变得冗余了。有些DBMS有去除多余表的选项，若使用这一选项，那么其中一个索引的叶子页将用于存储表行。

在Oracle中，这一选项被称为索引组织表，包含表行的索引被称为主键索引。在SQL Server中，可以用CLUSTERED创建一个存储表行的索引。在这两种环境中，其余索引（在Oracle中被称为次级索引，在SQL Server中被称为非聚集索引）都指向包含表行的索引。

索引组织表最明显的好处就是节省磁盘空间，INSERT、UPDATE和DELETE的操作速度也更快，因为少了一个需要更新的页。

但也会给其他索引带来不利。如果这些索引使用的是直接指向表行的指针（指针中包含页号），那么主键（聚集）索引的一次叶子页分裂将会导致其余索引上大量的磁盘I/O。任何对于主键索引键的更新操作，涉及到移动索引行，都会导致DBMS去更新那些指向这一索引行的其他索引行。

这也就是为什么SQL Server会把主键索引的索引键作为聚集索引的指针值，这么做可以避免叶子页分裂所带来的额外负载，但如果聚集索引的键值很长，那么在这种方式下，那些非聚集索引将变得很大。此外，任何经由非聚集索引的访问都需要读取两组非叶子页（非聚集索引的叶子页和聚集索引的叶子页）。

本书介绍的技术对于索引组织表页同样适用，虽然在图表中总是会画出单独的表。如果使用了索引组织表，那么主键表应当被视为一个满足所有SELECT语句的宽聚簇索引。索引行的顺序是有索引键决定，其余的列则为非键列。

注意，在SQL Server中聚集索引并非必须为主键索引。然而，为了降低指针维护的成本，通常会选择一个键值不会被更新的索引作为聚集索引，如主键索引或候选键索引。在大部分索引中，所有的索引列一起构成了索引键，因此，在其余的索引中很难找到键值不会被更新的索引。

页邻接

逻辑上相邻的页如果在磁盘上也是物理相邻的，那么顺序读将会非常快。

在某些较早的DBMS（如SQL/DS和SQL Server的早期版本）中，一个索引或表的页可以分布在一个大文件的各个地方。这种情况下，随机读和顺序读在性能上的唯一区别就是，一些逻辑上相邻的行被保存在同一页中。读取下一个页将需要进行一次随机I/O。如果每页包含10行数据，且一次随机I/O耗费10ms，那么顺序读取的I/O时间为1ms/行。

SQL Server在为索引和表分配空间时，一次性分配8个8kb的页。DB2 for z/OS以区间的粒度分配空间，一个区间的大小可能为许多MB，通常一个中等大小的索引或表的所有页都能保存在一个区间中，于是，逻辑相邻的页在物理上也互相挨着了。在Oracle（和其他一些系统）中，页的放置位置取决于所选择的文件选项。

现今许多数据库都存储在RAID5或RAID10（即RAID0+RAID1）的磁盘中。RAID5提供了具有冗余能力的条带。RAID10提供了具有镜像能力的条带。

RAID条带是指将一张表或一个索引的第一个条带存在驱动器1上，将第二个条带存在驱动器2上，以此类推。这能够将负载平均分配在一组驱动器上，并对顺序读的性能可能产生积极影响。

假如进行一次全盘扫描，被扫描的表页在7个驱动器上做条带。于是，磁盘服务器可以在7个驱动器上做并发前读了。当DBMS请求下一组页时，它们很可能已经在磁盘服务器的读缓冲区了。这一预读操作可能将I/O时间降至0.1ms每4kb大小的页。当拥有快速通道，且磁盘服务器能够发现一个文件正在被顺序访问时，就能使时间降到0.1ms。

关于术语聚簇的易混淆但非常重要的问题

聚簇在不同产品中的含义各不相同，因此容易产生混淆。

在DB2（z/OS、LUW、VM和VSE）中，聚簇索引是指定义了新插入表行所在表页的索引。如果索引行顺序与表行顺序之间具有强关联性，那么就可以说该索引是聚集的。一张表上只能有一个聚簇索引，但是，在某个特定的时间，可能有多个索引是聚集的。索引的聚簇比例（CLUSTERRATIO）是指索引行与表行顺序之间关联度的一个量度。优化器会使用这一测量值来估算I/O时间。

DB2的表上通常都有一个聚簇索引。

在SQL Server中，存储表行的索引被称为是聚集的，只有当需要一张索引组织表时才定义一个聚集索引。其余的索引（SQL Server中的术语为非聚集索引）都指向这一聚集索引。

在Oracle中，聚簇一词被用于代表将多个表的行交错存储（聚簇的表）的选项。该词与我们之前所讨论的限定表行顺序的聚簇索引毫不相干。

DB2 for LUW V8版本有一个称为多维度聚簇的选项，它使得相关的行能够被放在一起。

B树索引的替代品

位图索引

位图索引由针对各个不同列值的位图（位向量）组成。在每一个位图中，表中的每一行对应一个位。若该行满足位图条件，则该位被置为1。

对于复杂且不可预测的组合谓词的大表查询，适合用位图索引。因为用位图索引进行与和或计算的速度非常快，即便表行数量达亿级也是如此。而若使用B树索引进行同样的操作则需要收集大量的指针并进行排序。

另一方面，一个包含合适的列的B树索引能够避免表访问。这一点很重要，因为对一张大表进行随机I/O是非常慢的（约10ms）。而若使用位图索引，那么就必须访问表行，除非SELECT只包含COUNT。因此，使用位图索引可能比使用一个合适的（宽）B树索引的总执行时间长的多。

位图索引应当在满足以下条件的情况下使用：

可能得谓词组合太多了，以至于设计足够的B树索引是不可行的；
单个谓词具有很高的过滤因子，但组合起来之后（WHERE子句）具有很低的过滤因子，或者SELECT列中只包含COUNT；
更新操作是批量进行的（不存在锁争用）。

散列

散列，或者说随机化，是在已知主键值的情况下读取一个表行的最快方式。当存储一行数据时，表页是由一个随机选择器选择的，改选择器将主键值转换为1至N之间的某个页号。如果该页已满，那么该页将被放在另一个串联至这个主页的页中。当发起一个SELECT时，随机发生器将被再次用来决定主页的页号。该行要么能在该页中找到，要么能通过遍历从该页开始的页链表中找到。

随机发生器在一些非关系型的DBMS中经常用到，如IMS和IDMS。当区间大小（N）设置的合适（对应于70%的空间利用率）时，读取一行所需的I/O次数可以低至1.1，这相比索引要低得多（一个3层索引需要3次I/O，其中第3次用于读取记录本身）。然而，使用随机发生器的方式需要不断的监视和调整空间利用率。当添加了许多记录后，页链表的长度会增长，而I/O次数也会大幅增加。此外，随机发生器还无法支持范围谓词。Oracle提供了一个将主键值通过散列转换为数据库页号的选项。