MySQL 表设计经验

表设计是后端开发的核心环节，合理的表结构能提升系统性能与可维护性。以下是MySQL表设计的关键经验总结：

1. 命名规范

原则：可读性优先，采用英文单词（禁止拼音），下划线或驼峰命名，避免无意义字符。

  • 反例：
表名：a、b、c  
字段名：aaa、bbb、ccc  
索引名：index1、index2、index3
  • 正例：
表名：customers、orders、products  
字段名：customer_id、order_date、product_name  
索引名：idx_customer_id、inx_order_date
  • Tips：
  • 表名/字段名用小写，禁止数字开头。
  • 索引命名规范：主键索引pk_字段名，唯一索引uk_字段名，普通索引idx_字段名。

2. 选择合适的字段类型

核心逻辑：根据数据类型、长度、精度匹配字段，避免空间浪费。

  • 整数：使用INT（4字节）或BIGINT（8字节）。
  • 浮点：精确计算用DECIMAL(m,d)（如货币），否则用FLOAT/DOUBLE。
  • 字符：变长用VARCHAR，定长用CHAR（如固定长度编码）。
  • 日期：日期用DATE，日期时间用DATETIME（与时区无关），时间戳用TIMESTAMP（4字节，有时区）。
  • 大文本：超过VARCHAR长度限制时用TEXT，并拆分为独立表。

3. 主键设计

  • 原则：非业务关联、自增、连续性。
  • 推荐方案：
  • 自增主键AUTO_INCREMENT（如INT）。
  • 分布式场景用雪花ID（优化版，避免主键冲突）。
  • 避免：使用业务字段（如手机号）作为主键，防止业务变更影响表结构。

4. 字段长度设计

  • 字符 vs 字节：
  • VARCHAR/CHAR：长度为字符数（如VARCHAR(32)支持32个字符）。
  • 其他类型（如BIGINT(4)）：长度为显示宽度，实际存储按类型固定长度（BIGINT占8字节）。
  • 经验值：字段长度设为2的幂次（如VARCHAR(32)、VARCHAR(64)），预留扩展空间。

5. 逻辑删除优先

  • 物理删除：直接从硬盘删除数据，释放空间但无法恢复，可能触发索引重构。
  • 逻辑删除：添加is_deleted字段（TINYINT类型，0未删除，1已删除），保留数据痕迹，方便恢复。
  • 适用场景：需要审计、数据回溯的业务（如订单、用户记录）。

6. 通用字段设计

必选字段：

  • id：主键（自增或雪花ID）。
  • create_time：创建时间（DATETIME类型，默认CURRENT_TIMESTAMP）。
  • update_time：更新时间（DATETIME类型，更新时自动刷新）。
    可选字段：
  • creator：创建人ID/姓名。
  • update_by：更新人ID/姓名。
  • remark：备注（VARCHAR或TEXT）。

7. 控制字段数量

  • 建议：单表字段不超过20个，超出则拆分（如查询表+详情表）。
  • 原因：
  • 性能：字段过多导致查询时IO成本高，索引效率下降。
  • 存储：浪费磁盘空间，尤其是稀疏字段（大量NULL）。
  • 维护：字段越多，表结构变更成本越高。

8. 字段非空约束（NOT NULL）

  • 默认策略：除非允许NULL，否则字段设为NOT NULL。
  • 优点：
  • 避免空指针问题，简化业务逻辑。
  • NULL存储需要额外空间，且可能导致索引失效（如EXPLAIN中key为NULL）。
  • 示例：字符串字段默认空字符串（DEFAULT ''），数值字段默认0。

9. 索引设计原则

适用场景：

  • 高频查询条件：如WHERE user_id=?中的user_id。
  • 高区分度字段：如email（唯一值多），避免低区分度字段（如gender）。
  • 联合索引：遵循最左匹配原则，如(user_id, status)支持WHERE user_id=? AND status=?。

禁忌：

  • 单表索引不超过5个（过多索引会降低写入性能）。
  • 避免在索引列使用函数（如WHERE DATE(create_time)=?会导致索引失效）。

示例：

CREATE TABLE order_tab (  
  id INT AUTO_INCREMENT PRIMARY KEY,  
  user_id INT NOT NULL,  
  amount DECIMAL(10,2) NOT NULL,  
  status VARCHAR(20) NOT NULL,  
  create_time DATETIME NOT NULL,  
  KEY idx_user_status (user_id, status) USING BTREE  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

10. 反范式设计（适当冗余）

  • 目的：通过字段冗余减少表关联，提升查询性能。
  • 示例：
  • 范式设计（订单表与产品表关联）：
CREATE TABLE order (  
  id INT PRIMARY KEY AUTO_INCREMENT,  
  user_id INT NOT NULL,  
  order_date DATE NOT NULL,  
  product_id INT NOT NULL,  
  quantity INT NOT NULL,  
  FOREIGN KEY (product_id) REFERENCES product(id)  
);  
CREATE TABLE product (  
  id INT PRIMARY KEY AUTO_INCREMENT,  
  name VARCHAR(255) NOT NULL,  
  price DECIMAL(10,2) NOT NULL  
);
  • 反范式设计（冗余产品信息到订单表）：
CREATE TABLE order (  
  id INT PRIMARY KEY AUTO_INCREMENT,  
  user_id INT NOT NULL,  
  order_date DATE NOT NULL,  
  product_name VARCHAR(255) NOT NULL,  
  product_price DECIMAL(10,2) NOT NULL,  
  quantity INT NOT NULL,  
  total_price DECIMAL(10,2) NOT NULL  
);
  • 权衡：牺牲数据一致性换取查询性能，适用于读多写少场景。

11. 避免保留字

  • 风险：使用MySQL保留字（如ORDER、GROUP）需用反引号包裹，增加SQL复杂度。
  • 解决方案：
  • 字段名加下划线（如order_time改为order_time_）。
  • 用别名替代（如SELECT col AS 'column' FROM table）。
  • 常见保留字：
ADD、ALL、ALTER、AND、AS、BETWEEN、BY、CASE、DELETE、FROM、GROUP、HAVING、INSERT

12. 外键约束的取舍

  • 优点：数据库层保证数据完整性（如级联更新/删除）。
  • 缺点：
  • 性能开销：每次写入需检查约束，影响插入/更新速度。
  • 扩展性限制：分库分表时外键难以维护。
  • 替代方案：
  • 在应用层通过业务逻辑保证数据一致性（如先查后插）。
  • 使用触发器实现轻量级约束（需谨慎，可能引发死锁）。

13. 字段注释

要求：每个字段必须添加注释，枚举类型需明确取值说明。

CREATE TABLE order (  
  order_status VARCHAR(2) NOT NULL COMMENT '订单状态：01-待支付，02-已支付，03-已发货，04-已完成，05-已取消'  
);

14. SQL优化经验

  • 禁止：SELECT *，只查询必要字段。
  • 分页优化：用LIMIT OFFSET替代深分页（如LIMIT 100000, 10性能差）。
  • 避免函数运算：WHERE user_id + 1 = 10会导致索引失效，应改为WHERE user_id = 9。
  • 批量操作：INSERT INTO ... VALUES (), (), ()减少事务次数。
  • 执行计划分析：用EXPLAIN SELECT ...查看索引使用情况。

发表评论