3.2 物理设计与存储优化

3.2 物理设计与存储优化

本节摘要：逻辑设计管"存什么"，物理设计管"怎么存"。本节讲清楚存储引擎选择、表空间/页/块设计、数据压缩、字符集，从物理层提性能。

存储引擎选择

不同存储引擎适合不同场景：

MySQL InnoDB（默认）：

• 支持事务（ACID）、行锁、外键。
• B+树聚簇索引（数据存主键索引叶子）。
• 适合 OLTP 高并发事务。

MySQL MyISAM（老引擎）：

• 无事务、表锁、全文索引。
• 简单查询快，但并发差。
• MySQL 8.0 弃用，不推荐。

PostgreSQL：

• 单一存储引擎，但支持表 AM（Access Method）扩展。
• MVCC 多版本并发控制。
• 适合复杂查询、OLTP/OLAP 混合。

列式存储（OLAP）：

• ClickHouse、Greenplum、Doris、Columnstore。
• 按列存储，分析查询快（只读需要列）。
• 压缩高（同列数据相似）。
• 不适合 OLTP（行更新慢）。

选择：OLTP 用行存（InnoDB/PostgreSQL），OLAP 用列存（ClickHouse/Doris），混合用 HTAP（TiDB/CockroachDB）。

表空间与文件组织

表空间（Tablespace）：逻辑存储单元，管理数据文件。

1. 系统表空间 vs 独立表空间

• MySQL InnoDB：innodb_file_per_table=ON（默认），每表独立 .ibd 文件。
• 独立表空间好处：单表可单独备份/迁移/优化，表碎片不影响其他。
• 系统表空间：所有表共享，扩容简单但单表问题影响全局。

2. 表空间分离

• 数据、索引、日志分不同磁盘/表空间——并行 IO。
• 热（频繁访问）冷（归档）分磁盘——热盘 SSD，冷盘 HDD。

3. 临时表空间

• 临时表/排序用独立表空间——避免占用系统表空间。
• MySQL innodb_temp_data_file_path 配置。

页/块大小

数据库以页（page/block）为单位读写，页大小影响性能：

1. 页大小权衡

• 大页（如 32KB/64KB）：每页存更多行，顺序扫描快，缓存命中率高。但随机点查询浪费（读一页只为一行）。
• 小页（如 4KB/8KB）：随机点查询高效（读少数据）。但顺序扫描慢、缓存命中率低。

2. 默认与配置

• MySQL InnoDB：默认 16KB，可配置 4KB-64KB。
• PostgreSQL：默认 8KB，编译时定。
• Oracle：默认 8KB，可配置。

3. 选择

• OLTP 随机查询多——小页（8KB/16KB）。
• OLAP 顺序扫描多——大页（32KB/64KB）。
• 大页还减少页表项，降低 TLB miss（大页支持）。

数据压缩

压缩省空间、提缓存命中率（同样内存存更多数据），但增加 CPU：

1. InnoDB 压缩

• ROW_FORMAT=COMPRESSED，页级压缩（zlib）。
• 适合文本/重复数据多。
• CPU 增加，IO 减少——IO 瓶颈系统受益。

2. 列存压缩

• 列存压缩率高（同列相似）——RLE、字典、Delta 编码。
• ClickHouse 压缩比可达 10:1。
• 适合 OLAP 大数据。

3. 表级压缩

• Oracle：COMPRESS FOR OLTP/QUERY。
• 压缩历史冷数据——省空间，查询不频繁。

权衡：IO 瓶颈用压缩（减 IO 换 CPU），CPU 瓶颈不用。压缩数据解压增加延迟，延迟敏感慎用。

字符集与排序规则

字符集：影响存储空间和比较性能。

1. utf8 vs utf8mb4

• MySQL utf8：最多 3 字节，不支持 emoji（4 字节）。
• utf8mb4：最多 4 字节，支持完整 Unicode（推荐）。
• utf8mb4 占空间多，但兼容性好。

2. 排序规则（Collation）

• 决定字符串比较和排序规则。
• utf8mb4_general_ci：大小写不敏感（ci），通用。
• utf8mb4_bin：二进制比较，大小写敏感，快。
• 选择：需要大小写不敏感用 _ci，需要精确匹配用 _bin（快）。

3. 字符集影响索引

• 同列字符集和排序规则要一致，否则 JOIN 失效索引。
• 表迁移改字符集要重建索引。

行格式

InnoDB 行格式：

• REDUNDANT：老格式，空间大。
• COMPACT：默认（5.7+），紧凑。
• DYNAMIC（8.0 默认）：变长列存溢出页，行只存指针——大 TEXT/BLOB 不占主页。
• COMPRESSED：压缩。

选择：默认 DYNAMIC 即可，大字段表用 DYNAMIC 避免行膨胀。压缩看场景。

日志文件优化

1. Redo Log

• InnoDB crash recovery 日志，顺序写。
• 大 redo log——减少 checkpoint 压力，但 crash recovery 慢。
• 放独立快速磁盘（SSD）。

2. Binlog

• MySQL 复制和 PITR 日志。
• 大 binlog——减少 fsync，但崩溃丢日志多。
• sync_binlog 配置——0（性能优先 OS 决定）/1（安全每事务 fsync）/N（折中）。

3. Undo Log

• 事务回滚和 MVCC 旧版本。
• 大事务产生大量 undo，放独立表空间。

⚠️ 常见误读：以为"压缩总是好"。压缩省空间提缓存，但增 CPU 和延迟。IO 瓶颈受益，CPU 瓶颈或延迟敏感慎用。

💡 关键直觉：物理设计——存储引擎（OLTP 行存 InnoDB/PG，OLAP 列存 ClickHouse/Doris 压缩高，HTAP TiDB）、表空间（独立 file_per_table 单表可优化、分离磁盘并行 IO 热冷分盘、临时表空间）、页大小（OLTP 小页 8/16KB 随机快，OLAP 大页 32/64KB 顺序快+大页降 TLB miss）、压缩（InnoDB COMPRESSED zlib/列存 RLE 字典 Delta 10:1/冷数据，IO 瓶颈用换 CPU，CPU 瓶颈不用）、字符集（utf8mb4 完整 Unicode/排序规则 _ci 不敏感 _bin 快）、行格式（DYNAMIC 大字段溢出页避免行膨胀）、日志优化（Redo 大减 checkpoint 放 SSD/Binlog sync_binlog 折中/Undo 独立表空间）。

本节要点回顾

• 存储引擎：InnoDB（ACID/行锁/聚簇索引 OLTP）、MyISAM（弃用）、PostgreSQL（单一+MVCC 复杂查询）、列存（ClickHouse/Doris 列存分析快压缩高，行更新慢不适合 OLTP）、HTAP（TiDB/CockroachDB 混合）。
• 表空间：独立（file_per_table 单表备份/迁移/优化）、分离磁盘（数据/索引/日志分盘并行 IO，热 SSD 冷 HDD）、临时表空间（避免占系统表空间）。
• 页大小：大页（32/64KB 顺序快缓存高，随机浪费）、小页（4/8KB 随机高效，顺序慢缓存低），OLTP 小页 OLAP 大页，大页降 TLB miss。
• 压缩：InnoDB COMPRESSED（zlib 页级，IO 瓶颈换 CPU）、列存（RLE/字典/Delta 10:1）、表级（Oracle 压缩冷数据），权衡 IO 瓶颈用 CPU 瓶颈不用，延迟敏感慎用。
• 字符集：utf8mb4（4 字节完整 Unicode 推荐）vs utf8（3 字节无 emoji）、排序规则（_ci 不敏感通用/_bin 二进制快精确）、字符集一致否则 JOIN 失效索引。
• 行格式：DYNAMIC（8.0 默认，变长列溢出页行存指针，大 TEXT/BLOB 不占主页）。
• 日志优化：Redo（大减 checkpoint 压力放 SSD，crash recovery 慢）、Binlog（sync_binlog 0 性能/1 安全/N 折中）、Undo（大事务独立表空间）。

页填充与写入放大的量化理解

物理层的核心概念"页"值得量化展开。数据库按页（通常八或十六KB）读写磁盘，一行数据在页内的填充度决定了三个关键行为：空间利用率（半满的页浪费一半 IO 带宽在读空气上）、写入放大（随机插入导致页分裂，一次逻辑写变成"分裂两页加标记旧页"三次物理写）、碎片整理收益（重建索引恢复填充度后，扫描同样数据的 IO 次数下降）。量化感觉：一亿行的表，页填充从九成跌到六成，全表扫描的物理读次数增加一半，缓冲池里能装下的有效数据减少三分之一——同一个查询变慢的物理根源就在这。应对策略分主动与被动：设计期用自增主键让插入天然有序（减少分裂）、批量导入前后控制填充参数；运维期定期检测碎片率、在低峰窗口重建。理解页的行为后你会重新看待很多"玄学"现象——为什么重建索引后变快了、为什么随机 UUID 主键的表越用越慢、为什么监控里"写 IO 远大于业务写入量"——它们都是同一门"页物理学"的不同切面。

压缩：被低估的 IO 杠杆

物理层还有一个常被低估的杠杆：数据压缩。它的收益逻辑是三重的——空间省（典型列压缩比三到五倍，存储成本直降）、IO 省（同样一页装下更多行，扫描与缓冲命中率同步受益——这是最主要的性能收益）、缓冲池等效放大（内存里能驻留的有效数据多了数倍，热工作集的定义被改写）。代价同样明确——CPU 升（压缩解压耗计算，CPU 已经是瓶颈的系统慎用）、更新成本（高频更新的页反复重压缩，写放大叠加）。适用判断的三问：数据是不是读多写少（报表与历史库：用，没有理由不用）、CPU 是否有余量（CPU 空闲而 IO 紧张：用，这是最优的交易）、压缩算法是否可选（按列选算法：字符列与数字列各取所需）。一个反直觉的实战现象值得记住：某些全表扫描型查询开启压缩后反而更快——省下的 IO 时间超过了压缩解压的 CPU 时间，这笔"用计算换带宽"的交易在存储受限的环境里几乎稳赚。压缩是物理层给预算有限的团队准备的免费午餐，漏掉它常常是因为不知道菜单上有这道菜。

行宽的体检与瘦身

物理层给一个实操性极强的动作：行宽体检。体检方法：对核心表统计平均行宽（理论宽度与实际占用），对照缓冲池大小算"一页能装几行、热区数据要几页、缓冲池能驻留几成"——这组数字直接决定了缓存效率的天花板。瘦身的四个动作：动作一，大字段外迁（详情文本、长备注移到扩展表，主表瘦身）；动作二，历史字段归档化（只在报表用的列移到归档表）；动作三，字段顺序与空值优化（可空列与变长列的物理布局影响实际占用，部分引擎对定长与变长的处理差异值得利用）；动作四，压缩启用（第 6 小节的杠杆）。瘦身的收益模型：行宽减三成，同样的缓冲池多装四成的行、同样的 IO 带宽多搬四成的数据——等效于免费扩了内存与存储带宽。行宽是设计期决策、运维期发现的最典型项：体检动作的意义就是把发现提前到还来得及瘦的时候。

物理层的两个经验数值

物理章收官给两个可以直接引用的经验数值（附适用条件）。数值一，页的半满阈值：核心表的页平均填充度低于六成时，扫描类查询的 IO 浪费开始可感（四成的空间在搬空气），碎片治理的优先级上调——低于五成就是明确的行动信号。数值二，行宽的舒适区：OLTP 高频表的平均行宽控制在几百字节内（考虑索引后的整行访问成本），超过一千字节就该评估大字段外迁——行宽翻倍近似等于热数据容量翻倍的内存压力。两个数值的适用边界：它们是"开始思考的阈值"而非"必须行动的红线"——具体行动要结合扫描频率与内存余量综合判断；但有了阈值，巡检时的"看一眼就知道有没有事"就成了可能。经验数值是老工程师的压缩饼干——不精致但顶饿，用的时候配上一杯"自己环境验证"的水。

补一句与大表治理的衔接：物理层的瘦身（行宽、压缩）与第 5 章的归档是互补的两条腿——归档解决"不需要的数据还占着地方"，瘦身解决"需要的数据占得太多"；治理大表的完整方案通常两条腿都要走，先后顺序按痛点定（扫描慢先瘦身、存储贵先归档）。两章配合读，大表治理的兵器谱就齐了。

再补一个存储引擎选择的提醒：同库多引擎的场景（部分数据库支持），物理层的优化要按引擎分区施策——行存引擎的页优化逻辑（本节内容）不适用于列存区域（列存按列压缩、按块读取，优化重心在排序键与压缩算法）；混合负载系统里给"事务热区用行存、分析冷区用列存"的分区方案，正在成为大数据量场景的主流选择——这其实是第 5 章冷热分离思想在物理层的提前落地，两章合读，效果最好。

收尾再点一个与监控的衔接：物理层的健康要进仪表盘的三个数字——页平均填充率、行宽分布、缓冲命中率（按表分解）——它们是物理设计的体检指标，也是本节所有优化动作的效果度量；没有这三个数字的物理优化是盲动，有了它们，物理层就从"设计时的一次性功夫"变成了"运行期的持续资产"。