外卖系统数据库设计：从ER建模到慢SQL优化实战

简介一份面向小程序外卖系统开发者的数据库设计PDF以美团、饿了么为参考给出了用户、用户地址、店铺、商家登录、店铺信息等核心数据表的建表SQL。文档采用InnoDB引擎与utf8编码表设计刻意未设外键约束而在程序层进行外键逻辑约束这一取舍能帮助读者理解物理外键与业务约束在真实项目中的差异化运用。资源包内共1个PDF文件约148KB内容直接呈现CREATE TABLE语句、字段类型与默认值设置适合数据库初学者临摹表结构也适合后端开发人员直接复用基础建表脚本。通过阅读可掌握自增主键、VARCHAR长度规划、DECIMAL钱包字段、状态与时间戳的默认值约定等实践细节。整体表结构简洁、命名清晰便于作为课程设计或毕业设计的起步模板。已有207人学习可作外卖类项目数据库设计的参考资料。1. 外卖系统数据库设计从零到能上线差的不是建表而是建模外卖系统是典型的“高并发读写 强状态流转”业务。一张订单从用户下单到骑手送达中间涉及店铺、商品、SKU、购物车、订单、支付、优惠券、配送、评价等十几个实体任何一个表设计失误后期都要用更复杂的补偿逻辑来兜底。做这套设计时SQL 本身不难难的是关系梳理和状态边界。本文按我做过的一版“外卖系统数据库设计”方案从 ER 建模讲到建表 DDL再落到订单状态机和库存扣减顺序最后给出索引优化和踩坑记录。适合正在做外卖/电商类毕设、刚接手配送业务后端、或者想系统过一遍关系型数据库设计的人。2. 外卖系统核心实体识别与 ER 建模先圈边界再谈字段做数据库设计最容易翻车的地方是一上来就建表。先别提订单表要放哪些字段光是外卖系统的业务边界就有多种画法有没有平台自营骑手是众包还是自营有没有预下单和定时单不同答案会直接改变 ER 图里的实体和关系。所以第一步永远是做实体识别。2.1 实体清单十几个表拆成四个域我习惯把外卖系统拆成四个域用户域、商品域、交易域、履约域。用户域包括用户表、用户地址表、骑手表如果骑手也是注册用户则骑手表单独建商品域包括店铺表、店铺分类表、商品表、SKU 表交易域包括购物车表、订单表、订单明细表、支付流水表、优惠券表、用户优惠券表履约域包括配送单表、配送轨迹表。再加一个平台侧的运营表比如店铺审核记录总共 15 张左右。这个规模对一套可演示、可扩展的系统是合理的。实体识别的产出物是 ER 图但 ER 图不用等图工具直接在草稿纸上划出“谁跟谁是一对多、谁跟谁是多对多”。这里最容易出问题的关系是商品与 SKU。一个商品比如“可乐”下可能有多规格500ml 瓶装、330ml 罐装每个规格是一个 SKUSKU 才是真正被加入购物车、被扣库存的对象。很多人只建商品表不建 SKU 表导致后续库存和价格根本没法控制。2.2 关系判定一对多关系的归属决定外键放哪关系判定的核心问题是“外键放哪张表”。订单与订单明细是一对多外键订单 ID 放明细表店铺与商品是一对多外键店铺 ID 放商品表用户与地址是一对多外键用户 ID 放地址表。这些归属可以看“谁的数量更大”来决定明细是订单的子项明细量大于订单量外键放在明细表。如果一张主表下面挂了多张子表且主表查子表时总是需要一次性带出那这种设计也合理。反例是购物车表。很多新手把购物车设计成“用户 ID 商品 ID 数量”这没问题但一旦用户同时用网页端和 App 端购物车同步就需要一个“会话标识”或者“最后修改时间”字段。我一般会在购物车表加一个 session_key用于未登录状态下的临时购物车登录后通过 user_id 合并。这不是标准外键但它是业务事实设计数据库时不能只顾范式。2.3 范式与反范式库存表为什么必须单独拆第三范式要求消除传递依赖但外卖系统里有些字段必须“返祖”。典型是订单表里的“商品名称快照”。如果订单明细只存 SKU ID不存商品名和下单时价格那商家改名、改价之后历史订单全部错乱。所以订单明细表必须冗余商品名称、下单价格、商品图片 URL这是反范式设计但业务上必须这么做。库存是另一个必须单独拆表的地方。SKU 表的库存字段在并发扣减时是热点行如果库存直接写在 SKU 表中每次扣减都要更新 SKU 表会带来行锁竞争。更稳的做法是拆出 inventory 表SKU ID 作为唯一键单独维护库存数量和锁定数量。这样商品详情查询和库存扣减的读写可以走不同的执行计划SQL 层能分开优化。3. 建表落地店铺、商品、订单、用户的 DDL 与字段参数选型ER 图定好后就可以写 DDL。以下用 MySQL 8.x 语法示例涉及金额和状态等参数选型时我会补充为什么不用别的类型。外卖系统的建表语句很多这里按业务链路选最核心的几张表展开。3.1 用户表与地址表唯一键和逻辑删除是必修课用户表主键用自增 ID 还是业务主键取决于是否有对外开放的用户 ID。常见做法是 BIGINT 自增做主键再单独建一个 user_no 做业务编号。逻辑删除字段必须预留外卖行业的用户注销可以走物理删除但订单关联的外键还在物理删用户会破坏订单表引用所以保留 is_deleted 更稳妥。地址表要冗余收货人姓名和电话不能只存地址 ID因为订单出餐、骑手联系时都要直接读取这些信息连表查既慢又容易因地址被修改而失真。CREATE TABLE t_user ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主键, user_no VARCHAR(32) NOT NULL COMMENT 用户编号, nickname VARCHAR(64) DEFAULT NULL, phone VARCHAR(20) NOT NULL, status TINYINT NOT NULL DEFAULT 1 COMMENT 1正常 2冻结, is_deleted TINYINT NOT NULL DEFAULT 0, 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), KEY idx_phone (phone) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里的关键参数有三个。phone 字段建普通索引而非唯一索引因为手机号有回收复用场景同一手机号可能被不同账号使用status 用 TINYINT 而不是 INT因为状态值不超过两位数TINYINT 能省 3 个字节订单表动辄千万行积少成多updated_at 用 ON UPDATE CURRENT_TIMESTAMP 自动维护避免每次更新都手动写时间。电话字段用 VARCHAR(20) 而非 VARCHAR(11)是给区号、短号留余量。3.2 订单表与明细表金额用 DECIMAL状态流转要有版本号订单表是外卖系统里字段最多的表也是后续 SQL 面试题、慢 SQL 优化等高热度话题集中出现的表。订单金额字段必须用 DECIMAL(10,2)不能用 FLOAT 或 DOUBLE。外卖订单有大量叠加优惠、满减、配送费减免浮点二进制的舍入误差会导致结算对不上账。订单状态字段我推荐用 TINYINT 存储数字状态码状态枚举在 Java 层用枚举类管理数据库里不直接存中文状态名否则后期改一个状态名要全表 UPDATE。订单表还要加一个 version 字段用于状态更新时的乐观锁控制。骑手接单、商家出餐、用户取消多个动作可能同时打到同一订单上没有 version 的话会出现“已取消的订单被标记成配送中”的脏更新。CREATE TABLE t_order ( id BIGINT NOT NULL AUTO_INCREMENT, order_no VARCHAR(32) NOT NULL COMMENT 业务订单号, user_id BIGINT NOT NULL, shop_id BIGINT NOT NULL, total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2备餐中 3配送中 4已完成 5已取消, address_snapshot VARCHAR(255) NOT NULL COMMENT 收货地址快照, version INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id_created (user_id, created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单表;address_snapshot 是个很容易被忽略的点。订单关联地址必须用地址快照而不是地址表外键否则用户下单后修改默认地址骑手看到的收货地址就变成新地址了。这个字段承载的是反范式冗余思想和前面说过的商品名称快照同理。订单号用唯一索引而不用主键是因为业务上经常要按 order_no 精确查询同时主键还是要保留自增 ID 以维持 InnoDB 聚簇索引的顺序写入。3.3 商品表与 SKU 表价格单位、上下架状态与库存拆分商品表与 SKU 表是一对多商品表存公共属性SKU 表存价格、库存、图片、规格描述。价格类型同样是 DECIMAL(10,2)但接入支付系统时如果需要以分为单位可以在接口层转换数据库里仍然保持元为单位避免到处写除以 100 的逻辑导致精度错乱。上下架状态建议放在 SKU 层因为同一个商品的不同规格可以独立上下架这在做“售罄自动下架”时尤其重要。库存表建议单独建物理表字段就四个id、sku_id、stock_quantity、locked_quantity、updated_at。stock_quantity 是可用库存locked_quantity 是用户下单后锁定的数量。用户下单扣的是 locked_quantity支付成功才真正扣减 stock_quantity超时未支付要回补 locked_quantity。这种“双数量”设计可以避免一个经典问题用户下单锁定库存后支付失败时库存已经扣了或者支付成功时库存被别人抢走。关于库存的具体写库顺序在下一章讲状态机时会展开。4. 订单状态机与库存扣减外卖系统最容易翻车的写库逻辑外卖系统数据库设计里表结构只是骨架订单状态流转和库存扣减顺序才是血泪经验集中地。这两块如果没设计好系统上线后会有大量对不上的账和超卖订单。4.1 订单状态机状态变迁必须由事件驱动不能随便 UPDATE状态机设计的核心原则是状态只能按预设方向流转不能跳跃。外卖订单的标准状态流是待支付 → 已支付 → 备餐中 → 配送中 → 已完成中间可插入分支“已取消”待支付可取消备餐中商家可取消配送中不可取消。落库时要做到两点一是更新语句里带当前状态条件二是同一时刻只有一个动作能改状态。-- 支付成功状态从0流转到1 UPDATE t_order SET status 1, paid_at NOW(), version version 1 WHERE order_no xxx AND status 0 AND version 0;这段 SQL 的关键是 WHERE 条件里同时带了 status 和 version。如果应用层拿到订单时 version 是 0支付回调时发现 version 已经变成 1说明有其他请求改过这条记录本次更新影响行数为 0需要重新拉取订单状态再决定是否补偿。这就是乐观锁防止状态跳跃的落地写法。注意 paid_at 只在支付成功时写一次不要在待支付阶段就填值否则报表统计支付时长的数据会失真。4.2 库存扣减顺序先锁后扣扣减和回补必须成套库存扣减的经典做法是先对库存行加锁再执行扣减最后写流水。SQL 层面可以用原子更新实现UPDATE t_inventory SET stock_quantity stock_quantity - 1 WHERE sku_id ? AND stock_quantity 1。这个语句本身就能防止超卖因为条件里带了 stock_quantity 1相当于把“检查是否有库存”和“扣减”合并成一个原子操作。很多新手先 SELECT 查库存再判断再 UPDATE这三个步骤之间库存会被别的请求改掉就是超卖隐患。下单时锁定库存的正确顺序是先写订单主表状态为待支付再写订单明细然后扣减 locked_quantity最后写库存流水表。如果中途失败要回滚前面所有写操作。回补库存则相反先检查订单是否处于可取消状态再回补 locked_quantity最后把订单状态改成已取消。取消订单回补库存时直接用locked_quantity locked_quantity - 1而不是stock_quantity stock_quantity 1因为锁定部分还没变成实际扣减直接加可用库存会导致库存总数虚增。4.3 库存流水表每次变动都留痕对账全靠它库存流水表是订单与库存之间唯一的对账凭证。每次扣减和回补都要插一条流水字段包括id、order_no、sku_id、change_type1锁定 2支付扣减 3取消回补 4超时回补、change_quantity、before_quantity、after_quantity、created_at。这里的 before 和 after 是快照值不是计算结果。比如锁定前库存是 10锁定后是 9流水里就同时记录 10 和 9。有了流水表日终对账就能直接跑一句 SQL按当天流水的 change_type 汇总跟订单表的支付成功订单数量、取消订单数量做比对。如果两边数量对不上说明代码里有某条路径漏写了流水。设计时优先保证“每个订单动作都有流水”比事后分析日志定位要省力得多。这也是为什么很多外卖系统即使上了 NoSQL核心订单和库存仍然留在关系型数据库里——流水和状态的强一致性关系型数据库的事务机制最稳妥。5. 外卖数据库设计避坑与常见问题排查清单本节把我在设计、评审、上线外卖系统数据库时遇到过的真实坑总结成清单。每一条都按“现象 → 原因 → 解决”来写方便你直接对照排查。5.1 金额字段用 FLOAT结算报表对不上现象订单金额明细和支付对账单差异 0.01 元反复查代码找不出问题。原因FLOAT 是浮点数二进制无法精确表示 0.1多个金额累加时误差累积。解决所有金额字段改成 DECIMAL(10,2)加减运算在数据库层完成应用层只做展示。这个坑在 SQL 面试题里也经常作为考点出现本质是考浮点数精度。5.2 订单明细没有冗余商品快照商家改价后历史订单错乱现象用户查看历史订单商品价格显示成当前价格报表统计某商品 GMV 时数值异常。原因订单明细只存了 SKU ID查询时联商品表取价格。解决在设计阶段就在订单明细表冗余下单时的商品名称、价格、图片字段。记住一个原则订单明细是交易快照不是商品表的子表。5.3 状态更新没有乐观锁超时未支付和用户取消同时触发现象用户点击取消支付同时超时任务也执行取消逻辑订单被重复回补库存库存多出 1。原因两段代码都先 SELECT 订单再 UPDATE 状态没有带版本条件。解决更新语句加AND version ?影响行数为 0 时抛异常并重新查询状态。5.4 未使用 utf8mb4用户昵称带 Emoji 导致写入报错现象个别用户昵称是 Emoji注册时数据库报 Incorrect string value 错误或者昵称显示成问号。原因utf8 字符集最多 3 字节Emoji 是 4 字节。解决建库时统一用 utf8mb4。注意改库字符集要连表一起改只改库不改表写入时还会报错。这条是实战高频坑SQL 报错信息里带着 sql 语句长度和内容排查方向很容易被误导。5.5 订单表复合索引顺序写反用户订单列表翻页慢现象用户订单列表页翻到第 5 页之后查询耗时从 50ms 涨到 800ms 以上。原因索引建成了(created_at, user_id)而查询条件是WHERE user_id ? ORDER BY created_at DESC导致索引失效走全表扫描。解决索引顺序改成(user_id, created_at)过滤条件放左边排序字段放右边。这个顺序问题在慢 SQL 优化里属于必查项可以用 EXPLAIN 快速验证。6. 慢 SQL 优化与订单查询压测把用户订单页从 800ms 压到 80ms 的手法最后一章讲一个具体技巧针对订单列表页的慢查询优化。这是外卖系统上线后最常遇到的性能问题也是慢 SQL 优化话题里最典型的场景。先用 EXPLAIN 定位问题。如果看到type ALL或rows扫描行数等于全表行数说明索引没走。优化命令如下-- 查看当前执行计划 EXPLAIN SELECT * FROM t_order WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20; -- 添加复合索引 ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at); -- 再次验证 EXPLAIN SELECT * FROM t_order WHERE user_id 1001 ORDER BY created_at DESC LIMIT 20;第一次 EXPLAIN 如果 type 是 ALL说明排序文件存在磁盘临时表加完索引后 type 变成 refExtra 里出现 Using index condition 或者只剩 Using filesort 消失就说明索引生效了。注意如果订单表已经有 1000 万行数据ALTER TABLE 加索引会锁表需要在业务低峰期执行或者用在线 DDL 工具。索引加完后继续压测翻页接口。翻页深了会有深度分页问题也就是 LIMIT 100000, 20 这种写法会把前 10 万行全部扫一遍再丢弃。常见做法是改成游标分页用上一页最后一条记录的 created_at 作为查询条件SQL 写法如下SELECT * FROM t_order WHERE user_id 1001 AND (created_at, id) (2024-11-02 12:00:00, 100078) ORDER BY created_at DESC LIMIT 20;这种写法用到了行值比较MySQL 会按联合索引的 B 树定位到指定位置直接向后扫描 20 条扫描量固定为 20 行不会随着页码增长。外层接口用循环记录上一次返回的最小时间戳和 ID形成透传参数。翻页到 100 页之后这种写法的耗时和第一页基本一致。就这个优化的完整经验来说最大的教训是加索引前一定要先用 EXPLAIN 看执行计划不要凭直觉猜。我见过很多次“索引加了但没用上”原因就是 WHERE 条件字段做了函数运算比如WHERE DATE(created_at) 2024-11-02导致索引失效。如果时间字段必须按天过滤改成created_at 2024-11-02 00:00:00 AND created_at 2024-11-03 00:00:00就能走索引。数据库优化里这种细节决定成败的地方还有很多每次翻车后根据执行计划调整通常都能把问题解决掉。希望帮到你。本文还有配套的精品资源点击获取