数据的物理存储
数据的物理存储
复习定位
数据库的数据和索引最终还是存放在磁盘上——磁盘的物理特性(机械硬盘的寻道时间约10ms——SSD的闪存页读写约0.1ms)直接影响数据库的性能。数据库通过缓冲池(Buffer Pool)在内存中缓存热点页——减少磁盘I/O。数据的组织方式(行存储vs列存储)决定了OLTP和OLAP的不同适用性。
磁盘与内存的I/O差异
磁盘最小读写单位是扇区(512B传统硬盘, 4KB高级格式化)。操作系统文件系统通常将多个扇区组成块(例如4KB)。数据库管理系统以连续的块——称为数据库页(通常4KB-16KB)——为基本单位进行读写。即使只需要32字节的一个整数——数据库必须从磁盘读取一整页(假设8KB)到内存中缓冲池——IO成本固定为一个读取8KB的页的寻道+旋转延迟+传输时间(机械硬盘约10ms)或寻址一个闪存页(0.1ms)。
缓冲池(Buffer Pool)在内存中维护最近读入的数据页——下次再访问同一页时直接命中缓冲池而不需要磁盘I/O。缓冲池的替换策略一般使用改进的LRU(中间命中LRU变种)——避免一次全表扫描(访问大量页)将热点数据全挤出去。
B+树索引的存储结构
B+树是数据库中最广泛使用的索引结构——索引在B+树中按照键值有序排列。B+树的内部结点仅存储键(指向下一层的指针)——不存储实际数据记录。所有实际数据(或主键指针)存储在叶子结点中。
查找一个特定键的过程——从根结点出发逐层查找——在每个内部结点二分搜索找到应该进入下一层的子结点指针——直到叶子结点。树高极小(3~4层)使查找任何记录最多3-4次I/O(假设根结点缓存在内存中则只需要2-3次I/O读取新的块)。
叶子结点之间通过双向链表连接——范围查询(${column} BETWEEN a AND b)在B+树中尤为高效——先找到起始键的叶子——然后沿叶子链表顺序遍历——不需要在树中来回回溯。
行存储与列存储
行存储(N-ary Storage, NSM)——同一行的所有列在页中连续存储。适合OLTP(点查询、小范围数据行更新)——一次读取一页包含整行的所有字段——插入一行也是写同一页的连续区域。数据库如MySQL的InnoDB、PostgreSQL使用行存储。
列存储(Decomposition Storage, DSM)——同一列的所有行连续存储。适合OLAP(按列聚合、扫描大范围行的部分列)——查询不会触及所有列——只需要读取涉及的列的压缩小片段。如Parquet、ORC格式以及ClickHouse、DuckDB的分析引擎。
| 特性 | 行存储 | 列存储 |
|---|---|---|
| 适合读多行列 | 部分行的所有列 | 所有行的部分列 |
| 压缩率 | 低 | 高(同列数据类型一致) |
| 插入/更新 | 快(整行一起) | 慢(需拆列分别写入) |
| 典型场景 | OLTP | OLAP/数据仓库 |
复习检查
磁盘的扇区大小(每个扇区512B→4KB)和数据库页的大小(4KB→16KB)——数据库为什么使用比扇区或文件系统块更大的页？
缓冲池的替换——为什么LRU不适合全表扫描场景——"中间命中"LRU(Midpoint LRU)是如何保护热点冷数据不在大扫描中被意外换出的？
B+树叶子结点间用双向链表连接——对于
SELECT * FROM t WHERE id BETWEEN 100 AND 200——具体从这个链表中怎样找到起始id=100的位置然后高效地扫到200为止？行存储在OLTP环境中如何减少写入时的随机I/O——插入一条新数据——由于聚簇索引乱序的id值可能导致行在页中的存放点不是当前页的末尾——这会触发页分裂吗——为什么会消耗额外的I/O(写新页、更新索引链)？
列存储为什么压缩率高——存储学生表中的性别和年龄：在行存储中生成了成对的行格式存储——在列存储中性别列只有"男男女男女女女"——可以用什么算法压缩这列？
数据库页的内部结构
数据库页(Page)是数据库和磁盘之间交换数据的基本单位。通常InnoDB的页大小为16KB。每个页除了存储行数据外——还包含页头(记录页的元信息如页号、所属表空间、上一页和下一页的指针、页内自由空间位置等)——行数据——以及页尾的校验和。页内的行按顺序排列——当插入新行时——优先填充页内的自由空间——当自由空间不够时——将页标记为填满并分配新页。
行在页内可以以堆(heap)或有序(Sorted)方式组织。在堆组织中——新行总是从页的某一特定方向填充——行没有特定顺序——插入极快但查找需要扫描页内所有行。在有主键索引的组织下(InnoDB的聚簇索引)——行实际上按照主键有序排列在叶子页中——查找通过B+树的索引快速定位到包含目标行的页——然后在页内通过二分查找快速定位到具体行。
B+树在数据文件上的实现
B+树的根节点经常被缓存在数据库缓冲池中——因此大多数查询从根节点到子节点的过程不需要I/O(根节点已在内存中)——真正需要I/O的只是从内层节点到叶子节点的那几层。B+树的扇出通常在几百到一千左右(每个节点可以容纳几百个键值+指针对)——因此2-3次I/O已经足够覆盖数百万到数十亿条记录的范围。
节点在B+树中的分裂(Split)与合并(Merge)操作——在写操作时发生——插入导致某叶子节点满时——节点被分裂为两个半满节点——中间键提升到父节点——父节点可能继续分裂直到根节点——根节点分裂时树的高度增加1。删除节点导致某叶子节点低于最小值时——尝试从兄弟节点借一个键(如果兄弟有富余)或与兄弟节点合并——父节点的键数随之减少——可能递归上溯触发父节点的合并。
缓冲池的替换策略对性能的量化影响
缓冲池命中率从95%提升到99%的变化:
假设数据库每秒需要访问10000个页——磁盘I/O每次10ms
命中率95%时: 10000×5%×10ms = 5000ms = 5秒磁盘等待时间
命中率99%时: 10000×1%×10ms = 1000ms = 1秒磁盘等待时间
命中率的5%的差距导致了5倍的磁盘等待时间差异。缓冲池替换策略直接影响命中率。传统的LRU策略扫到一个大表的所有页时——会使缓冲池中的热点页全部被替换出去(因为全表扫描顺序访问了大量页——且这些页短期内不会被再次访问了)。Midpoint LRU(中间插入LRU)将LRU链分为两段——新读入的页先插入到中间点——只有被再次访问后才移动到链首——这样一次全表扫描不会将原有的热点数据冲走。大多数数据库(InnoDB/PostgreSQL/Oracle)都实现了类似Midpoint LRU的缓冲池管理机制。
行和列的物理存储效率
行存储在存放同一条记录的多列时——每一列在磁盘上连续的物理分布让单次I/O读取(几近整行)能够以一次小规模的I/O次数获取整行数据——这对OLTP类型应用很好。而列存储将大表各列独立分开存储后——数据压缩率可以得到极大提升——因为列内部的同类型值相似度高——使得游程编码和列内字典编码或增量编码有效压缩数据量。压缩后的数据减少了I/O量和存储成本——在大规模数据分析(查询大的范围子集、但列数很少)时有明显优势。
复习检查（续）
数据库页大小为什么通常选择16KB而不是512B——更大的页可以减少单次I/O的索引树深度(更大的扇出)——读一条记录时预读周围数据的概率更大(空间局部性)——相对于磁盘I/O的分钟级寻道时间多加载几KB并不会显著影响本次I/O的延迟。
Midpoint LRU如何防止全表扫描冲走缓冲池中的热点数据——新读入的页不放在LRU的首部——而是放在距离尾部一定比例的中间点——只在被重新访问后才移动到首部——全表扫描顺序访问的数据如果只被访问一次——就会从中间点一路被推到尾部被淘汰——不会影响原有的热点页。
B+树叶子节点碎裂的影响——如果一个表的插入顺序和主键顺序不一致(如UUID主键)——叶子节点会频繁分裂——产生大量碎片——导致B+树的高度增加——范围查询的效率下降——Page的填充因子也会降低——数据库的"物理读取"数量会增加。
数据库在磁盘上的文件存储形式——一个InnoDB表空间可以是一个文件(独立表空间.ibd)或多个表共享(系统表空间ibdata)——表空间由一组段(Segment)组成——一个段对应一个索引(B+树)——段由一组区(Extent——1MB共连续64页)组成——区由页组成。
列存储压缩率的实际数据——在一个包含100万行的订单表中——"订单状态"列只有"已支付/待支付/已取消/已退款"4个可能值——字典编码可以将该列的存储空间从1M*10字节=10MB压缩到原始数据不到300KB的映射(ASCII码加一字节状态码)。
数据库文件的物理组织结构详解
数据库数据最终以一个或多个文件的形式存储在磁盘上。InnoDB的数据文件(ibd)结构：
表空间文件(.ibd)
├── 段1: 聚簇索引(主键索引整理全部数据)
│ ├── 区0 (64数据页 = 1MB)
│ │ ├── 页0 (文件头/系统页)
│ │ ├── 页1-N (数据页——实际行数据)
│ ├── 区1 ...
├── 段2: 二级索引a
│ ├── 区0
│ ├── 区1 ...一个区(extent)是1MB的连续空间(64个16KB页)——在表空间被填满时每次分配一个区——而不是一页一页分配——这样可以保证新分配的空间在磁盘上是连续的——对顺序扫描有利——也简化了空间管理。区的管理由表空间的FSP(File Space Management)页维护。
读取数据时的I/O行为
一个SELECT * FROM users WHERE id=42 的物理I/O流程:
1. InnoDB检查缓冲池——是否包含users的主键索引根节点——如果没有——从磁盘读入根节点页(区段页边界可能引起一次连续回读相关性)
根节点在内存中检查——下一次进入第N个内层节点——该节点页如果不在缓冲池中——读入
3. 根据内层节点索引到的叶子节点页号——读入叶子节点页(缓冲池已在末命中)——在叶子节点页的页内目录二分查找——找到id=42的行——获取行数据
4. 如果SELECT的所有列都在索引中——结束(覆盖索引)
5. 如果需要其他列——按得到的主键回表——去聚簇索引查找完整的行数据一次简单的查询可能涉及1-3次磁盘I/O(如果缓冲池冷启动)。这就是B+树3-4层高度的实际代价——每个查询在最坏情况下(缓冲池空)需要3-4次磁盘I/O。如果启用了预读(Read-Ahead)——线性邻近的页会读入后被缓存——连续的多行查询性能会立即从3-4次降为1次I/O命中缓冲池。
复习检查（续二）
InnoDB表空间中"区"(extent)的大小和作用——每个区64个连续页(1MB InnoDB默认)——连续页可以保证顺序扫描的物理连续性和缓冲池的预读效率——一个区是InnoDB空间分配的基本单位——而不是页。
缓冲池预读——InnoDB在顺序扫描一个段时——预测下一个将要访问的区——提前将其读入缓冲池——将顺序I/O模式变为后台预读——减少查询的等待时间。
表空间(TableSpace)在MySQL中分为独立表空间(.ibd)和系统表空间(ibdata)的区别——独立表空间每个表单独文件——便于管理和迁移(直接拷贝.ibd文件即可备份单表)——但空间利用率不如共享表空间(每张表至少占一个区1MB——小表可能有浪费)。
B+树在半满页时的填充因子——理想的主键是自增整数——新行按顺序追加到叶子节点的最后一页——填充因子接近满(约15/16)。如果使用UUID等随机主键——插入时经常触发页面分裂——叶子节点只有约5/8的填充率——相同的行数需要更多存储页——浪费更多的缓冲池和磁盘空间。
聚集索引(主键)的行在磁盘上的分布与插入顺序的关系——按自增顺序插入——每个新行追加到B+树最后一个叶子页的末尾——页满后开辟新页——几乎所有页都保持满填充。按随机顺序插入——行插入到随机位置——多次触发页分裂——空间利用率低——页碎片化——全表扫描需要读取更多页——性能退化。
存储数据在物理介质上的进一步架构
数据库将数据写入磁盘时的I/O路径：
应用层请求写入一行数据
→ InnoDB将写入记录到重做日志(redo log,顺序追加——磁盘顺序写约200MB/s)
→ 将数据页标记为脏页(在缓冲池中修改但不立即写盘)
→ 后台线程将脏页刷入磁盘(Data File)——在系统空闲/检查点时执行这种延迟写的策略是数据库高性能的核心——一次写入仅需一次顺序I/O追加日志——而数据页的随机写入被合并后批量处理——大大减少了随机I/O的次数。
缓冲池竞争对应用性能的量化影响
在InnoDB中——缓冲池是一个需要内部锁保护的共享资源。在高并发(多线程同时访问不同数据页)情况下——InnoDB将缓冲池分成多个实例(InnoDB Buffer Pool Instance)——每个实例拥有独立的锁——将并发争用分散到多个锁上——提升高并发场景下的扩展性。建议将缓冲池实例数量设置为CPU核心数——使每个CPU负责一个实例的元数据操作。
复习检查（续三）
InnoDB将缓冲池分为多个实例的优势——降低多个线程同时访问缓冲池数据页时的锁竞争——每个实例独立管理自己的页范围——并发性能随实例数增加而提升(建议实例数<=CPU核心数)。
数据库在写数据时先将日志(redo log)写入磁盘的原因——日志是顺序写入的——比数据文件的随机写入速度快100倍——所有写入操作的"成本"瞬间降低——用户等待时间缩短——数据文件在后台慢慢写入不会产生多余的请求等待。
B+树在插入大量自增主键行时的页面行为——新数据总是追加到当前最靠后的叶子页——该页持续被填充直到满——然后分配一个新页——几乎无页分裂开销——写性能很好。
数据库表使用UUID主键对比自增整数主键的写入性能差异——自增主键插入几乎不触发页分裂——UUID主键随机插入频繁触发页面分裂——插入速度可能自增主键的数倍以上——且空间利用率和碎片程度都不及自增整数主键。
预读(Read-Ahead)如何和缓冲区配合减少查询延迟——当数据库检测到顺序访问模式时——提前异步地将接下来需要的页读入缓冲池——使真正的查询触发时(顺放之后的访问循环内)目标页已经在缓冲池中——不需要等待磁盘I/O。
物理存储设计对查询性能影响的总结
一个查询在数据库中的I/O时间主要受数据页大小、缓冲池命中率、B+树高度、页面的填充率和磁盘访问延迟共同决定。理解数据的物理存储特性——有助于为数据库选择正确的文件系统和I/O配置：
磁盘方面:
- HDD: 延迟不是线性的——顺序读写≈200MB/s 但随机≈1MB/s(?由于寻道)→ 数据库不应在HDD上运行
- SSD: 延迟约0.1-0.2ms——远低于HDD的5-15ms——是数据库的首选存储介质
- NVMe SSD: 延迟更低(≈0.02ms)——IOPS高达数十万——适合高并发数据库
数据库配置方面:
- 缓冲池大小: 建议设为系统内存的70-80%——减少物理I/O
- 页大小: 按I/O模式选——随机小查询多用较小(8K)页——顺序大查询多用较大(32K页)
- 预读设置: 顺序扫描多时增大预读值——随机I/O多时减小