简介这份资源聚焦数据库物理模型设计面向数据库设计初学者与有一定经验的开发人员帮助读者理解如何将逻辑数据模型落地到实际存储系统兼顾性能优化、存储效率与数据管理。内容以四种核心设计模式为线索重点讲解主扩展模式通过抽取共性属性形成公共属性表再以一对一关系扩展专有属性表从而减少冗余、提升一致性并结合公司员工类型等实例说明其应用方式同时涉及PowerDesigner中CDM与PDM的表示方法。资源包为1个docx文档约104KB适合作为设计思路梳理与模式入门的参考材料。目前已有2210人学习读者可从中获得主扩展模式的完整推导过程、表结构关系示例以及后续主从、名值等模式的延伸线索便于在实际项目中灵活套用。1. 数据库物理模型设计为什么你的表建完三个月就开始卡很多团队在需求评审后直接冲进建表环节逻辑模型刚定完就急着写 DDL结果上线三个月订单表查询从 20ms 涨到 800ms运维半夜被叫起来加索引。数据库物理模型设计要解决的正是这个问题它把逻辑模型实体、关系、范式翻译成具体数据库能执行的存储结构包括表空间规划、字段类型选择、索引策略、分区方案、约束与默认值。适合两类人看一是后端开发兼 DBA 的中小团队主力二是数据平台工程师。物理模型不是画 ER 图而是决定数据落在磁盘上长什么样、查询走哪条路径。逻辑模型对了物理模型错了性能照样翻车。2. 从逻辑模型到物理模型字段类型与存储引擎的选型逻辑逻辑模型告诉你“用户有一个手机号”物理模型要回答用 VARCHAR(11) 还是 CHAR(11)用 InnoDB 还是别的引擎字符集选 utf8mb4 还是 utf8这些选择直接决定单行长度、页分裂频率和索引效率。2.1 字段类型映射的三个硬规则第一条规则能用定长就不用变长但前提是长度真的固定。手机号、身份证号、MD5 值这类长度确定的字段用 CHAR 比 VARCHAR 少一个长度字节且不会产生行迁移。但地址、备注这类字段必须用 VARCHAR并且要设一个合理的上限不要直接 VARCHAR(255) 了事。第二条规则金额字段一律用 DECIMAL禁止用 FLOAT 或 DOUBLE。浮点数在累加时会出现精度丢失财务对账时这是灾难级问题。DECIMAL(18,4) 能覆盖绝大多数业务场景18 位总长度、4 位小数单行占 9 字节左右。第三条规则时间字段优先用 DATETIME 而不是 TIMESTAMP。TIMESTAMP 有 2038 年问题且受时区影响跨时区业务容易出玄学 bug。DATETIME 占 5 字节MySQL 5.6范围到 9999 年省心。下面是一个典型的用户表物理模型 DDL注释里标了每个选择的理由CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 雪花ID或自增BIGINT防溢出, phone CHAR(11) NOT NULL COMMENT 定长11位比VARCHAR省1字节, id_card CHAR(18) NOT NULL COMMENT 身份证定长18位, nickname VARCHAR(64) NOT NULL DEFAULT COMMENT 昵称上限64避免行溢出, balance DECIMAL(18,4) NOT NULL DEFAULT 0.0000 COMMENT 金额禁用FLOAT, status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 状态枚举1字节, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用户主表;逻辑说明主键用 BIGINT UNSIGNED 而不是 INT因为 INT 上限 21 亿用户量大的业务三年就可能打满。phone 加唯一索引既做业务约束又加速登录查询。created_at 加普通索引方便按时间范围做运营统计。字符集用 utf8mb4 而不是 utf8因为 utf8 在 MySQL 里是阉割版存不了 emoji后期改字符集要锁表重建血泪经验。参数说明DECIMAL(18,4) 中 18 是总位数4 是小数位整数部分最多 14 位足够覆盖万亿级金额。TINYINT UNSIGNED 范围 0-255状态枚举够用且只占 1 字节。VARCHAR(64) 的 64 是字符数不是字节数utf8mb4 下最多占 256 字节仍在 InnoDB 行溢出阈值内。2.2 存储引擎与字符集的落地选择MySQL 场景下 InnoDB 是唯一选择它支持事务、行锁、崩溃恢复和外键。MyISAM 只读场景才考虑但现在已经很少见了。字符集统一用 utf8mb4排序规则用 utf8mb4_general_ci 还是 utf8mb4_unicode_ci前者比较快但不完全符合 Unicode 排序后者准确但稍慢。一般业务用 general_ci 够用涉及多语言排序再换 unicode_ci。PostgreSQL 场景下没有引擎选择但要注意 TOAST 机制变长字段超过 2KB 会自动压缩或挪到副表所以大文本字段不要和主表放一起拆到扩展表更稳。2.3 表空间与文件组织的规划InnoDB 默认用共享表空间 ibdata1所有表数据混在一起删表后空间不释放。生产环境建议开启 innodb_file_per_table每张表独立 .ibd 文件方便单表备份和空间回收。参数在 my.cnf 里设[mysqld] innodb_file_per_table ON innodb_data_file_path ibdata1:1G:autoextend innodb_log_file_size 512M innodb_buffer_pool_size 8G逻辑说明innodb_file_per_table 开启后每张表的数据和索引存在独立文件里DROP TABLE 会直接删除文件并释放磁盘。innodb_log_file_size 设 512M 是为了减少 checkpoint 频率写密集场景下太小会导致频繁刷盘。innodb_buffer_pool_size 设物理内存的 60%-70%这是 InnoDB 最重要的参数缓存数据和索引命中率直接决定查询速度。参数说明ibdata1 设 1G 起步并 autoextend避免共享表空间频繁扩容。log_file_size 在 MySQL 5.7 之后支持在线调整但 5.6 需要重启。buffer_pool_size 在 5.7 支持在线调整8.0 支持动态扩缩。3. 索引设计从最左前缀到覆盖索引的实战推演索引是物理模型里最影响性能的部分。建少了查询慢建多了写入慢、占空间。核心原则为高频查询建索引为写入频繁的表控制索引数量。3.1 联合索引的字段顺序怎么定联合索引 (a, b, c) 能加速 WHERE a? AND b? AND c?也能加速 WHERE a? AND b?但不能加速 WHERE b? AND c?。这就是最左前缀原则。字段顺序按区分度从高到低排但也要考虑查询频率。比如订单表按用户查最多按状态查其次那索引就是 (user_id, status, created_at)。下面是一个订单表的索引设计示例ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, created_at), ADD INDEX idx_status_time (status, created_at), ADD UNIQUE KEY uk_order_no (order_no);逻辑说明idx_user_status_time 覆盖“查某用户某状态的订单按时间排序”这个最高频查询因为 user_id 等值匹配、status 等值匹配、created_at 范围排序顺序刚好。idx_status_time 覆盖运营后台“查某状态订单按时间排序”。uk_order_no 保证订单号唯一且加速按订单号查详情。参数说明联合索引字段顺序不能随意调user_id 放第一位是因为它区分度最高且查询必带。status 放第二位是因为它区分度低但查询常带。created_at 放最后是因为范围查询后面的字段用不到索引放最后损失最小。3.2 覆盖索引与回表的取舍覆盖索引指查询的所有字段都在索引里不需要回主键索引取数据。比如 SELECT user_id, status FROM t_order WHERE user_id?如果索引是 (user_id, status)那就直接走覆盖索引少一次回表。但覆盖索引会让索引变宽占更多空间。取舍标准高频查询且字段少做覆盖索引低频查询或字段多不做。3.3 索引选择性计算与冗余索引排查选择性 不重复值数量 / 总行数。选择性越接近 1 越好。比如性别字段选择性只有 0.5建索引意义不大。排查冗余索引用 sys.schema_redundant_indexes 视图SELECT * FROM sys.schema_redundant_indexes WHERE table_schema your_db;逻辑说明这个视图会列出被其他索引覆盖的冗余索引比如已有 (a, b, c)再建 (a, b) 就是冗余。冗余索引浪费写入性能定期清理。参数说明table_schema 换成你的库名。MySQL 5.7 自带 sys 库5.6 需要手动安装。4. 分区表与分库分表物理模型层面的水平扩展单表数据量超过 2000 万行后B 树高度增加查询变慢DDL 操作锁表时间不可接受。物理模型设计必须提前考虑水平扩展方案。4.1 RANGE 分区与 LIST 分区的适用场景RANGE 分区按时间范围分适合日志表、订单表。LIST 分区按枚举值分适合按地区、按业务线分的表。HASH 分区按哈希值分适合均匀分布但查询不带范围条件的表。下面是一个按月份 RANGE 分区的订单表CREATE TABLE t_order_partition ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, order_no CHAR(32) NOT NULL, amount DECIMAL(18,4) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at), UNIQUE KEY uk_order_no (order_no, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202401 VALUES LESS THAN (TO_DAYS(2024-02-01)), PARTITION p202402 VALUES LESS THAN (TO_DAYS(2024-03-01)), PARTITION p202403 VALUES LESS THAN (TO_DAYS(2024-04-01)), PARTITION p_max VALUES LESS THAN MAXVALUE );逻辑说明分区键必须包含在主键和唯一键里所以主键改成 (id, created_at)唯一键改成 (order_no, created_at)。TO_DAYS 把日期转成天数RANGE 分区按天数分。p_max 兜底防止插入超出范围的数据报错。参数说明分区键选 created_at 是因为查询常带时间范围分区裁剪能直接跳过无关分区。主键加 created_at 是 MySQL 分区表的硬性要求。唯一键加 created_at 同理。4.2 分区裁剪的验证与常见失效场景分区裁剪指查询时优化器自动跳过不满足条件的分区。用 EXPLAIN 看 partitions 列EXPLAIN SELECT * FROM t_order_partition WHERE created_at 2024-02-01 AND created_at 2024-03-01;逻辑说明如果 partitions 列只显示 p202402说明裁剪生效。如果显示所有分区说明裁剪失效。失效常见原因分区键上用了函数比如 WHERE YEAR(created_at)2024优化器无法推导分区范围。参数说明EXPLAIN 的 partitions 列在 MySQL 5.7 才有5.6 需要用 EXPLAIN PARTITIONS。4.3 分库分表的中间件选型与路由键设计分区表在单机内解决扩展问题分库分表跨机解决。中间件常见做法是应用层分片如 ShardingSphere或代理层分片如 MyCat。路由键选 user_id 还是 order_no看查询模式。如果 90% 查询按 user_id就用 user_id 做路由键订单号查询走基因法或映射表。5. 物理模型设计的避坑清单五个真实翻车现场5.1 坑一VARCHAR(255) 滥用导致行溢出现象表里大量 VARCHAR(255) 字段单行长度超过 8126 字节InnoDB 把变长字段挪到溢出页查询变慢。 原因VARCHAR(255) 在 utf8mb4 下最多占 1020 字节几个字段加起来就超限。 解决按实际需要设长度昵称 64、标题 128、备注 512超长文本拆到扩展表。5.2 坑二隐式类型转换让索引失效现象phone 字段是 CHAR(11)查询用 WHERE phone 13800138000数字索引失效走全表。 原因MySQL 在比较时把字符串转成数字索引列上发生隐式转换。 解决查询参数类型和字段类型一致字符串字段必须加引号。5.3 坑三唯一索引与业务唯一性混淆现象订单号做了唯一索引但业务上允许软删除后重新下单导致插入冲突。 原因唯一索引不区分软删除标记。 解决唯一索引加 deleted_at 字段或者用业务层生成全局唯一订单号。5.4 坑四分区表唯一键必须包含分区键现象建分区表时唯一键没加分区键报错 “A UNIQUE INDEX must include all columns in the tables partitioning function”。 原因MySQL 要求唯一键必须包含分区键否则无法保证分区内唯一。 解决唯一键加上分区键字段如 (order_no, created_at)。5.5 坑五大字段与主表混放导致查询放大现象文章表 content 字段 TEXT 类型列表查询 SELECT * 把正文也拉出来网络传输和内存暴涨。 原因TEXT 字段存在溢出页SELECT * 会触发额外 IO。 解决列表查询只取必要字段正文拆到 t_article_content 扩展表按需 JOIN。6. 用 sysbench 验证物理模型从压测数据反推参数调整物理模型设计完不能拍脑袋上线要用压测验证。sysbench 是常用工具能模拟 OLTP 读写混合场景测出 QPS、TPS、延迟分布。6.1 sysbench 安装与建表脚本# 安装 sysbench以 Ubuntu 为例 apt-get install sysbench # 准备测试表10 张表每张 100 万行 sysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyour_password \ --mysql-dbtest_db \ --tables10 \ --table-size1000000 \ prepare逻辑说明oltp_read_write 是读写混合模式模拟真实业务。tables10 建 10 张表table-size1000000 每张 100 万行总共 1000 万行接近生产数据量。prepare 阶段建表并灌数据。参数说明mysql-host 换成你的数据库地址。table-size 根据磁盘空间调整100 万行约 200MB。prepare 阶段耗时较长建议在低峰期做。6.2 压测执行与关键指标解读sysbench oltp_read_write \ --mysql-host127.0.0.1 \ --mysql-port3306 \ --mysql-userroot \ --mysql-passwordyour_password \ --mysql-dbtest_db \ --tables10 \ --table-size1000000 \ --threads64 \ --time300 \ --report-interval10 \ run逻辑说明threads64 模拟 64 并发time300 跑 5 分钟report-interval10 每 10 秒输出一次中间结果。重点看 QPS、TPS、95% 延迟。参数说明threads 根据 CPU 核数调整一般是核数的 2-4 倍。time 至少 300 秒太短数据不稳定。95% 延迟超过 100ms 就要排查索引或锁竞争。6.3 根据压测结果反推物理模型调整如果 QPS 低且 95% 延迟高先看慢查询日志找出全表扫描的 SQL补索引。如果写入 TPS 低看 innodb_log_file_size 和 innodb_flush_log_at_trx_commit。如果磁盘 IO 打满看 buffer_pool_size 是否太小或者考虑分区表分散 IO。我一般会跑三轮第一轮基线第二轮加索引第三轮调参数。每轮只改一个变量否则不知道哪个改动生效。压测数据是物理模型设计的后悔药上线前吃比上线后吃便宜得多。希望帮到你。本文还有配套的精品资源点击获取