目的
为了规范数据库设计,减少设计失误,提高数据安全及性能,特制订本规范。
适用范围
所有mysql数据库。原则上,数据库设计应遵循本规范说明,特殊情况可例外,但需跟DBA说明原因。
规范
命名
库名、表名、字段名必须使用小写字母,并采用下划线分割
库名、表名、字段名禁止超过32个字符
库名、表名、字段名禁止使用MySQL保留字
表
原则上,业务范畴内的表都统一使用innodb存储引擎;如需使用其它存储引擎,需说明原因,并征得DBA同意。
使用外部引擎
禁止使用分区表
减少或避免使用临时表
为了切换后可以正常工作,无论主备,相同的表需使用相同的引擎。
每一个表都需要设置主键
将大字段、访问频率低的字段拆分到单独的表中存储,分离冷热数据
范式
表不要求一定满足第三范式,根据实际情况可适当添加冗余字段。
约束
对于字典类型的表,因数据量小,修改少,影响面大,应依赖数据库约束来确保数据质量。
对于日志或流水型表,为了提升效率,可以释放放宽限制。
字段
对于字段设计,概况下来一个原则是:越简单越好,越小越好。
常用数据类型介绍:
数据类型
|
取值范围
|
占用空间(字节)
|
---|---|---|
tinyint [unsigned] | -128到127或0到255 | 1 |
smallint [unsigned] | -32768到32767或0到65536 | 2 |
int [unsigned] | -2147483648到2147483647或0到4294967295 | 4 |
bigint [unsigned] | -9223372036854775808到9223372036854775807 或 0到18446744073709551615 | 8 |
date | yyyy-mm-dd,精确到天 | 3 |
datetime | yyyy-mm-dd hh24:mi:ss 精确到秒 | 8 |
timestamp | yyyy-mm-dd hh24:mi:ss 精确到秒,只能存储1970到2037年之间的时间 | 4 |
char(m) | 0<M<=255 | m个字符( 非字节)。具体空间与字符集相关 |
varchar(m) | 0<m<=(65532/n) | m个字符。与字符集有关,最多不超过65532字节 |
text | 64K个字符 | 与字符集相关 |
选择最合适的数据类型,能用数字类型不用varchar类型;能用date/datetime类型不用varchar类型;避免使用char类型;不使用浮点数,可以通过乘以一个系数来转换为整型数据。
尽量避免text/lob类型
字段长度定义遵循最小化原则,够用就行,不能贪图方便定义很大的长度。
字段实际值也遵循最小化原则,在满足业务需求的前提下,选择最合适的数据类型,写入最少的数据
对于字符类型,字段长度定义的是字符个数,而不是字节个数,所占空间与字符集相关。对于int类型,占用空间是固定的,指定长度的话只是制定了显示长度。
区分使用DATETIME和TIMESTAMP。存储年使用YEAR类型。存储日期使用DATE类型。 存储时间(精确到秒)建议使用TIMESTAMP类型。
使用TIMESTAMP的自动赋值属性
一个表的字段个数控制在30-50个字段以内;如果字段超过50个,可考虑将字段按冷热程度分表。
严格禁止单条记录超过8K
建议每一个重要的业务表都加上 create_time 和 modify_time 两个字段,数据类型为datetime;后续的所有更新都必须更新modify_time字段。
对于可能出现在where条件中的字段,尽量设置为非空(not null)
字段字符集与表保持一致,不单独设置字符集。
相同含义的字段在不同表中应使用相同的名称,数据类型及长度必须保持一致。
索引/主键设计
Innodb表每一个表都要设置主键。主键越短越好,最好是auto_increment类型;如果不能使用自增,则应考虑构造使用单向递增型主键;禁止使用随机类型值用于主键。
主键最好由一个字段构成,最多不要超过3个,禁止超过3个字段的组合主键。如果业务要求,则可以创建一个自增字段作为主键,再添加一个唯一索引。
如果一个业务上存在多个(组)唯一键,以查询最常用的唯一键作为主键。
索引会降低DML的性能,不是越多越好,只创建需要的索引,避免冗余索引。
选择作为主键的列必须在插入后不再修改或者极少修改,否则需考虑使用自增列作为主键。
创建组合索引/主键时,常用的字段放在前面;选择性高的字段放在前面。
创建组合索引时,如果where条件中过滤性不强,且需要排序分页操作,建议把排序字段也加到组合索引中,放在组合索引最后列。
对较长的字符数据类型的字段建索引,优先考虑前缀索引。
低选择性的列不加索引,例如”性别”
禁止冗余索引
在建表时,应充分考虑需要添加什么索引,尽量避免上线后添加索引
分库分表
如果数据有时效性,则建议按时间分表或者分区
如果所有数据热度相同,则建议根据hash或者其他手段分库分表
杜绝只按月份,不考虑年份方式分表
如果多个独立业务需要共用一台DB的话,不同业务的表必须放在不同的database(schema)里
对于分库分表,每一个表的索引结构及名称都必须一致
其它
开发在提交数据库新增对象请求单时,需按附件模板提供足够的信息供DBA决策
示例:
表名
|
t_order_x
|
---|---|
DDL | 详见req文件(如果少的话,可直接附在这里) |
用途 | 用户订单表 |
未来一年数据量预测 | 每天增量200W条 |
是否分表 | 是 |
分表方式 | 百库十表 |
活跃数据范围 | 最近3月 |
核心SQL及执行频率 | select col1,col2 from c2c_db_00.t_order_1 where flistid='xxxxx';每天执行400W次 |
需求提出者 | developer name |
其他说明 | 无 |