GaussDB数据库编程规范（主备模式）_gaussdb batchmode-优快云博客

GaussDB数据库编程规范（主备模式）

数据库编程规范
客户端编程规范

数据库编程规范

访问数据库对象时，应使用schemaname.tablename进行访问，以减少不必要的路径搜索性能开销。

WHERE

禁止where条件中涉及隐式数据类型转换。
禁止where条件中字段使用表达式或函数。
禁止where条件中使用不等号与NULL作比较。
禁止where条件中使用不等号与索引字段作比较。
禁止where条件中索引字段与NULL作比较。
禁止where条件中索引字段使用NOT。
禁止where条件中索引字段使用NOT IN。
禁止where条件中使用LIKE模糊查询时%放在首字符位置。
建议where条件中IN的候选子集不超过500个可选项。
建议where条件中IN的候选子集为表字段时改写为子查询。
建议where条件中多使用等值查询、少使用非等值操作。

数据库编程开发过程中可以开启以下参数（生产环境不建议开启）来检测SQL语句中是否存在隐式数据类型转换。

set enable_fast_query_shipping=off;
set check_implicit_conversions=true;

对where条件字段使用函数或表达式是导致索引失效的常见原因之一。

... where date_format(created,'%Y%m%d %H:%i:%s') = '20150815 00:00:00';
--应修改为
... where created=str_to_date('20150815 00:00:00','%Y%m%d %H:%i:%s');

使用LIKE模糊查询时，%放在首字符位置将无法使用字段上的索引，会导致全表扫描。

WHERE条件中的IN操作的候选子集不是常量、而是表字段时，实际上会转化为一个不等值的JOIN，会通过NESTLOOP计划执行。建议修改为等值JOIN的子查询。

select c1,coalesce(max(c2 - 1),0)
from t1,t2 where t1.c1=any(values(id1),(id2)) group by c1;

--建议改写为
select c1,coalesce(max(tmp),0) 
from (
  ( 
    select c1,(c2 - 1) as tmp
    from t1,t2 where t1.c1=t2.id1 and t1.c1 != t2.id2;
  )
  union all
  (
    select c1,(c2 - 1) as tmp
    from t1,t2 where t1.c1 = t2.id2;
  )
) group by c1;

SELECT

避免对大字段进行ORDER BY、DISTINCT、GROUP BY、UNION等会引起排序的操作。
避免使用LOCK TABLE锁表，尽量使用SELECT … FOR UPDATE加行锁替代，减少死锁发生。
避免在SELECT目标列中使用子查询，可能会导致计划无法下推到DN节点，影响SQL执行性能。
如果没有去重的需求，建议优先使用UNION ALL，少使用UNION（会排序）。
避免频繁使用COUNT函数计算大表的行数。

对大字段（例如VARCHAR(2000)）进行排序操作会消耗大量CPU和内存资源。

如果不需要实时的行数统计信息，可以从系统视图中获取。

select reltules from pg_class where relname='TABLE_NAME';

INSERT

INSERT ON DUPLICATE KEY UPDATE不支持对主键或唯一约束的字段执行UPDATE。
避免对存在多个唯一约束的表执行INSERT ON DUPLICATE KEY UPDATE。
批量插入时，建议使用executeBatch执行INSERT INTO VALUES。

INSERT ON DUPLICATE KEY UPDATE语句可以在检测到唯一约束冲突时，将对冲突记录的INSERT替换为UPDATE，以避免插入失败。但是只能UPDATE没有约一约束的字段。
当存在多个唯一约束时，默认会检查所有的唯一约束条件。任何一个约束条件存在冲突，都会导致UPDATE操作，因此可能更新多条记录，与业务预期不符。
使用executeBatch执行INSERT INTO VALUES (?)，执行效率高于执行多条INSERT INTO VALUES (?)或INSERT INTO VALUES (?),...,(?)。

UPDATE

不支持在单条SQL语句中对多个表进行更新。
UPDATE语句中必须有WHERE子句。
UPDATE同时更新多个列时，不允许被更新列同样是更新源。
有主键或索引的列，更新时WHERE条件应尽量使用主键列或索引字段。

UPDATE同时更新多个列时，如果被更新列同样是更新源，在不同的数据库中行为结果不同。为避免兼容性问题，应避免此类写法。

--下面的SQL中c1字段既是被更新列又是更新源
update table t1 set c1=c2,c3=c1 where c1=101;

DELETE

不支持在单条SQL语句中对多个表进行删除。
DELETE语句中必须有WHERE子句。
清空整张表的数据建议使用TRUNCATE。
有主键或索引的列，DELETE时WHERE条件应尽量使用主键列或索引字段。

关联查询

多表关联的嵌套深度应小于8。
表关联查询必须指定各表的连接条件，以避免笛卡尔积。
表关联查询应避免使用JOIN关键字，而要使用CROSS JOIN、INNER JOIN、LEFT JOIN、RIGHT JOIN。
多表关联查询时，应尽量对表使用别名。
建议关联字段使用比较效率高的字段类型。比较效率：整型数值类 > 浮点数值类 > NUMERIC数值类 > 字符串类型。
关联字段应为相同的数据类型，避免发生隐式数据类型转换。
建议少用嵌套子查询，尽量使用表关联。子查询会生成临时表，影响SQL性能。
如果关联字段上存在大量NULL值，建议在WHERE条件中对该字段增加IS NOT NULL过滤条件，提高执行效率。

子查询

禁止一条SQL语句中出现重复的子查询语句。
建议少用标量子查询。
避免在SELECT目标列中使用子查询，可能会导致计划无法下推到DN节点，影响SQL执行性能。
子查询嵌套深度不建议超过2层。

标量子查询是指查询结果为一个值，并且条件表达式为等值的子查询。

select * from t1 where id=(select id from t2 limit 1);

事务

大对象操作不支持事务。包括创建和删除database、analyze和vacuum。
通过JDBC客户端接入时，应关闭autocommit参数，通过commit显式提交事务。
通过JDBC客户端接入时，应避免拼接多条SQL语句为一条语句发送执行。

开启autocommit参数会导致部分参数失效（例如fetchsize）。业务逻辑应减少对数据库的依赖。

多条SQL语句拼接为一条语句发送执行时，如果其中一条执行错误会整体返回失败，不利于定位问题错误。

客户端编程规范

应根据业务上层请求超时时间，合理设置JDBC连接超时时间，避免作业超时或完成后持续占用数据库连接资源。
单条SQL语句长度不允许超过2G字节。建议单条SQL语句不超过5K。
目前仅支持对CREATE/ALTER TABLE中字段的default值进行参数化设置。其他DDL使用Prepare Execute执行方式进行参数设置无效。
JDBC每条PreparedStatement语句中的参数不能超过32767个。
连接参数必须在autocommit关闭时使用，否则fetchsize配置无效。
应使用默认的GUC参数，避免通过JDBC发送SET请求修改GUC参数。
应使用Prepare Execute方式执行查询语句，以提高执行效率。
在同一事务中，应逐条执行SQL语句，避免拼接多条SQL语句为一条语句发送执行。
JDBC客户端所在主机时区、数据库所在主机时区、数据库配置过程中的时区，三者应保持一致。
如果在连接中创建了临时表，在将连接归还给连接池之前，必须将临时表删除，避免业务出错。
建议合理配置prepareThreshold，如果query语句固定不变，可以设置为1。
建议设置连接参数batchmode=true，使用batch模式连接，以提高执行性能。