【mysql】数据库字段设计原则

本文将分享17个关键字段设计原则，这些经验可规避80%的数据库设计缺陷，涵盖性能、扩展性、可维护性等核心维度，附具体场景示例：

一、数据类型选择：避免“隐形成本杀手”

1. 整数类型精确匹配

坑：滥用BIGINT(20)存储用户年龄（值域0-150）。

优化：

age TINYINT UNSIGNED NOT NULL DEFAULT 0  -- 范围0-255，占用1字节

原理：BIGINT浪费7字节/行，百万级数据多消耗7MB内存。

2. 字符串类型长度克制

坑：盲目使用VARCHAR(255)存储用户名（实际业务最长15字符）。

优化：

username VARCHAR(32) NOT NULL COMMENT '用户名最长支持中英文混合16字'

原理：VARCHAR长度影响内存计算和排序性能，超长定义导致临时表溢出到磁盘。

3. 时间类型拒绝字符串存储

坑：created_time VARCHAR(20)存储'2023-10-01 12:34:56'。

优化：

created_time DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)  -- 支持微秒级精度

原理：字符串无法利用时间函数索引，且校验成本高。

4. 禁用ENUM类型

坑：status ENUM('new','processing','done')存储订单状态。

优化：

status TINYINT NOT NULL COMMENT '0:新订单,1:处理中,2:已完成'  -- 配套代码中维护映射

原理：ENUM新增选项需修改表结构，无法灰度发布。

二、字符集与编码：乱码与性能的平衡

5. 强制统一字符集

规范：全库使用utf8mb4字符集 + utf8mb4_0900_ai_ci排序规则。
原理：
- utf8是MySQL的“阉割版”（最大3字节），无法存储Emoji（需4字节）。
- 统一排序规则避免Illegal mix of collations错误。

6. 大文本独立存储

场景：商品详情字段description TEXT与核心表耦合。

优化：

-- 核心表只存摘要
CREATE TABLE products (id BIGINT PRIMARY KEY,summary VARCHAR(500) NOT NULL,description_id BIGINT NOT NULL  -- 外键关联到大文本表
);-- 独立大文本表（可分离存储引擎）
CREATE TABLE product_descriptions (id BIGINT PRIMARY KEY,content LONGTEXT NOT NULL
) ENGINE=MyISAM;  -- 适合读多写少的场景

原理：TEXT/BLOB字段导致行溢出，破坏InnoDB页存储效率。

三、默认值与NULL：隐式陷阱

7. 非必要不用NULL

坑：address VARCHAR(200) NULL允许空地址，但业务要求必填。

优化：

address VARCHAR(200) NOT NULL DEFAULT ''  -- 明确空字符串语义

原理：
- NULL需要额外字节标记，且WHERE col=NULL必须用IS NULL判断。
- 索引不存储NULL值，影响覆盖索引使用。

8. 动态默认值策略

场景：记录数据更新时间，但开发人员常漏写。

自动化：

updated_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  -- 自动更新

四、主键与索引：性能根基

9. 主键设计铁律

推荐：雪花IDBIGINT主键（无业务意义），禁止用UUID或组合键。
对比：
方案存储空间插入性能索引效率分页查询
雪花ID BIGINT 8字节极高最优稳定
UUIDv4 36字节差较差随机IO
业务组合键不定可能锁竞争碎片率高不可控

方案	存储空间	插入性能	索引效率	分页查询
雪花ID BIGINT	8字节	极高	最优	稳定
UUIDv4	36字节	差	较差	随机IO
业务组合键	不定	可能锁竞争	碎片率高	不可控

10. 索引禁忌与优化

三不原则：
1. 不超过5个索引/表（维护成本与写入性能）。
2. 不在低区分度字段建索引（如gender）。
3. 不重复索引（已有(a,b)索引时不再单独建a索引）。

前缀索引示例：

ALTER TABLE logs ADD INDEX idx_url_prefix (url(32));  -- 截取前32字符

五、扩展性设计：为未来留余地

11. 预留扩展字段

技巧：添加ext_info JSON NOT NULL DEFAULT '{}'字段。
场景：快速响应产品需求变更，避免频繁ALTER TABLE。

查询优化：

-- 利用生成列建立虚拟索引
ALTER TABLE orders ADD COLUMN discount_rate DECIMAL(5,2) GENERATED ALWAYS AS (ext_info->'$.discount_rate');
CREATE INDEX idx_discount ON orders(discount_rate);

12. 逻辑删除范式

反模式：直接物理删除订单数据导致关联数据断裂。

标准化：

is_deleted TINYINT NOT NULL DEFAULT 0 COMMENT '0:正常,1:删除';
deleted_time DATETIME DEFAULT NULL;

配套：所有SELECT语句自动附加WHERE is_deleted=0。

六、高级避坑技巧

13. 禁止保留字字段名

错误示例：rank、desc、group等MySQL保留字。
安全方案：统一添加后缀，如user_rank、product_desc。

14. 时区敏感字段

关键点：
- 用TIMESTAMP存储需要时区转换的时间（如用户操作时间）。
- 用DATETIME存储业务事件固定时间（如合同签订时间）。

对比：

CREATE TABLE events (event_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  -- 自动转换时区expire_time DATETIME NOT NULL  -- 固定时间（如'2025-12-31 23:59:59'）
);

15. 数值精度控制

金融字段规范：

price DECIMAL(19,4) NOT NULL DEFAULT 0.0000  -- 兼容比特币等小数位多的场景

禁止方案：用FLOAT/DOUBLE存储金额（存在精度丢失风险）。

七、设计验证工具链

静态检查：
- 使用pt-upgrade检查Schema与MySQL版本的兼容性。
- 用mysqldef生成差异化的DDL变更脚本。

性能压测：

sysbench oltp_read_write --tables=32 --table-size=1000000 prepare
sysbench oltp_read_write --time=300 --threads=128 run

监控预警：
- 部署Prometheus + Grafana监控索引命中率、锁等待、慢查询。

总结

优秀的字段设计需在存储效率、查询性能、扩展性之间取得平衡，遵循“最小够用、预见变化、显式表达”原则。建议在Schema设计阶段引入Code Review机制，结合业务特性制定《数据库设计规范》，后续通过自动化工具（如gh-ost）降低变更风险。