§ Schema设计规范参考


本文介绍在使用和基于GreatSQL数据库进行业务系统应用开发时建议遵循的Schema开发设计规范。

§ 命名规范

对数据库对象采用统一的命名规则和标准,使得应用代码的可读性更强,便于他人阅读、理解和继承。

下面是数据库对象命名规范的参考:

  • 所有命名长度都不要超过30个字符。
  • 统一使用大小写风格,即要么都大写,要么都小写,不要大小写混合。
  • 用简洁又合适的英文单词表示业务意义,单词间用下划线 "_" 分隔开。
  • 对象名不要用下划线 "_" 开头和结尾。
  • 尽量不用中文拼音命名。
  • 尽量不用纯数字命名。
  • 日志业务类型表名加上 "log_" 前缀。
  • 数据归档类型表名加上 "arch_" 前缀。
  • 备份用途类型表名加上 "bak_" 前缀。
  • 临时用途类型表名加上 "tmp_" 前缀。
  • 以日期时间作为分库分表的对象名,可以加上日期时间后缀,示例:trans_log_2023
  • 要避免使用保留字和关键字,详情参考:保留字、关键字
  • 尽可能使用 id 作为表的主键列名,如果主键索引包括多个列,每个列也都尽量带上 _id 关键字,例如:(city_id, user_id)。
  • 普通索引名带上前缀 "idx_"。
  • 主键约束加上 "pk+" 前缀。
  • 唯一约束加上 "uk_" 前缀。
  • 外键约束加上 "fk_" 前缀。
  • 存储过程加上 "sp_" 前缀。
  • 视图加上 "v_" 前缀。
  • 用户自定义函数加上 "udf_" 前缀。
  • 触发器加上 "tr_" 前缀。
  • 序列加上 "seq_" 前缀。

下面是一些较为不错的命名示例:

  • user_db
  • users
  • idx_user_id
  • uk_user_id
  • fk_city_id
  • v_user_detail_info
  • seq_user_id

§ Schema开发设计规范

  1. 所有表默认都采用InnoDB存储引擎,若需要使用MyISAM等不支持事务的存储引擎要先和DBA确认必要性。

  2. 每个表都应有显式声明的主键列,尽可能采用自增INT/BIGINT类型作为主键列,且该主键列没有业务用途。这么做的目的是为了避免在业务中因插入、删除、更新数据时,不会因为主键列值变化而导致InnoDB数据页产生过多碎片。

  3. 每个表都尽量加上 gmt_creategmt_modify 这两个列,用于表示用户数据 首次创建时间最后一次更新时间,这两个列数据还可以用于后续更多用途,例如一段时间后要归档冷数据,就可以根据 gmt_modify 时间做判断。

  4. 数据类型INT(包括BIGINT/INT/MEDIUMINT/SMALLINT/TINYINT等)的效率相对而言是最好的,因此优先采用这个类型,例如ENUM/SET也可以改用INT来表示;此外,INT类型尽量加上UNSIGNED约定,增加可用范围。

  5. 除非真的有必要,否则尽量不使用TEXT/BLOB/JSON等大对象数据类型。

  6. 尽量不使用FLOAT/DOUBLE等浮点型,改用DECIMAL型,可以提高数据精确度,也便于满足校验主从数据一致性等需求。

  7. 每个列定义都加上NOT NULL属性。

  8. 多表中的相同业务意义的列,必须保证列定义一致。

  9. 从实例级,到Schema(database),再到表、列,包括存储过程、触发器、events等所有数据对象,都采用同一种字符集,默认推荐使用utf8mb4,其兼容性最好。

  10. 可能进行join关联的列,其数据类型定义(包括字符集和校验集)要一致,避免发生隐式转换。

  11. 一个实例内,数据表(包含表分区在内)数量通常不要超过一万个,每个Schema(database)内数据表的数量通常不要超过一千个,避免元数据管理开销太高。

  12. 数据表结构设计尽可能满足第三范式要求,但也不能过于教条机械执行,而应以达到实际业务最佳性能为指导原则,适当冗余存储部分数据,减少表间关联查询从而提升业务性能。一般而言,适当冗余的数据不包括以下几种情况:

    • VARCHAR/CHAR等长字符串类型;
    • TEXT/BLOB等大对象类型;
    • 频繁更新的列。

greatsql-wx