排序字段设计_数据库设计规范

数据库的设计至关重要，设计好了，让系统健步如飞，设计不好，让系统蹒跚前行。今天，系统学习一下，数据库的设计，总结出来，以备后用。数据库库名库名不超过 32 个字符，相关模块的表名与表名之间体现 join 关系。如表 user_login 。库名格式：业务系统名称_子系统名，同一模块使用的表名用统一前缀。分库名格式：库通配名_编号，编号从 0 开始递增，如 wendan_001创建数据...

weixin_39608063

1796人浏览 · 2020-12-04 17:03:41

weixin_39608063 · 2020-12-04 17:03:41 发布

数据库的设计至关重要，设计好了，让系统健步如飞，设计不好，让系统蹒跚前行。今天，系统学习一下，数据库的设计，总结出来，以备后用。

数据库

库名

库名不超过 32 个字符，相关模块的表名与表名之间体现 join 关系。如表 user_login 。
库名格式：业务系统名称_子系统名，同一模块使用的表名用统一前缀。
分库名格式：库通配名_编号，编号从 0 开始递增，如 wendan_001
创建数据库时，显式指定字符集 utf8mb4。如 create database db1 default character set utf8mb4

表结构

表名、列名不超过 32 个字符，且只能使用字母、数字和下划线，一律小写
表名要求模块名强相关，如师资系统采用 ”sz" 作为前缀
创建表时，显式指定字符集 utf8mb4
创建表时，显式指定存储引擎 InnoDB
建表必须有 comment
建表时，要求主键为 id, 类型为 int 或 bigint，且为 auto_increment
核心表必须有数据的创建时间字段 create_time 和最后更新时间字段 update_time，便于查找问题。
表中所有字段必须都是 NOT NULL 属性，业务可以根据需要定义 DEFAULT 值
表中 blob、text 等大字段，必须拆分到其他表中，做到仅在需要读这些数据时，才去 select
中间表用于保存中间结果，名称必须以 tmp_ 开头。备份表用于备份数据，必须以 bak_ 开头。中间表和备份表要定期清理
对于超过 100 W 行的大表进行 alter table , 必须经过 DBA 审核，低峰执行，避免锁表对业务影响

列数据类型

业务中的状态 status，类型 type 等字段使用 tinyint 或者 smallint 类型，节省存储空间
存储金钱的字段，使用 int，程序端乘以 100 和除以 100 进行存取。
不超过 2700 字符数据，使用 varchar 存储。超过的，使用 mediumtext 或 text 存储
时间类型尽量选取 timestamp，更高级的做法使用 int 存储

索引

InnoDB 表主键必须为 id int/bigint auto_increment，且禁止被更新
主键名以 pk_ 开头，唯一键以 uk_ 或 uq_ 开头。普通索引以 idx_ 开头，使用小写格式，以表名/字段名或缩写作为后缀
InnoDB 存储引擎，索引类型必须为 BTREE
单个索引中每个索引记录的长度不能超过 64 KB
单个表上的索引个数不能超过 7 个
建立索引时，多考虑联合索引，并把区分度最高的字段放在最前面
建表或加索引时，保证表里互相不存在冗余索引

分库分表

分区表的分区字段必须有索引，或者是组合索引的首列
单个分区表中的分区个数不能超过 1024
上线前， RND 或者 DBA 必须指定分区表的创建、清理策略
访问分区表的 SQL 必须包含分区字段
单个分区文件不超过 2G，总大小不超过 50G。建议总分区数不超过 20 个
对于分区表执行 alter table 操作，必须在业务低峰执行
采用分库策略的，库的数量不超过 1024
采用分表策略的，表的数量不超过 4096
单个分表不超过 500W 行，ibd 文件不超过 2G，才能让性能更佳
水平分表尽量用取模方式，日志、报表数据采用日期进行分表

字符集

数据库的库、表、列所有字符集必须保持一致，为 utf8mb4
前端程序、环境变量的字符集为 utf8

采用上述规则样例语句如下：

CREATE TABLE user (  `id` bigint(11) NOT NULL AUTO_INCREMENT,  `user_id` bigint(11) NOT NULL COMMENT ‘用户id’  `username` varchar(45) NOT NULL COMMENT '真实姓名',  `email` varchar(30) NOT NULL COMMENT ‘用户邮箱’,  `nickname` varchar(45) NOT NULL COMMENT '昵称',  `avatar` int(11) NOT NULL COMMENT '头像',  `birthday` date NOT NULL COMMENT '生日',  `sex` tinyint(4) DEFAULT '0' COMMENT '性别',  `short_introduce` varchar(150) DEFAULT NULL COMMENT '一句话介绍',  `user_resume` varchar(300) NOT NULL COMMENT '用户的简历存放地址',  `user_register_ip` int NOT NULL COMMENT ‘用户注册时的源ip’,  `create_time` timestamp NOT NULL COMMENT ‘用户记录创建的时间’,  `update_time` timestamp NOT NULL COMMENT ‘用户资料修改的时间’,  `user_review_status` tinyint NOT NULL COMMENT ‘用户资料审核状态’,  PRIMARY KEY (`id`),  UNIQUE KEY `idx_user_id` (`user_id`),  KEY `idx_username`(`username`),  KEY `idx_create_time`(`create_time`,`user_review_status`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网站用户基本信息';