反范式设计

反范式 = 适度冗余 + 空间换时间

SELECT p.comment_text, p.comment_time, u.user_name 
FROM product_comment AS p
LEFT JOIN user AS u ON p.user_id = u.user_id
WHERE p.product_id = 10001
ORDER BY p.comment_id DESC 
LIMIT 1000;

-- 创建带有冗余字段的评论表
CREATE TABLE product_comment2 (
    comment_id INT PRIMARY KEY,
    product_id INT,
    comment_text VARCHAR(1000),
    comment_time DATETIME,
    user_id INT,
    user_name VARCHAR(50)  -- 冗余字段
);

SELECT comment_text, comment_time, user_name 
FROM product_comment2 
WHERE product_id = 10001 
ORDER BY comment_id DESC 
LIMIT 1000;

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_user`(
    IN start INT(10), 
    IN max_num INT(10)
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE date_start DATETIME DEFAULT ('2017-01-01 00:00:00');
    DECLARE date_temp DATETIME;
    SET date_temp = date_start;
    SET autocommit = 0;

    REPEAT
        SET i = i + 1;
        SET date_temp = date_add(date_temp, interval RAND()*60 second);
        INSERT INTO user(user_id, user_name, create_time)
        VALUES((start+i), CONCAT('user_', i), date_temp);
    UNTIL i = max_num
    END REPEAT;

    COMMIT;
END

CALL insert_many_user(10000, 1000000);  -- 生成100万用户

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_many_product_comments`(
    IN start INT(10), 
    IN max_num INT(10)
)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE date_start DATETIME DEFAULT ('2018-01-01 00:00:00');
    DECLARE date_temp DATETIME;
    DECLARE comment_text VARCHAR(25);
    DECLARE user_id INT;
    SET date_temp = date_start;
    SET autocommit = 0;
    
    REPEAT
        SET i = i + 1;
        SET date_temp = date_add(date_temp, INTERVAL RAND()*60 SECOND);
        SET comment_text = substr(MD5(RAND()), 1, 20);  -- 随机20字符评论
        SET user_id = FLOOR(RAND() * 1000000);          -- 随机用户ID
        
        INSERT INTO product_comment(comment_id, product_id, comment_text, comment_time, user_id)
        VALUES((start+i), 10001, comment_text, date_temp, user_id);
    UNTIL i = max_num
    END REPEAT;
    
    COMMIT;
END

CALL insert_many_product_comments(10000, 1000000);  -- 生成100万评论

-- 用户修改昵称时，需要同步更新所有评论表中的user_name
UPDATE product_comment2 
SET user_name = '新昵称' 
WHERE user_id = 10001;

-- 使用触发器自动同步（示例）
CREATE TRIGGER after_user_update
AFTER UPDATE ON user
FOR EACH ROW
BEGIN
    IF OLD.user_name != NEW.user_name THEN
        UPDATE product_comment2 
        SET user_name = NEW.user_name 
        WHERE user_id = NEW.user_id;
    END IF;
END;

-- 好的选择：稳定字段
ALTER TABLE orders ADD COLUMN customer_name VARCHAR(50);  -- 客户名称相对稳定

-- 不好的选择：频繁变化字段
ALTER TABLE orders ADD COLUMN customer_level INT;  -- 客户等级可能频繁变化

-- 适度冗余：只冗余必要的字段
CREATE TABLE order_detail (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- 冗余：避免关联产品表
    -- 不要冗余太多字段
    -- product_price DECIMAL(10,2),  -- 价格可能变化，不建议冗余
    quantity INT
);

-- 方案1：应用层同步
public void updateUserName(int userId, String newName) {
    // 更新用户表
    userMapper.updateName(userId, newName);
    // 同步更新评论表
    commentMapper.updateUserName(userId, newName);
}

-- 方案2：数据库触发器
-- 方案3：定时任务同步

-- 定期检查数据一致性
SELECT pc.user_id, pc.user_name, u.user_name
FROM product_comment2 pc
LEFT JOIN user u ON pc.user_id = u.user_id
WHERE pc.user_name != u.user_name;

核心原则：
1. 以第三范式为基础进行设计
2. 根据实际查询需求适度反范式化
3. 在性能和一致性之间找到平衡点

-- 基础表（符合3NF）
CREATE TABLE user (
    user_id INT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100),
    create_time DATETIME
);

-- 订单表（适度反范式）
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    user_name VARCHAR(50),      -- 冗余：避免关联查询
    receiver_name VARCHAR(50),  -- 冗余：历史快照
    receiver_phone VARCHAR(20), -- 冗余：历史快照
    receiver_address VARCHAR(200), -- 冗余：历史快照
    order_time DATETIME,
    FOREIGN KEY (user_id) REFERENCES user(user_id)
);