第34章实战：电商数据库设计与优化

本章目标

完整设计一个电商系统的数据库架构
掌握 ER 图到 CREATE TABLE 的完整转换流程
理解每个索引的设计理由（不只是"要不要加"，而是"为什么这样加"）
掌握深分页优化在电商场景中的应用
理解高并发秒杀场景下的库存扣减策略
能够独立完成一条慢查询从发现到优化的完整流程

前置知识

掌握 CREATE TABLE、索引创建、外键约束
了解 EXPLAIN 执行计划分析
理解事务隔离级别和行锁
了解慢查询日志

34.1 业务需求分析

34.1.1 核心功能

用户端：
├── 浏览商品（分类浏览、搜索、分页、排序）
├── 加入购物车（增加、减少数量、删除）
├── 下订单（生成订单、扣减库存）
└── 查看订单（订单列表、订单详情）

管理端：
├── 商品管理（CRUD、上下架）
├── 分类管理
├── 订单管理（查看、改状态）
└── 库存管理（入库、盘点、出库记录）

34.1.2 ER 图（文字描述）

users ──1:N──> orders ──1:N──> order_items <──N:1── products
users ──1:N──> cart_items <──N:1── products
categories ──1:N──> products
categories ──1:N──> categories (self-referencing: parent_id)
products ──1:N──> inventory_log

34.2 完整表结构设计

34.2.1 用户表 (users)

sql

CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  password_hash CHAR(60) NOT NULL COMMENT 'bcrypt 密码哈希',
  email VARCHAR(254) NOT NULL COMMENT '邮箱',
  phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '手机号',
  avatar_url VARCHAR(500) DEFAULT '' COMMENT '头像URL',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=正常 0=禁用',
  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_username (username),
  UNIQUE KEY uk_email (email),
  KEY idx_phone (phone),
  KEY idx_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

34.2.2 商品分类表 (categories)

sql

CREATE TABLE categories (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '分类ID',
  name VARCHAR(100) NOT NULL COMMENT '分类名称',
  parent_id INT UNSIGNED DEFAULT NULL COMMENT '父分类ID（NULL=顶级分类）',
  sort_order INT NOT NULL DEFAULT 0 COMMENT '排序值（越小越前）',
  icon VARCHAR(200) DEFAULT '' COMMENT '分类图标URL',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_name (name),
  KEY idx_parent_sort (parent_id, sort_order),
  CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品分类表（支持无限级分类）';

34.2.3 商品表 (products)

sql

CREATE TABLE products (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
  name VARCHAR(200) NOT NULL COMMENT '商品名称',
  subtitle VARCHAR(200) DEFAULT '' COMMENT '副标题/卖点',
  description TEXT COMMENT '商品描述（详情页）',
  price DECIMAL(10,2) NOT NULL COMMENT '销售价格',
  cost_price DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '成本价',
  stock_quantity INT NOT NULL DEFAULT 0 COMMENT '库存数量',
  sold_quantity INT NOT NULL DEFAULT 0 COMMENT '已售数量（冗余统计）',
  main_image VARCHAR(500) DEFAULT '' COMMENT '主图URL',
  images JSON DEFAULT NULL COMMENT '图片列表 ["url1","url2",...]',
  status ENUM('draft','published','discontinued') NOT NULL DEFAULT 'draft' COMMENT '草稿/已发布/已停产',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_category_id (category_id),
  KEY idx_name (name),
  KEY idx_status_created (status, created_at),
  KEY idx_price (price),
  FULLTEXT KEY ft_name_desc (name, description),
  CONSTRAINT fk_prod_category FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品表';

products 表索引设计理由：

text

idx_category_id            → 按分类浏览商品：WHERE category_id = ?
idx_name                   → 按名称精确搜索
idx_status_created         → 商品列表页：WHERE status='published' ORDER BY created_at DESC
                             覆盖索引：(status, created_at) 辅助高效分页
idx_price                  → 按价格排序/筛选
ft_name_desc               → 全文搜索：MATCH(name, description) AGAINST('keyword')
                             比 LIKE '%keyword%' 高效得多

34.2.4 订单表 (orders)

sql

CREATE TABLE orders (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  order_no VARCHAR(20) NOT NULL COMMENT '订单号（唯一，如 ORD20240115001）',
  user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(12,2) NOT NULL COMMENT '订单总金额',
  discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
  actual_amount DECIMAL(12,2) NOT NULL COMMENT '实付金额(=total-discount)',
  status ENUM('pending','paid','shipped','delivered','cancelled','refunded') NOT NULL DEFAULT 'pending' COMMENT '订单状态',
  shipping_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '收货人姓名',
  shipping_phone VARCHAR(20) NOT NULL DEFAULT '' COMMENT '收货人电话',
  shipping_address TEXT NOT NULL COMMENT '收货地址',
  tracking_no VARCHAR(50) DEFAULT '' COMMENT '物流单号',
  paid_at DATETIME DEFAULT NULL COMMENT '支付时间',
  shipped_at DATETIME DEFAULT NULL COMMENT '发货时间',
  delivered_at DATETIME DEFAULT NULL COMMENT '签收时间',
  cancelled_at DATETIME DEFAULT NULL COMMENT '取消时间',
  remark VARCHAR(500) DEFAULT '' COMMENT '用户备注',
  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_order_no (order_no),
  KEY idx_user_created (user_id, created_at),
  KEY idx_status_created (status, created_at),
  KEY idx_paid_at (paid_at),
  CONSTRAINT fk_order_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';

orders 表索引设计理由：

text

uk_order_no                → 按订单号精确查询（支付回调、用户查单号）
idx_user_created           → "我的订单"页：WHERE user_id=? ORDER BY created_at DESC
                             复合索引避免 filesort，覆盖用户最近订单查询
idx_status_created         → 管理后台订单管理：WHERE status='pending' ORDER BY created_at
                             复合索引支持按状态筛选+时间排序
idx_paid_at                → 对账查询：WHERE paid_at BETWEEN ? AND ?

34.2.5 订单明细表 (order_items)

sql

CREATE TABLE order_items (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '明细ID',
  order_id INT UNSIGNED NOT NULL COMMENT '订单ID',
  product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
  product_name VARCHAR(200) NOT NULL COMMENT '商品名称快照（下单时的名称）',
  product_main_image VARCHAR(500) DEFAULT '' COMMENT '商品主图快照',
  product_price DECIMAL(10,2) NOT NULL COMMENT '商品单价快照（下单时的价格）',
  quantity INT UNSIGNED NOT NULL COMMENT '购买数量',
  subtotal DECIMAL(12,2) NOT NULL COMMENT '小计(=price*quantity)',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_order_id (order_id),
  KEY idx_product_id (product_id),
  CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单明细表';

设计要点：

text

product_name 和 product_price 是快照值！
  原因：商品名称和价格可能会变化（涨价、改名），但历史订单必须保存下单时的原始信息。
  
  反例：如果 SELECT products.name FROM order_items JOIN products
       → 用户在半年后查看订单时看到的是修改后的商品名称，与下单时不同。

34.2.6 购物车表 (cart_items)

sql

CREATE TABLE cart_items (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '购物车项ID',
  user_id INT UNSIGNED NOT NULL COMMENT '用户ID',
  product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
  quantity INT UNSIGNED NOT NULL DEFAULT 1 COMMENT '数量',
  added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_user_product (user_id, product_id),
  CONSTRAINT fk_cart_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_cart_product FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='购物车表';

设计要点：

text

UNIQUE KEY uk_user_product (user_id, product_id)：
  同一用户对同一商品只允许一条购物车记录。
  
  添加到购物车时用 INSERT ... ON DUPLICATE KEY UPDATE：
    INSERT INTO cart_items (user_id, product_id, quantity) VALUES (1, 100, 1)
    ON DUPLICATE KEY UPDATE quantity = quantity + 1;
    
  避免了先 SELECT 检查是否存在再决定 INSERT 还是 UPDATE 的竞态条件。

34.2.7 库存变更日志表 (inventory_log)

sql

CREATE TABLE inventory_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '日志ID',
  product_id INT UNSIGNED NOT NULL COMMENT '商品ID',
  order_id INT UNSIGNED DEFAULT NULL COMMENT '关联订单ID（手动调整时可以为NULL）',
  quantity_change INT NOT NULL COMMENT '库存变化量（+入库/-出库）',
  quantity_after INT NOT NULL COMMENT '变更后库存',
  change_type ENUM('initial','purchase','sale','return','adjustment','cancel') NOT NULL COMMENT '变更类型',
  operator VARCHAR(50) DEFAULT 'system' COMMENT '操作人',
  remark VARCHAR(200) DEFAULT '' COMMENT '备注',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_product_time (product_id, created_at),
  KEY idx_order_id (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='库存变更日志表';

34.3 索引设计深入分析

34.3.1 为什么使用复合索引而不是单列索引？

sql

-- 场景：查询某用户最近 30 天的订单
SELECT * FROM orders 
WHERE user_id = 100 AND created_at >= '2024-06-01'
ORDER BY created_at DESC;

-- 方案 A：两个单列索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_created_at ON orders(created_at);
-- 效果：MySQL 只能使用其中一个索引，然后对结果排序（Using filesort）

-- 方案 B：一个复合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);
-- 效果：
--   1. user_id 等值筛选 → ref
--   2. created_at 范围筛选 → range
--   3. created_at 顺序与 ORDER BY 匹配 → 无需 filesort
--   一举三得！

-- EXPLAIN 对比：
-- 方案 A：type=ref, rows=1000, Extra=Using where; Using filesort
-- 方案 B：type=range, rows=1000, Extra=Using index condition（无 filesort）

34.3.2 覆盖索引在商品列表中的应用

sql

-- 商品列表页查询
SELECT id, name, price, main_image, stock_quantity
FROM products
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 0, 20;

-- 覆盖索引：(status, created_at, id, name, price, main_image, stock_quantity)
-- 太宽了！影响写入性能和内存使用

-- 实际设计（见 products 表）：idx_status_created (status, created_at)
-- 没有覆盖所有需要的列（name, price 等不在索引中）

-- 结论：不是所有查询都值得用覆盖索引
-- 决策因素：
--   1. 查询频率（列表页 → 高频 → 值得）
--   2. 列的基数（name, price 等值频繁变化 → 索引维护成本高）
--   3. SELECT 的列数（过多列 → 索引太宽 → 得不偿失）
--   在大多数情况下，(status, created_at) 已经足够优化此查询

34.4 关键业务场景 SQL 实现

34.4.1 商品搜索与分页

sql

-- 普通关键字搜索
SELECT id, name, price, main_image
FROM products
WHERE status = 'published'
  AND MATCH(name, description) AGAINST('蓝牙耳机' IN NATURAL LANGUAGE MODE)
ORDER BY created_at DESC
LIMIT 0, 20;

-- 按分类 + 价格筛选 + 排序
SELECT id, name, price, main_image
FROM products
WHERE status = 'published'
  AND category_id = 5
  AND price BETWEEN 100 AND 500
ORDER BY price ASC       -- 或 sold_quantity DESC（按销量）
LIMIT 0, 20;

-- 深分页优化：价格在 100-500 的分类 5 商品，第 5000 页
-- 方案：覆盖索引 + 延迟关联
SELECT p.id, p.name, p.price, p.main_image
FROM products p
JOIN (
  SELECT id FROM products
  WHERE status = 'published' AND category_id = 5 AND price BETWEEN 100 AND 500
  ORDER BY price ASC
  LIMIT 100000, 20
) AS tmp ON p.id = tmp.id;

34.4.2 下单流程（事务）

sql

-- 完整的下单流程
START TRANSACTION;

-- 1. 检查库存并锁定（行锁）
SELECT id, stock_quantity, price FROM products
WHERE id = 101 FOR UPDATE;
-- 假设 stock_quantity = 50, price = 299.00

-- 2. 库存充足 → 扣减库存（原子操作，避免 SELECT + UPDATE 的竞态条件）
UPDATE products
SET stock_quantity = stock_quantity - 2,
    sold_quantity = sold_quantity + 2
WHERE id = 101 AND stock_quantity >= 2;

-- 检查影响行数
-- 如果 ROW_COUNT() = 0 → 库存不足，ROLLBACK
-- 如果 ROW_COUNT() = 1 → 扣减成功

-- 3. 创建订单
INSERT INTO orders (order_no, user_id, total_amount, actual_amount,
                    status, shipping_name, shipping_phone, shipping_address)
VALUES ('ORD2024011500123', 100, 598.00, 598.00,
        'pending', '张三', '13800138000', '北京市朝阳区XX路XX号');

SET @order_id = LAST_INSERT_ID();

-- 4. 创建订单明细
INSERT INTO order_items (order_id, product_id, product_name, product_price, quantity, subtotal)
VALUES (@order_id, 101, '蓝牙耳机 Pro Max', 299.00, 2, 598.00);

-- 5. 记录库存变更日志
INSERT INTO inventory_log (product_id, order_id, quantity_change, quantity_after, change_type, operator)
VALUES (101, @order_id, -2,
        (SELECT stock_quantity FROM products WHERE id = 101),
        'sale', 'user_100');

-- 6. 清空购物车中该商品（如果有）
DELETE FROM cart_items WHERE user_id = 100 AND product_id = 101;

COMMIT;

34.4.3 我的订单列表（分页）

sql

-- 用户查看自己的订单（按时间倒序）
SELECT o.id, o.order_no, o.total_amount, o.status,
       o.created_at, oi.product_main_image, COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 100
GROUP BY o.id
ORDER BY o.created_at DESC
LIMIT 0, 10;

-- 如果需要先 GROUP BY 再分页（当需要聚合信息时），
-- 使用派生表优化：
SELECT o.*, tmp.item_count, tmp.first_image
FROM orders o
JOIN (
  SELECT order_id,
         COUNT(*) AS item_count,
         MIN(product_main_image) AS first_image
  FROM order_items
  GROUP BY order_id
) AS tmp ON o.id = tmp.order_id
WHERE o.user_id = 100
ORDER BY o.created_at DESC
LIMIT 0, 10;

34.4.4 管理端订单查询

sql

-- 筛选待发货订单（按时间排序）
SELECT o.*, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.paid_at ASC
LIMIT 50;

-- 统计每日订单量和销售额
SELECT
  DATE(created_at) AS order_date,
  COUNT(*) AS order_count,
  SUM(actual_amount) AS daily_revenue,
  AVG(actual_amount) AS avg_order_value
FROM orders
WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
  AND status NOT IN ('cancelled', 'refunded')
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

-- 热销商品排行
SELECT
  oi.product_id,
  p.name,
  SUM(oi.quantity) AS total_sold,
  SUM(oi.subtotal) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o ON oi.order_id = o.id
WHERE o.status NOT IN ('cancelled', 'refunded')
  AND o.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY oi.product_id, p.name
ORDER BY total_sold DESC
LIMIT 10;

34.5 高并发秒杀/抢购策略

34.5.1 库存扣减的正确姿势

sql

-- 错误做法 1：SELECT → 应用层判断 → UPDATE（竞态条件！）
-- 线程 A：SELECT stock FROM products WHERE id = 1; → 读到 stock=1
-- 线程 B：SELECT stock FROM products WHERE id = 1; → 读到 stock=1
-- 线程 A：UPDATE products SET stock = 0 WHERE id = 1; → 成功
-- 线程 B：UPDATE products SET stock = 0 WHERE id = 1; → 也成功！超卖了！

-- 错误做法 2：在应用层加互斥锁/分布式锁
-- 性能瓶颈，将并发变串行

-- 正确做法：利用 InnoDB 的原子更新 + 行锁
UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = ? AND stock_quantity >= 1;
-- 如果影响行数为 0 → 库存不足
-- 如果影响行数为 1 → 扣减成功

-- 正确做法背后的原理：
-- 1. WHERE stock_quantity >= 1 利用了 InnoDB 的行锁 + 条件过滤
-- 2. 并发更新同一行时，只有一条 UPDATE 能同时持有行锁
-- 3. 持有行锁的事务提交后，下一个事务看到更新后的 stock_quantity

34.5.2 连接池配置

sql

-- 应用层连接池配置（以 HikariCP 为例）
-- 最大连接数 = (CPU核心数 * 2) + 硬盘数
-- 但也要确保不超过 MySQL 的 max_connections
-- 
-- MySQL 连接数公式：pool_size × app_instance_count < max_connections - 20
-- 预留 20 个连接给管理用户

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';
SHOW VARIABLES LIKE 'max_connections';

34.5.3 读写分离

sql

-- 架构：
-- 写操作 → Master（主库）
-- 读操作 → Slave1, Slave2（从库）

-- 注意事项：
-- 1. 复制延迟：写入 Master 后立刻读 Slave 可能读到旧数据
--    解决：关键操作（如刚创建的订单）强制读 Master
-- 2. 实现方式：
--    应用层：路由中间件（ShardingSphere, MyCat, ProxySQL）
--    代码层：AOP 注解标记读写类型

-- 示例：ProxySQL 配置读写分离
-- INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply)
-- VALUES (1, 1, '^SELECT.*FOR UPDATE$', 0, 1),     -- SELECT ... FOR UPDATE → Master
--        (2, 1, '^SELECT', 1, 1);                  -- 普通 SELECT → Slaves

34.6 慢查询端到端优化实例

问题：管理端订单查询慢

1. 发现

sql

-- 慢查询日志中出现：
-- Query_time: 3.456789  Lock_time: 0.001234
-- Rows_sent: 20  Rows_examined: 523891
SELECT o.*, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01'
  AND o.status = 'pending'
ORDER BY o.id
LIMIT 20;

2. EXPLAIN 分析

sql

EXPLAIN SELECT o.*, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01'
  AND o.status = 'pending'
ORDER BY o.id
LIMIT 20\G

-- 结果：
-- id: 1
-- select_type: SIMPLE
-- table: o
-- type: ALL                  ← 全表扫描！
-- possible_keys: idx_status_created
-- key: NULL
-- rows: 523891
-- Extra: Using where; Using filesort

-- 分析：
-- idx_status_created (status, created_at)
-- 但优化器没有使用它！为什么？
-- 可能原因：
--   1. created_at 是范围条件（BETWEEN），status 是等值条件
--   2. 索引 (status, created_at) 的顺序：status 在前，created_at 在后
--   3. 当 created_at 范围很宽（一个月跨度可能返回大量行），优化器可能认为全表扫描更快

3. 优化方案

sql

-- 方案：创建复合索引 idx_created_status
CREATE INDEX idx_created_status ON orders(created_at, status);

-- 索引列顺序分析：
-- created_at 是范围条件（BETWEEN），放在前面：
--   → 先按时间范围缩小结果集
--   → 再过滤 status
-- 如果 status 过滤性很强（pending 可能只占 5%），这个索引更高效

-- 验证
EXPLAIN SELECT o.*, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01'
  AND o.status = 'pending'
ORDER BY o.id
LIMIT 20\G

-- type: range
-- key: idx_created_status
-- rows: 5000                ← 从 523891 降到 5000
-- Extra: Using index condition; Using where; Using filesort

4. 进一步优化：消除 filesort

sql

-- ORDER BY o.id 但索引是 (created_at, status)，不匹配
-- 如果查询改为按 created_at 排序，可以直接利用索引排序：
SELECT o.*, u.username, u.phone
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01' AND o.created_at < '2024-02-01'
  AND o.status = 'pending'
ORDER BY o.created_at         -- 改为按 created_at 排序
LIMIT 20;

-- Extra: Using index condition; Using where  ← 没有 filesort 了！

-- 如果必须按 id 排序，可以创建索引 (created_at, status, id)：
-- CREATE INDEX idx_created_status_id ON orders(created_at, status, id);
-- 不过这可能使索引过大，需要根据实际情况权衡

5. 效果总结

优化前：3.45s → type: ALL, rows: 523891
优化后：0.01s → type: range, rows: 5000
提升：约 345 倍

常见错误

错误 1：订单金额字段用 FLOAT

问题：total_amount 定义为 FLOAT，导致金额计算可能出现精度误差。
     例如 0.1 + 0.2 可能不等于 0.3。

解决：使用 DECIMAL(12,2) 存金额，精确到分。

错误 2：SELECT ... FOR UPDATE 的滥用

问题：下单时对 products 表全表加锁或对过多行加锁。

解决：
  - 仅对需要的行加锁（WHERE id = ?）
  - UPDATE products SET stock = stock - 1 WHERE id = ? AND stock >= 1;
    这个写法不需要 SELECT ... FOR UPDATE，单条 UPDATE 就是原子的
  - FOR UPDATE 仅在需要读取+修改且中间不能有变化时使用

错误 3：订单明细表不做快照

问题：order_items 中 product_name 和 product_price 通过 JOIN products 获取。

后果：用户一个月后查看历史订单，看到的商品名称和价格已经变了，
     与下单时的不一致，引发纠纷。

解决：order_items 中冗余存储下单时的 product_name 和 product_price（快照）。

错误 4：未使用事务保护下单流程

问题：下单流程中的库存扣减、创建订单、创建明细、清空购物车分成
     多个独立语句执行，没有包裹在事务中。

后果：
  - 库存扣减成功但创建订单失败 → 库存被"吃掉"
  - 创建订单成功但创建明细失败 → 空订单
  - 部分操作成功部分失败 → 数据不一致

解决：整个下单流程放在一个事务中（START TRANSACTION ... COMMIT）。

错误 5：深分页没有优化

问题：管理端商品列表页支持跳转到第 5000 页，
     使用 SELECT ... LIMIT 100000, 20 查询。

后果：每次翻到后面都要扫描大量数据，响应时间 5-10 秒。

解决：
  - 使用覆盖索引 + 延迟关联
  - 或使用游标分页（"下一页"代替页码跳转）
  - 或限制最大页面数为 100

本章练习

练习 1：ER 图转换为 DDL

根据以下 ER 图描述，写出完整的 CREATE TABLE 语句（含索引、约束）：

商家(merchants) → 店铺(shops) → 商品(products)
用户(users) → 订单(orders) → 订单明细(order_items) → 商品
用户(users) → 评论(reviews) → 商品(products)
订单(orders) → 支付记录(payments)
订单(orders) → 退款记录(refunds)

练习 2：索引设计分析

对 34.2 节中的每个表，回答以下问题：

每个索引被哪些查询使用？
哪些索引可以合并？
哪些索引可以被覆盖索引替代？
哪些索引是冗余的？（如已经有了(a,b)，还需要单独的(a)吗？）
如果表有 1000 万行，哪些查询最可能成为瓶颈？

练习 3：下单流程性能测试

准备测试数据（100 万用户，10 万商品，1000 万订单）
模拟 500 TPS 的下单压力
监控数据库的 CPU、I/O、锁等待
找出瓶颈并优化
测试不同隔离级别对性能和正确性的影响

练习 4：深分页优化实战

在 products 表中使用普通 LIMIT 分页到第 50000 行
使用覆盖索引 + 延迟关联优化
使用游标分页优化
对比三种方案的：执行时间、EXPLAIN 结果、适用场景
如果能结合 Redis 缓存，设计缓存策略

练习 5：慢查询优化报告

为本章的分析任务写一份完整的优化报告，包含：

慢查询 SQL 和执行时间
EXPLAIN 分析
瓶颈定位
优化方案（含 CREATE INDEX 语句）
优化后的 EXPLAIN 和执行时间对比
优化方案的风险评估（对写入性能的影响等）

练习 6：架构演进

假设电商系统日活 100 万，日订单 10 万，设计数据库架构演进方案：

单机阶段 → 读写分离 → 分库分表 → 微服务拆分
每个阶段的触发条件
每个阶段的技术选型和数据迁移方案
画出每个阶段的架构图（文字描述）

第34章 实战：电商数据库设计与优化 ​

本章目标 ​

前置知识 ​

34.1 业务需求分析 ​

34.1.1 核心功能 ​

34.1.2 ER 图（文字描述） ​

34.2 完整表结构设计 ​

34.2.1 用户表 (users) ​

34.2.2 商品分类表 (categories) ​

34.2.3 商品表 (products) ​

34.2.4 订单表 (orders) ​

34.2.5 订单明细表 (order_items) ​

34.2.6 购物车表 (cart_items) ​

34.2.7 库存变更日志表 (inventory_log) ​

34.3 索引设计深入分析 ​

34.3.1 为什么使用复合索引而不是单列索引？ ​

34.3.2 覆盖索引在商品列表中的应用 ​

34.4 关键业务场景 SQL 实现 ​

34.4.1 商品搜索与分页 ​

34.4.2 下单流程（事务） ​

34.4.3 我的订单列表（分页） ​

34.4.4 管理端订单查询 ​

34.5 高并发秒杀/抢购策略 ​

34.5.1 库存扣减的正确姿势 ​

34.5.2 连接池配置 ​

34.5.3 读写分离 ​

34.6 慢查询端到端优化实例 ​

问题：管理端订单查询慢 ​

常见错误 ​

错误 1：订单金额字段用 FLOAT ​

错误 2：SELECT ... FOR UPDATE 的滥用 ​

错误 3：订单明细表不做快照 ​

错误 4：未使用事务保护下单流程 ​

错误 5：深分页没有优化 ​

本章练习 ​

练习 1：ER 图转换为 DDL ​

练习 2：索引设计分析 ​

练习 3：下单流程性能测试 ​

练习 4：深分页优化实战 ​

练习 5：慢查询优化报告 ​

练习 6：架构演进 ​

第34章实战：电商数据库设计与优化

本章目标

前置知识

34.1 业务需求分析

34.1.1 核心功能

34.1.2 ER 图（文字描述）

34.2 完整表结构设计

34.2.1 用户表 (users)

34.2.2 商品分类表 (categories)

34.2.3 商品表 (products)

34.2.4 订单表 (orders)

34.2.5 订单明细表 (order_items)

34.2.6 购物车表 (cart_items)

34.2.7 库存变更日志表 (inventory_log)

34.3 索引设计深入分析

34.3.1 为什么使用复合索引而不是单列索引？

34.3.2 覆盖索引在商品列表中的应用

34.4 关键业务场景 SQL 实现

34.4.1 商品搜索与分页

34.4.2 下单流程（事务）

34.4.3 我的订单列表（分页）

34.4.4 管理端订单查询

34.5 高并发秒杀/抢购策略

34.5.1 库存扣减的正确姿势

34.5.2 连接池配置

34.5.3 读写分离

34.6 慢查询端到端优化实例

问题：管理端订单查询慢

常见错误

错误 1：订单金额字段用 FLOAT

错误 2：SELECT ... FOR UPDATE 的滥用

错误 3：订单明细表不做快照

错误 4：未使用事务保护下单流程

错误 5：深分页没有优化

本章练习

练习 1：ER 图转换为 DDL

练习 2：索引设计分析

练习 3：下单流程性能测试

练习 4：深分页优化实战

练习 5：慢查询优化报告

练习 6：架构演进