MySQL数据库表设计十大核心原则与最佳实践

一、数据类型选择原则

1.1 精确数值类型

整数类型按存储空间和数值范围选择：

TINYINT      -- 1字节（-128~127）
SMALLINT     -- 2字节（±3.2万）
MEDIUMINT    -- 3字节（±838万） 
INT          -- 4字节（±21亿）✅最常用
BIGINT       -- 8字节（±922亿亿）

浮点型陷阱示例：

-- 错误用法
CREATE TABLE payments (
    amount FLOAT(7,2)
);

-- 正确做法
CREATE TABLE financial_records (
    transaction DECIMAL(15,2) UNSIGNED,
    rate DECIMAL(5,4)
);

1.2 字符串类型优化

不同场景类型对比：

VARCHAR(255)   -- UTF8实际存储765字节
CHAR(32)       -- 固定长度验证码存储
TEXT           -- 最大65535字符
BLOB           -- 二进制文件存储
ENUM('Y','N')  -- 有限选项时性能提升40%

JSON类型实战：

CREATE TABLE product_specs (
    id INT PRIMARY KEY,
    attributes JSON,
    INDEX idx_attrs ((CAST(attributes->"$.weight" AS UNSIGNED)))
);

UPDATE product_specs 
SET attributes = JSON_SET(attributes, '$.color', 'red')
WHERE id = 1001;

二、范式与反范式设计

2.1 三大范式实践

订单系统示例：

-- 违反第二范式设计
CREATE TABLE bad_orders (
    order_id INT,
    product_name VARCHAR(100),  -- 依赖product_id更合适
    price DECIMAL(10,2),
    PRIMARY KEY(order_id)
);

-- 符合第三范式设计
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    created_at DATETIME
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY(item_id),
    FOREIGN KEY(order_id) REFERENCES orders(order_id)
);

2.2 反范式设计场景

实时统计场景：

-- 原始设计（查询慢）
SELECT COUNT(*) FROM comments WHERE post_id=123;

-- 反范式优化
CREATE TABLE posts (
    post_id INT PRIMARY KEY,
    comment_count INT DEFAULT 0  -- 新增计数字段
);

-- 通过触发器维护
DELIMITER $$
CREATE TRIGGER update_comment_count
AFTER INSERT ON comments
FOR EACH ROW
BEGIN
    UPDATE posts 
    SET comment_count = comment_count + 1
    WHERE post_id = NEW.post_id;
END$$
DELIMITER ;

三、索引设计黄金法则

3.1 B+树索引原理

索引失效案例：

-- 创建组合索引
ALTER TABLE employees ADD INDEX idx_name_age (last_name, age);

-- 有效查询
SELECT * FROM employees 
WHERE last_name = 'Smith' AND age > 30;

-- 索引失效查询（缺少最左前缀）
SELECT * FROM employees WHERE age = 35;

-- 使用函数导致失效
SELECT * FROM employees 
WHERE YEAR(hire_date) = 2020;

3.2 全文索引实战

中文分词解决方案：

-- 启用ngram分词
CREATE TABLE articles (
    id INT PRIMARY KEY,
    content TEXT,
    FULLTEXT INDEX ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

-- 搜索示例
SELECT * FROM articles 
WHERE MATCH(content) AGAINST('数据库优化' IN BOOLEAN MODE);

四、表结构优化策略

4.1 垂直拆分案例

用户表拆分示例：

-- 原始大表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password CHAR(60),
    profile_text TEXT,
    last_login DATETIME,
    login_count INT
);

-- 拆分后
CREATE TABLE user_basic (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    password CHAR(60),
    last_login DATETIME,
    login_count INT
);

CREATE TABLE user_profile (
    user_id INT PRIMARY KEY,
    profile_text TEXT,
    FOREIGN KEY(user_id) REFERENCES user_basic(user_id)
);

4.2 水平分表方案

时间范围分表示例：

-- 2023年日志表
CREATE TABLE access_log_2023 (
    log_id BIGINT AUTO_INCREMENT,
    user_id INT,
    access_time DATETIME,
    PRIMARY KEY(log_id)
) PARTITION BY RANGE (YEAR(access_time)) (
    PARTITION p2023 VALUES LESS THAN (2024)
);

-- 2024年自动扩展
ALTER TABLE access_log 
ADD PARTITION p2024 VALUES LESS THAN (2025);

五、高性能设计技巧

5.1 预计算字段

物化视图实现：

-- 创建汇总表
CREATE TABLE sales_summary (
    product_id INT PRIMARY KEY,
    total_sales DECIMAL(15,2),
    last_updated TIMESTAMP
);

-- 定时更新任务
CREATE EVENT update_sales_summary
ON SCHEDULE EVERY 1 HOUR
DO
BEGIN
    REPLACE INTO sales_summary
    SELECT product_id, SUM(amount), NOW()
    FROM sales
    GROUP BY product_id;
END

5.2 冷热数据分离

归档策略示例：

-- 创建历史表
CREATE TABLE orders_archive LIKE orders;

-- 迁移数据
INSERT INTO orders_archive
SELECT * FROM orders 
WHERE order_date < '2020-01-01';

-- 原表删除旧数据
DELETE FROM orders 
WHERE order_date < '2020-01-01'
LIMIT 1000;  -- 分批删除

六、安全设计要点

6.1 敏感字段处理

加密存储示例：

-- 使用AES加密
CREATE TABLE users_secure (
    user_id INT PRIMARY KEY,
    id_card VARBINARY(200)
);

-- 插入加密数据
INSERT INTO users_secure 
VALUES (1, AES_ENCRYPT('123456789', 'secret_key'));

-- 查询解密
SELECT AES_DECRYPT(id_card, 'secret_key') 
FROM users_secure WHERE user_id=1;

6.2 权限控制

最小权限原则实现：

-- 创建只读用户
CREATE USER 'report_user'@'%' IDENTIFIED BY 'secure_pwd';
GRANT SELECT ON analytics.* TO 'report_user'@'%';

-- 应用用户权限控制
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'app_pwd123';
GRANT INSERT, UPDATE, DELETE ON orders.* TO 'app_user'@'192.168.1.%';
REVOKE DROP ON *.* FROM 'app_user'@'192.168.1.%';