MySQL教程 / 第 310 节

第31章：企业级实战案例

从理论到实践的跨越

31.1 电商系统数据库设计

31.1.1 业务需求

系统规模：

用户：1000万+
商品：100万+
订单：1亿+
QPS：10000+

31.1.2 数据库设计

用户表：

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    status TINYINT DEFAULT 1 COMMENT '1:正常 0:禁用',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone (phone),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

商品表：

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL DEFAULT 0,
    sales INT NOT NULL DEFAULT 0,
    status TINYINT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_price (price),
    INDEX idx_sales (sales)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表（分区）：

CREATE TABLE orders (
    id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 0 COMMENT '0:待支付 1:已支付 2:已发货 3:已完成',
    create_time DATETIME NOT NULL,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id, create_time),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (YEAR(create_time)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

订单详情表：

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

31.1.3 高并发秒杀设计

秒杀商品表：

CREATE TABLE seckill_products (
    id BIGINT PRIMARY KEY,
    product_id BIGINT NOT NULL,
    stock INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    version INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

秒杀订单表：

CREATE TABLE seckill_orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_product (user_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

秒杀逻辑（乐观锁）：

-- 1. 检查库存
SELECT stock, version FROM seckill_products WHERE id = 1;

-- 2. 扣减库存（乐观锁）
UPDATE seckill_products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND stock > 0 AND version = @old_version;

-- 3. 创建订单
INSERT INTO seckill_orders (user_id, product_id) VALUES (123, 1);

31.2 社交平台数据库设计

31.2.1 用户关系表

关注表：

CREATE TABLE user_follows (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL COMMENT '关注者',
    follow_user_id BIGINT NOT NULL COMMENT '被关注者',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_follow (user_id, follow_user_id),
    INDEX idx_follow_user (follow_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询粉丝数：

SELECT COUNT(*) FROM user_follows WHERE follow_user_id = 123;

查询关注数：

SELECT COUNT(*) FROM user_follows WHERE user_id = 123;

31.2.2 动态表（分表）

动态表（按用户ID哈希分表）：

-- posts_0, posts_1, posts_2, posts_3
CREATE TABLE posts_0 (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    like_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 分表规则：user_id % 4
-- user_id = 123 → 123 % 4 = 3 → posts_3

31.2.3 评论表

评论表：

CREATE TABLE comments (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    post_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    content TEXT NOT NULL,
    parent_id BIGINT DEFAULT 0 COMMENT '父评论ID，0表示一级评论',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_post_id (post_id),
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

31.3 金融系统数据库设计

31.3.1 账户表

账户表：

CREATE TABLE accounts (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL UNIQUE,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    frozen_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    version INT NOT NULL DEFAULT 0 COMMENT '乐观锁',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

31.3.2 交易流水表

交易流水表：

CREATE TABLE transactions (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    account_id BIGINT NOT NULL,
    type TINYINT NOT NULL COMMENT '1:充值 2:提现 3:转账 4:消费',
    amount DECIMAL(15,2) NOT NULL,
    balance_before DECIMAL(15,2) NOT NULL,
    balance_after DECIMAL(15,2) NOT NULL,
    status TINYINT DEFAULT 0 COMMENT '0:处理中 1:成功 2:失败',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_account_id (account_id),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

31.3.3 转账事务

转账逻辑（事务）：

START TRANSACTION;

-- 1. 扣减转出账户余额
UPDATE accounts 
SET balance = balance - 100, version = version + 1
WHERE id = 1 AND balance >= 100 AND version = @version1;

-- 2. 增加转入账户余额
UPDATE accounts 
SET balance = balance + 100, version = version + 1
WHERE id = 2 AND version = @version2;

-- 3. 记录流水
INSERT INTO transactions (account_id, type, amount, balance_before, balance_after, status)
VALUES (1, 3, -100, @balance1, @balance1 - 100, 1);

INSERT INTO transactions (account_id, type, amount, balance_before, balance_after, status)
VALUES (2, 3, 100, @balance2, @balance2 + 100, 1);

COMMIT;

31.4 性能优化实战

31.4.1 慢查询优化

问题SQL：

-- 慢查询：全表扫描
SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;

-- EXPLAIN分析
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;
-- type: ALL
-- rows: 10000000

优化方案：

-- 添加复合索引
CREATE INDEX idx_user_time ON orders(user_id, create_time);

-- 优化后
EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY create_time DESC LIMIT 10;
-- type: ref
-- key: idx_user_time
-- rows: 100

31.4.2 分页优化

问题SQL：

-- 深分页慢
SELECT * FROM orders ORDER BY id LIMIT 1000000, 10;

优化方案：

-- 使用子查询优化
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 1000000, 1)
LIMIT 10;

-- 或使用延迟关联
SELECT o.* FROM orders o
INNER JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 10) t
ON o.id = t.id;

31.5 高可用架构实战

31.5.1 主从复制+读写分离

架构：

应用
  ↓
ProxySQL
  ↓
Master（写）
  ↓
Slave1（读）
Slave2（读）

ProxySQL配置：

-- 添加服务器
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (1, '192.168.1.10', 3306);  -- Master
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (2, '192.168.1.11', 3306);  -- Slave1
INSERT INTO mysql_servers(hostgroup_id, hostname, port) 
VALUES (2, '192.168.1.12', 3306);  -- Slave2

-- 配置路由规则
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (10, 1, '^SELECT.*FOR UPDATE', 1, 1);  -- 写操作到Master
INSERT INTO mysql_query_rules(rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (20, 1, '^SELECT', 2, 1);  -- 读操作到Slave

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;

31.6 本章总结

本章学习内容：

✅ 电商系统数据库设计⭐⭐⭐⭐⭐
✅ 社交平台数据库设计⭐⭐⭐⭐
✅ 金融系统数据库设计⭐⭐⭐⭐⭐
✅ 性能优化实战⭐⭐⭐⭐⭐
✅ 高可用架构实战⭐⭐⭐⭐⭐

重点掌握：

秒杀系统：乐观锁
分表分区：提高性能
事务：保证数据一致性
索引优化：提高查询性能
读写分离：提高并发

设计原则：

合理分表分区
添加合适索引
使用事务保证一致性
乐观锁处理并发
读写分离提高性能

面试重点：

秒杀系统如何设计
如何处理高并发
如何保证数据一致性
如何优化慢查询
如何设计高可用架构

下一章预告： MySQL面试题精选

练习题

如何设计秒杀系统？
如何处理库存超卖问题？
如何优化深分页？
如何设计用户关系表？
如何保证转账事务的一致性？
如何设计订单表？
如何优化慢查询？
如何设计读写分离架构？
乐观锁和悲观锁有什么区别？
实战：设计一个电商系统数据库

继续学习： 第32章：MySQL面试题精选