误区和误解

误区1: 索引层级不要超过5层

这个通常基于的假设就是:只有根页是留在内存中的. 对于现代的硬件来说,对索引的层数做强制限制是没有什么意义的.

现今,我们完全可以假设B树索引的所有非叶子页通常都会留在内存或读缓存中.

误区2: 单表的索引数不要超过6个

保证所有的SQL语句都能够流畅运行是设计的底线.我们总能找到一种方法来达到这一点.如果为了达到这一点需要在表上创建10个索引,那么你就应该在表上建10个索引.

误区3: 不应该索引不稳定的列

这人与误区1相似,也是基于:只有根页是留在内存中的. 对于现代的硬件来说,更新一个不稳定的列,只会对该更新操作增加10ms的响应时间.

只有在更新频率多于10次/秒的情况下,不稳定列才可能成为问题,但是这样的列并不常见.

系统化的索引设计

步骤1

找到由于索引不合适而导致运行太慢的查询语句(最差输入,导致执行时间最长的变量值)

步骤2

设计索引,使所有查询语句都运行得足够快(表的维护,插入,更新,删除等也必须足够快)

表和索引结构

索引页和表页

表和索引行,都被存储在页中.页的大小,一般为4KB,这是一个可以满足大多数需求的大小,不过,也可以使用其他大小.

页的大小,仅决定了一个页可以存储多少个索引行,表行,以及一共需要多少个页来存储表或者索引.

当表和索引被加载或者重组时,每个页都会预留出一定比例的空闲空间,以满足向其添加新的表行或索引行的需求.

缓冲池和I/O都是基于页的.这意味着,一次I/O会读入多条记录到缓冲池,而不仅是一条.我们还可以看到,一次I/O可以读入多个页到缓冲池.

索引行

一个索引行,等同于叶子页中的一个索引条目.字段的值从表中复制到索引上,并加上一个指向表中记录的指针.通常,表页的编号是这个指针的组成部分,我们需要牢记这一点.

索引结构

非叶子页通常包含着一个(可能被截断的)键值,以及一个指向下一层级页的指针,该键值是下一层级页中的最大值.多个索引层级按照这一方式逐层建立,直到只剩下一个页,我们把这个页叫作根页.

它位于索引结构的最上层,这种组织方式的索引被称为B树索引.因为通过这种索引来查找任何一条索引记录都需要访问相同数量的非叶子页.

表行

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

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

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

缓冲池和磁盘I/O

关系型数据库管理系统最重要的一个目标是确保表或者索引中的数据是随时可用的.为了尽可能实现这个目标,我们使用内存中的缓冲池来最小化磁盘活动.索引或者表页在不在缓冲池中,访问的成本是不同的.

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

我们必须记住,一个页包含了多条记录,我们可能需要该页上所有的行,或者其中的一部分行,又或者是其中的一行,但所花的成本都是相同的,约10ms.

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

如果DBMS所需要的页,不在数据库缓冲区中,会继续向磁盘服务器发起请求,磁盘服务器会判断该页是否在服务器缓冲区中,只有当它发现不在缓冲区中时才从磁盘驱动器上读取该页,如果该页在磁盘服务器的读缓冲区中,那么所花费的时间将从10ms降低到1ms.即

数据库缓冲池 ==> 磁盘服务器的读缓冲区 ==> 磁盘I/O.

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

  • 全表扫描
  • 全索引扫描
  • 索引片扫描
  • 通过聚簇索引扫描表行

顺序地读取页的优势 :

(1) 同时读取多个页意味着平均读取每个页的时间将减少.在当前的磁盘服务器条件下,对于4KB大小的页而言,这一值可能会低至0.1ms(40MS/s)

(2) 由于DBMS事先知道需要读取哪些页,所以,可以在页被真正请求之前就提前将其读取进来,我们称为预读.

自动跳跃式顺序读

从定义上看,如果一系列不连续的行被按照同一个方向扫描,那么访问模式将会是跳跃顺序的.于是,每行的平均I/O时间自然比随机访问时间短,跳跃的距离越短则节省的距离越多.

当表行是通过一个聚簇索引读取时,访问模式即为跳跃式顺序的.

跳跃式顺序读的好处能在两种情况下被放大:

  1. 磁盘服务器可能注意到对某一驱动器的访问是顺序的(或者几乎是顺序的),于是服务器开始向前提前读取多个页.
  2. DBMS可能注意到SELECT语句正以顺序的或几乎顺序的方式访问索引或表页,于是DBMS便开始向前提前读取多个页.

列表预计

DB2 for z/OS 能够在表和索引行顺序不一致的情况下主动创造跳跃式顺序访问.为了做到这一点,它需要访问所有满足条件的索引行,然后按表页的顺序对其进行排序后再访问表行.

数据块预读

当表行和索引行的访问顺序不一致时,Oracle会使用数据块预读这一特性.

DBMS特性

页

表页的大小限定了表行的最大长度.通常,一个表行必须能够存入一个表页中, 一个索引行也必须能够存入一个叶子页中.

如果一张表的平均行长大于表页大小的三分之一,那么空间利用率将很糟.

Oracle使用术语:块(BLOCK_SIZE)来代表术语页.

表聚簇

通常情况下,一个表页中只包含一张表中的数据.但Oracle提供了一个选项以支持在一个表页中存储多个相关表的数据.(如一个张表的主键,其他表引用这个主键为外键).当把所有与这个表相关的数据交错存储在一张表中,这些数据可能都能够保存在同一页中.(注意:这与聚簇索引是完全不同的东西)

索引行

通常,索引键由所有被复制到索引上的列组成,它决定了索引条目的顺序.在唯一索引中,索引条目等同于索引行.在非唯一索引中,对于每一个唯一的索引键值,都存在一个索引条目,以及指向每一个满足该索引键的表行的指针. 在这种方式下, DBMS先从索引片上收集指针,然后再进行多重随机I/O来并行地读取表行.

表行

使用聚簇索引的目的是为了使得表行的存储顺序尽可能地与索引行的顺序保持一致.如果表上没有聚簇索引,那么新插入的表行将会被放置在表的最后一个页上,或者被放置在任何一个有足够空闲空间的表页上.

无论何种DBMS,都可以通过频繁地重组表来使得表行按照所需要的顺序存储–通过重载前经由某个特定的索引读取表行来实现,或者通过在重载前对数据进行排序来实现.

索引组织表

如果一个表的行不是特别长,那么可以考虑将表上所有的列复制到索引上,以加快SELECT的执行速度.如此一来,表就变得冗余了.

包含表行的索引被称为主键索引.

其余的索引(Oracle称为次级索引,SQL Server中称为非聚集索引)都指向包含表行的那个索引.

好处:

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

然而, 这给其余的索引带来了不利.任何对于主键索引键的更新操作,由于需要移动索引行,都会导致DBMS去更新那些指向这一索引行的其他索引行.(即导致大量磁盘I/O) 所以,为了降低指针维护成本, 通常会选择一个键值不会被更新的索引作为聚集索引.

磁盘前读

当DBMS请求一个页时,磁盘系统可能也会将接下来的一组页加载至磁盘缓冲区中(它预测这些页也可能很快被请求到).这种机制称为磁盘前读.

页邻接

物理相邻的三个级别:

  1. 读取一个页, 得到许多行.(这是自动的,如果4KB大小的页包含10行,那么I/O时间=1ms每行)

  2. 读取一个磁轨, 得到许多页.(这是DBMS或磁盘系统支持的,可能将顺序I/O时间降到0.1ms每行)

  3. 磁盘服务器提前从驱动器上将数据读取至读缓冲区中(这是磁盘服务器支持的,可能将顺序I/O时间降到0.01ms每行)

B树索引的替代品

位图索引

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

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

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

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

  1. 可能的谓词组合数量太多了.以至于设计足够的B树索引是不可行的.

  2. 单个谓词具有很高的过滤因子, 但组合起来后具有很低的过滤因子, 或者select列表中只包含COUNT

  3. 更新操作是批量进行的.(不存在锁争用)

散列

散列,或者说是随机化, 是在已知主键值的情况下,读取一个表行的最快方式.

聚簇的许多含义

聚簇索引: 定义了新插入的表行所在表页的索引.如果索引行的顺序和表行的顺序之间具有强关联性,那么就可以说该索引是聚集的.一张表上只能有一个聚簇索引. 索引的聚簇比例,是指索引行和表行顺序之间关联度的一个量度.优化器会使用这一测量值来估算I/O时间.

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

Oracle中,聚簇一词被用于代表将多个表的行交错存储(聚簇的表).这个词与限定表行顺序的聚簇索引毫不相干.