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

最新推荐文章于 2021-05-12 17:49:17 发布

轻舟要过万重山

最新推荐文章于 2021-05-12 17:49:17 发布

阅读量346

点赞数

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

本文链接：https://blog.csdn.net/zgytransmit/article/details/88365833

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

索引页和表页

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

索引行

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

索引结构

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

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

表行

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

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

关于数据读取的一些概念

用内存缓冲池来最小化磁盘IO

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

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

辅助式顺序读：当要扫描一张大表，优化器可能会选择开启并行机制。例如，把一个游标拆分成多个范围谓词限定的游标，每个扫一片段。在多处理器、多磁盘驱动器下，会快。
同步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树索引总执行时间唱的多。
位图索引适合的情况

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

B树索引的替代品：散列（Hash）

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

聚簇的许多含义

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

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