[Database] Physical Storage Media and File Organisation

Physical Storage Media

Access time

$$ Access\ time = Seek\ time + Rotational\ latency + (Transfer\ time) $$

img1

Disk Block

Features

  • A contiguous sequence of sectors from a single track
  • Data is transferred between disk and main memory in blocks
  • The operating system stipulates that a disk block can only contain one file, so the space occupied by the file can only be an integer multiple of the disk block

Block Size

Compare
  • Smaller blocks: more transfers from disk
  • Larger blocks: more space wasted due to partially filled blocks

Therefore, Lareger block sizes may mean space wasted, smaller may lead to more IO times.

Typical block sizes today range from 4 to 16 kilobytes

Access Optimisation

The elevator algorithm:
img2

File Organisation

Definition

Optimise block access time by organising the blocks to correspond to how data will be accessed

  • Fixed-length records
  • Variable-length records

Fixed-length records

  • Allocate only as many records to a block as would fit entirely in the block
  • Undesirable to delete records (requires additional block accesses)

Free List

Reference: https://songlee24.github.io/2...

Delete

When delete a record, the record's pointer is inserted in the free list. It will not be deleted immediately.

Insert

We change the header pointer to point to the next available record in the free list.
If no space is available, we add the new record to the end of the file.

Variable-length Records

Variable-length attributes are represented in the initial part of the record by a pair (offset, length)

slotted-page structure

  • The actual records are allocated contiguously in the block, starting from the end of the block.
  • The free space in the block is contiguous, between the final entry in the header array, and the first record

img3

The header stores the information of each records.

Insert

If a record is inserted, space is allocated for it at the end of free space.

Delete

Free the space that it occupies and the records in the block before the deleted record are moved.

Organisation

  • Heap
  • Sequential
  • Hashing
  • Multitable clustering file organisation

img4

Sequential File Organisation

It is applied in many databases, such as Rocksdb

Insert

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
Delete

use pointer chains

Multitable Clustering File Organisation

Example

img5

Features
  • good for queries involving join
  • bad for queries involving only one table
  • results in variable size records
  • can add pointer chains to link records of a particular relation

Move on.

4 声望
3 粉丝
0 条评论
推荐阅读
[RocksDB剖析系列] Iterator
参考:[链接][链接][链接][链接][链接][链接]IntroductionRocksDB的Iterator在经过高度封装后,可以像C++ stl库为每一个容器构造的迭代器的iterator一样被使用,它可以定位到某个key,并可以从这个key开始进行sca...

1iin1阅读 1.5k

花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据库管理系统)应用软件之一。

民工哥11阅读 1k

封面图
一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山8阅读 2.2k评论 2

程序员英语学习指南
动机为什么程序员要学习英语?工作:我们每天接触的代码都是英文的、包括很多技术文档也是英文的学习:最新最前沿的技术最开始都是只有English版本就业:学好英语让你的就业范围扩大到全球,而不只限于国内目标读...

九旬6阅读 618

又一款内存数据库横空出世,比 Redis 更强,性能直接飙升一倍!杀疯了
KeyDB是Redis的高性能分支,专注于多线程,内存效率和高吞吐量。除了多线程之外,KeyDB还具有仅在Redis Enterprise中可用的功能,例如Active Replication,FLASH存储支持以及一些根本不可用的功能,例如直接备份...

民工哥4阅读 674评论 1

封面图
Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 916

封面图
面试官:请说一下如何优化结构体的性能?
使用内存对齐机制优化结构体性能,妙啊!前言之前分享过2篇结构体文章:10秒改struct性能直接提升15%,产品姐姐都夸我好棒 和 Go语言空结构体这3种妙用,你知道吗? 得到了大家的好评。这篇继续分享进阶内容:结...

王中阳Go4阅读 1.6k评论 2

封面图

Move on.

4 声望
3 粉丝
宣传栏