4.1.1 数据库范式 · 1NF / 2NF / 3NF / BCNF 工程实践

-- 违反 1NF:tags 字段存了多个值,用逗号分隔
CREATE TABLE article_bad (
    id    INT PRIMARY KEY,
    title VARCHAR(200),
    tags  VARCHAR(500)
);

-- 符合 1NF:拆成 2 张表
CREATE TABLE article (
    id    INT PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

CREATE TABLE article_tag (
    article_id INT NOT NULL,
    tag        VARCHAR(50) NOT NULL,
    PRIMARY KEY (article_id, tag),
    INDEX idx_tag (tag)
);

-- 查询:找含 mysql 标签的文章
SELECT a.* FROM article a
JOIN article_tag t ON t.article_id = a.id
WHERE t.tag = 'mysql';

-- 违反 2NF:product_name 只依赖 product_id(主键的一部分)
CREATE TABLE order_items_bad (
    order_id    INT,
    product_id  INT,
    quantity    INT,
    unit_price  DECIMAL(10,2),
    product_name VARCHAR(200),
    PRIMARY KEY (order_id, product_id)
);

-- 符合 2NF:拆出 products 表
CREATE TABLE products (
    id    INT PRIMARY KEY,
    name  VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    INDEX idx_name (name)
);

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    INDEX idx_product (product_id)
);

-- 违反 3NF:dept_name、dept_location 传递依赖 emp_id
CREATE TABLE employee_bad (
    emp_id        INT PRIMARY KEY,
    emp_name      VARCHAR(100),
    dept_id       INT,
    dept_name     VARCHAR(100),
    dept_location VARCHAR(200)
);

-- 符合 3NF
CREATE TABLE departments (
    id       INT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    location VARCHAR(200) NOT NULL,
    UNIQUE KEY uk_name (name)
);

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id  INT NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(id),
    INDEX idx_dept (dept_id)
);

-- 查询「张三所在部门位置」(1 次 JOIN,性能可接受)
SELECT e.emp_name, d.location
FROM employees e
JOIN departments d ON d.id = e.dept_id
WHERE e.emp_name = '张三';

-- 违反 BCNF 的关系 R(teacher, student, subject)
-- FD1: {teacher, subject} → student (一个老师教一门课只有一个学生 → 不真实,只是示例)
-- FD2: {student, subject} → teacher (一个学生一门课只有一个老师)
-- 两个候选键:{teacher, subject} 和 {student, subject}
-- 但 FD:student → subject 存在吗? 不一定,先简化

-- 真实反例:导师指导研究生
-- 每位研究生有一位导师,每种研究方向有一位导师
CREATE TABLE advise_bad (
    student_id  INT,
    research    VARCHAR(50),   -- 研究方向(每个学生一个研究方向)
    advisor_id  INT,
    PRIMARY KEY (student_id, research)
);
-- FD1: {student_id} → advisor_id (每个学生有一个导师)
-- FD2: {research} → advisor_id (每个方向有一位导师)
-- 候选键:{student_id, research} 和 {advisor_id, research}
-- 违反 BCNF:FD1 的左部 student_id 不是候选键

-- 符合 BCNF:拆 3 张表
CREATE TABLE student (
    id        INT PRIMARY KEY,
    name      VARCHAR(100)
);

CREATE TABLE research (
    name       VARCHAR(50) PRIMARY KEY,
    advisor_id INT NOT NULL,
    FOREIGN KEY (advisor_id) REFERENCES advisor(id)
);

CREATE TABLE advisor (
    id   INT PRIMARY KEY,
    name VARCHAR(100)
);

-- 查询「学生 X 选的方向的导师」
SELECT s.name AS student, r.name AS research, a.name AS advisor
FROM student s
JOIN research r ON r.name = s.research
JOIN advisor  a ON a.id = r.advisor_id
WHERE s.id = ?;

-- 反范式前:每次报表查询都 JOIN 5 张表
SELECT u.name, COUNT(o.id) AS order_cnt, SUM(p.amount) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN payments p ON p.order_id = o.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products pr ON pr.id = oi.product_id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id;

-- 反范式后:user_stats 表存了 order_cnt 和 total_amount
CREATE TABLE user_stats (
    user_id      INT PRIMARY KEY,
    order_cnt    INT DEFAULT 0,
    total_amount DECIMAL(12,2) DEFAULT 0,
    last_order_at TIMESTAMP,
    updated_at   TIMESTAMP
);

-- 报表查询:直接读 user_stats,不再 JOIN
SELECT name, order_cnt, total_amount FROM user_stats WHERE ...;

-- 反范式前:订单列表页每次查商品图、商品名都要 JOIN
SELECT o.id, o.created_at, u.name, p.product_name, p.thumbnail
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;  -- 每次查 4 表 JOIN

-- 反范式后:订单表冗余商品图、商品名
ALTER TABLE order_items ADD COLUMN product_name_snap VARCHAR(200);
ALTER TABLE order_items ADD COLUMN thumbnail_snap VARCHAR(500);
-- 下单时一次性写入快照,后续查询不 JOIN products

-- 事实表 sales_fact(销量事实)
CREATE TABLE sales_fact (
    date_key       INT,        -- 引用 dim_date
    product_key    INT,        -- 引用 dim_product
    customer_key   INT,        -- 引用 dim_customer
    store_key      INT,        -- 引用 dim_store
    quantity       INT,
    revenue        DECIMAL(12,2),
    cost           DECIMAL(12,2)
);

-- 维度表:故意冗余多个层级属性(违反 3NF)
CREATE TABLE dim_product (
    product_key  INT PRIMARY KEY,
    sku          VARCHAR(50),
    product_name VARCHAR(200),
    category     VARCHAR(100),
    category_dept VARCHAR(100),     -- 反范式:存了 category 的上级
    brand        VARCHAR(100),
    supplier     VARCHAR(100)
);

-- 维度表也用「缓慢变化维 SCD」保留历史版本,这是反范式常见手法

-- 读模型:订单详情缓存表(冗余了用户、商品、地址)
CREATE TABLE order_view_cache (
    order_id        INT PRIMARY KEY,
    user_name       VARCHAR(100),
    user_phone      VARCHAR(20),
    product_names   TEXT,        -- 拼接商品名
    total_amount    DECIMAL(12,2),
    shipping_addr   VARCHAR(500),
    created_at      TIMESTAMP
);
-- 通过 CDC / 异步任务同步,容忍秒级延迟

-- 表 1:用户(users)—— 严格 3NF,只存用户本身
CREATE TABLE users (
    id         BIGINT PRIMARY KEY,
    username   VARCHAR(50) UNIQUE NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    phone      VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 表 2:商品(products)—— 不存分类名,只引用 categories
CREATE TABLE categories (
    id   INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE products (
    id          BIGINT PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    category_id INT NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    stock       INT NOT NULL DEFAULT 0,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- 表 3:订单(orders)—— 存 user_id 外键,不冗余用户名地址
CREATE TABLE orders (
    id           BIGINT PRIMARY KEY,
    user_id      BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status       VARCHAR(20) DEFAULT 'pending',
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_created (user_id, created_at)
);

-- 表 4:订单详情(order_items)—— 冗余 product_name 和 unit_price 作为快照
CREATE TABLE order_items (
    order_id          BIGINT NOT NULL,
    product_id        BIGINT NOT NULL,
    product_name_snap VARCHAR(200) NOT NULL,   -- 快照
    unit_price_snap   DECIMAL(10,2) NOT NULL,  -- 快照
    quantity          INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 表 5:支付(payments)—— 不冗余订单状态
CREATE TABLE payments (
    id            BIGINT PRIMARY KEY,
    order_id      BIGINT NOT NULL UNIQUE,
    method        VARCHAR(20) NOT NULL,
    amount        DECIMAL(12,2) NOT NULL,
    paid_at       TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

-- 事实表 sales_fact(只存数值 + 外键)
CREATE TABLE sales_fact (
    sale_id      BIGINT PRIMARY KEY,
    date_key     INT NOT NULL,
    product_key  INT NOT NULL,
    customer_key INT NOT NULL,
    store_key    INT NOT NULL,
    quantity     INT NOT NULL,
    revenue      DECIMAL(12,2) NOT NULL,
    cost         DECIMAL(12,2) NOT NULL,
    INDEX idx_date (date_key),
    INDEX idx_product (product_key)
);

-- 维度表 dim_date(冗余了月、季度、年,违反 3NF)
CREATE TABLE dim_date (
    date_key    INT PRIMARY KEY,
    full_date   DATE,
    day_of_week VARCHAR(10),
    month       INT,
    quarter     INT,
    year        INT,
    is_holiday  BOOLEAN
);

-- 维度表 dim_product(冗余了 category 和 brand,违反 3NF)
CREATE TABLE dim_product (
    product_key    INT PRIMARY KEY,
    sku            VARCHAR(50),
    product_name   VARCHAR(200),
    category       VARCHAR(100),      -- 反范式
    category_dept  VARCHAR(100),      -- 反范式
    brand          VARCHAR(100)       -- 反范式
);

-- 方案 A:全 JSON 列(违反 1NF 精神)
CREATE TABLE product_json (
    id        BIGINT PRIMARY KEY,
    name      VARCHAR(200),
    attrs     JSON,
    INDEX idx_attrs_brand ((CAST(attrs->>'$.brand' AS CHAR(50))))
);
-- 查询:iPhone 14
SELECT * FROM product_json WHERE attrs->>'$.brand' = 'Apple';
-- 插入
INSERT INTO product_json VALUES (1, 'iPhone 14',
  '{"brand":"Apple","cpu":"A16","color":"black","storage":"256GB"}');

-- 方案 B:JSON 存扩展属性 + 常用字段拆列(混合)
CREATE TABLE product_mix (
    id        BIGINT PRIMARY KEY,
    name      VARCHAR(200),
    brand     VARCHAR(50),          -- 高频字段拆出来,加索引
    price     DECIMAL(10,2),
    attrs     JSON,                 -- 低频扩展属性
    INDEX idx_brand (brand)
);
-- 查询:iPhone 14(走索引,快)
SELECT * FROM product_mix WHERE brand = 'Apple' AND attrs->>'$.cpu' = 'A16';

-- 原始查询(严格 3NF,每次报表都要 JOIN 4 张表,5 秒)
SELECT u.name, COUNT(o.id) AS cnt, SUM(oi.quantity * oi.unit_price_snap) AS total
FROM users u
JOIN orders o      ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.created_at BETWEEN '2026-01-01' AND '2026-06-30'
GROUP BY u.id, u.name;

-- 第 1 步:加聚合表 user_stats(反范式冗余)
CREATE TABLE user_stats (
    user_id       BIGINT PRIMARY KEY,
    order_cnt     INT DEFAULT 0,
    total_amount  DECIMAL(14,2) DEFAULT 0,
    last_order_at TIMESTAMP,
    refresh_at    TIMESTAMP
);

-- 第 2 步:用触发器 / 异步任务同步
-- 同步触发器:订单完成时更新 user_stats
DELIMITER $$
CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    INSERT INTO user_stats (user_id, order_cnt, total_amount, last_order_at, refresh_at)
    VALUES (NEW.user_id, 1, NEW.total_amount, NEW.created_at, NOW())
    ON DUPLICATE KEY UPDATE
        order_cnt = order_cnt + 1,
        total_amount = total_amount + NEW.total_amount,
        last_order_at = NEW.created_at,
        refresh_at = NOW();
END$$
DELIMITER ;

-- 第 3 步:报表查询改读 user_stats(200 毫秒)
SELECT u.name, s.order_cnt, s.total_amount
FROM users u
JOIN user_stats s ON s.user_id = u.id
WHERE s.refresh_at > DATE_SUB(NOW(), INTERVAL 1 DAY);

-- 第 4 步:每晚定时任务对账,防止触发器漏单
INSERT INTO user_stats (user_id, order_cnt, total_amount, refresh_at)
SELECT o.user_id, COUNT(*), SUM(o.total_amount), NOW()
FROM orders o
WHERE o.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY o.user_id
ON DUPLICATE KEY UPDATE
    order_cnt = VALUES(order_cnt),
    total_amount = VALUES(total_amount),
    refresh_at = NOW();

-- 修法 A:加触发器
DELIMITER $$
CREATE TRIGGER trg_user_after_update
AFTER UPDATE ON users FOR EACH ROW
BEGIN
    IF NEW.address <> OLD.address THEN
        UPDATE orders
        SET user_address_snap = NEW.address
        WHERE user_id = NEW.id AND status = 'pending';
    END IF;
END$$
DELIMITER ;

-- 修法 B:去掉冗余字段,改 JOIN
ALTER TABLE orders DROP COLUMN user_address_snap;
SELECT o.id, o.total_amount, u.address
FROM orders o JOIN users u ON u.id = o.user_id;

-- 拆出 brand 列
ALTER TABLE product_json ADD COLUMN brand VARCHAR(50)
  GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.brand'))) VIRTUAL;
CREATE INDEX idx_brand ON product_json(brand);
-- 查询就走 B+Tree 索引,毫秒级
SELECT * FROM product_json WHERE brand = 'Apple';

CREATE MATERIALIZED VIEW order_detail_view AS
SELECT o.id AS order_id, u.name AS user_name, u.phone AS user_phone,
       p.product_name, p.thumbnail,
       oi.quantity, oi.unit_price_snap,
       pay.method AS pay_method, pay.paid_at,
       o.total_amount, o.created_at
FROM orders o
JOIN users u         ON u.id = o.user_id
JOIN order_items oi  ON oi.order_id = o.id
JOIN products p      ON p.id = oi.product_id
LEFT JOIN payments pay ON pay.order_id = o.id;

-- 详情页直接查视图
SELECT * FROM order_detail_view WHERE order_id = ?;

-- 改前:orders 表有 first_product_name 列
ALTER TABLE orders DROP COLUMN first_product_name;

-- 订单详情查商品(JOIN 即可)
SELECT o.id, oi.product_name_snap FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = ?;
-- 注意:历史订单应该用 product_name_snap(下单时快照),不用 products.name

-- 第 1 步:新旧表都加冗余字段
ALTER TABLE users_v2 ADD COLUMN name_snap VARCHAR(100);
-- 双写期同步两套表,确保 JOIN 列存在

-- 第 2 步:迁移完成后,慢慢 DROP
ALTER TABLE orders DROP COLUMN user_name_snap;  -- 旧字段
-- 新表的 user_name_snap 也保留一段时间作回滚备用