GUC参数编程规范

客户端（如JDBC）应使用默认（全局）参数执行查询，禁用会话级别的GUC参数。

通过ODBC或JDBC修改GUC参数时，需注意GUC参数仅会在当前connection中生效，特别是在连接池场景下，容易发生问题，且导致问题定位困难。

如果在连接中必须进行GUC参数设置，那么在将连接归还给连接池之前，必须使用

SET SESSION AUTHORIZATION DEFAULT;

RESET ALL;

将连接的状态清空。

对象访问编程规范

访问对象（表，函数等）时建议带上SCHEMA名称，即使用schemaname.tablename进行访问。

如果不追加SCHEMA名称前缀，会根据当前search_path中表空间列表，依次搜索所有表空间直到找到匹配的表作为目标表，带来不必要的性能开销。

WHERE

• 表查询时，WHERE条件中应包含所有分布键字段等值查询条件，否则将在多个节点上进行查询，影响系统并发度和性能。
• 禁止在WHERE条件相同表字段进行相互比较。

  例如如下语句应考虑合理性：

  SELECT * FROM t1 WHERE col1 = col1;

  应考虑修改为：

  SELECT * FROM t1 WHERE col1 IS NOT NULL;

• 禁止WHERE条件涉及隐式数据类型转换。

  数据库中进行隐式转换后可能导致无法使用所创建的索引，导致潜在的性能问题。

  强烈建议在开发过程中开启GUC参数check_implicit_conversions，并关闭enable_fast_query_shipping，以便检查询语句中是否存在可能带来不良性能影响的隐式数据类型。

  SET enable_fast_query_shipping = off;

  SET check_implicit_conversions = true;

  由于隐式数据类型转换检测存在额外的开销，一旦查询语句开发完成后，请关闭check_implicit_conversions参数，并重置enable_fast_query_shipping。

  示例：

  如下代码不符合规范：

  t_tablename表的phonenumber字段为VARCHAR类型（而不是数值类型），以下语句利用phonenumber进行条件过滤时，优化器会将phonenumber隐式转化为bigint类型。

  SELECT column1

  INTO i_l_variable1

  FROM t_tablename

  WHERE phonenumber = 13512345678;

  导致两个后果：

  1. 不能进行DN裁剪，计划下发到所有的DN上执行。
  2. 计划中不能使用index scan方式扫描数据。

  建议修改t_tablename表的phonenumber字段为VARCHAR类型（而不是数值类型）

  SELECT column1

  INTO i_l_variable1

  FROM t_tablename

  WHERE phonenumber = '13512345678';

• 禁止WHERE 条件字段使用表达式或是函数。

  对条件字段使用表达式或函数时，索引会失效，同时会对每一行数据进行计算，产生不必要的性能消耗。主要因为非常量的表达式在预处理阶段不能转化为Const值，因此不能用来剪枝，导致查询语句扫描所有的数据。

  示例：

  如下代码不符合规范：

  SELECT income FROM table WHERE abs(income) > ?;

  SELECT income FROM table WHERE income * 10 > ?;

  SELECT create_time

  FROM table

  WHERE date_format(create_time, '%Y­%m­%d %H:%i:%s') = '2009­01­01 00:00:0';

  应修改为：

  SELECT income FROM table WHERE income > ? OR income < (-1) * ?;

  SELECT income FROM table WHERE income > ?/10;

  SELECT create_time

  FROM table

  WHERE create_time = str_to_date('2009­01­01 00:00:0', '%Y­%m­%d %H:%i:%s');

• 查询条件中与NULL做比较时，禁止使用“！=”比较符，应使用IS NULL或IS NOT NULL。

  不能写expression=NULL或expression ！= NULL，因为NULL代表一个未知的值，不能通过表达式判断两个未知值是否相等。

• 查询条件中禁止对索引字段使用“!= ”比较符，避免索引失效。
• 在where子句中，应当对过滤条件进行排序，把筛选出的记录数较少的条件排在前面。
• where子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如col op expression，其中col为表的一个列， op为‘ =’、‘ >’的等比较操作符， expression为不含列名的表达式。

  示例：

  如下代码不推荐使用，根据time列进行筛选

  SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHEREcurrent_timestamp(6) - time < '1 days'::interval;

  建议修改为：

  SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time >current_timestamp(6) - '1 days'::interval;

• 查询条件的索引字段上禁止避免与NULL（IS NULL和IS NOT NULL）进行比较。
• 查询条件的索引字段上避免使用NOT。
• 查询条件的索引字段上避免使用NOT IN。
• 模糊查询LIKE语句，非必要情况下，%不应放在首字符位置。

  如果%放在首字符位置，将无法使用索引，会导致全表扫描。

• WHERE条件中IN的候选子集不易过大，建议不超过500。

  说明：

  查询时，会对IN中每一条数据进行等值比较，开销较大。

  如果包含的值为较为固定的值，应考虑创建REPLICATION表，并将候选数据写入表中，然后通过INNER JOIN来实现包含查询。

• WHERE条件中IN的候选子集不为常量，而是表中的列时，建议改写为子查询。

  说明：

  在这种情况下，实际上是一个不等值的JOIN，会通过nestloop计划执行。在表过大时执行效率低下，建议修改为等值JOIN的子查询。

  示例

  如下代码不推荐使用：

  SELECT col1, COALESCE(max(col2 - 1), 0)

  FROM t1, t2

  WHERE t1.col1 = ANY(VALUES(id1), (id2))

  GROUP BY col1;

  建议修改为：

  SELECT col1, COALESCE(max(tmp), 0) FROM

  (

  (

  SELECT col1, (col2-1) AS tmp

  FROM t1, t2

  WHERE t1.col1 = t2.id1 AND t1.col1 != t2.id2

  ) UNION ALL (

  SELECT col1, (col2-1) AS tmp

  FROM t1, t2

  WHERE t1.col1 = t2.id2

  )

  ) GROUP BY col1;

• 多使用等值操作，少使用非等值操作。

  WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引，因为不能同时用到两个范围条件。

• 表查询时，WHERE条件中应包含所有分布键字段等值查询条件，否则将在多个节点上进行查询，影响系统并发度和性能。
• 禁止在WHERE条件相同表字段进行相互比较。

  例如如下语句应考虑合理性：

  SELECT * FROM t1 WHERE col1 = col1;

  应考虑修改为：

  SELECT * FROM t1 WHERE col1 IS NOT NULL;

• 禁止WHERE条件涉及隐式数据类型转换。

  数据库中进行隐式转换后可能导致无法使用所创建的索引，导致潜在的性能问题。

  强烈建议在开发过程中开启GUC参数check_implicit_conversions，并关闭enable_fast_query_shipping，以便检查询语句中是否存在可能带来不良性能影响的隐式数据类型。

  SET enable_fast_query_shipping = off;

  SET check_implicit_conversions = true;

  由于隐式数据类型转换检测存在额外的开销，一旦查询语句开发完成后，请关闭check_implicit_conversions参数，并重置enable_fast_query_shipping。

  示例：

  如下代码不符合规范：

  t_tablename表的phonenumber字段为VARCHAR类型（而不是数值类型），以下语句利用phonenumber进行条件过滤时，优化器会将phonenumber隐式转化为bigint类型。

  SELECT column1

  INTO i_l_variable1

  FROM t_tablename

  WHERE phonenumber = 13512345678;

  导致两个后果：

  1. 不能进行DN裁剪，计划下发到所有的DN上执行。
  2. 计划中不能使用index scan方式扫描数据。

  建议修改t_tablename表的phonenumber字段为VARCHAR类型（而不是数值类型）

  SELECT column1

  INTO i_l_variable1

  FROM t_tablename

  WHERE phonenumber = '13512345678';

• 禁止WHERE 条件字段使用表达式或是函数。

  对条件字段使用表达式或函数时，索引会失效，同时会对每一行数据进行计算，产生不必要的性能消耗。主要因为非常量的表达式在预处理阶段不能转化为Const值，因此不能用来剪枝，导致查询语句扫描所有的数据。

  示例：

  如下代码不符合规范：

  SELECT income FROM table WHERE abs(income) > ?;

  SELECT income FROM table WHERE income * 10 > ?;

  SELECT create_time

  FROM table

  WHERE date_format(create_time, '%Y­%m­%d %H:%i:%s') = '2009­01­01 00:00:0';

  应修改为：

  SELECT income FROM table WHERE income > ? OR income < (-1) * ?;

  SELECT income FROM table WHERE income > ?/10;

  SELECT create_time

  FROM table

  WHERE create_time = str_to_date('2009­01­01 00:00:0', '%Y­%m­%d %H:%i:%s');

• 查询条件中与NULL做比较时，禁止使用“！=”比较符，应使用IS NULL或IS NOT NULL。

  不能写expression=NULL或expression ！= NULL，因为NULL代表一个未知的值，不能通过表达式判断两个未知值是否相等。

• 查询条件中禁止对索引字段使用“!= ”比较符，避免索引失效。
• 在where子句中，应当对过滤条件进行排序，把筛选出的记录数较少的条件排在前面。
• where子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如col op expression，其中col为表的一个列， op为‘ =’、‘ >’的等比较操作符， expression为不含列名的表达式。

  示例：

  如下代码不推荐使用，根据time列进行筛选

  SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHEREcurrent_timestamp(6) - time < '1 days'::interval;

  建议修改为：

  SELECT id, from_image_id, from_person_id, from_video_id FROM face_data where time >current_timestamp(6) - '1 days'::interval;

• 查询条件的索引字段上禁止避免与NULL（IS NULL和IS NOT NULL）进行比较。
• 查询条件的索引字段上避免使用NOT。
• 查询条件的索引字段上避免使用NOT IN。
• 模糊查询LIKE语句，非必要情况下，%不应放在首字符位置。

  如果%放在首字符位置，将无法使用索引，会导致全表扫描。

• WHERE条件中IN的候选子集不易过大，建议不超过500。
  说明：

  查询时，会对IN中每一条数据进行等值比较，开销较大。

  如果包含的值为较为固定的值，应考虑创建REPLICATION表，并将候选数据写入表中，然后通过INNER JOIN来实现包含查询。

• WHERE条件中IN的候选子集不为常量，而是表中的列时，建议改写为子查询。
  说明：

  在这种情况下，实际上是一个不等值的JOIN，会通过nestloop计划执行。在表过大时执行效率低下，建议修改为等值JOIN的子查询。

  示例

  如下代码不推荐使用：

  SELECT col1, COALESCE(max(col2 - 1), 0)

  FROM t1, t2

  WHERE t1.col1 = ANY(VALUES(id1), (id2))

  GROUP BY col1;

  建议修改为：

  SELECT col1, COALESCE(max(tmp), 0) FROM

  (

  (

  SELECT col1, (col2-1) AS tmp

  FROM t1, t2

  WHERE t1.col1 = t2.id1 AND t1.col1 != t2.id2

  ) UNION ALL (

  SELECT col1, (col2-1) AS tmp

  FROM t1, t2

  WHERE t1.col1 = t2.id2

  )

  ) GROUP BY col1;

• 多使用等值操作，少使用非等值操作。

  WHERE条件中的非等值条件（IN、BETWEEN、<、<=、>、>=）会导致后面的条件使用不了索引，因为不能同时用到两个范围条件。

• 
  

• SELECT

• SELECT语句中慎用通配符字段“*”。

  使用通配符字段查询表时，如果因业务或数据库升级导致表结构发生变化，可能出现与业务语句不兼容的情况。

  因此业务应指明所需查询的表字段名称，避免使用通配符。

• 带有LIMIT的查询语句中必须带有ORDER BY保证有序。

  说明：

  GaussDB是一种分布式数据库，表数据将分布在多个DN上。

  如果SQL语句中只带有LIMIT，而不带有ORDER BY子句，数据库将会把网络传输较快的DN所发送的（符合查询要求的）结果作为最终结果输出到客户端。

  由于网络传输效率不同时刻可能发生改变，因此导致多次执行该SQL语句时，返回结果表现出不一致的情况。

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

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

• 禁止使用慎用LOCK TABLE语句加锁，仅允许应考虑使用 SELECT .. FOR UPDATE语句。

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

• 避免在SELECT目标列中使用子查询，可能导致计划无法下推到DN执行，影响执行性能。
• 考虑使用UNION ALL，少使用UNION，注意考虑去重。

  UNION ALL不去重，少了排序操作，速度相对UNION更快。

  如果没有去重的需求，优先使用UNION ALL。

• 需要统计表中所有记录数时，不要使用count(col)来替代count(*)。count(*)会统计NULL值（真实行数），而count(col)不会统计。
• 在执行count(col)时，将“值为NULL”的记录行计数为0。在执行sum(col)时，当所有记录都为NULL时，最终将返回NULL；当不全为NULL时，“值为NULL”的记录行将被计数为0。
• count(多个字段)时，多个字段名必须用圆括号括起来。例如，count( (col1,col2,col3) )。注意：通过多字段统计行数时，即使所选字段都为NULL，该行也被计数，效果与count(*)一致。
• count(distinct col)用来计算该列不重复的非NULL的数量， NULL将不被计数。
• count(distinct (col1,col2,...))用来统计多列的唯一值数量，当所有统计字段都为NULL时，也会被计数，同时这些记录被认为是相同的。
• 使用连接操作符“ ||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。
• 当in(val1, val2, val3…)表达式中字段较多时，建议使用in (values(va11), (val2),(val3)…)语句进行替换。优化器会自动把in约束转换为非关联子查询，从而提升查询性能。
• 避免频繁使用下使用count()获取大表行数，该操作资源消耗较大，影响并行作业执行效率。

  如果不需要实时的行数统计信息，可以尝试使用如下语句来获取表行数。

  SELECT reltuples FROM pg_class WHERE relname = 'tablename';

  须知：

  pg_class中所记录的表行数信息只会在对该表执行ANALYZE以后才会更新。

  目前ANALYZE有两种触发条件:

  • 业务主动发送ANALYZE语句，例如：

    --分析连接库中所有表

    ANALYZE;

    --分析指定表

    ANALYZE tablename;

  • 助AUTO VACCUUM机制，在每间隔一定时间或表的增删达到一定行数时触发。间隔时间和增删比例可通过GUC参数设置。
    • SELECT语句中慎用通配符字段“*”。

      使用通配符字段查询表时，如果因业务或数据库升级导致表结构发生变化，可能出现与业务语句不兼容的情况。

      因此业务应指明所需查询的表字段名称，避免使用通配符。

    • 带有LIMIT的查询语句中必须带有ORDER BY保证有序。
      说明：

      GaussDB是一种分布式数据库，表数据将分布在多个DN上。

      如果SQL语句中只带有LIMIT，而不带有ORDER BY子句，数据库将会把网络传输较快的DN所发送的（符合查询要求的）结果作为最终结果输出到客户端。

      由于网络传输效率不同时刻可能发生改变，因此导致多次执行该SQL语句时，返回结果表现出不一致的情况。

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

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

    • 禁止使用慎用LOCK TABLE语句加锁，仅允许应考虑使用 SELECT .. FOR UPDATE语句。

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

    • 避免在SELECT目标列中使用子查询，可能导致计划无法下推到DN执行，影响执行性能。
    • 考虑使用UNION ALL，少使用UNION，注意考虑去重。

      UNION ALL不去重，少了排序操作，速度相对UNION更快。

      如果没有去重的需求，优先使用UNION ALL。

    • 需要统计表中所有记录数时，不要使用count(col)来替代count(*)。count(*)会统计NULL值（真实行数），而count(col)不会统计。
    • 在执行count(col)时，将“值为NULL”的记录行计数为0。在执行sum(col)时，当所有记录都为NULL时，最终将返回NULL；当不全为NULL时，“值为NULL”的记录行将被计数为0。
    • count(多个字段)时，多个字段名必须用圆括号括起来。例如，count( (col1,col2,col3) )。注意：通过多字段统计行数时，即使所选字段都为NULL，该行也被计数，效果与count(*)一致。
    • count(distinct col)用来计算该列不重复的非NULL的数量， NULL将不被计数。
    • count(distinct (col1,col2,...))用来统计多列的唯一值数量，当所有统计字段都为NULL时，也会被计数，同时这些记录被认为是相同的。
    • 使用连接操作符“ ||”替换concat函数进行字符串连接。因为concat函数生成的执行计划不能下推，导致查询性能严重劣化。
    • 当in(val1, val2, val3…)表达式中字段较多时，建议使用in (values(va11), (val2),(val3)…)语句进行替换。优化器会自动把in约束转换为非关联子查询，从而提升查询性能。
    • 避免频繁使用下使用count()获取大表行数，该操作资源消耗较大，影响并行作业执行效率。

      如果不需要实时的行数统计信息，可以尝试使用如下语句来获取表行数。

      SELECT reltuples FROM pg_class WHERE relname = 'tablename';

      须知：

      pg_class中所记录的表行数信息只会在对该表执行ANALYZE以后才会更新。

      目前ANALYZE有两种触发条件:

      • 业务主动发送ANALYZE语句，例如：

        --分析连接库中所有表

        ANALYZE;

        --分析指定表

        ANALYZE tablename;

      • 助AUTO VACCUUM机制，在每间隔一定时间或表的增删达到一定行数时触发。间隔时间和增删比例可通过GUC参数设置。