简介：这份资源聚焦数据库物理模型设计，面向数据库设计人员、后端开发与数据建模学习者，帮助读者理解如何将逻辑模型落地到实际存储系统，兼顾性能优化、存储效率与数据管理。内容以四种核心设计模式为线索，重点讲解主扩展模式：通过抽取共性属性形成公共属性表，再以一对一扩展表承载专有属性，从而减少冗余、提升一致性，并结合公司员工类型等实例与PowerDesigner的CDM、PDM图加以说明。资源包为1个docx文档，约104KB，便于快速阅读与查阅。目前已有2210人学习，适合希望系统掌握物理模型设计策略、为后续主从模式、名值模式等学习打基础的读者参考。
1. 数据库物理模型设计：从表结构到存储引擎的落地拆解
很多团队在概念模型和逻辑模型阶段讨论得热火朝天，一到物理模型设计就草草收场，结果上线三个月后慢查询扎堆、磁盘告警、DDL 锁表。我见过一个订单系统，逻辑模型里字段类型全是 VARCHAR(255)，物理层没做任何调整，单表跑到两千万行时一个统计查询要四十多秒。数据库物理模型设计要解决的核心问题很具体：把逻辑模型翻译成特定数据库能高效执行的存储结构，包括表空间规划、字段类型选型、索引策略、分区方案和存储引擎参数。它适合后端开发、DBA 和系统架构师，尤其是那些正在做数据层重构或新系统落地的从业者。这份资源把物理设计的每个决策点拆成了可对照的参数和步骤，不是泛泛而谈的范式理论。
2. 字段类型与存储引擎：物理设计的第一层决策
2.1 为什么逻辑模型不能直接映射到物理表
逻辑模型关心的是实体和关系，物理模型关心的是字节和页。同一个“用户状态”字段，逻辑层写的是枚举，物理层可以选 TINYINT、ENUM 或者 CHAR(1)，三者在存储占用、索引效率和迁移成本上完全不同。我一般会先做一轮字段类型收敛，把逻辑模型里所有文本型字段按实际最大长度重新定标，再根据数据库引擎的特性决定是否使用变长类型。
以 MySQL InnoDB 为例，VARCHAR(255) 和 VARCHAR(50) 在存储短字符串时占用空间几乎一样，但索引前缀长度和内存临时表的行为会不同。更关键的是，InnoDB 的索引页默认 16KB，一个包含多个 VARCHAR(255) 的联合索引很容易让单个索引条目膨胀，导致页分裂频繁。常见做法是：能定长的用 CHAR，长度波动大的用 VARCHAR，但必须设一个基于业务上限的合理值，而不是默认 255。
-- 反例：逻辑模型直接映射，所有文本字段一刀切 CREATE TABLE user_profile_bad ( user_id BIGINT, nickname VARCHAR(255), status VARCHAR(255), region_code VARCHAR(255), created_at VARCHAR(255) ) ENGINE=InnoDB; -- 正例：按业务上限收敛类型，状态用 TINYINT，地区码用 CHAR(6) CREATE TABLE user_profile_good ( user_id BIGINT UNSIGNED NOT NULL, nickname VARCHAR(64) NOT NULL DEFAULT '', status TINYINT UNSIGNED NOT NULL DEFAULT 0, region_code CHAR(6) NOT NULL DEFAULT '000000', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (user_id), KEY idx_status_region (status, region_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;上面这段代码的逻辑说明：第一张表把所有字段都设成 VARCHAR(255)，在 InnoDB 里虽然实际存储按内容长度分配，但索引和排序时会按最大长度预留内存，尤其是 created_at 用字符串存储会导致时间范围查询无法走索引。第二张表把 status 收敛为 TINYINT，region_code 用 CHAR(6)，created_at 用 DATETIME，这样联合索引 idx_status_region 的每个条目长度可控，范围扫描效率明显提升。参数上注意：BIGINT UNSIGNED 用于自增主键可以撑到 1844 亿亿，TINYINT UNSIGNED 范围 0-255，够绝大多数状态枚举用。
2.2 存储引擎选型：InnoDB、MyISAM 还是 RocksDB
物理模型设计绕不开存储引擎。MySQL 生态里 InnoDB 是默认选择，支持事务、行锁和外键，适合 OLTP 场景。MyISAM 只读场景下全表扫描快，但不支持事务，崩溃恢复能力弱，现在新系统基本不选。如果写入吞吐要求极高且能接受最终一致性，有些团队会考虑 RocksDB 作为底层引擎，通过 MyRocks 插件接入 MySQL。
选型时我一般看三个指标：读写比、事务隔离要求和单表数据量预期。读写比超过 10:1 且以主键查询为主，InnoDB 完全够用；如果写入量每天过亿且允许 LSM 树带来的读放大，可以评估 MyRocks。下面是一个引擎参数对照，方便在物理设计评审时直接引用。
| 维度 | InnoDB | MyISAM | MyRocks |
|---|---|---|---|
| 事务支持 | 完整 ACID | 无 | 完整 ACID |
| 锁粒度 | 行锁 | 表锁 | 行锁 |
| 索引结构 | B+Tree | B+Tree | LSM Tree |
| 写放大 | 中等 | 低 | 高 |
| 读放大 | 低 | 低 | 高 |
| 适用场景 | OLTP 通用 | 只读归档 | 写密集 |
提示：物理设计阶段如果选了 MyRocks，一定要在测试环境压测读放大对 P99 延迟的影响，LSM 树的 compaction 会周期性抢占 IO。
2.3 字符集与排序规则对索引的影响
字符集不是“统一用 utf8mb4”就完事。utf8mb4 下每个字符最多占 4 字节，而 utf8mb3 最多 3 字节。如果一个索引列是 VARCHAR(64) 的 utf8mb4，索引条目最大 256 字节，加上主键回表开销，单个索引页能放的条目数比 utf8mb3 少约 25%。排序规则影响更大：utf8mb4_general_ci 和 utf8mb4_0900_ai_ci 在比较和排序时的 CPU 开销不同，后者基于 Unicode 9.0 规则，更准确但稍慢。
我一般会建议：如果业务不需要存储 Emoji 和生僻字，用 utf8mb3 可以省空间；如果必须用 utf8mb4，排序规则统一用 utf8mb4_0900_ai_ci，避免混用导致隐式转换让索引失效。物理设计文档里要明确写出每个表的字符集和排序规则，不能留给建表时随手写。
3. 索引策略与分区方案：把查询模式翻译成物理结构
3.1 联合索引的最左前缀与覆盖索引设计
索引是物理模型里对性能影响最大的部分。逻辑模型只告诉你“按用户查订单”，物理模型要决定是建 (user_id, created_at) 还是 (user_id, status, created_at)。最左前缀原则大家都知道，但实际设计时容易忽略“索引列顺序由等值查询和范围查询的边界决定”。等值条件列放前面，范围条件列放后面，排序需求尽量用索引顺序满足。
覆盖索引是另一个关键手段。如果一个查询只需要索引里已有的列，InnoDB 不用回表，直接从二级索引返回数据。下面这个例子展示如何把高频查询改造成覆盖索引。
-- 高频查询：查某用户最近 10 笔已支付订单的金额和时间 SELECT order_id, amount, created_at FROM orders WHERE user_id = 10086 AND status = 2 ORDER BY created_at DESC LIMIT 10; -- 物理设计：建联合索引，把查询涉及的列都放进去 ALTER TABLE orders ADD INDEX idx_user_status_time_amount (user_id, status, created_at, amount);逻辑说明：这个联合索引的顺序是 user_id（等值）、status（等值）、created_at（范围+排序）、amount（覆盖列）。查询时优化器可以直接用索引完成过滤、排序和返回，不需要回表。参数上注意：created_at 放在 amount 前面是因为 ORDER BY 需要它有序，amount 只是覆盖列不参与排序。如果查询里还有 order_id 需要返回，而 order_id 是主键，InnoDB 二级索引叶子节点自带主键值，所以 order_id 不需要额外加入索引。
3.2 分区表：什么时候该分，怎么分
单表超过五千万行后，即使索引设计合理，B+Tree 的深度也会增加，DDL 和备份恢复时间变得不可接受。分区表把数据按规则拆到多个物理文件，查询时通过分区裁剪只扫描相关分区。常见分区方式有 RANGE、LIST、HASH 和 KEY。
RANGE 分区按时间最常用，比如按月分区，历史数据可以快速归档。HASH 分区适合均匀打散写入，但范围查询会扫描所有分区。我一般会先评估查询模式：如果 90% 的查询都带时间范围，用 RANGE 按月或按周分区；如果查询主要是主键点查，分区收益不大，不如直接分库分表。
-- 按月 RANGE 分区，订单表按 created_at 拆分 CREATE TABLE orders_partitioned ( order_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, status TINYINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (order_id, created_at), KEY idx_user_status_time (user_id, status, created_at) ) ENGINE=InnoDB PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS('2025-02-01')), PARTITION p202502 VALUES LESS THAN (TO_DAYS('2025-03-01')), PARTITION p202503 VALUES LESS THAN (TO_DAYS('2025-04-01')), PARTITION pmax VALUES LESS THAN MAXVALUE );逻辑说明：分区键必须包含在主键里，所以主键改成 (order_id, created_at)。TO_DAYS 函数把日期转成天数，RANGE 分区按天数边界划分。pmax 分区兜底，避免插入超出范围的数据时报错。参数上注意：分区表在 MySQL 8.0 里支持原生分区，但外键约束不能用于分区表，物理设计时要提前去掉外键改用应用层保证。
3.3 索引选择性计算与冗余索引清理
索引不是越多越好。每个二级索引都是一棵独立的 B+Tree，写入时要维护，占用额外磁盘。选择性 = 不重复值数 / 总行数，选择性低于 0.1 的列单独建索引意义不大。我一般会跑一遍统计，把选择性低且不在联合索引最左列的索引删掉。
-- 查看索引选择性 SELECT INDEX_NAME, COLUMN_NAME, SEQ_IN_INDEX, CARDINALITY, (SELECT COUNT(*) FROM orders) AS total_rows, ROUND(CARDINALITY / (SELECT COUNT(*) FROM orders), 4) AS selectivity FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders' ORDER BY INDEX_NAME, SEQ_IN_INDEX;逻辑说明：CARDINALITY 是优化器估算的不重复值数，除以总行数得到选择性。如果某个单列索引的选择性低于 0.05，且该列是另一个联合索引的最左前缀，这个单列索引就是冗余的，可以删除。参数上注意：CARDINALITY 是采样估算值，不是精确值，大表上会有偏差，建议用 ANALYZE TABLE 更新统计信息后再看。
4. 物理设计避坑：五条血泪经验
4.1 坑一：用 UUID 做主键导致页分裂
现象：插入性能随数据量增长急剧下降，磁盘 IO 飙升，索引页填充率低。 原因：UUID 随机分布，新插入的主键不在 B+Tree 末尾，导致频繁页分裂和随机写。 解决：用自增 BIGINT 或雪花算法生成的趋势递增 ID 做主键。如果业务必须用 UUID，把它作为唯一索引列，主键仍用自增 ID。
4.2 坑二：隐式类型转换让索引失效
现象：明明建了索引，EXPLAIN 却显示 type=ALL 全表扫描。 原因：查询条件里字段类型和传入参数类型不一致，比如 user_id 是 BIGINT，查询写 WHERE user_id = '10086'，MySQL 会把列转成字符串比较，索引失效。 解决：物理设计文档里标注每个字段的精确类型，应用层参数绑定用对应类型。上线前用 EXPLAIN 逐条核对高频查询。
4.3 坑三：大字段和主表混存拖慢查询
现象：查询主表时即使只取几个列，响应时间也明显偏长。 原因：TEXT/BLOB 大字段和主表存在同一个页里，InnoDB 读取时会把整页加载进内存，浪费 Buffer Pool。 解决：把大字段拆到独立的扩展表，用主键关联。主表只保留定长和短变长字段，保证单行数据不超过一个页的合理比例。
4.4 坑四：分区表查询没带分区键
现象：分区表查询延迟和单表一样，分区裁剪没生效。 原因：WHERE 条件里没有分区键，或者对分区键使用了函数导致无法裁剪。 解决：物理设计时明确分区键必须出现在高频查询的 WHERE 条件里。如果业务查询确实不带时间范围，评估是否改用 HASH 分区或直接分库分表。
4.5 坑五：忽略连接池和物理连接数匹配
现象：数据库 CPU 不高但连接数经常打满，应用报连接超时。 原因：物理模型设计只关注表结构，没评估最大并发连接数和连接池配置的匹配关系。 解决：根据 max_connections 和业务峰值 QPS 反推连接池大小。一般单个应用实例连接池不超过 20，总连接数控制在数据库 max_connections 的 70% 以内。
5. 物理设计评审清单与自动化校验脚本
物理模型设计做完后，我习惯用一份检查清单过一遍，再用脚本自动校验。清单包括：每个表是否有主键、主键类型是否趋势递增、字符集是否统一、索引选择性是否达标、是否有冗余索引、大字段是否拆分、分区键是否覆盖高频查询、外键是否移除。下面这个 Python 脚本连接 information_schema，自动输出可疑项。
import pymysql # 连接数据库，读取物理设计元数据 conn = pymysql.connect(host='localhost', user='dba', password='***', database='information_schema') cursor = conn.cursor() # 检查没有主键的表 cursor.execute(""" SELECT t.TABLE_NAME FROM TABLES t LEFT JOIN STATISTICS s ON t.TABLE_NAME = s.TABLE_NAME AND s.INDEX_NAME = 'PRIMARY' WHERE t.TABLE_SCHEMA = 'your_db' AND s.INDEX_NAME IS NULL """) for row in cursor.fetchall(): print(f"缺少主键: {row[0]}") # 检查选择性低于 0.05 的单列索引 cursor.execute(""" SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY FROM STATISTICS WHERE TABLE_SCHEMA = 'your_db' AND SEQ_IN_INDEX = 1 AND INDEX_NAME != 'PRIMARY' AND CARDINALITY < 100 """) for row in cursor.fetchall(): print(f"低选择性索引: {row[0]}.{row[1]} 列={row[2]} 基数={row[3]}") cursor.close() conn.close()逻辑说明：第一个查询用 LEFT JOIN 找出没有 PRIMARY 索引的表，这类表在 InnoDB 里会隐式创建 row_id，但无法用于业务查询。第二个查询找出基数低于 100 的单列索引，这些索引大概率选择性不足，需要人工复核是否删除或合并到联合索引。参数上注意：CARDINALITY 阈值 100 是经验值，大表上可以按总行数的 5% 动态计算。
注意：自动化脚本只能做初筛，最终是否删除索引要结合慢查询日志和业务查询模式判断。我一般会把脚本输出和慢查询 Top 20 放在一起评审。
从那以后我每次做完物理模型设计，都会强制走一遍“类型收敛 → 索引选择性校验 → 分区裁剪验证 → 连接数匹配”这四步，少一步都不敢上生产。希望帮到你。
本文还有配套的精品资源，点击获取
