Menu Close

数据库命名与设计规约

lcc0556

说明：本文参考阿里巴巴Java开发手册，以MySQL语法为主、Oracle语法为辅做为示例撰写。

1. 设计规约

1.1. 表设计规约

  1. 遵守数据的设计规范 3NF 规定。
    参见：附录

  2. 表的字段数不超过30个，如果超过该值，可以根据业务情况进行拆分，比如根据常用字段和非常用字段将一张表拆分成两张；也可以根据用途、配置等分成不同的表。

  3. 采用UTF8字符集或UTF8mb4字符集。

  4. 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。
    说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

  5. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

    • 不是频繁修改的字段。
    • 不是唯一索引的字段。
    • 不是 varchar 超长字段，更不能是 text 字段。
      正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。

  6. 分区表——对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。
    如果表按某些字段进行增长，则采用按字段值范围进行范围分区；
    如果表按某个字段的几个关键值进行分布,则采用列表分区；
    对于静态表,则采用 hash 分区或列表分区；
    在范围分区中，如果数据按某关键字段均衡分布,则采用子分区的复合分区方法。

1.2. 字段设计规约

  1. 凡是可能被索引的字段,必须定义为NOT NULL,可以设置default值。

  2. 非负值的数字统一使用 unsigned（无符号） 类型存储。

  3. 大对象字段

    • 通常情况下，禁止使用LOB类型保存大文本、图片和文件,建议使用其他方式存储（例如文件系统,数据库只保存其地址信息）。
    • MySQL：尽量不要使用TEXT数据类型，mysql的varchar类型支持65535字节，满足大多数场景，仅当字符数特别大时,才考虑text类型。

  4. 禁止使用enum,对于boolean类型或者表示简单状态的字段,MySQL用 tinyint,Oracle用NUMBER(1)

    • 建议字段not null,根据业务要求来设置默认值（例如默认为0）。
    • 对于boolean类型,以1代表是（true）, 0 代表否（false）。
    • 对于状态类型，注释中应该注明每一种状态的含义，例如0：编辑中,1：审核中,2：已完成。

  5. 数字、小数类型

    • 对于数字、小数类型，不得使用VACHAR等字符串类型来保存，应该使用相应精度的数字、小数类型。
    • 尽量确保数值型列都有默认值。
    • 对于Oracle，确定好Number的精度。
    • 对于MySQL,选好数字类型：TINYINT > SMALLINT > MEDIUMINT > INT > BIGINT > DECIMAL(存储空间逐渐变大,而性能却逐渐变小),超过 tinyint（256） 但不超过 65536 的使用smallint；当该字段超过42亿时,才使用bigint；
      file

  6. 时间类型标准

    • Oracle有两种时间类型：DATE和TIMESTAMP,DATE的精度只保存到秒,例如2013-11-02 11:16:36，而TIMESTAMP精度更高可以保存小数秒，例如2013-11-03 11:16:36.000000 。有时候 DATE 只保存到秒,不足够区别出两个事件哪个先发生，这时建议使用TIMESTAMP类型。
    • MySQL存储年使用year类型，仅存储日期使用date类型；存储带时间的日期时使用datetime类型；使用精确时间戳（精确到秒）尽量使用timestamp类型,因为timestamp使用4字节,datetime使用8字节,它们的区别：TIMESTAMP值不能早于1970或晚于2037（1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC）。

1.3. 索引设计规约

  1. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。
    说明：不要以为唯一索引影响了 insert 速度，这个速度损耗可以忽略，但提高查找速度是明显的；另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，根据墨菲定律，必然有脏数据产生。

  2. 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
    说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名, 索引长度))/count(*) 的区分度来确定。

  3. 在分区表中不建议使用全局索引，因为做分区维护操作时可能会导致全局索引失效，造成难以维护。

  4. 单表的索引数量尽量控制在5个以内，单个字段上的索引不能超过2个。

  5. Order by、distinct、group by后的字段尽量建立索引。
    说明：如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
    正例：where a=? and b=? order by c; 索引：a_b_c
    反例：索引如果存在范围查询，那么索引有序性无法利用，如：WHERE a>10 ORDER BY b; 索引 a_b 无法排序。

  6. 利用覆盖索引来进行查询操作，避免回表。
    说明：如果一本书需要知道第 11 章是什么标题，会翻开第 11 章对应的那一页吗？目录浏览一下就好，这个目录就是起到覆盖索引的作用。
    正例：能够建立索引的种类分为主键索引、唯一索引、普通索引三种，而覆盖索引只是一种查询的一种效果，用 explain 的结果，extra 列会出现：using index。

  7. update、delete的where尽量使用有索引的字段或主键。

  8. 不建议在低基数列上(类似于数据字典列)创建索引,例如性别列。

  9. 高并发表的索引设计，应避免索引字段采用单序列生成，否则容易产生索引争用，降低数据插入效率。

  10. 经常与其他表进行连接的表,在连接字段上应该建立索引。

  11. 经常出现在 Where 子句中的字段且过滤性很强的,特别是大表的字段,应该建立索引。

  12. 复合索引的建立需要进行仔细分析

    • 复合索引的字段数不能超过5个，并合理创建联合索引（避免冗余）,(a,b,c) 相当于（a）、（a、b）、（a、b、c）;
    • 正确选择复合索引中的第一个字段,一般是选择性较好的且在where子句中常用的字段上；
    • 复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是,则可以建立复合索引；否则考虑单字段索引；
    • 如果复合索引中包含的字段经常单独出现在Where子句中,则分解为多个单字段索引；
    • 如果既有单字段索引,又有以这个字段为首列的复合索引,一般可考虑删除单字段索引；
    • 复合索引第一个字段一般不使用时间字段，因为时间字段多用于范围扫描，而前面的字段使用范围扫描后，后续字段无法用于索引过滤。

  13. 建组合索引的时候，区分度最高的在最左边。
    正例：如果 where a=? and b=?，a 列的几乎接近于唯一值，那么只需要单建 idx_a 索引即可。
    说明：存在非等号和等号混合判断条件时，在建索引时，请把等号条件的列前置。如：where c>? and d=? 那么即使 c 的区分度更高，也必须把 d 放在索引的最前列，即建立组合索引 idx_d_c。

  14. 频繁 DML(写次数明显超过读次数)的表,不要建立太多的索引。

  15. 无用的索引以及重复索引应删除，避免对执行计划及数据库性能造成负面影响。

  16. OLTP 系统，禁止使用位图索引。OLAP 系统，对于部分经常在条件中出现，且唯一值不大的字段，可以考虑使用位图索引，但是需要注意该表不能进行多个进程的同时 DML。

  17. 防止因字段类型不同造成的隐式转换，导致索引失效。

  18. 创建索引时避免有如下极端误解：

    • 1） 索引宁滥勿缺。认为一个查询就需要建一个索引。
    • 2） 吝啬索引的创建。认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度。
    • 3） 抵制惟一索引。认为惟一索引一律需要在应用层通过“先查后插”方式解决。

1.4. 性能优化

  1. SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好。
    说明：
    1） consts 单表中最多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
    2） ref 指的是使用普通的索引（normal index）。
    3） range 对索引进行范围检索。
    反例：explain 表的结果，type=index，索引物理文件全扫描，速度非常慢，这个 index 级别比较 range 还低，与全表扫描是小巫见大巫。

  2. 利用延迟关联或者子查询优化超多分页场景。
    说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当 offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL 改写。
    正例：先快速定位需要获取的 id 段，然后再关联：SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id

  3. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
    说明：索引文件具有 B-Tree 的最左前缀匹配特性，如果左边的值未确定，那么无法使用此索引。

2. 命名规约

2.1. 总命名规约

  1. 不得使用数据库保留关键字，以及java等常用语言的保留关键字，或者可能成为关键字的单词作为完整命名。（对于一些疑似关键字的单词，可以在后面加一个下划线来避免，例如 key_）.
    附：

  2. 表名、字段名必须用英文字母开头，禁止出现数字开头、采用有特征含义的单词或缩写，单词中间用 _ 分割，不能用双引号包含。数据库字段名的修改代价很大，因为无法进行预发布，所以字段名称需要慎重考虑。
    说明：MySQL 在 Windows 下不区分大小写，但在 Linux 下默认是区分大小写。因此，数据库名、表名、字段名，都不允许出现任何大写字母，避免节外生枝。
    正例：aliyun_admin，rdc_config，level3_name
    反例：AliyunAdmin，rdcConfig，level_3_name

  3. 表、字段、索引等命名不超过30个字符。（Oracle 11g中元数据的名称长度有30个字符的限制，在Oracle12c及更高的版本中，这个长度可达到128个字符，而在MariaDB10.5版本中该长度不能超过64个字符。）

  4. 小数类型为 decimal，禁止使用 float 和 double。
    说明：在存储的时候，float 和 double 都存在精度损失的问题，很可能在比较值的时候，得到不正确的结果。如果存储的数据范围超过 decimal 的范围，建议将数据拆成整数和小数并分开存储。

  5. 不得使用外键与级联，一切外键概念必须在应用层解决。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

2.2. 表命名规约

  1. 表名不使用复数名词。
    说明：表名应该仅仅表示表里面的实体内容，不应该表示实体数量，对应于 DO 类名也是单数形式，符合表达习惯。

  2. 表必备三字段：id, create_time, update_time。
    说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。create_time, update_time的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。

2.3. 字段命名规约

  1. 各表之间相同含义的字段，类型定义要完全相同（包括精度、默认值等）。

  2. 如果存储的字符串长度几乎相等，使用 char 定长字符串类型。

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

  4. 表达是与否概念的字段，必须使用 is_xxx 的方式命名，数据类型是 unsigned tinyint（1 表示是，0 表示否）。
    说明：任何字段如果为非负数，必须是 unsigned。
    注意：POJO 类中的任何布尔类型的变量，都不要加 is 前缀，所以，需要在 <resultMap> 设置从 is_xxx 到 Xxx 的映射关系。数据库表示是与否的值，使用 tinyint 类型，坚持 is_xxx 的命名方式是为了明确其取值含义与取值范围。
    正例：表达逻辑删除的字段名 is_deleted，1-表示删除，0-表示未删除。

  5. 字段命名及其注释,要做到清楚、无歧义。
    说明：有些数据可能会存在多种完全不同类型的状态，例如：例如汽车数据有启停状态、参保状态、维修状态、年审状态……总之，在有些数据表中，有许多的状态字段。例如有个字段 STATUS – 状态，这是让人很疑惑的，状态？到底是什么状态？状态的取值有哪些？——如果改成 DELETE_STATUS – 删除状态(1-已删除,默认为0-未删除)，这样的命名和注释，让人一目了然。
    说明：再比如belong_dept – 所属部门，这也有歧义。因为部门除了数据唯一ID之外,还有一个部门编码CODE也是唯一的。那到底是存 部门ID，还是 部门编码 CODE？实际情况是，有的人认为存ID，有的却认为存编码。所以，在命名上就应该做到无歧义,如果要存ID，就应该命名为belong_dept_id – 所属部门ID；如果要存部门编码，就应该为belong_dept_code – 所属部门编码。

2.4. 约束与索引命名规约

  1. 主键约束命名：pk_表名
    说明：pk_ 即 primary key 的简称。创建主键约束时，会自动的创建与主键约束同名的主键索引。

  2. 外键约束命名：fk_表名_字段名
    说明：fk_ 即 foreign key 的简称。表名可以去掉前缀。不建议使用外键约束，但在数据库模型中需要体现外键关系。

  3. 唯一约束命名：uk_表名_字段名；
    说明：uk_ 即 unique key 的简称。表名可以去掉前缀。创建唯一约束时，会自动的创建与唯一约束同名的唯一索引。

    • 唯一约束和唯一索引，都可以实现列数据的唯一，列值可以有null。
    • 创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。
    • 创建一个唯一索引，这个索引就是独立，可以单独删除。
    • 如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。
    • 如果表的一个字段，要作为另外一个表的外键，这个字段必须有唯一约束（或是主键），如果只是有唯一索引，就会报错。

  4. 普通索引命名： idx_表名_字段名。
    说明：idx_ 即 index 的简称。表名可以去掉前缀。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。

2.5. 其他命名规约

  1. 函数命名：fn_模块名称_功能描述。
    说明： fn_ 即 function 的简称。函数名应与其实际功能保持一致，导致发生某动作应以动词为前缀命名。
    正例：fn_public_getdate（公共函数：获取日期）

  2. 存储过程命名：sp_模块名称_功能描述
    说明：sp_ 即 storage procedure 的简称。命名方式同函数命名。
    正例：sp_admin_getparameters(管理模块的存储过程：获取参数)。

  3. 视图命名：vw_模块名称_功能描述
    说明：vw_ 是 view 的简称。
    正例：vw_user_detail(用户详情视图)

  4. 包命名：pkg_模块名称_功能描述
    说明：pkg_ 是 package 的简称。

  5. 序列命名：seq_表名_字段名
    说明：seq_ 是 sequence 的简称。如果只有主键用到序列，则可简写为：seq_表名。

  6. 触发器命名：tr_表名_字段名
    说明：tr_ 是 trigger 的简称。

3. SQL规约

  1. 不要使用 count(列名)或 count(常量)来替代 count(*)，count(*)是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和非 NULL 无关。
    说明：count(*)会统计值为 NULL 的行，而count(列名)不会统计此列为 NULL 值的行。

  2. count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。

  3. 当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为NULL，因此使用 sum()时需注意 NPE 问题。
    正例：可以使用如下方式来避免 sum 的 NPE 问题：SELECT IFNULL(SUM(column), 0) FROM table;

  4. 使用 ISNULL()来判断是否为 NULL 值。
    说明：NULL 与任何值的直接比较都为 NULL。
    1） NULL<>NULL 的返回结果是 NULL，而不是 false。
    2） NULL=NULL 的返回结果是 NULL，而不是 true。
    3） NULL<>1 的返回结果是 NULL，而不是 true。
    反例：在 SQL 语句中，如果在 null 前换行，影响可读性。select * from table where column1 is null and column3 is not null; 而ISNULL(column)是一个整体，简洁易懂。从性能数据上分析，ISNULL(column)
    执行效率更快一些。

  5. 代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句。

  6. 不得使用外键与级联，一切外键概念必须在应用层解决。
    说明：（概念解释）学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。

  7. 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

  8. 数据订正（特别是删除或修改记录操作）时，要先 select，避免出现误删除，确认无误才能执行更新语句。

  9. 对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定。
    说明：对多表进行查询记录、更新记录、删除记录时，如果对操作列没有限定表的别名（或表名），并且操作列在多个表中存在时，就会抛异常。
    正例：select t1.name from table_first as t1 , table_second as t2 where t1.id=t2.id;
    反例：在某业务中，由于多表关联查询语句没有加表的别名（或表名）的限制，正常运行两年后，最近在某个表中增加一个同名字段，在预发布环境做数据库变更后，线上查询语句出现出 1052 异常：Column 'name' in field list is ambiguous。

  10. SQL 语句中表的别名前加 AS，并且以 t1、t2、t3、...的顺序依次命名。
    说明：
    1）别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。
    2）别名前加 AS 使别名更容易识别。
    正例：select t1.name from table_first as t1, table_second as t2 where t1.id=t2.id;

  11. in 操作能避免则避免，若实在避免不了，需要仔细评估 in 后边的集合元素数量，控制在 1000 个之内。

  12. 因国际化需要，所有的字符存储与表示，均采用 utf8 字符集，那么字符计数方法需要注意。
    说明：
    SELECT LENGTH("轻松工作")； 返回为 12
    SELECT CHARACTER_LENGTH("轻松工作")； 返回为 4
    如果需要存储表情，那么选择 utf8mb4 来进行存储，注意它与 utf8 编码的区别。

  13. TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE 无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。
    说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。

附录

附录A. 相关联的文章

【强制】
说明：
正例：
反例：
【推荐】
【参考】

附录B. 其他参考

附录C. 数据库设计三范式

  • 什么是范式
    规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

  • 三大范式
    目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。
    满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

  • 第一范式1NF
    数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性。
    file

  • 第二范式2NF
    在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。
    所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。(比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。)

    • 特点：
      1. 一张表只描述一件事情。
      2. 表中的每一列都完全依赖于主键。
    • 概念：
      1. 函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A
        例如：学号–>姓名。 （学号，课程名称） --> 分数
      2. 完全函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。
        例如：（学号，课程名称） --> 分数
      3. 部分函数依赖：A–>B， 如果A是一个属性组，则B属性值得确定只需要依赖于A属性组中某一些值即可。
        例如：（学号，课程名称） – > 姓名
      4. 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A
        例如：学号–>系名，系名–>系主任
      5. 码：如果在一张表中，一个属性或属性组，被其他所有属性所完全依赖，则称这个属性(属性组)为该表的码
        例如：该表中码为：（学号，课程名称）
        主属性：码属性组中的所有属性
        非主属性：除过码属性组的属性
        file

  • 第三范式3NF
    在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。
    简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足 2NF 的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在 A → B → C 的决定关系，则 C 传递依赖于 A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列 x → 非主键列 y
    file

附录D. MySQL唯一索引约束

  • 创建一张测试表
CREATE TABLE test_unique (
  id int(10) UNIQUE
) ENGINE=InnoDB;
  • 插入数据
INSERT INTO test_unique VALUES(1);
INSERT INTO test_unique VALUES(5);
INSERT INTO test_unique VALUES(6);
INSERT INTO test_unique VALUES(10);
SELECT * FROM test_unique;
  • 查看表结构

file

  • 执行更新操作
UPDATE test_unique
   SET id = id + 1;
  • 抛出异常
ERROR 1062 (23000): Duplicate entry '6' for key 'id'
Post Views: 372
Tagged ,