磐维数据库使用建议

1 数据库设计规范

1.1【规则】数据库使用最佳实践推荐

1.2【规格】不使用保留或非保留关键字命名数据库对象

可以使用如下命令查询数据库关键字：

SELECT * FROM pg_get_keywords();

1.3【规则】避免使用双引号括起来的字符串来定义数据库对象名 称，除非必须限制数据库对象名称的大小写

1.4【规则】创建数据库时指定字符集为UTF8，创建数据库时必须选择与客户端统一的编码字符集

说明：数据库编码应能够存储与标识绝大多数的字符，因此推荐使用UTF8。 UTF8字符集与MySQL的UTF8MB4等价，能够支持emoji表情字符。 如果客户端的编码方式与数据库的编码方式不统一，会带来转码性能，同时，针对同编码的内核 优化无法触发，影响查询效率。

1.5【建议】从便捷性和资源共享效率上考虑，建议使用SCHEMA 进行业务隔离

说明：可以使用DATABASE和SCHEMA两种方式实现业务的隔离。区别在于：

● DATABASE方式隔离共享资源极少，可实现连接隔离、权限隔离等，隔离更加彻底。但 DATABASE之间无法互相访问，JDBC建连时必须指明DATABASE，连接后无法切换 DATABASE。

● SCHEMA方式隔离共用资源较多，可以通过GRANT与REVOKE语法便捷地控制不同用户对各 SCHEMA及其下属对象的权限。

1.6【建议】创建数据库时建议指定LC_CTYPE和LC_COLLATE， 该参数将影响数据的排序顺序

2 权限设计规范

2.1【规则】数据库初始用户连接和访问数据库应作为DBA管理用 途，不允许业务直接使用该数据库用户连接和访问数据库

2.2【规则】数据库初始用户为业务创建用户和数据库，业务使用所创建的用户登录和访问数据库

2.3【规则】合理对角色和用户赋权，应使用最小化权限原则

2.4【建议】通过角色而不是用户来管理权限

说明：使用角色管理权限，即在角色中配置权限，再将角色赋予用户。 通过角色管理权限，更便于多用户、用户变更等场景下的权限管理。

例如： ● 角色和用户为多对多关系，一个角色可以赋予多个用户，修改角色中的权限，被赋予角色的 用户权限就可以同时更新；

● 删除用户时，不会影响到角色；

● 新建用户后可以通过赋予角色快速获取所需权限

2.5【建议】在删除指定数据库时，应回收用户对该数据库的 CONNECT权限，避免删除时仍然存在活跃的数据库连接而失败

3 表设计规范

3.1【规则】对于大数据表，选取适当的分区方案

3.2【规则】视图不建议嵌套

3.3【建议】视图定义中尽量避免排序操作

4 字段设计规范

4.1【规则】尽量使用高效的数值类数据类型。在满足业务精度的 情况下，选择的优先级从高到低依次为整数、浮点数、NUMREIC

4.2【规则】合理设置数值字段的数据类型，根据取值范围选择合 适的数值类型，尽量少用NUMERIC/DECIMAL类型

4.3【规则】合理选用字符串数据类型。如果该字段输入确定为固 定字符则使用定长字符类型，或需要自动补充空格，否则请使用变长 字符类型VARCHAR。

说明：典型的定长字段类型，例如“sex”字段，仅允许输入“f”或“m”一个字节长度的字符。这类 字段建议使用定长数据类型（如CHAR(n)）。 如果不存在此特点，或者后续可能扩展需要输入更长的字符，请优先使用变长字符类型（如 VARCHAR, TEXT），且不建议指定变长类型的长度。 原因如下：

● 定长字段会对不够长度的输入数据补充空格，然后存入数据库中，产生不必要的存储空间浪 费。

● 如果定义为定长字符类型，后续扩展长度，需要对全表进行扫描重写，性能开销大，影响在 线业务。

● 对于指定固定长度的变长字段，每次插入时会检查是否长度越界，带来性能开销。

4.4【规则】对于明确不存在NULL值的字段加上NOT NULL约束

4.5【建议】不建议对表预留字段。大部分场景下可支持快速新 增、删除表字段，或者修改字段的DEFAULT值

说明：新增列必须符合以下要求，否则会带来全表更新开销，影响在线业务。

1. 数据类型为以下类型中的一种：BOOL, BYTEA, SMALLINT, BIGINT, SMALLINT, INTEGER, NUMERIC, FLOAT, DOUBLE PRECISION, CHAR, VARCHAR, TEXT, TIMESTAMPTZ, TIMESTAMP, DATE, TIME, TIMETZ, INTERVAL；

2. 新增列的DEFAULT值长度不超过128个字节；

3. 新增列DEFAULT值不包含volatile函数；

4. 新增列设置有DEFAULT值，且DEFAULT值不为NULL。

5 索引设计规范

5.1【规格】使用数据库索引实践推荐的索引类型

5.2【规则】合理设计组合索引，避免冗余

说明：例如已对(a,b,c)创建索引，则不应再单独对 (a)、（b）、（c）、(a,b)、(b,c)创建索引。 当查询时如果只带有a字段上的过滤条件，一般也会利用组合索引进行查询。

5.3【规则】不建议单表创建多个唯一索引

说明:同时维护多个唯一索引的开销远大于维护一个多列唯一索引，如果业务逻辑上多个唯一索引，与 一个多列唯一索引等价，应使用多列唯一索引。

5.4【规则】组合索引字段个数不超过5个

5.5【建议】同字段上创建索引的维护效率不同。数值类型字段优 于字符类型及其他数据类型，因此对于考虑创建索引的ID、时间等 字段，建议使用数值类型进行存储

5.6【建议】应考虑在关联列上创建索引

说明:支持HASH JOIN，但是当内表较小等RESCAN代价较低的情况下，仍然可能选择NESTLOOP JOIN来完成关联。如果通过EXPLAIN可以查看到NESTLOOP JOIN计划，则可以通过在关联列上 创建索引，提高NESTLOOP JOIN效率。

6 函数/存储过程设计规范

6.1【规则】使用存储过程、触发器等实现业务逻辑，应该将这 些逻辑都放到业务服务器上处理，避免对数据库产生逻辑依赖

6.2【规则】仅创建对固定入参有固定返回值的函数，函数必须设为IMMUTABLE和SHIPPABLE类型

说明:目前数据库支持三种类型的函数，分别是IMMUTABLE、STABLE、VOLATILE。 对于IMMUTABLE函数且设置为SHIPPABLE的函数，会允许函数在DN上执行。在大部分场景 下，该函数的执行效率较高。 但是此类函数要求对于固定的入参得到固定的返回值，来保证函数在DN上执行的正确性。如果 函数的结果依赖对数据表的扫描结果（例如获取某个表中列的max值）或依赖时间（如获取当前 时间），那么函数应设置为STABLE或者VOLATILE，且NOT SHIPPABLE，以保证函数执行的正 确性。

7 WHERE使用建议

7.1【规则】禁止在WHERE条件相同表字段进行相互比较

7.2【规则】禁止WHERE条件涉及隐式数据类型转换

7.3【规则】禁止WHERE 条件字段使用表达式或是函数

7.4【规则】模糊查询LIKE语句，%不应放在首字符位置

7.5【建议】WHERE条件中IN的候选子集不易过大，建议不超过500

7.6【建议】多使用等值操作，少使用非等值操作

8 SELECT使用建议

8.1【规则】避免SELECT语句中通配符字段“*”

说明：使用通配符字段查询表时，如果因业务或数据库升级导致表结构发生变化，可能出现与业务语句不兼容的情况。 因此业务应指明所需查询的表字段名称，避免使用通配符

8.2【规则】避免对大字段（如VARCHAR(2000)）执行ORDER BY、DISTINCT、GROUP BY、UNION等会引起排序的操作

说明：此类操作将消耗大量的CPU和内存资源，执行效率低下

8.3【规则】避免LOCK TABLE 语句加锁，使用 SELECT .. FOR UPDATE语句

说明：LOCK TABLE提供多种锁级别，但如果对数据库原理和业务理解不足，误用表锁可能触发死锁，导致数据库不可用

8.4【建议】避免在SELECT目标列中使用子查询，可能导致计划 无法下推到DN执行，影响执行性能

8.5【建议】考虑使用UNION ALL，少使用UNION，注意考虑去重

说明UNION ALL不去重，少了排序操作，速度相对UNION更快。 如果没有去重的需求，优先使用UNION ALL

8.6【建议】避免频繁使用下使用count()获取大表行数，该操作 资源消耗较大，影响并行作业执行效率

9 UPDATE使用建议

9.1【规格】不支持UPDATE语句中直接使用LIMIT，应使用 WHERE条件明确需要更新的目标行

9.2【规格】不支持多表更新

9.3【规则】UPDATE语句中使用ORDER BY、GROUP BY子 句，避免不必要的排序

9.4【建议】有主键/索引的表，更新时WHERE条件应结合主键/索引

10 DELETE 使用建议

10.1【规格】不支持DELETE语句中使用LIMIT。应使用WHERE 条件明确需要更新的目标行

10.2【规格】不支持多表删除 说明 多表删除即在单条SQL语句中，对多个表进行删除。

10.3【规则】DELETE语句中必须有WHERE子句，避免全表扫描

10.4【规则】DELETE语句中使用ORDER BY、GROUP BY子句， 避免不必要的排序

10.5【规则】如果需要清空一张表，建议使用TRUNCATE

10.6【建议】DELETE有主键或索引的表，WHERE条件应结合主 键或索引，提高执行效率

11 关联查询使用建议

11.1【规则】多表关联嵌套深度小于8

说明：关联嵌套过深，容易产生慢SQL，应从业务层考虑优化。

11.2【规则】表关联查询必须明确指定各表的连接条件（ON）， 以避免产生笛卡尔积

说明：例如在B数据库中，JOIN与CROSS JOIN和INNER JOIN等价，但是在SQL标准中，JOIN仅与 INNER JOIN等价，必须配合使用ON连接条件。

11.3【规则】关联时，应该根据SQL标准指明连接方式，避免直接 使用JOIN关键词，而是使用CROSS JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN 等

11.4【规则】多表关联查询时, 对表添加使用别名，保证语句逻辑 清晰，便于维护

11.5【建议】不同字段的比较开销不同，关联字段应尽量使用比较 效率高的字段类型

说明：数值类型的比较效率远高于字符串类型。 在数值类型中，整型效率高于NUMERIC和浮点类型。

11.6【建议】关联字段应为相同数据类型，避免存在隐式类型转换 影响执行效率

11.7【建议】少用嵌套子查询，尽量使用表关联，因为子查询会产生临时表，对SQL性能影响较

12 子查询

12.1【规则】一条SQL语句中，避免出现重复子查询语句

12.2【建议】避免在SELECT目标列中使用子查询，可能导致计划无法下推影响执行性能

12.3【建议】子查询嵌套深度不建议超过2层

说明：由于子查询会带来临时表开销，过于复杂的查询应考虑从业务逻辑上进行优化