数据库原理与安全笔记：存储结构与文件结构

该部分为本科期间数据库原理与安全笔记备份。

Storage Hierarchy

Typical storage hierarchy:

• Main memory (RAM) for currently used data
• Disk for the main database (secondary storage).
• Tapes for archiving older versions of the data (tertiary storage)

FAQ:Why Not Store Everything in Main Memory?

1. Costs too much.
2. Main memory is volatile(不稳定的) . We want data to be saved between runs. (Obviously!)

Disks

Secondary storage device of choice

This has major implications for DBMS design

• Read:data from disk -> main memory(RAM)
• Write:data from RAM -> disk
• Both are high-cost operations, relative to in-memory operations, so must be planned carefully!

一个磁盘通常包括几个盘片。每一个盘片上都覆盖着磁性物质，信息就记录在上面。使用磁盘时，磁盘高速恒定的速度旋转。读写头位于盘片的上面。磁盘表面有被划分为磁道，磁道有被划分为扇区。扇区是从磁盘读写数据的最小单位。通常512字节。每个盘片的正反面都有一个读写头，读写头通过在盘片上移动来访问不同磁道。

硬盘的逻辑结构

1. 盘片

两个盘面，自上而下自0开始依次编号，在硬盘系统中，盘面号又叫做磁头号。硬盘的盘片组在2-14片不等，通常有2-3个盘片，故片面号（磁头号）为0-3或0-5。

2. 磁道

磁盘在格式化时被划分成许多同心圆，这些同心圆轨迹叫做磁道（Track）磁道从外向内自“0”开始顺序编号。磁道又被划分成一段段的圆弧，每段圆弧叫做一个扇区，扇区从“1”开始编号，每个扇区中的数据是作为一个单元同时读出或写入的。

3. 柱面

所有盘面上的同一磁道构成一个圆柱，通常称作柱面（Cylinder）,每个圆柱上的磁头，由上而下从“0”开始编号。数据的读写是按柱面进行的。提高了硬盘的读/写效率。

4. 扇区

每个扇区包括512字节的数据和一些其他信息，一个扇区有两个主要部分：即存储数据地点的标示和存储数
据的数据段。标识符就是扇区的头标，包括有扇区的三维地址：磁头（盘面）、磁道（或柱面号）,以及扇区在磁道上的位置及扇区号。头标中还包括一 个字段，其中有显示扇区是否能可靠存储数据，还有校验（CRC）值作为结束。扇区的第二个主要部分是存储数据的数据段，可分为数据和保护数据的纠错码 （ECC）。

头标中还有一个可识别该扇区的扇区号，但并不是连续编号的，而是使用交叉因子编号。例如：每 磁道有17个扇区的磁盘按照2：1的交叉因子编号：1、10、2、11、3、12、4、13、5、14、6、15、7、16、8、17、9，而按照3：1 的交叉因子编号：1、7、13、2、8、14、3、9、15、4、10、16、5、11、17、6、12。

FAQ:为什么要引入交叉因子

系统将文件存储到磁盘上时，是按柱面、磁头、扇区的方式进行的， 读取数据也是如此。在读取或写入数据时控制器都要对数据进行后处理，这需要一定的时间，在这段时间内，磁盘已经转过了相当的角度。
交叉因子值太高，就需要多花一段时间等待数据在磁盘上存入和读出，如果太低，就会大大降低磁盘性能。取决于磁盘控制器的速度、主板的时钟速度、总线速度等。交叉因子的信息记录在扇区头标中，每个磁道可以有自己的交叉因子。

Accessing a Disk Page

Time to access (read/write) a disk block:

• seek time 寻道时间:moving arms to position disk head on track
• rotational delay 旋转等待时间:waiting for block to rotate under head
• ransfer time传输时间: actually moving data to/from disk surface

Sequential I/O is much faster than random(10 and 100 times faster)，Key to lower I/O cost

Storage Access

A database file is partitioned into fixed-length storage units called blocks (or pages).

Blocks:units of both storage allocation and data transfer.

We can reduce the number of disk accesses by keeping as many blocks as possible in main memory.

Buffer:portion of main memory available to store copies of disk blocks

Buffer manager:subsystem responsible for allocating buffer space in main memory

The buffer pool is organized as an array of frames.

frame：a region of memory the size of a database disk block.

Page Frame

Associated with the array of buffer pool frames is a hash table that maps：

• map the page numbers currently held in memory to their location in the frame table(把内存中当前的页面编号映射到它们在帧表中的位置)
• the location for that page on backing disk storage(页面在备份磁盘存储中的位置)
• some metadata about the page
  • Dirty bit
  • information needed by the page replacement policy
  • pin count

Buffer Manager

Programs call on the buffer manager when they need a block from disk.

• block already in the buffer -> given the address of the block in main memory
• block not in the buffer ->
  • allocates space,replacing (throwing out) some other block,make space for the new block;
  • The block that is thrown out is written back to disk only if it was modified since the most recent time that it was written to/fetched from the disk.
  • Once space is allocated in the buffer, the buffer manager reads the block from the disk to the buffer, and passes the address of the block in main memory to requester.

Buffer-Replacement Policies

LRU、MRU、FIFO、LIFO

Most operating systems replace the block least recently used (LRU strategy)

LRU can be a bad strategy for certain access patterns involving repeated scans of data

Toss-immediate strategy： frees the space occupied by a block as soon as the final tuple of that block has been processed

Most recently used (MRU) strategy：system must pin the block currently being processed

File Organization

Each file is a sequence of records. A record is a sequence of fields.

Most common approach:

1. record size is fixed
2. each file has records of one particular type only
3. different files are used for different relations

Fixed-Length Records

Free Lists

Do not move records in page. Store the address of the first deleted record in the file header.

Use this first record to store the address of the second deleted record, and so on

More space efficient representation: reuse space for normal attributes of free records to store pointers. (No pointers stored in in-use records.)

Variable-Length Records

1. Storage of multiple record types in a file
2. Record types that allow variable lengths for one or more fields
3. Record types that allow repeating fields (used in some older data models)

Byte string

Simple (but bad) solution: Byte string representation

特点：Attach an end-of-record control character to the end of each record

缺点：Difficulty with deletion，Difficulty with growth

Slotted Page

header:number of record entries、end of free space in the block、location and size of each record.

特点：

1. Pointers do not point directly to record — instead they point to the entry for the record in header.
2. Records can be moved around within a page to keep them contiguous with no empty space between them; entry in the header must be updated.

Organization of Records in Files

Heap：a record can be placed anywhere in the file where there is space

Sequential：store records in sequential order, based on the value of the search key of each record

Hashing：a hash function computed on some attribute of each record; the result specifies in which block of the file the record should be placed

Heap file

Simplest file structure contains records in no particular order.

最简单、最早使用的文件结构。记录按其插入的先后次序存放（所有记录在物理上不一定相邻接）

Records are added to the end of the file

优点：Cheap record addition, they imply linear search.

缺点：Expensive record retrieval, removal and update

堆文件插入容易、查找不方便（所提供的唯一存取路径就是顺序搜索）

解决删除麻烦问题：做删除标记，集中删除记录，并进行记录重排。

Heap File Using a Page Directory

The entry for a page can include the number of free bytes on the page.

The directory is a collection of pages; linked list implementation is just one alternative.Much smaller than linked list of all HF pages!

Direcoty是文件索引，把所有的文件组织起来。

Sequential File Organization

Suitable for applications that require sequential processing of the entire file

The records in the file are ordered by a search-key (not the same concept a key in the relational model)

operation:

Deletion – use pointer chains

Insertion –locate the position where the record is to be inserted

• if there is free space insert there
• if no free space, insert the record in an overflow block
• In either case, pointer chain must be updated

Need to reorganize the file from time to time to restore sequential order

优点：Cheap ordered record retrieval, less expensive record deletion

缺点：Expensive record addition

Hashing as a file organization

When we insert a new record we compute the hash function and insert the record in the appropriate bucket.

If the bucket is full we create an overflow page/bucket and insert the new record there.

解决hash function多个同一结果：

we read the page, we can check all its records and select only the ones that satisfy the query condition.

• If there are no overflow buckets, answering the query requires just a single read.
• If there are overflow buckets, we have to read all of them.

缺点：Hashing is not good for range search

Data Dictionary Storage

Data dictionary (also called system catalog): stores metadata: that is, data about data.such as Information about relations、Physical file organization information、Information about indices.

由系统定义，系统所有。

1. 系统维护
2. 被频繁访问的数据。
3. 有条件的查询。

Catalog structure:a set of relations, with existing system features used to ensure efficient access

Index

Basic Concepts

Indexing mechanisms

索引是定义在存储表（table）之上，有助于无需检查所有记录而快速定位所需记录的一种辅助存储结构。由一系列索引项组成。

Search Key: attribute to set of attributes used to look up records in a file. 搜索码类似于书中的词条。

index file:consists of records (called index entries索引项) of the form <search key, pointer>

指针指向搜索码所指向的记录在磁盘中的存储位置。类似于书中的页码。比较复杂，存储块的地址，和在存储块中的具体位置。

Two basic kinds of indices:

Ordered indices: search keys are stored in sorted order

Hash indices: search keys are distributed uniformly across “buckets” using a “hash function”.

索引不是越多越好。根据具体的应用。

索引文件，table叫做主文件。索引是辅助存储结构，不改变存储表的物理存储结构。

索引文件有两种组织方式，顺序和散列。而主文件在上一节中讲过，有堆文件，顺序文件，散列文件和聚簇文件等。

Index Evaluation Metrics

评判索引效果标准：

1. Access types supported efficiently
   • Equality selection queries,both ordered and hash indexes support this
   • Range selection queries,only ordered indexes support this
2. Access time
3. Insertion time:包括找到插入这个新数据项的正确位置所需的时间和更新索引结构所需要的时间
4. Deletion time:包括找到删除项所需的时间和更新索引结构所需要的时间
5. Space overhead

索引可以加快查询，但是也增加了额外的负担。除了对主文件进行维护，索引也要进行维护。

索引类别

索引按照是否跟文件中记录保持的顺序是否一致，可以分为主索引与辅助索引；

按照是否每一个搜索码都有一个索引记录，可以分为稠密索引与稀疏索引；

此外，还有多级索引

Ordered Indices

index entries are stored、sorted on the search key value.

Primary index 主索引(clustering index 聚集索引):in a sequentially ordered file, the index whose search key specifies the sequential order of the file

The search key of a primary index is usually but not necessarily the primary key.

example:

Secondary index 辅助索引(also called non-clustering index): an index whose search key specifies an order different from the sequential order of the file.

example：a secondary index with an index record for each search-key value; index record points to a bucket that contains pointers to all the actual records with that particular search-key value

Dense Index Files

Dense index 稠密索引:Index record appears for every search-key value in the file (in this example we have clustering index).

文件每个搜索码的值都有索引记录

Ordered Indices的主索引、辅助索引的例子都属于稠密索引。

稠密索引的特点:只要是索引上找不到的记录，那么在主文件中肯定没有这个记录。

Sparse Index Files

Sparse Index 稀疏索引: contains index records for only some search-key values.

Sparse Index:clustering indexes（文件必须排序）

locate a record with search-key value K we:

1. Find index record with largest search-key value <= K
2. Search file sequentially starting at the record to which the index record points

优点：

1. Less space and less maintenance overhead for insertions and deletions.
2. Good tradeoff: sparse index with an index entry for every block in file, corresponding to least search-key value in the block.

Multilevel Index

FAQ：为什么选择多级索引？

1. To reduce number of disk accesses to index records
   • inner index – the primary index file
   • outer index – a sparse index of primary index
2. If primary index does not fit in memory, access becomes expensive.
3. If even outer index is too large to fit in main memory, yet another level of index can be created, and so on.
4. Indices at all levels must be updated on insertion or deletion from the file.

example:

适合作索引的列

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

不适合作索引的列

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。
   • 因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。
   • 相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2. 对于那些只有很少数据值的列也不应该增加索引。
   • 因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3. 对于那些定义为text, image和bit数据类型的列不应该增加索引。
   • 因为，这些列的数据量要么相当大，要么取值很少,不利于使用索引。
4. 当修改性能远远大于检索性能时，不应该创建索引。
   • 因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。
   • 当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

索引的SQL定义

sql sever默认会将主键设置为聚集索引，不管是单列主键还是复合主键。复合主键则按照复合顺序逐层索引。

Create an index:

create index on ()

Drop an index:

drop index

Clustered index:

create clustered index b-index on branch(branch-name)

create unique index:indirectly specify and enforce the condition that the search key is a candidate key.

create unique index b-index on branch(branch-name)

当删除了table之后，在tabel上的索引都被删除。

用户建立了索引之后，索引的维护就有DBMS来进行，当插入一个记录后，DBMS自动更新索引。

在哪些属性上建立索引，由DBA来进行管理。经常进行检索的条件，进行连接的条件的属性等等。

在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。

Problem example

Problem 1

Assume a movie database with the following sizes of each attribute:

Film (Title : 40 bytes, Director Name: 20 bytes, Year: 4 bytes, Company: 20 bytes)

Actor (ID: 4 bytes, Name: 20 bytes, Date_of_Birth: 4 bytes)

There exist 30,000 films in the database and 100,000 actors. Each page is 512 bytes and each pointer is 6 bytes. The blocking factor of a file (bfr) is the number of records that fit in a page.

1. What is the blocking factor for Film relation bfrF and bfrA for Actor relation?
   • bfrF =512/84 = 6, bfrA = 512/28 =18
2. Assuming that the Film relation is sorted on the Title and there is no index what is the cost (in terms of page reads) for:
   • finding the film with title "Titanic":
     • The file is stored in 30,000/6=5,000 pages. Cost of binary search: log2(5000)=13
   • finding all the films directed by director “John Woo":
     • We need sequential scan since sorting is not based on director (5000 pages)
3. Assume that the Actor relation is sorted on the name and you want to create an ordered index on ID (each index entry has the form <ID, pointer>).
   • What is the blocking factor for the index (single-level)
     • 512/(4+6)=51
   • How many index entries you need:
     • 100,000 we need dense index because sorting is according to name (not ID).
   • How many pages are required for these entries:
     • 100,000/51=1961
   • What is the cost of retrieval based on a single id using this organization
     • log2(1961)+1=12
   • If you convert the above index in multiple-level index, how many levels you need (assuming full pages)?
     • At the next level we index 1961 pages – i.e., index contains 1961/51=39 pages. We need an additional top level with 1 page
     • 
   • What is the cost of answering the query “Find actor with id=100”?
     • 4 page accesses

Problem 2

Assume that a big company keeps a file with the records of its employees: Employee (eid: 6 bytes, ename: 10 bytes, did: 4 bytes) , where did is the id of the department where the employee works.

There exist 100,000 employee records and 1,000 departments (each department has 100 employees). A page is 1,000 bytes and a pointer is 4 bytes.

1. Assume that the employee file is sorted sequentially (no pointers between leaf pages) on did and there is no index. What is the cost (in terms of page reads) for retrieving the records of all employees working in a department with a given id (for instance, in department number 64):
   • record size = 20 bytes, 50 records per page, 2,000 pages.
   • Finding the first record requires log2(2000) + 1 more page to search the remaining records (each dept has 100 employees which are distributed in 2 pages).
   • The answer log2(2000) + 2 is also considered correct (e.g., the first page contains 25 records – the second one 50 – and the third one 25).
2. Assume that we add a single-level ordered index on eid on the above file (each entry has the form <eid, pointer> - the number of pointers is the same as the number of search keys).
   • How many index entries you need (explain briefly):
     • 100,000 entries – the index must be dense since the file is sorted on a different attribute
   • How many pages are required for these entries, and what is the cost of retrieving the record of an employee with a given eid:
     • index entry size = 10 bytes, 100 entries per page, 1,000 pages for the index, cost: log2(1000)+1
   • If you convert the above index in multiple-level index, how many levels you need (assuming full pages)?
     • 

\(B^+\)-Tree Index Files

Disadvantage of indexed-sequential files: performance degrades as file grows.Periodic(定期) reorganization of entire file is required.

Advantage of \(B^+\)-tree index files: automatically reorganizes itself with small, local, changes, in the face of insertions and deletions.Reorganization(改变) of entire file is not required to maintain performance.

\(B^+\)树也是顺序索引。多级索引。是一个平衡多多叉树。在每个叶子节点存放具体的搜索码。但是有一些差别，数据结构，指针不同。

对应于每个\(B^+\)树索引都有一个参数n，它决定了\(B^+\)树的所有存储块的布局，每个存储块存放n个查找键值和n+1个指针；对应3种节点，叶子节点，中间节点，根节点。

example:

B 代表balance
叶子节点含有搜索码的全集

特点：

1. All paths from root to leaf are of the same length(Balance Tree)
2. Each node has between [n/2] and n pointers.
3. Each leaf node stores between [(n–1)/2] and n–1 values.
4. n is called fanout (it corresponds to the maximum number of pointers/children).
5. The value [(n-1)/2] is called order (it corresponds to the minimum number of values).
6. Special cases:
   • If the root is not a leaf, it has at least 2 children.
   • If the root is a leaf (that is, there are no other nodes in the tree), it can have between 0 and (n–1) values.

Node Structure

Leaf Nodes in \(B^+\)-Trees

\(K_i\) are the search-key values

\(P_i\) are pointers to children (for non-leaf nodes) or pointers to records or buckets of records (for leaf nodes).

The search-keys in a node are ordered:

\[K_1 < K_2 < K_3 < . . . < K_{n–1} \]

For \(i = 1, 2, . . ., n–1,\) pointer \(P_i\) either points to a file record with search-key value \(K_i\), or to a bucket of pointers to file records, each record having search-key value \(K_i\).

If \(L_i\), \(L_j\) are leaf nodes and i < j, \(L_i\)’s search-key values are less than \(L_j\)’s search-key values

叶子节点包括搜索码的值

注意，只有叶子节点节点才保存有指向下一个节点的指针，而中间节点是不存在的。

Non-Leaf Nodes in \(B^+\)-Trees

For a non-leaf node with m pointers:

1. All the search-keys in the subtree to which \(P_1\) points are less than \(K_1\)
2. For 2 \(≤ i ≤ n–1\), all the search-keys in the subtree to which Pi points have values greater than or equal to \(K_{i–1}\) and less than \(K_i\)

The non-leaf levels of the \(B^+\)-tree form a hierarchy of sparse indices.

\(B^+\)-tree on candidate key

基于候选码，每一个候选码记录只能有一个，所以不用再列一个指针。 non-candidate key的记录可以有多条，所以叶子节点不能直接指向记录，而是叶子结点为每一真正的数据行存储一个“键-指针”对

clustering (primary) \(B^+\)-tree on candidate key：

non-clustering (secondary) \(B^+\)-tree on candidate key：

clustering \(B^+\)-tree on non-candidate key：

non-clustering \(B^+\)-tree on non-candidate key：

How to choose n

example:

Suppose our blocks are 4096 bytes. Also let keys be integers of 4 bytes and let pointers be 8 bytes. If there is no header information kept on the blocks, n?

we want to find the largest integer value of n such that 4n + 8(n + 1) < 4096. That value is n = 340.

A node is generally the same size as a disk block, typically 4 kilobytes, and n is typically around 100 (40 bytes per index entry).level?

Generally, the level of \(B^+\) tree is 4,5.

\(B^+\)-Tree Operation

查找

1. 从root开始
   • 如果key=find，顺着右边的儿子继续查找。
   • 看find的树比root大还是小，大走右边，小走左边
2. 知道leaf节点为止。
3. 已经是叶子节点，查找是否有find的值，有的话，找到find指向的数据记录。

插入

1. Find correct leaf L.
2. Put data entry onto L.
   • If L has enough space, done!
   • Else, must split L (into L and a new node L2)
     • Redistribute entries evenly, copy up middle key.
     • Insert index entry pointing to L2 into parent of L.
3. This can happen recursively
   • To split index node, redistribute entries evenly, but push up middle key. (Contrast with leaf splits.)
4. Splits “grow” tree; root split increases height.
   • Tree growth: gets wider or one level taller at top.

Note:

• Observe how minimum occupancy is guaranteed in both leaf and index pg splits.
• Note difference between copy-up and push-up; be sure you understand the reasons for this.

Copy up:因为要有分界点的，所以要把中间的值送上去。

Deleting

1. Start at root, find leaf L where entry belongs
2. Remove the entry.
   • If L is at least half-full, done!
   • If L has only d-1 entries
     • Try to re-distribute, borrowing from sibling (adjacent node with same parent as L).
     • If re-distribution fails, merge L and sibling.
3. If merge occurred, must delete entry (pointing to L or sibling) from parent of L.
4. Merge could propagate to root, decreasing height

Note:

• 一定要保证叶子节点的桶是半满到满之间；
• 合并的时候要把父节点拉下来
• 删除的时候，删除叶子节点，如果不违法规则，则不需要删除非叶子节点。因为仅仅起到指引的作用。

Example of Non-leaf Re-distribution:

1. 把20，30合并。
2. 将20右儿子的指针作为20，30合并后的最左边的指针。
3. 原先20，30用22区别，但是现在20，30合并后，22不能作为20，17的区分了，将20和22的位置互换。

Hash Indices

A hash index organizes the search keys, with their associated record pointers, into a hash file structure.

使用hash结构来查找的话，只需要1个I/o的索引操作，insert和delete的也只是2个i/o的操作。但是如果文件增加的话，一个buck有多个overflow的page，就需要做线性搜索。

Deficiencies of Static Hashing

In static hashing, function h maps search-key values to a fixed set of B of bucket addresses. Databases grow or shrink with time.

1. If initial number of buckets is too small, and file grows, performance will degrade due to too much overflows.
2. If space is allocated for anticipated growth, a significant amount of space will be wasted initially (and buckets will be underfull).
3. If database shrinks, again space will be wasted.

Better solution: allow the number of buckets to be modified dynamically.

Extendible Hashing

Use directory of pointers to buckets

Directory much smaller than file, so doubling it is much cheaper. Only one page of data entries is split, no overflow page.

Insert: If bucket is full, split it ;If necessary, double the directory.

Dynamic Hashing

优点：

1. Good for database that grows and shrinks in size
2. Allows the hash function to be modified dynamically

插入overflow的页面后多出来的桶指针仍然指向以前没满的桶。

Comments on Extendible Hashing

If directory fits in memory, equality search answered with one disk access; else two.

• chances are high that directory will fit in memory.
• Directory grows in spurts, and, if the distribution of hash values is skewed, directory can grow large.
• Multiple entries with same hash value cause problems

Delete: If removal of data entry makes bucket empty, can be merged with `split image’. If each directory element points to same bucket as its split image, can halve directory.

Comparison of Ordered Indexing and Hashing

1. Expected type of queries:
   • Hashing is generally better at retrieving records having a specified value of the key
   • If range queries are common, ordered indices are to be preferred
2. In practice:
   • PostgreSQL supports hash indices, but discourages use due to poor performance
   • Oracle supports static hash organization, but not hash indices
   • SQLServer supports only \(B^+\)-trees

Grid Files

Structure used to speed the processing of general multiple search-key queries involving one or more comparison operators

has a single grid array and one linear scale for each search-key attribute. The grid array has number of dimensions equal to number of search-key attributes

Multiple cells of grid array can point to same bucket

locate the row and column of its cell using the linear scales and follow pointer

Insert

Add overflow blocks to the buckets, as needed

Reorganize the structure by adding or moving the grid lines.