首页 > 新闻动态 > 电商资讯

易写科技数据库设计规范

1、         表的命名:使用有意义的英文词汇,词汇中间以下划线分割,全部采用小写;如示例表表名student_info,数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。

2、         表必须有无符号int型自增主键,对应示例表中id字段。

必须得有主键的原因:采用RBR模式复制,无主键的表删除,会导致备库夯住;

使用自增的原因:数据写入可以提高插入性能,避免page分裂,减少表碎片。

mysql复制主要有三种方式:基于SQL语句的复制(statement-based replication, SBR),基于行的复制(row-based replication, RBR),混合模式复制(mixed-based replication, MBR)。对应的,binlog的格式也有三种:STATEMENTROWMIXED

3、         必须把字段定义为NOT NULL并且提供默认值。

原因:a.null的列使索引、统计都更加复杂,使优化更加困难;b.NULL并不是空值,也会占用空间,所以在MySQL进行比较时,NULL会参与字段比较,所以对效率有一部分影响。

4、         所有表、字段都应该有 comment ,来描述表、字段所代表的含义,方便同事查看。

5、         能用smallint或者tinyint的情况就不用int,如一些状态位就使用的是smallint

原因:使用smallint或者tinyint能节约存储空间。

bigint

-2^63 (-9223372036854775808) 2^63-1 (9223372036854775807) 的整型数据(所有数字)。存储大小为 8 个字节。

P.S. bigint已经有长度了,在mysql建表中的length,只是用于显示的位数

int

-2^31 (-2,147,483,648) 2^31 – 1 (2,147,483,647) 的整型数据(所有数字)。存储大小为 4 个字节。int SQL-92 同义字为 integer

smallint

-2^15 (-32,768) 2^15 – 1 (32,767) 的整型数据。存储大小为 2 个字节。

tinyint

0 255 的整型数据。存储大小为 1 字节。

6、         涉及到金额的字段使用DECIMAL

7、         电话号码建议使用varchar(20),如示例表字段phone_number

原因:a.涉及到区号或者国家代号,可能出现+-()b.不会有谁用手机号做运算;c.varchar可以支持模糊查询。

8、         表建议增加create_timeupdate_time,以记录某条数据的创建时间和修改时间。

注意:这里5.55.6有区别,5.5使用的是TIMESTAMP,并且5.5不支持多个CURRENT_TIMESTAMP 默认值,因此如上示例设计;5.6版本使用了datetime,因为datetime支持的范围更广(范围为:'1000-01-01 00:00:00''9999-12-31 23:59:59'),并且create_timeupdate_time两个字段都设置了CURRENT_TIMESTAMP(从5.6.5开始支持多个字段默认值设置为CURRENT_TIMESTAMP),原因:增加这两个字段方便统计和归档。

9、         表建议包含一个状态标记字段,来标识数据是否被删除,而不使用物理删除;比如示例表字段status

10、      使用唯一索引约束字段值唯一的数据,唯一索引以uniq_字段名方式命名;如表中的uniq_stu_num

11、      在经常作为查询条件的字段上添加索引,普通索引以idx_字段名方式命名;如示例表中的idx_stu_score

12、      经常同时出现在where条件中的几个字段可以放在联合索引中;如idx_update_time_tuition;需要注意的是应该把选择性更大的列放在联合索引的最左边。

13、      尽量不使用TEXTBLOB类型,原因:会浪费更多的磁盘和内存空间,非必要的大量的大字段查询会淘汰掉热数据,导致内存命中率急剧降低,影响数据库性能。

14、      使用innodb存储引擎,原因:innodb支持事务,是行级锁,并发性能更好、CPU及内存缓存页优化使得资源利用率更高。

15、      建议使用utf8mb4字符集

16、      表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint ( 1表示是,0表示否)。说明:任何字段如果为非负数,必须是 unsigned

17、      如果存储的字符串长度几乎相等,使用 char 定长字符串类型。

18、      varchar 是可变长字符串,不预先分配存储空间,长度不要超过 5000,如果存储长 度大于此值,定义字段类型为 text,独立出来一张表,用主键来对应,避免影响其它字段索 引效率。

19、      如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释。

20、      字段允许适当冗余,以 高性能,但是必须考虑数据同步的情况。冗余字段应遵循:

1)    不是频繁修改的字段;

2)    不是 varchar 超长字段,更不能是 text 字段;

3)    比如用户名称,手机号码等。

21、      业务上具有唯一特性的字段,即使是组合字段,也必须建成唯一索引。

说明:不要以为唯一索引影响了 insert 速度,这个速度损耗可以忽略,但 高查找速度是明 显的;另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,根据墨菲定律,必 然有脏数据产生。

22、      超过三个表禁止 join。需要 join 的字段,数据类型必须一致;多表关联查询 时,保证被关联的字段需要有索引。

说明:即使双表 join 也要注意表索引、SQL 性能。

23、      varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据 实际文本区分度决定索引长度即可。

说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分 度会高达 90%以上,可以使用 count(distinct left(列名, 索引长度))/count(*)的区分度 来确定。

24、      页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。

25、      创建索引时避免有如下极端误解:

1)    宁滥勿缺。误认为一个查询就需要建一个索引;

2)    宁缺勿滥。误认为索引会消耗空间、严重拖慢更新和新增速度。


产品推荐

关于我们  | 联系我们  | 加入我们 版权所有 ©2015-©2024 北京易写科技有限公司.保留一切权利.ICP证:京ICP备16052268号-3