[Database] Physical Storage Media and File Organisation
Physical Storage Media
Access time
$$ Access\ time = Seek\ time + Rotational\ latency + (Transfer\ time) $$
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:
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
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
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
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
[RocksDB剖析系列] Iterator
1iin赞 1阅读 1.5k
花了几个月时间把 MySQL 重新巩固了一遍,梳理了一篇几万字 “超硬核” 的保姆式学习教程!(持续更新中~)
民工哥赞 11阅读 1k
一次偶然机会发现的MySQL“负优化”
骑牛上青山赞 8阅读 2.2k评论 2
程序员英语学习指南
九旬赞 6阅读 618
又一款内存数据库横空出世,比 Redis 更强,性能直接飙升一倍!杀疯了
民工哥赞 4阅读 674评论 1
Mysql索引覆盖
京东云开发者赞 2阅读 916
面试官:请说一下如何优化结构体的性能?
王中阳Go赞 4阅读 1.6k评论 2
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用
。你还可以使用@
来通知其他用户。