【MySQL深入详解】第15篇：Schema设计陷阱——那些年我们踩过的坑

开篇引入

Schema设计是数据库的根基。根基不稳，后患无穷。

见过太多这样的场景：表设计的时候图省事，上线后数据量大了，查询变慢了，扩展做不了了，迁移困难了。这都是Schema设计时埋下的坑。

《高性能MySQL》第6章总结了MySQL Schema设计的常见陷阱，这篇文章帮你把这些坑都填上。

陷阱1：太多列

什么是"太多列"

MySQL的存储引擎API以行缓冲区格式在服务器和存储引擎之间复制行。服务器需要把行缓冲区解码成列数据结构。

-- 典型的"万能表"
CREATE TABLE everything (
    id INT PRIMARY KEY,
    col_1 VARCHAR(100),
    col_2 VARCHAR(100),
    col_3 VARCHAR(100),
    col_4 VARCHAR(100),
    -- ... 继续几百列
    col_300 VARCHAR(100),
    col_301 INT,
    col_302 DATE,
    ...
);

性能问题

-- 即使只查询2列，MySQL也要：
-- 1. 从磁盘读取整行
-- 2. 解码所有列
-- 3. 返回需要的2列

-- 结果：CPU消耗高、内存占用大、查询变慢
SELECT col_1, col_2 FROM everything WHERE id = 1;

正确做法

-- 拆分成多个相关表
CREATE TABLE core_info (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    created_at TIMESTAMP
);

CREATE TABLE contact_info (
    id INT PRIMARY KEY,
    user_id INT,
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT
);

CREATE TABLE profile_info (
    id INT PRIMARY KEY,
    user_id INT,
    avatar VARCHAR(255),
    bio TEXT,
    interests JSON
);

-- 垂直拆分的好处：
-- 1. 只读取需要的列
-- 2. 不同表可以有不同的访问模式
-- 3. 热点数据分离

陷阱2：太多关联（EAV模式）

EAV是什么

Entity-Attribute-Value（实体-属性-值）是一种设计模式，号称"灵活"，实际上是灾难：

-- EAV模式：把关系数据库变成键值存储
CREATE TABLE eav_data (
    entity_id INT,
    attribute_name VARCHAR(50),
    attribute_value VARCHAR(255)
);

-- 存储用户信息
INSERT INTO eav_data VALUES (1, 'name', '张三');
INSERT INTO eav_data VALUES (1, 'email', 'zhang@example.com');
INSERT INTO eav_data VALUES (1, 'age', '30');
INSERT INTO eav_data VALUES (1, 'city', '北京');

EAV的问题

-- 查询用户年龄
SELECT attribute_value 
FROM eav_data 
WHERE entity_id = 1 AND attribute_name = 'age';

-- 添加新属性
INSERT INTO eav_data VALUES (1, 'new_field', 'value');

-- 获取完整用户信息（需要Pivot）
SELECT 
    MAX(CASE WHEN attribute_name = 'name' THEN attribute_value END) AS name,
    MAX(CASE WHEN attribute_name = 'email' THEN attribute_value END) AS email,
    MAX(CASE WHEN attribute_name = 'age' THEN attribute_value END) AS age
FROM eav_data
WHERE entity_id = 1
GROUP BY entity_id;

-- 问题：
-- 1. 无法建立有效的索引
-- 2. 数据类型无法约束
-- 3. 查询极其复杂
-- 4. 违反MySQL每个JOIN最多61个表的限制

正确做法

-- 使用JSON字段（MySQL 5.7+）
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    age TINYINT UNSIGNED,
    city VARCHAR(50),
    extra JSON  -- 真正的扩展字段
);

-- 查询和索引都正常
SELECT * FROM users WHERE age > 25;
CREATE INDEX idx_age ON users(age);

陷阱3：滥用ENUM

过度使用ENUM

-- 看起来很方便，实际上很糟糕
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    category ENUM('electronics', 'clothing', 'food', 'books', 'sports', 
                  'home', 'toys', 'beauty', 'automotive', 'garden', ...),
    status ENUM('draft', 'pending', 'approved', 'rejected', 'archived', 'deleted', ...),
    priority ENUM('low', 'medium', 'high', 'urgent', 'critical', ...),
    source ENUM('web', 'mobile', 'api', 'import', 'manual', ...)
);

ENUM的问题

1. 添加新值需要ALTER TABLE

-- 表越大，ALTER TABLE越慢
ALTER TABLE products MODIFY COLUMN category 
    ENUM('electronics', 'clothing', 'food', 'books', 'sports', 
         'home', 'toys', 'beauty', 'automotive', 'garden', 'health');

2. 排序基于位置，不是字母顺序

CREATE TABLE status_demo (
    status ENUM('pending', 'processing', 'completed', 'cancelled')
);

SELECT * FROM status_demo ORDER BY status;
-- 按枚举位置排序：pending(1), processing(2), completed(3), cancelled(4)
-- 而不是字母顺序

3. 无法表示多选

-- 如果产品可能属于多个分类
-- ENUM只能单选，无法处理

正确做法

-- 用独立表 + 外键
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE,
    slug VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE product_categories (
    product_id INT,
    category_id INT,
    PRIMARY KEY (product_id, category_id)
);

-- 或者用JSON（如果分类变化不频繁）
CREATE TABLE products (
    id INT PRIMARY KEY,
    categories JSON  -- ['electronics', 'accessories']
);

陷阱4：变相的ENUM（SET）

-- SET可以存多个值，但更糟糕
CREATE TABLE permissions (
    user_id INT,
    permissions SET('read', 'write', 'delete', 'admin', 'superadmin')
);

-- 问题：
-- 1. 难以查询
SELECT * FROM permissions WHERE permissions LIKE '%delete%';
-- 2. 难以索引
-- 3. 数据难以维护

陷阱5：NULL的使用不当

过度害怕NULL

-- 为了避免NULL，用特殊值代替
CREATE TABLE users_bad (
    name VARCHAR(50) NOT NULL,
    nickname VARCHAR(50) NOT NULL DEFAULT '',  -- 用空字符串代替NULL
    birthday DATE NOT NULL DEFAULT '1970-01-01',  -- 用特殊日期代替NULL
    phone VARCHAR(20) NOT NULL DEFAULT 'unknown'  -- 用'unknown'代替NULL
);

-- 结果：
-- 1. 无法区分"没有值"和"有这个特殊值"
-- 2. 应用程序逻辑变得复杂
-- 3. 聚合查询可能出错
SELECT COUNT(*) FROM users WHERE birthday = '1970-01-01';  -- 统计的是默认值，不是真实用户

过度使用NULL

-- 相反，也不能所有字段都设为NULL
CREATE TABLE users_bad2 (
    name VARCHAR(50) NULL,  -- 名字可以是NULL？
    email VARCHAR(100) NULL,  -- 邮箱可以是NULL？
    age TINYINT UNSIGNED NULL  -- 年龄可以是NULL？
);

-- 正确做法：
-- 1. 业务上必须有值的字段 → NOT NULL
-- 2. 业务上可能没有值的字段 → 允许NULL
CREATE TABLE users_good (
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    birthday DATE NULL,  -- 用户可以不填生日
    phone VARCHAR(20) NULL,  -- 用户可以不填手机
    bio TEXT NULL  -- 用户可以不写简介
);

陷阱6：过度规范化

过度规范化的例子

-- 为了"范式"，拆得太细
CREATE TABLE countries (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE cities (
    id INT PRIMARY KEY,
    country_id INT,
    name VARCHAR(50)
);

CREATE TABLE addresses (
    id INT PRIMARY KEY,
    city_id INT,
    street VARCHAR(100),
    zip_code VARCHAR(10)
);

CREATE TABLE users (
    id INT PRIMARY KEY,
    address_id INT,
    ...
);

-- 查询用户所在城市
SELECT u.name, a.street, c.name, co.name
FROM users u
JOIN addresses a ON u.address_id = a.id
JOIN cities c ON a.city_id = c.id
JOIN countries co ON c.country_id = co.id;

什么时候可以反规范化

-- 适当反规范化，提升查询性能
CREATE TABLE users_denormalized (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(50),    -- 反规范化：直接存国家名
    city VARCHAR(50),        -- 反规范化：直接存城市名
    street VARCHAR(100),
    created_at TIMESTAMP
);

-- 查询简单多了
SELECT * FROM users_denormalized WHERE country = '中国';

-- 维护成本：更新时需要同步多处
-- 适合场景：读多写少、需要极致查询性能

陷阱7：忽略索引设计

建表时没有考虑索引

-- 常见的错误
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_no VARCHAR(50),  -- 查询时常用，但没有索引
    status VARCHAR(20),
    amount DECIMAL(10,2),
    created_at DATETIME
);

-- 需要手动添加索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE UNIQUE INDEX idx_order_no ON orders(order_no);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);

-- 更好的做法：在CREATE TABLE时直接规划
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_no VARCHAR(50) NOT NULL,
    status TINYINT NOT NULL DEFAULT 0,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_user_id (user_id),
    KEY idx_status (status),
    KEY idx_created_at (created_at)
) ENGINE=InnoDB;

陷阱8：忽视未来变化

字段长度预留不足

-- 当时觉得够用，后来发现不够
CREATE TABLE users_bad (
    name VARCHAR(20),  -- 后来发现很多用户名字超过20字符
    phone VARCHAR(11),  -- 中国手机号11位，但可能有国家代码
    id_card VARCHAR(15)  -- 身份证15位，但新版是18位
);

-- 正确做法：预留一定余量
CREATE TABLE users_good (
    name VARCHAR(50),
    phone VARCHAR(20),
    id_card VARCHAR(20)
);

不考虑分库分表

-- 自增ID + 单机设计
CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    ...
);

-- 如果未来要分库分表：
-- 1. 自增ID会冲突
-- 2. 需要使用分布式ID生成器

-- 更好的做法：从一开始就规划好
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,  -- 使用雪花ID
    ...
);

陷阱9：错误的默认值

用"魔法值"

-- 魔法值让代码难以理解
CREATE TABLE orders_bad (
    status INT DEFAULT 0,  -- 0是什么？1是什么？
    is_paid TINYINT DEFAULT 0  -- 0=未支付？-1=失败？
);

-- 正确做法：用ENUM或外键关联字典表
CREATE TABLE order_status (
    id TINYINT PRIMARY KEY,
    name VARCHAR(20),
    description VARCHAR(100)
);

CREATE TABLE orders_good (
    status_id TINYINT DEFAULT 1,  -- 引用字典表
    ...
);

Schema设计checklist

建表前检查这些问题：

-- 1. 列数是否合理？（通常不超过50列）
-- 2. 每列是否有明确的用途？
-- 3. NULL的使用是否必要？
-- 4. 数据类型是否最小化？
-- 5. 是否考虑了未来的扩展？
-- 6. 索引是否规划好了？
-- 7. 外键是否必要？
-- 8. 是否有合理的默认值？
-- 9. 是否有明显的设计反模式？
-- 10. 是否考虑了查询模式？

小结

1. 避免太多列：拆分成多个相关表
2. 避免EAV模式：使用JSON代替
3. 慎用ENUM：用独立表代替
4. 正确使用NULL：不要用魔法值代替NULL
5. 避免过度规范化：根据实际查询需求调整
6. 建表时规划索引：而不是事后补救
7. 字段长度预留余量：考虑未来变化
8. 避免魔法值：用ENUM或字典表
9. 考虑分库分表场景：提前规划ID生成
10. 定期review Schema：随着业务发展调整

Schema设计是艺术，不是教条。要理解原则，也要知道什么时候打破原则。

---

延伸阅读

• 《高性能MySQL》第6章 schema设计与管理
• 反模式：Account ID, EAV, Polymorphic Association等
• MySQL Performance: Schema Design