MySQL购物网站数据库设计：核心表结构、索引与库存扣减事务

简介：面向学习 MySQL 数据库设计与商城类项目建模的学生和开发者，这份资源围绕“购物网站系统数据库设计”展开，可用于课程设计、数据库实验或小型电商项目前期方案参考，重点解决用户、地址、商品、购物车、订单及订单项等核心数据如何组织的问题。内容从数据需求与处理需求两侧梳理 MyShop 商城系统，覆盖账号密码邮箱、收货地址与联系方式、商品类别与详情、购物车记录、订单管理以及订单项明细等对象，并对应注册登录、权限控制、商品录入更新查询、购物车维护、订单生成处理和地址管理等业务场景。资源以 zip 压缩包提供，体积约 196.67MB；上游未提供文件总数与类型明细，具体构成以下载后目录为准。目前已有 393 人学习，适合用来理解商城数据库表结构划分、实体关系与功能映射，并作为后续建表和业务逻辑设计的参考。

1. 从一张订单表说起：购物网站数据库到底该建几张表

很多人第一次做购物网站系统，是把商品名、收货人、支付状态全塞进一张orders表，几千单时毫无感觉，等到要做「按商品维度看销量」或者「同一个用户复购分析」时，才发现数据根本拆不开。MySQL 数据库在电商场景里真正难的地方不是写 SQL，而是在建模阶段就决定好哪张表冗余、哪张表拆开、哪个字段留快照。这份资源围绕购物网站系统的数据库设计展开，从会员、商品、SKU、购物车，到订单、明细、支付、库存、评价，把表结构、字段类型、约束、索引、事务边界一层层落成能直接执行的 DDL。它适合正在做课程设计的同学照着复现，也适合第一次独立负责电商库的后端，拿来对照自己手上的表是不是漏了快照字段、是不是把金额写成了 FLOAT。

2. 表结构与字段选型：11 张核心表的建表顺序与字段类型

建表顺序不是随便排的。我的习惯是先定「不变的东西」——会员、分类、商品，再定「会变的东西」——库存、购物车，最后定「只增不改的东西」——订单、明细、支付流水。顺序反了，很容易在订单表里塞进一堆本该属于商品的字段，后面想拆就得洗数据。

2.1 先划实体边界：哪些数据必须独立成表

判断标准只有一条：这个对象的生命周期和主对象是否一致。商品下架了，历史订单里的商品名还得能看，所以订单明细必须存快照而不是只存product_id；收货地址改了，两年前的订单还得发货到老地址，所以订单表必须存地址快照，而不是每次 JOIN 地址表。

按这条标准，购物网站系统至少要拆出这些表：

十张上下是合理的，少于八张基本就是把该拆的合了，多于十五张则要考虑是不是把统计报表也塞进了业务库。血泪经验是：stock_log这种流水表一定要单独建，出问题的时候它是唯一能回溯「谁在什么时候把库存改成了负数」的黑匣子。

2.2 会员、商品、分类三张基础表的 DDL

先看会员表。登录名、密码哈希、状态放主表，昵称头像这类可改可不改的放扩展表也行，小项目合在一起问题不大。

-- 会员主表：登录凭证与账户状态 CREATE TABLE `member` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '会员ID', `mobile` CHAR(11) NOT NULL COMMENT '手机号，登录名，定长11', `password_hash` CHAR(60) NOT NULL COMMENT 'bcrypt 结果固定60位', `nickname` VARCHAR(32) NOT NULL DEFAULT '' COMMENT '昵称', `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1正常 2冻结 3注销', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`), UNIQUE KEY `uk_mobile` (`mobile`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='会员主表';

逻辑说明：手机号用CHAR(11)而不是VARCHAR，因为长度绝对固定，定长在索引里更省空间；uk_mobile唯一索引既保证账号不重复，也天然成为手机号登录的查询路径。密码哈希用CHAR(60)是 bcrypt 的输出长度，换成VARCHAR(255)会让二级索引页能塞的条目变少。

参数说明：DATETIME(3)保留毫秒，是因为订单相关操作在同一秒内可能有多条记录，用秒级时间排序会乱序；utf8mb4_0900_ai_ci是 MySQL 8.0 的默认排序规则，支持 emoji，评价表里用户发个表情不会插入失败。

商品和 SKU 是必须拆的。SPU 存「这是什么」，SKU 存「具体哪个规格、卖多少钱、还有几件」：

-- 商品 SPU：列表页和详情页的公共信息 CREATE TABLE `product` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `category_id` BIGINT UNSIGNED NOT NULL COMMENT '末级分类ID', `title` VARCHAR(120) NOT NULL COMMENT '商品标题', `subtitle` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '卖点副标题', `main_image` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '主图URL', `min_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '冗余最低SKU价，列表页排序用', `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0草稿 1上架 2下架', `attrs` JSON NULL COMMENT '品牌/材质等扩展属性', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`), KEY `idx_cat_status_price` (`category_id`, `status`, `min_price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品SPU表'; -- 规格 SKU：真正下单和扣库存的对象 CREATE TABLE `sku` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `product_id` BIGINT UNSIGNED NOT NULL, `spec_json` JSON NOT NULL COMMENT '{"颜色":"黑","尺码":"42"}', `price` DECIMAL(12,2) NOT NULL, `stock` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '可售库存，无符号防负数', `version` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '乐观锁版本号', PRIMARY KEY (`id`), KEY `idx_product` (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品SKU表';

逻辑说明：min_price是刻意冗余的。商品列表页要按价格排序，如果每次都去 SKU 表聚合MIN(price)，一页二十条就要扫二十次子表，翻到第五页基本就卡住了。冗余一个最低价，代价是 SKU 改价时同步更新，这个更新放在同一个事务里就行。

参数说明：stock用INT UNSIGNED，无符号不是为了省那一位，而是让「扣成负数」在数据库层面直接报错，比事后查日志强。spec_json用 JSON 存规格，是因为不同类目的规格维度差别太大，服装是颜色尺码、电器是容量功率，用固定列会被迫建几十个空字段。

2.3 订单、明细与库存：金额字段为什么必须是 DECIMAL

订单表是整个库的中心，字段取舍直接决定后面几年好不好过。

-- 订单主表：只增不改，状态用状态机推进 CREATE TABLE `orders` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_no` CHAR(20) NOT NULL COMMENT '业务单号，对外展示', `member_id` BIGINT UNSIGNED NOT NULL, `total_amount` DECIMAL(12,2) NOT NULL COMMENT '商品金额合计', `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额', `freight_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '运费', `pay_amount` DECIMAL(12,2) NOT NULL COMMENT '应付=合计-优惠+运费', `status` TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消 5退款中', `pay_time` DATETIME(3) NULL, `address_snapshot` JSON NOT NULL COMMENT '收货人/电话/地址快照', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`), KEY `idx_member_created` (`member_id`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单主表';

逻辑说明：order_no和id分开，是为了对外不暴露业务量。自增主键在第 100 万单时会暴露订单规模，也方便后面做分库分表时替换主键策略。address_snapshot用 JSON 而不是三个固定列，是因为地址字段随业务变化（加个「门牌号 2」、加个「驿站编号」）很频繁，快照字段一改就要 DDL 锁表。

参数说明：金额一律DECIMAL(12,2)。这里没有商量余地，FLOAT存 0.1+0.2 会得到 0.30000000000000004，对账时差一分钱都能查一整天。(12,2)表示总共十二位、小数两位，支撑到百亿级金额，够用到这个业务结束。

订单明细表的关键是快照：

CREATE TABLE `order_item` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_id` BIGINT UNSIGNED NOT NULL, `order_no` CHAR(20) NOT NULL COMMENT '冗余单号，便于按单号直查', `sku_id` BIGINT UNSIGNED NOT NULL, `product_id` BIGINT UNSIGNED NOT NULL, `title_snapshot` VARCHAR(120) NOT NULL COMMENT '下单时商品标题', `spec_snapshot` VARCHAR(120) NOT NULL DEFAULT '' COMMENT '下单时规格描述', `price` DECIMAL(12,2) NOT NULL COMMENT '下单时单价', `quantity` INT UNSIGNED NOT NULL, `amount` DECIMAL(12,2) NOT NULL COMMENT 'price*quantity', PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_sku` (`sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单明细表';

逻辑说明：明细里冗余了order_no，看起来违反范式，但后台客服最常见的操作是「用户报单号，我要看这单买了什么」，有这列就能直接走单号定位到明细，省掉一次从主表拿order_id的回表。第三范式在电商里是个参考，不是戒律。

参数说明：price和amount都存实际值而不是靠quantity现算，因为退款时可能只退部分数量，现算会和支付流水对不上。spec_snapshot存字符串而不是引用spec_json，同理，SKU 改名后历史订单必须保持原样。

2.4 购物车、地址、评价这类附属表的字段取舍

购物车表最常见的错误是把它设计成「临时表」，一清空就没了。实际业务里购物车要能跨设备同步，所以必须有member_id：

CREATE TABLE `cart_item` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `member_id` BIGINT UNSIGNED NOT NULL, `sku_id` BIGINT UNSIGNED NOT NULL, `quantity` INT UNSIGNED NOT NULL DEFAULT 1, `checked` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1勾选 0未勾选', `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`), UNIQUE KEY `uk_member_sku` (`member_id`, `sku_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='购物车';

逻辑说明：uk_member_sku是这张表的核心。没有这个唯一索引，用户连点两次「加入购物车」就会产生两行同 SKU 记录，结算时数量翻倍，是电商里最经典的翻车之一。有了它，加购逻辑直接写成INSERT ... ON DUPLICATE KEY UPDATE quantity = quantity + ?，一行 SQL 解决并发重复。

参数说明：checked用来记住勾选状态，用户下次打开 App 时按上次勾选恢复，这个字段看着小，缺了体验会掉一档。updated_at上带ON UPDATE，清理半年未动的僵尸购物车时按它筛。

评价表要注意的是「评价的对象是订单明细，不是商品」：

CREATE TABLE `review` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_item_id` BIGINT UNSIGNED NOT NULL COMMENT '一单一评，防刷', `product_id` BIGINT UNSIGNED NOT NULL, `member_id` BIGINT UNSIGNED NOT NULL, `score` TINYINT UNSIGNED NOT NULL COMMENT '1-5星', `content` VARCHAR(1000) NOT NULL DEFAULT '', `images` JSON NULL COMMENT '晒图URL数组', `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), PRIMARY KEY (`id`), UNIQUE KEY `uk_order_item` (`order_item_id`), KEY `idx_product_created` (`product_id`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='商品评价';

逻辑说明：唯一键建在order_item_id上，一行明细只能评一次，这是防刷最省事的做法，比在应用层查一遍再插入可靠得多。idx_product_created支撑商品详情页「按时间倒序取前 20 条评价」这个最高频查询。

参数说明：score用TINYINT UNSIGNED，取值范围 1-5，业务层校验即可，不必上CHECK约束——真到了数据修复的时候，约束会变成阻碍。images用 JSON 存数组，因为晒图数量不定，拆成子表在这个量级上收益不划算。

3. 约束与索引：把唯一性和查询路径提前焊死在表上

表建完只是骨架，真正决定这套库能不能撑住的是索引。我见过太多项目，表设计得漂漂亮亮，上线一个月慢查询日志一天几百兆，翻开来一看全是没索引的ORDER BY create_time。这一章按「主键 → 联合索引 → 外键 → EXPLAIN 验证」的顺序往下走。

3.1 主键与业务单号：自增 BIGINT 之外的第二种选择

自增BIGINT UNSIGNED是默认答案，优点不用多说：写入顺序递增，B+ 树不会频繁分裂，页利用率高。但它有两个绕不过去的限制：一是分库分表后多库自增会撞号，二是对外暴露业务量。

常见做法是主键仍用自增或雪花 ID，另设一列order_no作为对外单号，格式是「年月日 + 6 位序列 + 2 位机器位」，例如202405180000123401。生成方式有两种：

-- 方式一：数据库自增序列表，取号后拼装 CREATE TABLE `seq_order_no` ( `biz_date` CHAR(8) NOT NULL COMMENT 'yyyyMMdd', `current` BIGINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (`biz_date`) ) ENGINE=InnoDB COMMENT='订单号日序列'; -- 取号：同一事务内自增并读回 INSERT INTO `seq_order_no` (`biz_date`, `current`) VALUES ('20240518', 1) ON DUPLICATE KEY UPDATE `current` = `current` + 1; SELECT `current` FROM `seq_order_no` WHERE `biz_date` = '20240518';

逻辑说明：按日期分行的序列表，每天一行，行锁竞争只发生在当天，量级可控。ON DUPLICATE KEY UPDATE保证首次插入和后续自增是同一条语句，不用先查再判。

参数说明：current用BIGINT UNSIGNED而不是INT，是给大促留余量，单日千万单不至于溢出。这里要注意，取号和写订单必须在同一个事务里，否则事务回滚后号段会出现空洞——空洞本身无害，但如果业务要求单号连续（对账场景偶尔有），就得换成预分配号段的方式。

3.2 联合索引的最左前缀：电商四个高频查询怎么建

电商库的查询其实高度集中，我一般先把这四条 SQL 写出来，再倒推索引：

-- Q1 我的订单列表 SELECT id, order_no, pay_amount, status, created_at FROM orders WHERE member_id = 10086 ORDER BY created_at DESC LIMIT 20; -- Q2 分类下的商品列表，按价格升序 SELECT id, title, main_image, min_price FROM product WHERE category_id = 33 AND status = 1 ORDER BY min_price ASC LIMIT 20; -- Q3 商品详情页的评价 SELECT id, score, content, created_at FROM review WHERE product_id = 5001 ORDER BY created_at DESC LIMIT 20; -- Q4 后台按单号查明细 SELECT sku_id, title_snapshot, price, quantity FROM order_item WHERE order_no = '202405180000123401';

对应的索引就是前面 DDL 里那几条：idx_member_created(member_id, created_at)、idx_cat_status_price(category_id, status, min_price)、idx_product_created(product_id, created_at)、idx_order_id之外再补一条idx_order_no。

逻辑说明：Q1 的索引顺序不能反。写成(created_at, member_id)，WHERE member_id = ?就用不上最左前缀，只能全表扫。同理 Q2 里status放中间，是因为category_id等值匹配后，status等值、min_price排序，三段刚好串起来，ORDER BY不再触发 filesort。

参数说明：LIMIT 20这种深分页要注意，LIMIT 100000, 20会让 MySQL 扫十万行再丢掉，索引帮不上忙。常见做法是改成「上一页最后一条的created_at作为游标」：WHERE member_id = ? AND created_at < ? ORDER BY created_at DESC LIMIT 20。这个改法在订单列表上几乎是标配。

3.3 外键取舍：物理外键、逻辑外键与级联删除的边界

这个问题争论了很多年，我的结论是：业务库不建物理外键，靠应用层保证，但唯一索引必须建。

原因是物理外键在高并发下会引入额外的锁检查和级联动作，ON DELETE CASCADE尤其危险——删一个会员，订单、明细、评价全被级联删掉，这在电商里是不可接受的数据事故。订单数据在法律和财务意义上都要保留，任何物理删除的想法都应该被拦下来。

替代方案是逻辑外键 + 定期校验：

-- 定期跑的一致性巡检：找出孤儿明细 SELECT oi.id, oi.order_id FROM order_item oi LEFT JOIN orders o ON o.id = oi.order_id WHERE o.id IS NULL LIMIT 100;

逻辑说明：这条 SQL 走order_item.idx_order_id和orders主键，几十万行也就几秒，放在低峰期跑完全可接受。真查出孤儿数据，说明应用层写明细时事务没包住，问题在代码而不是数据库。

参数说明：LIMIT 100是防止一次查出几万行把客户端内存打爆，巡检脚本分页拉取即可。真要禁止删除，用逻辑删除字段is_deleted配合应用层拦截，比数据库层面的CASCADE可控得多。

3.4 用 EXPLAIN 验证：三行输出看懂索引有没有吃到

索引建完必须验证，不能靠「我觉得应该走了」。

EXPLAIN SELECT id, order_no, pay_amount FROM orders WHERE member_id = 10086 ORDER BY created_at DESC LIMIT 20;

看三个字段就够：type应该是ref而不是ALL；key应该是idx_member_created；Extra里不应该出现Using filesort。三个里有一个不对，就说明索引设计或者 SQL 写法有问题。

逻辑说明：如果Extra出现Using filesort，先检查排序方向。MySQL 8.0 之前联合索引不能混用升降序，ORDER BY created_at DESC遇上索引里的升序列会退化成排序；8.0 之后可以建(member_id, created_at DESC)解决。

参数说明：EXPLAIN的rows列是估算值，别太当真，但它和实际行数差两个数量级时，说明统计信息过期了，跑一次ANALYZE TABLE orders;通常能修正。生产环境我更习惯用EXPLAIN ANALYZE（8.0.18 起支持），它给的是真实执行耗时，比估算值靠谱。

4. 下单链路的事务设计：库存扣减、隔离级别与并发压测

前面都是静态设计，这一章进入动态部分。下单是整个购物网站系统里唯一同时碰订单、明细、库存、优惠、支付五张表的操作，事务边界画错，后面全是坑。

4.1 一次下单动几张表：事务边界画在哪

标准下单链路是这样的：校验购物车 → 校验库存 → 扣库存 → 写订单主表 → 写订单明细 → 扣优惠券 → 清购物车 → 提交事务 → 异步发消息。

关键在于「异步」两个字。支付回调、短信通知、库存同步到搜索，这些都要放在事务提交之后做。血泪经验：早期把发消息写在事务里，结果消息中间件偶尔超时三秒，事务就挂三秒，行锁跟着挂三秒，高峰期连接池直接打满，整站 502。

事务边界应该只包住数据库写入：

START TRANSACTION; -- 1. 扣库存，带条件防止超卖 UPDATE sku SET stock = stock - 2, version = version + 1 WHERE id = 8801 AND stock >= 2; -- 2. 写订单主表 INSERT INTO orders (order_no, member_id, total_amount, pay_amount, status) VALUES ('202405180000123401', 10086, 398.00, 388.00, 0); -- 3. 写明细（order_id 取上一步的 LAST_INSERT_ID） INSERT INTO order_item (order_id, order_no, sku_id, product_id, title_snapshot, price, quantity, amount) VALUES (LAST_INSERT_ID(), '202405180000123401', 8801, 5001, '某款运动鞋', 199.00, 2, 398.00); COMMIT;

逻辑说明：第 1 步的AND stock >= 2是整个下单链路里最重要的一行。它在UPDATE时就完成了「检查 + 扣减」的原子操作，InnoDB 会对这一行加排他锁，两个并发请求会串行执行，第二个请求发现stock已经不够就更新 0 行。应用层判断影响行数为 0 就回滚并提示「库存不足」。

参数说明：version列在这里其实用不上，它是给乐观锁方案准备的，两种方案二选一即可。如果走条件更新，version可以保留用于排查；如果走乐观锁，WHERE里要加AND version = ?，冲突时重试。真实项目里条件更新更简单，冲突率低时性能也更好。

4.2 乐观锁与悲观锁扣库存的两种 SQL

两种写法都给出来，按场景选：

-- 方案 A：乐观锁，先查后改，靠 version 兜底 SELECT stock, version FROM sku WHERE id = 8801; -- 应用层判断 stock >= 2 后执行 UPDATE sku SET stock = stock - 2, version = version + 1 WHERE id = 8801 AND version = 7; -- 影响行数为 0 说明被别的请求抢先改了，重试或直接失败 -- 方案 B：悲观锁，先锁行再判断 START TRANSACTION; SELECT stock FROM sku WHERE id = 8801 FOR UPDATE; -- 应用层判断后再更新 UPDATE sku SET stock = stock - 2 WHERE id = 8801; COMMIT;

逻辑说明：方案 A 读的时候不加锁，并发高时大量请求会读到同一个version，然后只有一个能更新成功，其余全部重试。它在「热点商品秒杀」场景下重试率高得吓人。方案 B 的FOR UPDATE直接排队，逻辑简单但锁持有时间长，事务里不能有任何远程调用。

参数说明：FOR UPDATE必须走主键或唯一索引，否则会升级成间隙锁，锁住一片范围，隔壁 SKU 也跟着排队。这是最容易被忽略的一点——WHERE spec_json->>'$.颜色' = '黑'这种条件加FOR UPDATE，锁的范围能吓死人。

我的取舍是：普通商品下单用方案 A 的条件更新（本质是方案 A 的简化版，不需要先 SELECT），秒杀场景用方案 B 加队列削峰，两者不混用。

4.3 REPEATABLE READ 下的幻读与唯一索引兜底

MySQL 默认隔离级别是REPEATABLE READ，在这个级别下，普通的SELECT是快照读，不加锁；SELECT ... FOR UPDATE是当前读，加锁。很多「库存查出来还有，扣的时候没了」的诡异现象，根源是快照读和当前读混用。

-- 事务 A：快照读，看到 stock = 5 SELECT stock FROM sku WHERE id = 8801; -- 事务 B 此时扣掉了 4 件并提交 -- 事务 A 继续用快照值判断，扣 5 件，结果为 -4（如果没加条件）

逻辑说明：解决办法就是 4.1 里那条带条件的UPDATE，它是当前读，永远看最新已提交的数据。快照值只能用来做展示，不能用来做扣减判断。这是我在生产环境里反复验证过的一条铁律。

参数说明：唯一索引在防幻读上有奇效。比如「同一用户同一活动只能领一张券」，与其在事务里SELECT ... FOR UPDATE判断存在性，不如直接给(member_id, activity_id)建唯一索引，插入冲突就返回「已领取」。这种方式不依赖隔离级别，实现也最省事。

4.4 用并发脚本验证超卖

设计完必须压一遍。用 Python 起 200 个线程打同一个 SKU，比任何口头论证都可靠：

import threading, pymysql SKU_ID, THREADS, PER = 8801, 200, 1 ok, fail = [], [] def buy(): conn = pymysql.connect(host='127.0.0.1', user='app', password='***', database='shop', autocommit=False) try: with conn.cursor() as cur: # 条件更新：库存不足时影响行数为 0 n = cur.execute( "UPDATE sku SET stock = stock - %s WHERE id = %s AND stock >= %s", (PER, SKU_ID, PER)) if n == 1: conn.commit() ok.append(1) else: conn.rollback() fail.append(1) finally: conn.close() ts = [threading.Thread(target=buy) for _ in range(THREADS)] [t.start() for t in ts] [t.join() for t in ts] print(f"成功 {len(ok)} 失败 {len(fail)}")

逻辑说明：每个线程独立连接，模拟真实并发。跑完去查SELECT stock FROM sku WHERE id = 8801，结果必须等于「初始库存 - 成功数」，一分不差。如果出现负数，说明条件更新写漏了或者隔离级别被改过。

参数说明：THREADS设成 200 已经能压出大部分问题，想更真实可以调大并加随机延迟。autocommit=False必须显式设，很多框架默认自动提交，事务根本没生效，压出来的结果是假的。

5. 避坑与排查：上线后最容易翻车的 5 个现场

这一章全是真金白银换来的。每一条我都按「现象 → 原因 → 解决」写，能对上号就直接抄。

5.1 数据类型与字符集的三次翻车

第一条：订单状态莫名变成 0。

现象是后台看到一批订单明明已支付，状态却是「待支付」。查日志发现更新语句执行成功。原因出在status字段用了TINYINT（有符号），应用层枚举里写了 128 表示「退款完成」，插入时溢出被截断成 -128，再被某段逻辑当作 0 处理。解决是把所有状态字段统一改成TINYINT UNSIGNED，并在应用层加一层枚举校验，超出 0-127 直接抛异常而不是静默截断。

第二条：用户评价带表情提交失败。

现象是评价接口隔三差五报Incorrect string value。原因是建表时用了utf8（三字节），而 emoji 需要四字节。utf8在 MySQL 里不是真正的 UTF-8，这个命名本身就是历史遗留的坑。解决是把库、表、连接三处的字符集全部统一成utf8mb4，连接串里加上charset=utf8mb4，只改表不改连接照样报错。

第三条：金额对账差一分钱。

现象是月底对账，订单表和支付流水的总金额差了几毛。原因是早期有一张表用了FLOAT存优惠金额，累加时精度丢失。解决是把所有金额列改成DECIMAL(12,2)，并且约定应用层也用BigDecimal或整数分，禁止double参与金额运算。改完还要跑一遍历史数据修正脚本，用ROUND(x, 2)重新落库。

5.2 索引与事务的两条血泪记录

第四条：订单列表翻到第三页就转圈。

现象是「我的订单」前两页很快，第三页开始要三秒以上。原因是分页写成了LIMIT 40, 20，随页码增大扫描行数线性增长。原因确认后，解决方式改成游标分页：把上一页最后一条的created_at和id传给后端，用WHERE member_id = ? AND (created_at, id) < (?, ?)定位。改完之后第一百页也是毫秒级。

第五条：大促开始十分钟连接池耗尽。

现象是监控上活跃连接数瞬间打满，接口大面积超时，但数据库 CPU 只有 30%。原因是下单事务里调用了优惠券服务做远程校验，那个服务响应从 20ms 抖到 2s，事务跟着挂起，行锁不释放，后续请求全排队。解决是三步：把远程校验挪到事务开启之前；给事务加超时（innodb_lock_wait_timeout调到 3 秒）；给远程调用加熔断降级，超时就直接走「不使用优惠券」的兜底路径。改完压测，同样的流量下连接数只到峰值的四成。

这五条有个共同点：都不是 SQL 写错，而是设计阶段的假设在真实流量下不成立。所以我现在的习惯是，任何一张核心表建完，先写三条最狠的并发场景，跑不通就不算设计完成。

6. 进阶：从单库扛到大促前的三件事

单库单表撑到百万订单是个坎。往下走不用急着上分库分表，先把这三件事做完，通常还能再撑一两年。

6.1 订单表按月归档与分区表

orders表超过两千万行后，即使有索引，COUNT和后台导出也会明显变慢。最常见的做法是按月分区：

ALTER TABLE orders PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202405 VALUES LESS THAN (TO_DAYS('2024-06-01')), PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01')), PARTITION p202407 VALUES LESS THAN (TO_DAYS('2024-08-01')), PARTITION pmax VALUES LESS THAN MAXVALUE );

逻辑说明：分区后查询带上created_at条件会自动裁剪分区，历史数据导出直接SELECT某个月，扫的是单个分区。要注意分区键必须进主键，所以orders的主键得改成(id, created_at)，这一步改动会影响关联查询，得提前评估。

参数说明：分区不是越多越好，超过一百个分区元数据开销会明显上升。我的做法是保留最近 12 个月的热分区，更早的数据EXCHANGE PARTITION到归档表再导出，热库只留一年。

6.2 慢查询日志与压测脚本

最后一步是把验证做成习惯。开慢查询日志，阈值设 1 秒，每周捞一次 top 10：

SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; SET GLOBAL log_queries_not_using_indexes = ON; -- 用 performance_schema 汇总，比翻日志文件快 SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT/1e12 AS avg_sec FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

逻辑说明：events_statements_summary_by_digest按 SQL 模板聚合，能直接看出哪类语句最耗时间，比 grep 日志文件高效得多。log_queries_not_using_indexes打开后会记录所有没走索引的语句，小表会刷屏，量大的库建议关掉。

参数说明：long_query_time设 1 秒是起步值，业务稳定后可以压到 0.2 秒，提前发现问题。压测用 sysbench 或自己写的并发脚本都行，重点不是工具，而是每次改完索引或事务逻辑都重跑一遍同一组场景，对比QPS和P99的变化。

从那以后我每接一个电商库，都会先把订单表的写入路径完整画一遍，再动手建表；索引也不是拍脑袋加，而是先写出四条最高频的查询 SQL 再倒推。这套顺序救过我不少次，也省掉了大量上线后的排查时间。希望帮到你。

本文还有配套的精品资源，点击获取