简介这份文档面向数据库设计初学者与需要梳理建模思路的开发者聚焦数据库物理模型设计这一关键环节讲解如何在实际存储系统中落地逻辑模型以兼顾性能、存储效率与数据管理。内容以四种核心设计模式为主线重点展开「主扩展模式」将采购员、营销员等对象的共性属性抽取为公共属性表专有属性另建扩展表通过一对一关系形成完整描述从而减少冗余、提升一致性并借助 PowerDesigner 的 CDM、PDM 图直观表达。资源包为 1 个 docx 文档约 104KB便于通读与检索。目前已有 2210 人学习适合希望理解建模定式、优化表结构并积累实战经验的读者参考。1. 数据库物理模型设计为什么你的表在测试环境飞快上线三个月就卡成 PPT数据库物理模型设计是把逻辑 ER 图翻译成真正能在磁盘上跑起来的那一层表怎么建、主键用什么、索引加在哪几列、分区怎么切、字段类型选多宽。它不关心业务有几个实体只关心数据落在哪个页、走哪条索引、锁住哪几行。很多团队把逻辑模型评审得滴水不漏物理模型却随手CREATE TABLE了事结果测试环境几千行数据跑得飞快上线三个月数据量一上来一条列表查询扫全表CPU 直接打满。这篇文章面向的是已经会写 SQL、但没系统做过物理设计的后端和 DBA。我会按「先定字段和主键 → 再定索引 → 再定分区和大字段 → 最后做压测验证」的顺序把每一步的参数、命令和踩过的坑讲清楚。你照着做至少能避开那批最常见的翻车点。2. 从逻辑模型到建表语句字段类型和主键怎么定才不返工物理设计的第一步不是加索引而是把每个字段的类型、长度、是否可空定死。这一步返工成本最高因为表一旦有数据改类型就是锁表加数据迁移。2.1 字段类型选型别用 VARCHAR(255) 糊弄所有字符串常见做法是给每个字符串字段一个「够用且不浪费」的长度。手机号固定 11 位就用CHAR(11)身份证 18 位用CHAR(18)状态码用TINYINT金额用DECIMAL(18,2)而不是FLOAT。VARCHAR(255)看着省事但在联合索引里会放大键长度InnoDB 单索引键超过 3072 字节就建不了。-- 用户主表字段类型按真实语义定不用万能 VARCHAR CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 自增主键, user_no CHAR(16) NOT NULL COMMENT 对外用户编号定长, phone CHAR(11) NOT NULL COMMENT 手机号定长, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 2冻结, balance DECIMAL(18,2) NOT NULL DEFAULT 0.00 COMMENT 余额禁用 FLOAT, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_no (user_no), UNIQUE KEY uk_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户主表;逻辑说明id用BIGINT UNSIGNED而不是INT是因为INT上限约 21 亿订单类表很容易撞顶。user_no和phone用CHAR定长是因为它们长度固定定长在索引里比较更快。balance用DECIMAL是硬性要求FLOAT做金额会出现0.1 0.2 ! 0.3的精度问题。参数说明utf8mb4是必须的utf8存不了 emoji 和部分生僻字。AUTO_INCREMENT配合BIGINT时注意自增主键在并发插入下是顺序写比 UUID 主键的随机写性能好很多这是后面索引章节的前提。2.2 主键选型自增、业务主键还是雪花 ID主键选型直接决定聚簇索引的写入模式。InnoDB 是聚簇索引数据行按主键顺序物理存放。自增主键是顺序追加页分裂少UUID 或随机字符串主键会导致随机插入页频繁分裂写入性能掉一半以上。主键方案写入性能分库分表友好适用场景自增 BIGINT高顺序写差需改造单库单表、日志类业务主键如订单号中取决于生成规则好订单、支付流水雪花 ID高趋势递增好分库分表、高并发我一般会单库阶段用自增BIGINT确定要分库分表时换成雪花 ID并且把主键类型统一成BIGINT UNSIGNED避免后期改类型。业务主键如果一定要做唯一约束用UNIQUE KEY而不是PRIMARY KEY把聚簇索引留给自增列。2.3 可空与默认值NULL 在索引里的代价允许NULL的列在索引里会额外占一个标记位而且NULL不参与某些索引优化。常见做法是状态、金额、计数类字段一律NOT NULL DEFAULT只有真正「未知」的字段才允许NULL比如「删除时间」这种。-- 反例可空字段导致索引统计失真 ALTER TABLE t_user ADD COLUMN last_login DATETIME NULL; -- 正例用默认值代替 NULL查询时不用 IS NULL 判断 ALTER TABLE t_user ADD COLUMN last_login DATETIME NOT NULL DEFAULT 1970-01-01 00:00:00;逻辑说明NOT NULL DEFAULT让列在索引里更紧凑WHERE last_login ?这类范围查询也能稳定走索引。参数上默认值要选一个业务上不可能出现的值比如1970-01-01这样「从未登录」和「登录过」能区分开。3. 索引设计联合索引的列顺序为什么总有人排错索引是物理模型里收益最高也最容易翻车的一环。加错索引不只是没效果还会拖慢写入、占磁盘。3.1 联合索引的最左前缀把等值列放前面范围列放后面联合索引(a, b, c)能命中a、a,b、a,b,c但b单独查命中不了。列顺序的原则是等值条件列在前范围条件列在后排序列尽量跟在等值列后面。-- 查询WHERE status 1 AND created_at 2024-01-01 ORDER BY created_at -- 正确等值列 status 在前范围/排序列 created_at 在后 ALTER TABLE t_order ADD INDEX idx_status_created (status, created_at); -- 错误把范围列放前面status 的等值过滤用不上索引 ALTER TABLE t_order ADD INDEX idx_created_status (created_at, status);逻辑说明idx_status_created先按status定位到所有正常订单再在created_at上有序扫描ORDER BY也能直接利用索引顺序省掉 filesort。反过来idx_created_status会先扫时间范围再回表过滤status效率差一个量级。参数说明联合索引列数不建议超过 5 列键总长度控制在 3072 字节内。EXPLAIN看key_len能判断用到了几列key_len只覆盖第一列说明后面的列没生效。3.2 覆盖索引让查询不回表如果查询需要的列都在索引里InnoDB 就不用回表查聚簇索引这叫覆盖索引。对高频列表查询覆盖索引能把响应时间压到原来的三分之一。-- 列表查询只需要 id、status、created_at -- 建覆盖索引把查询列都放进索引 ALTER TABLE t_order ADD INDEX idx_cover_list (status, created_at, id); -- 验证是否覆盖Extra 出现 Using index 即命中覆盖索引 EXPLAIN SELECT id, status, created_at FROM t_order WHERE status 1 ORDER BY created_at DESC LIMIT 20;逻辑说明idx_cover_list把id也放进索引是因为 InnoDB 二级索引叶子节点本来就存主键显式写出来能让优化器更明确地走覆盖。EXPLAIN的Extra列出现Using index就是覆盖出现Using filesort说明排序没走索引。参数说明覆盖索引会增大索引体积写多的表要权衡。一般只给 Top 5 的高频查询建覆盖索引不要每个查询都建。3.3 索引选择性区分度低的列别单独建索引选择性 不同值数量 / 总行数。性别、状态这种只有几个值的列单独建索引选择性极低优化器可能直接放弃走索引。-- 查看列的选择性越接近 1 越好 SELECT COUNT(DISTINCT status) / COUNT(*) AS sel_status FROM t_order; SELECT COUNT(DISTINCT user_id) / COUNT(*) AS sel_user FROM t_order;逻辑说明sel_status可能只有 0.001这种列单独建索引没意义但作为联合索引的前缀列配合高选择性列就有用。sel_user接近 1适合单独建索引或作为联合索引首列。参数说明选择性低于 0.01 的列不要单独建索引。判断标准不是绝对值而是「过滤后剩余行数」——如果过滤后还剩几十万行索引反而增加开销。4. 分区、大字段与冷热分离数据量上亿后的物理层改造单表过千万行后索引树变高查询和 DDL 都变慢。物理模型要在这一层做分区和冷热拆分。4.1 范围分区按时间切让查询只扫一个分区分区把一张大表在物理上拆成多个文件查询带分区键时只扫对应分区。订单、日志类表按时间做 RANGE 分区最常见。-- 按 created_at 月份做范围分区 ALTER TABLE t_order 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 pmax VALUES LESS THAN MAXVALUE );逻辑说明TO_DAYS把日期转成整数分区边界清晰。查询WHERE created_at 2024-02-01 AND created_at 2024-03-01时优化器只扫p202402。pmax兜底分区防止插入越界报错。参数说明分区键必须出现在查询条件里否则会扫全部分区比不分区还慢。分区数建议控制在 50 到 100 之间太多会导致元数据开销。MySQL 分区表不支持外键设计时要提前确认。4.2 大字段拆分把 TEXT/BLOB 挪到扩展表主表里放大字段会让行溢出索引页能存的行数变少查询变慢。常见做法是把大字段拆到扩展表主表只留 ID 和摘要。-- 主表只留轻量字段 CREATE TABLE t_article ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(200) NOT NULL, author_id BIGINT UNSIGNED NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), INDEX idx_author (author_id) ) ENGINEInnoDB; -- 扩展表存正文按需 JOIN CREATE TABLE t_article_content ( article_id BIGINT UNSIGNED NOT NULL, content MEDIUMTEXT, PRIMARY KEY (article_id) ) ENGINEInnoDB;逻辑说明列表页只查t_article不碰正文单页能存更多行索引扫描更快。详情页再按article_idJOIN 扩展表。参数上MEDIUMTEXT上限 16MB够放长文如果确定不超过 64KB 用TEXT更省。4.3 冷热分离历史数据归档到独立表超过一定时间的冷数据查询频率极低留在主表只会拖慢索引。做法是按时间把冷数据迁到历史表主表只保留热数据。-- 归档三个月前的订单到历史表 INSERT INTO t_order_history SELECT * FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 3 MONTH); DELETE FROM t_order WHERE created_at DATE_SUB(NOW(), INTERVAL 3 MONTH);逻辑说明先INSERT再DELETE保证数据不丢。DELETE要分批做一次删太多会锁大量行、撑爆 undo log。参数上每批删 1000 到 5000 行循环执行中间 sleep 几十毫秒让主从同步跟上。5. 物理模型避坑五条血泪经验这一章是我在几个项目里真实踩过的坑每条按「现象 → 原因 → 解决」写你对照自己的表检查一遍。5.1 现象上线后写入越来越慢磁盘 IO 打满原因主键用了 UUID 或随机字符串聚簇索引随机插入导致页分裂每次插入都可能触发页重组。解决把主键换成自增BIGINT或趋势递增的雪花 ID。已经上线的表如果主键是 UUID新建一张自增主键的表用双写加数据迁移的方式切换别直接ALTER。5.2 现象明明建了索引查询还是全表扫原因查询条件对索引列做了函数运算或隐式类型转换比如WHERE DATE(created_at) 2024-01-01或WHERE phone 13800000000phone 是字符串但传了数字。解决把函数移到等号右边WHERE created_at 2024-01-01 AND created_at 2024-01-02参数类型和列类型保持一致字符串列传字符串。用EXPLAIN确认type不是ALL。5.3 现象联合索引建了但只用到第一列原因列顺序排错把范围列或低选择性列放在了前面后面的等值列用不上。解决按「等值在前、范围在后、排序跟随」重排。用EXPLAIN看key_len只覆盖第一列说明顺序有问题。重建索引时注意大表加索引要用ALGORITHMINPLACE, LOCKNONE避免锁表。5.4 现象分区表查询没变快反而更慢原因查询条件没带分区键优化器扫了所有分区元数据开销叠加。解决确认查询 SQL 里带分区键或者用EXPLAIN PARTITIONS看实际扫了哪些分区。如果业务查询天然不带时间条件分区方案就不适合改用冷热分离。5.5 现象改字段类型时锁表几十分钟业务中断原因直接ALTER TABLE MODIFY COLUMN在大表上会重建整张表期间写入被阻塞。解决用pt-online-schema-change或gh-ost这类在线 DDL 工具它们建影子表、分批拷贝、最后原子切换。或者选业务低峰期执行并提前在从库演练一遍估算耗时。6. 用压测验证物理模型别等上线才发现索引没用上物理模型设计完不能靠感觉要用真实数据量和真实查询压一遍。我一般会搭一个和线上同规格的测试库灌入至少千万级数据再用压测工具跑核心查询。6.1 造数据用存储过程灌千万级测试数据-- 造 1000 万订单测试数据 DELIMITER $$ CREATE PROCEDURE gen_orders(IN total INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i total DO INSERT INTO t_order (user_id, status, amount, created_at) VALUES (FLOOR(RAND() * 100000), FLOOR(RAND() * 3) 1, ROUND(RAND() * 1000, 2), DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)); SET i i 1; END WHILE; END$$ DELIMITER ; CALL gen_orders(10000000);逻辑说明user_id随机分布模拟真实用户status只有 1 到 3 模拟低选择性列created_at分散在一年内方便测分区和范围查询。参数上total按测试库磁盘调整1000 万行大约占 1 到 2GB。6.2 用 EXPLAIN 和慢查询日志定位问题查询-- 开启慢查询日志阈值 0.1 秒 SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 0.1; -- 对核心查询做执行计划分析 EXPLAIN SELECT id, status, created_at FROM t_order WHERE status 1 AND created_at DATE_SUB(NOW(), INTERVAL 30 DAY) ORDER BY created_at DESC LIMIT 20;逻辑说明慢查询日志抓出实际慢的 SQLEXPLAIN看type要range或ref不能是ALL、key实际用的索引、rows预估扫描行数、Extra不能有Using filesort和Using temporary。参数上long_query_time设 0.1 秒能抓到大部分问题查询生产环境可以设 0.5 秒减少日志量。6.3 压测对比加索引前后 QPS 和 P99 的变化用压测工具对同一查询跑加索引前后两组记录 QPS 和 P99 延迟。我习惯用一张对比表记录方便判断索引是否值得保留。场景QPSP99 延迟扫描行数无索引120850ms980万联合索引320018ms1200覆盖索引51009ms1200逻辑说明QPS 提升 40 倍以上说明索引有效如果提升不明显检查是不是选择性太低或查询没命中。P99 比平均值更能反映用户体验压测时重点看 P99。参数上压测并发从 50 逐步加到 500观察 QPS 拐点拐点对应的并发就是这套物理模型的容量上限。6.4 一个容易忽略的验证索引的写入代价加索引不是免费的。每多一个索引写入时就要多维护一棵 B 树。压测时要同时跑写入看加了索引后写入 TPS 掉了多少。-- 对比加索引前后的写入 TPS -- 用 sysbench 或自写脚本固定并发 100跑 5 分钟 -- 关注指标TPS、平均写入延迟、磁盘写 IOPS逻辑说明如果写入 TPS 掉了 30% 以上而查询只快了 2 倍这个索引可能不值得。参数上写多读少的表索引数控制在 3 个以内读多写少的表可以放宽到 6 个。我自己的习惯是物理模型设计完先不急着上线用真实数据量压一遍把EXPLAIN和压测数据贴到评审文档里谁反对谁举证。这套流程帮我挡掉过好几次「拍脑袋加索引」的返工。希望帮到你。本文还有配套的精品资源点击获取