?? 2025年10月9日
?? 工具：HeidiSQL + MySQL 8.0
?? 目標(biāo)：設(shè)計一個電商訂單系統(tǒng)數(shù)據(jù)庫

為什么寫這篇博客

在做SQL優(yōu)化實戰(zhàn)時，我用到了users、orders、order_items三張表。當(dāng)時覺得這個設(shè)計挺合理的，現(xiàn)在系統(tǒng)性的設(shè)計出這個數(shù)據(jù)庫。

今天想弄明白：如果讓我從需求開始，怎么一步步設(shè)計出這三張表？

這篇博客記錄我的設(shè)計過程。

第一步：需求分析（找主體）

假設(shè)需求是：做一個電商訂單系統(tǒng)。

我的第一個問題：這個系統(tǒng)里有哪些"東西"？

我一開始列出來的主體

1. 用戶（買東西的人）
2. 商品（要賣的東西）
3. 訂單（用戶下的單）

然后我想了想，訂單里要包含多個商品，比如：

• 訂單1：買了iPhone、AirPods、充電器
• 訂單2：買了iPad、Magic Keyboard

我發(fā)現(xiàn)：一個訂單可以包含多個商品，每個商品的數(shù)量、價格可能不同。

所以我又加了一個主體：
4. 訂單明細(xì)（訂單里的每個商品）

最終確定的主體

? 用戶（User）
? 訂單（Order）
? 訂單明細(xì)（Order Item）

我沒加"商品"表，因為這次練習(xí)重點(diǎn)是訂單查詢，所以把商品名直接存在訂單明細(xì)里了。（實際項目中應(yīng)該有獨(dú)立的商品表）

第二步：畫E-R圖（找關(guān)系）

主體之間的關(guān)系

1. 用戶 和 訂單

• 一個用戶可以下多個訂單
• 一個訂單只屬于一個用戶
• 關(guān)系：一對多（1:N）

2. 訂單 和 訂單明細(xì)

• 一個訂單可以包含多個商品
• 一個訂單明細(xì)只屬于一個訂單
• 關(guān)系：一對多（1:N）

E-R圖

符號說明：

• ||--o{：一對多關(guān)系
• PK：主鍵（Primary Key）
• FK：外鍵（Foreign Key）

第三步：確定字段（每個主體存什么）

用戶表（users）

需要存什么信息？

我想了想，一個用戶需要：

• id：唯一標(biāo)識（主鍵）
• name：姓名
• email：郵箱（可以用來登錄、發(fā)郵件）
• city：城市（可以按地區(qū)統(tǒng)計）

我一開始想加：

• ? password（密碼）→ 這次練習(xí)不涉及登錄，不加了
• ? phone（手機(jī)號）→ 暫時不需要

最終字段：

id, name, email, city

訂單表（orders）

需要存什么信息？

• id：訂單號（主鍵）
• user_id：哪個用戶下的單（外鍵，指向users.id）
• total_amount：訂單總金額
• status：訂單狀態(tài)（pending/completed/cancelled）
• created_at：下單時間

我一開始犯了個錯誤：

我想在orders表里存用戶名、城市，方便查詢。

但我想起來：這違反第三范式（傳遞依賴）！

訂單ID → 用戶ID → 用戶名、城市
       直接依賴   傳遞依賴（不應(yīng)該存）

正確做法：

• orders表只存user_id
• 需要用戶名？JOIN users表

最終字段：

id, user_id, total_amount, status, created_at

訂單明細(xì)表（order_items）

需要存什么信息？

• id：明細(xì)ID（主鍵）
• order_id：屬于哪個訂單（外鍵，指向orders.id）
• product_name：商品名稱
• quantity：購買數(shù)量
• price：單價

我一開始又想犯錯：

我想存訂單總金額、用戶名，方便查詢。

但我又想起來：這又是傳遞依賴！

訂單明細(xì)ID → 訂單ID → 訂單總金額、用戶ID → 用戶名
           直接依賴   傳遞依賴（不應(yīng)該存）

正確做法：

• order_items表只存order_id
• 需要訂單金額、用戶名？一層層JOIN

最終字段：

id, order_id, product_name, quantity, price

第四步：檢查三范式

1NF：字段不可再分 ?

每個字段都是原子的：

• ? name是name，email是email，沒有"用戶信息"字段塞一堆東西
• ? product_name是商品名，quantity是數(shù)量，分開存

2NF：不要部分依賴 ?

每張表只存自己的事：

• ? users表：只存用戶信息
• ? orders表：只存訂單信息（不存用戶名、郵箱）
• ? order_items表：只存商品信息（不存訂單金額、用戶名）

3NF：不要傳遞依賴 ?

不存"可以通過別人找到"的數(shù)據(jù)：

• ? orders表：不存用戶名（可以通過user_id找到）
• ? order_items表：不存訂單金額、用戶名（可以通過order_id找到）

關(guān)系鏈：

graph LR
    A[order_items表] -->|order_id| B[orders表]
    B -->|user_id| C[users表]
    A -->|只存：商品信息| A
    B -->|只存：訂單信息| B
    C -->|只存：用戶信息| C

第五步：建表SQL

確認(rèn)設(shè)計沒問題后，開始寫建表語句。

創(chuàng)建數(shù)據(jù)庫

CREATE DATABASE IF NOT EXISTS sql_optimization_test;
USE sql_optimization_test;

創(chuàng)建用戶表

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    city VARCHAR(50),
    INDEX idx_city (city)  -- 按城市查詢的索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

為什么加idx_city索引？

• 我預(yù)計會經(jīng)常按城市查詢（比如：深圳的用戶）
• 提前建好索引

創(chuàng)建訂單表

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),       -- JOIN時用
    INDEX idx_status (status),         -- 按狀態(tài)查詢
    INDEX idx_created_at (created_at), -- 按時間查詢
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

關(guān)鍵點(diǎn)：

• user_id設(shè)置了外鍵約束（保證數(shù)據(jù)一致性）
• 加了3個索引（根據(jù)常用查詢場景）

創(chuàng)建訂單明細(xì)表

CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_order_id (order_id),          -- JOIN時用
    INDEX idx_product_name (product_name),  -- 按商品查詢
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第六步：驗證設(shè)計（寫查詢測試）

建表后，我寫了幾個常見查詢，測試設(shè)計是否合理。

查詢1：找深圳用戶的訂單

SELECT u.name, o.id, o.total_amount, o.status
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = '深圳';

測試通過！ ?

查詢2：找買了iPhone的用戶

SELECT DISTINCT u.id, u.name, u.email
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_name LIKE '%iPhone%';

測試通過！ ?

查詢3：統(tǒng)計每個用戶的訂單總額

SELECT u.id, u.name, SUM(o.total_amount) AS total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name
ORDER BY total DESC;

測試通過！ ?

第七步：插入測試數(shù)據(jù)

為了后續(xù)做SQL優(yōu)化練習(xí)，我準(zhǔn)備了測試數(shù)據(jù)。

數(shù)據(jù)規(guī)模

• users：1000條
• orders：5000條
• order_items：10000條

數(shù)據(jù)生成方法

我用存儲過程批量生成：

-- 生成1000個用戶
DELIMITER $$
CREATE PROCEDURE generate_users()
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= 1000 DO
        INSERT INTO users (name, email, city) VALUES (
            CONCAT('用戶', i),
            CONCAT('user', i, '@example.com'),
            ELT(FLOOR(1 + RAND() * 5), '深圳', '北京', '上海', '廣州', '杭州')
        );
        SET i = i + 1;
    END WHILE;
END$$
DELIMITER ;
CALL generate_users();

類似的方法生成訂單和訂單明細(xì)。

我的收獲

1. 數(shù)據(jù)庫設(shè)計的核心流程

需求分析 → 找主體 → 畫E-R圖 → 確定字段 → 檢查范式 → 建表 → 驗證

2. 三范式不是教條

我一開始以為"符合三范式就是好設(shè)計"。

但后來發(fā)現(xiàn)：

• 有時候需要反范式化（為了性能，適當(dāng)冗余）
• 關(guān)鍵是理解為什么要這樣設(shè)計

比如：

• 電商系統(tǒng)可能會在訂單表冗余"收貨地址"（雖然違反3NF）
• 因為用戶可能改地址，但歷史訂單的地址不能變

3. 索引要根據(jù)查詢場景設(shè)計

我在建表時就考慮了：

• 哪些字段會用來JOIN？→ 加索引
• 哪些字段會用來WHERE？→ 加索引
• 哪些字段會用來ORDER BY？→ 加索引

這樣后續(xù)做SQL優(yōu)化時，心里有底。

4. 外鍵約束要慎用

我在建表時加了外鍵約束：

FOREIGN KEY (user_id) REFERENCES users(id)

好處：

• 保證數(shù)據(jù)一致性（不能插入不存在的user_id）

壞處：

• 插入/刪除性能下降（要檢查約束）
• 實際項目中很多公司不用外鍵，在應(yīng)用層保證一致性

最終的表結(jié)構(gòu)

總結(jié)

這次從零設(shè)計數(shù)據(jù)庫，最大的感受是：

數(shù)據(jù)庫設(shè)計不是背范式規(guī)則，而是理解"為什么要這樣存"。

三個核心問題：

1. 這個數(shù)據(jù)屬于誰？（決定放哪個表）
2. 這個數(shù)據(jù)能通過別人找到嗎？（決定要不要冗余存）
3. 這個字段會怎么查？（決定要不要加索引）

把這三個問題想清楚，數(shù)據(jù)庫設(shè)計就不難了。

到此這篇關(guān)于MySQL數(shù)據(jù)庫設(shè)計實戰(zhàn)之如何從需求到建表(完整流程)的文章就介紹到這了,更多相關(guān)mysql數(shù)據(jù)庫設(shè)計內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論