数据库设计：你应该知道的 10 条军规
Database Design — 10 Rules You Should Know
| iDev Team | 2026-08-12T01:35:01
糟糕的数据库设计是技术债的最大来源。这 10 条实战经验帮你设计出健壮、高效、可扩展的数据库。
Poor database design is the biggest source of technical debt. These 10 battle-tested rules help you design robust, efficient, and scalable databases.
1. 主键用自增 BIGINT不用 UUID 做主键（索引效率差），不用业务字段做主键（业务会变）。自增 BIGINT 是最安全的选择。2. 字段加 NOT NULL 和默认值NULL 是万恶之源：NULL 参与比较结果是 NULL，NULL 参与计算结果是 NULL。能用默认值的字段都加上默认值。3. 时间字段用 DATETIME不要用 VARCHAR 存时间，不要用 INT 存时间戳。DATETIME 可读性好，支持时间函数，范围足够大。4. 表名和字段名用蛇形命名user_name 而不是 userName。SQL 不区分大小写，蛇形命名在各种工具里都能正确显示。5. 建表就建索引WHERE 条件、JOIN 字段、ORDER BY 字段，建表时就要考虑索引。不要等到上线后发现慢查询才补。6. 外键约束看情况小系统用外键保证数据完整性；高并发系统外键可能影响性能，用应用层保证。7. 大字段分表LONGTEXT 类型的字段（如文章正文）和高频查询字段分开存储，列表查询不需要加载正文内容。8. 预留扩展字段要谨慎不要加 ext1, ext2, ext3 这种万能字段。需要扩展时，加明确含义的字段或者新建关联表。9. 软删除还是硬删除重要业务数据用软删除（加 deleted_at 字段）；日志类、临时类数据可以硬删除。10. 写好注释每个表、每个关键字段都要有 COMMENT。三个月后你自己都不记得这个字段是干什么的。
1. Use Auto-Increment BIGINT for Primary KeysDon't use UUID (poor index efficiency) or business fields (business rules change). Auto-increment BIGINT is the safest choice.2. Add NOT NULL and DefaultsNULL is the root of many evils: NULL in comparisons yields NULL, NULL in calculations yields NULL. Add default values wherever possible.3. Use DATETIME for Time FieldsDon't store time as VARCHAR or timestamps as INT. DATETIME is readable, supports time functions, and has sufficient range.4. Snake_case for Namesuser_name not userName. SQL is case-insensitive; snake_case displays correctly across all tools.5. Create Indexes at Table CreationWHERE conditions, JOIN fields, ORDER BY columns — plan indexes when creating tables. Don't wait until production slow queries force you to add them.6. Foreign Keys: It DependsSmall systems benefit from FK constraints for data integrity. High-concurrency systems may see performance impact — enforce at the application layer instead.7. Separate Large FieldsLONGTEXT fields (like article content) should be stored separately from frequently queried fields. List queries shouldn't load full content.8. Be Cautious with Extension FieldsDon't add ext1, ext2, ext3 catch-all columns. When extension is needed, add explicitly named fields or create related tables.9. Soft Delete vs Hard DeleteImportant business data: soft delete (add deleted_at). Logs and temporary data: hard delete is fine.10. Write CommentsEvery table and every key field needs a COMMENT. Three months later, even you won't remember what that field is for.