数据库索引设计与优化(笔记):第2章 表和索引结构(还是一些概念)

索引页和表页

  1. 表和索引都存在页中。一般页大小是4KB(可变,但不是索引设计考虑的关键)。
  2. 每个页会预留一定比例的空闲空间,以便于添加新行。
  3. 缓冲池和IO活动都是基于页的,所以一次IO可以将多行读入。也可以一次IO读取多个页。
  4.  

索引行

  1. 对唯一索引(如主键):一个索引行在叶子节点是一个索引条目。字段值从表中复制到索引上,并加上一个指向表中记录的指针。(通常页编号是这个指针的一部分,这样可以一次定位
  2. 对非唯一索引:一个特定的索引值对应的索引行被想象成独立的索引条目(它还包含子索引条目,即还要带多个指针,才能指向不同的字段)

索引结构

非叶子节点包含一个(可能被截断)的键值,以及一个指向下一层级页的指针。该键值是下一层页中最大的键值。形成分层的、有序的、树状结构。最上一个叫做根页。

这种结构叫做B树,是一种平衡树:查找任何记录都需要访问相同数量的非叶子页(平衡

表行

索引行的指针指向行记录。表中行除了存储行的字段外,还包含一些控制信息。

表中的记录顺序可以被定义成和它某一个索引记录相同的顺序。这样索引和表都可以按照相同的顺序被访问,这是一种高效的处理过程。

关于数据读取的一些概念

  1. 用内存缓冲池来最小化磁盘IO
  1. 一页包含多条记录。不管是需要其中一行,还是所有行,所花费成本是一样的。
  2. 从磁盘驱动器读取:这本书里面是一个(在特定时间期间)粗略的经验值:10ms(包含排队、寻道、旋转、传输)。现在SSD盘要快很多。us级别。
  3. 磁盘提供缓冲区优化读性能,粗略估计,成本是1ms
  4. 有时需要将多个页读入缓冲池,并按顺序处理,则会发出多页IO请求。按照40MB/s计算,则每页的花费只有0.1ms。----当然这是针对磁盘存储的。
  5. 数据库还知道哪些页要读,可以提前读,称为预读
  6. 辅助式随机读
    • 自动跳跃式顺序读:在一系列不连续的行上,按照同一个方向扫描,比随机方位每行,每行所用的平均时间要小。

当表行和索引行访问顺序不一致时,先通过预读,从同一个方向扫描索引,然后进行多重随机IO来并行读取行。

  1. 辅助式顺序读:当要扫描一张大表,优化器可能会选择开启并行机制。例如,把一个游标拆分成多个范围谓词限定的游标,每个扫一片段。在多处理器、多磁盘驱动器下,会快。
  2. 同步IO和异步IO
  • 同步IO:进行IO操作时,DBMS不能继续其他操作。
  • 异步读:前异步的页尚在处理时,就提前发起新页的读。有一定重叠处理时间。
  • 当DBMS请求一个页时,磁盘会将接下来的页加载到磁盘缓存区。这是磁盘前读。

硬件特性(写这个书时候的一些信息)

磁盘转速:1W转/分~1.5万转/分----现在磁盘也差不多

平均寻道时间:3-4ms

DBMS特性

页:

  • 页的大小限制了表行的最大长度。通常一个表必须能够存入一个表页中。一个索引行业必须能存入到一个叶子页中。如果一个表的平均行长度大于页的1/3,则很浪费。如果索引行长度大约叶子页的20%,就可能造成糟糕的空间利用率和频繁的叶子分裂。
  • 由于一个磁轨容量通常大于100KB,所以磁盘对于2KB、4KB、8KB的随机读取时间大致相同。不过RAID的条带大小必须大到足以存放一个页。否则为了读取一个页而多次访问磁盘,就不好了。
  • 由于一般IO都会顺序多读一些至缓存中,所以页的大小对顺序读的性能影响不大
  • SQL Server2000:只支持8KB页。索引行最大长度是900字节
  • Oracle:用BLOCK来代替页的说法。BLOCK_SIZE可支持2KB、4KB、8KB、16KB、32KB、64KB。有可能受到OS的限制。索引行的最大长度为BLOCK_SIZE的40%。

表聚簇(主外键关联场景下的一个优化特例)

  • 通常一个表页中只包含一张表中的数据。Oracle提供了一个选项,支持在一个表页中交错存储多个相关表的数据。这是根据业务访问特性的一种优化。
  • 主要在多表主外键关联,合起来保存完整信息时。如果放在一起,可一次性从不同表获取完整信息,而不是需要访问多个表的页。

索引行

  • 在一个索引上,DBMS规定了支持列的数量上限
  • SQL Server:16
  • Oracle:32
  • 索引建由所有被复制到索引上的列组成,它决定了索引条目的顺序。在唯一索引中,索引条目等同于索引行;在非唯一索引中,对每个唯一索引键值,都存在一个索引条目,以及指向每个满足该索引建的表行的指针。
  • 每个索引条目还需要一定数量的控制信息,用以将其按键值顺序串联起来。假设控制信息占10个字节。

索引组织表

  • 如果一个表的行不是特别长,可以考虑将表上所有列复制到索引上,以加快SELECT的执行速度。这样,表就变得冗余了。有些DBMS支持选项,将多余的表去除。若使用这个选项,则其中一个索引的叶子页将用于存储表行。包含表行的索引,被称为主键索引。其他索引(Oracle叫次级索引,SQL叫非聚集索引)
  • 好处:节省空间,INSERT、DELETE、UPDATE会加快,因为少了一个需要更新的页
  • 不好:给其他索引带来不利。因为这些索引使用的是直接指向表行的指针,那么主键索引的一次叶子页分裂将导致其余索引上大量的磁盘IO。任何对于主键索引建的更新操作,由于需要移动索引行,都会导致DBMS去更新那些指向这一索引行的其他索引行。
  • SQL Server做了个优化:把主键索引的索引建(而不是具体的表行)作为指向聚集索引的指针值,能避免叶子分裂带来的额外负载(主键索引的键值没变)。
  • 但也有负面影响:
    1. 但如果聚集索引的键值很长,则非聚集索引会变得更大(都保存这个长键值)。
    2. 任何经由非聚集索引访问,都需要设计两组非叶子节点的读取;第一组是非聚集索引的非叶子节点、第二组是聚集索引的非叶子节点。不过,认为这个负载不是主要问题,因为非叶子节点可被缓冲。
  • 另外:SQL server中聚集索引并非必须为主键索引。为了降低维护成本,通常会选择一个键值不会被更新的索引作为聚集索引。在大部分索引中,所有的索引列一起构成索引建。

页邻接

  • 逻辑上相邻的页,如果在物理上相邻,顺序读会非常快
  • 三个级别:读取一个页,得到许多行(自动的,1ms/行);读取一个磁轨,得到许多页(DBMS或磁盘系统支持0.1ms/行);磁盘服务器提前从驱动器上将数据读取到缓存区(可能将顺序IO时间降至0.01ms/行)

B树索引的替代品:位图索引

  • 位图索引:对于复杂且不可预测的组合谓词的大表查询,适合用位图索引。用位图索引计算与或的速度非常快,计时表行达到亿级也如此
  • 但是位图索引必须访问表行,除非SELCT 列只包含COUNT。因此,位图索引可能比使用一个合适的B树索引总执行时间唱的多。
  • 位图索引适合的情况
  1. 可能的谓词组合数量太多,以至于设计足够的B树索引是不可行的
  2. 单个谓词具有很高的过滤因子,但组合起来之后具有很低的过滤因子;或者SELECT列只包含COUNT
  3. 更新操作是批量进行的(不存在锁争用)---频繁更新,只更新1位,会造成争用

B树索引的替代品:散列(Hash)

  • 就是随机:在已知主键值得情况下,读取一个表行最快的方式。当存储一行数据时,表页是由一个随机选择器选择的,它将主键值转换为1~N的某个页号。如果页满,则该行被放在另一个串联至整个主页的页中。
  • 当发起SELECT where Pk=:PK,随机发生器再次用来决定主页的页号。
  • 当区间大小(N)设置合适(对应70%的空间利用率)时,读取一行记录所需的IO次数可以低至1.1。比索引要低很多。当添加很多记录,页链表长度会增加,IO次数随之增加。
  • 随机数发生器无法支持范围谓词(已经变得不可理解了)。

聚簇的许多含义

  • 这个词有不同含义
  • DB2:聚簇索引使指定义了新插入的表行所在表页的索引。如果索引行的顺序和表的顺序之间具有强关联性,就说该索引使聚集的。一张表只能有一个聚簇索引(也只能这样),但是在某个特定时间,可能有多个索引是聚集的(标红这句话不理解,后面再看)。索引的聚簇比例(CLUSTERRATIO)是指索引行和表行顺序之间关联度的一个亮度。优化器会使用这个来估算IO时间。

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

    • SQL Server中,存储表行的索引被称为聚集的(就是上面说的索引组织表)
    • Oracle中,聚簇一词用于代表将多个表的行交错存储(就是上面说的表聚簇)
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值