数据库设计规范
数据库使用最佳实践
名称 | 最佳实践(建议值) |
---|---|
单节点数据量最大值 | 16TB(需考虑备份恢复规格目标) |
表和索引的总数 | 10000个(单个Schema的表数量不建议超过200个) |
单表的字段个数 | 小于50个 |
单表的索引个数 | 小于5个 |
单表的复合索引个数 | 小于3个 |
复合索引的列数 | 小于5列 |
单行的行宽 | 小于2000 |
单个字段的容量 | 小于10MB |
SQL语句的长度 | 小于5000 |
数据库对象命名规范
- 不能使用数据库关键字命名用户创建的数据库对象。
查询数据库保留关键字:
select * from pg_get_keywords();
- 常见数据库对象命名长度不能超过63个字节。
包括数据库名、表名、临时表(非业务表)、表字段、视图名及其字段名、函数名及其参数名。
- 应该尽量避免使用双括号括起来的字符串来命名数据库对象。
GaussDB默认不区分SQL中数据库对象的大小写,除非该名称是用双括号括起来的。
数据库对象命名不建议区分大小写,因为会使得问题定位的难度增加。
数据库设计规范
- 通过JDBC连接数据库时需指定数据库名称,并且连接成功后不支持切换到其他数据库。
- 目前不支持不区分大小写的排序方式。
- 数据库管理员需要为业务创建database、schema和user。
- 建议使用SCHEMA进行业务隔离(而不是DATABASE)。
- 创建数据库推荐使用UTF8字符集(与MySQL utf8mb4等价)。数据库创建后无法修改字符集。
- 创建数据库时建议指定
LC_CTYPE
和LC_COLLATE
参数(与排序有关)。
权限管理规范
- 用户授权应尽量满足最小化授权原则。
- 建议通过角色而不是用户来管理权限。
- 在删除指定的数据库前,应回收用户对其的CONNECT权限,以避免删除失败。
用户和角色为多对多的关系。删除用户时,不会影响到角色。
修改角色的权限,会同时更新到被授予该角色的所有用户。
字符集规范
- 客户端连接参数中的字符集必须与数据库字符集保持一致。建议数据库和客户端均使用统一的UTF8字符集。
- 目前仅支持对数据库定义字符集,暂不支持对表、字段等指定字符集。
表和视图设计规范
- 对于数据量大的表,选择合理的分区方案。目前支持范围分区、列表分区和哈希分区。
- 范围分区的上边界分区值应该定义为MAXVALUE,以防止数据溢出。
- 单表的分区数量不建议超过100个。单个分区的数据量不建议超过5千万。单个分区的数据容量不建议超过50GB。
- 视图定义中不建议嵌套。
- 视图定义中应尽量避免排序操作。
视图嵌套可能会因为无法使用索引而导致查询效率底下,建议尽量使用带有索引的基表。
ORDER BY子句在顶层视图上无效,如果必须对输出数据排序,建议在调用视图中使用ORDER BY。
字段设计规范
- 尽量使用高效的数值类数据类型。在满足精度的前提下,选择的优先级为:整数 > 浮点数 > NUMERIC。
- 尽量少用NUMERIC和DECIMAL数据类型(对CPU资源消耗高)。
- 选用合适的字符串数据类型:定长字符类型CHAR(n)或变长字符类型VARCHAR。
- 不建议为变长字符类型指定长度。
不建议为变长字符类型(VARCHAR/TEXT)指定长度的原因如下:
- 定长字段会为长度不够的输入数据补充空格,然后存入数据库,会导致不必要的存储空间浪费;
- 变长字段如果指定了长度,后续扩展长度时需要对全表进行扫描重写,性能开销大;
- 变长字段如果指定了长度,每次插入数据时会检查是否长度越界,会有性能开销。
- 字符类型字段不应该存储数字类型的数据。
- 字符类型字段不应该存储时间或日期类型的数据。
使用字符类型字段存储数字、时间或日期类型的数据,会有额外的数据类型转换开销、并且可能会导致索引失效等问题。
- 对于明确不存在NULL值的字段,建议加上NOT NULL约束。
- 进行关联操作的字段,字段类型应保持一致,否则会产生额外的数据类型转换开销。
- 用于WHERE条件过滤和关联操作的字段,建议都应设置NOT NULL约束。
- 同一张表中,大字段(例如VARCHAR(1000)这种字段)不应超过8个。
- 建议为字段添加COMMENT信息,以便于未来维护。
- 不建议对表预留字段。大部分场景下支持快速新增、删除字段。
索引设计规范
- 索引字段必须为NOT NULL。
- 不建议单表上创建多个唯一索引。可以使用一个多列唯一索引替代。
同时维护多个唯一索引的开销远大于维护一个多列唯一索引。
- 数值类型字段上创建索引的维护效率高于字符和其他数据类型。对于考虑创建索引的id和时间等字段,建议使用数值类型存储。
- 建议在关联操作的字段上创建索引。
- 复合索引字段个数不应超过5个。
- 禁止总字符串长度超过200的复合索引。
存过和函数
- 使用存储过程、触发器等实现业务逻辑,应该将业务逻辑放到业务服务器上处理,避免对数据库产生逻辑依赖。
- 仅创建对固定入参有固定返回值的函数,函数必须为IMMUTABLE和SHIPPABLE类型。
关于GaussDB中的函数类型:
- GaussDB支持IMMUTABLE、STABLE、VOLATILE三种类型的函数。
- 对于IMMUTABLE和SHIPPABLE类型的函数,允许在DN节点上执行,执行效率很高。
- 如果函数的结果依赖对数据表的扫描结果(例如某列的最大值)、或依赖时间,函数必须为STABLE或VOLATIBLE,且NOT SHIPPABLE,以保证执行结果的正确性。