# SQL编写
#### DDL
- 【建议】在GaussDB中，建议DDL（建表、COMMENT等）操作统一执行。在批处理作业中尽量避免DDL操作。避免大量并发事务对性能的影响。
- 【建议】在非日志表（unlogged table）使用完后，立即执行数据清理（TRUNCATE）操作。因为在异常场景下，GaussDB不保证非日志表(unlogged table)数据的安全性。
- 【建议】临时表和非日志表的存储方式建议和基表相同。当基表为行存表时，临时表和非日志表也推荐创建为行存表。
- 【建议】索引字段的总长度不超过50字节。否则，索引大小会膨胀比较严重，带来较大的存储开销，同时索引性能也会下降。
- 【建议】不要使用DROP...CASCADE方式删除对象，除非已经明确对象间的依赖关系，以免误删。
 
#### 数据加载和卸载
- 【建议】在INSERT语句中显式给出插入的字段列表。例如：
  ```
  INSERT INTO task(name,id,comment) VALUES ('task1','100','第100个任务');
  ```
  
- 【建议】在批量数据入库之后，或者数据增量达到一定阈值后，建议对表进行ANALYZE操作，防止统计信息不准确而导致的执行计划劣化。
- 【建议】如果要清理表中的所有数据，建议使用TRUNCATE TABLE方式，不要使用DELETE TABLE方式。DELETE TABLE方式删除性能差，且不会释放那些已经删除了的数据占用的磁盘空间。
 
#### 类型转换
- 【建议】在需要数据类型转换（不同数据类型进行比较或转换）时，使用强制类型转换，以防隐式类型转换结果与预期不符。
- 【建议】在查询中，对常量要显式指定数据类型，不要试图依赖任何隐式的数据类型转换。
- 【说明】若sql_compatibility参数设置为A，在导入数据时，空字符串会自动转化为NULL。如果需要保留空字符串，则需要将sql_compatibility参数设置为C。
 
#### 查询操作
- 【建议】除ETL程序外，应该尽量避免向客户端返回大量结果集的操作。如果结果集过大，应考虑业务设计是否合理。
- 【建议】使用事务方式执行DDL和DML操作。例如，TRUNCATE TABLE、UPDATE TABLE、DELETE TABLE、DROP TABLE等操作，一旦执行提交就无法恢复。对于这类操作，建议使用事务进行封装，必要时可以进行回滚。
- 【建议】在查询编写时，建议明确列出查询涉及的所有字段，不建议使用"SELECT \*"这种写法。一方面基于性能考虑，尽量减少查询输出列；另一方面避免增删字段对前端业务兼容性的影响。
- 【建议】在访问表对象时带上schema前缀，可以避免因schema切换导致访问到非预期的表。
- 【建议】超过3张表或视图进行关联（特别是FULL JOIN）时，执行代价难以估算。建议使用WITH TABLE AS语句创建中间临时表的方式增加SQL语句的可读性。
- 【建议】尽量避免使用笛卡尔积和FULL JOIN。这些操作会造成结果集的急剧膨胀，同时其执行性能也很低。
- 【说明】NULL值的比较只能使用IS NULL或者IS NOT NULL的方式判断，其他任何形式的逻辑判断都返回NULL。例如：NULL\<\>NULL、NULL=NULL和NULL\<\>1返回结果都是NULL，而不是期望的布尔值。
- 【说明】需要统计表中所有记录数时，不要使用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函数本身需要额外查询类型表和函数表，基础性能较慢；另外concat的输出跟data type有关，生成的执行计划时不能提前计算结果值，导致查询性能严重劣化。
- 【建议】使用下面时间相关的宏替换now函数来获取当前时间。因为now函数生成的执行计划无法下推，导致查询性能严重劣化。
  表1时间相关的宏 
  | **宏名称**              | **描述**                                                                                                                                                                                  | **示例**                                                                                                                             |
  |:---|:---|:---|
  | CURRENT_DATE         | 获取当前日期，不包含时分秒。                                                                                                                                                                          | ``` openGauss=# SELECT CURRENT_DATE; date ------------ 2018-02-02 (1 row) ```                                                      |
  | CURRENT_TIME         | 获取当前时间，不包含年月日。                                                                                                                                                                          | ``` openGauss=# SELECT CURRENT_TIME; timetz -------------------- 00:39:34.633938+08 (1 row) ```                                    |
  | CURRENT_TIMESTAMP(n) | 获取当前日期和时间，包含年月日时分秒。 说明： n表示存储的毫秒位数。 | ``` openGauss=# SELECT CURRENT_TIMESTAMP(6); timestamptz ------------------------------- 2018-02-02 00:39:55.231689+08 (1 row) ``` |
     
  
- 【建议】尽量避免标量子查询语句的出现。标量子查询是出现在SELECT语句输出列表中的子查询，在下面例子中，下划线部分即为一个标量子查询语句：
  ```
  SELECT id, (SELECT COUNT(*) FROM films f WHERE f.did = s.id) FROM staffs_p1 s;
  ```
  标量子查询往往会导致查询性能急剧劣化，在应用开发过程中，应当根据业务逻辑，对标量子查询进行等价转换，将其写为表关联。
  
- 【建议】在WHERE子句中，应当对过滤条件进行排序，把选择读较小（筛选出的记录数较少）的条件排在前面。
- 【建议】WHERE子句中的过滤条件，尽量符合单边规则。即把字段名放在比较条件的一边，优化器在某些场景下会自动进行剪枝优化。形如col op expression，其中col为表的一个列，op为'='、'\>'的等比较操作符，expression为不含列名的表达式。例如，
  ```
  SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHERE current_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;
  ```
  
- 【建议】尽量避免不必要的排序操作。排序需要耗费大量的内存及CPU，如果业务逻辑许可，可以组合使用ORDER BY和LIMIT，减少资源开销。GaussDB默认按照ASC \& NULL LAST进行排序。
- 【建议】使用ORDER BY子句进行排序时，显式指定排序方式（ASC/DESC），NULL的排序方式（NULL FIRST/NULL LAST）。
- 【建议】不要单独依赖LIMIT子句返回特定顺序的结果集。如果部分特定结果集，可以将ORDER BY子句与Limit子句组合使用，必要时也可以使用OFFSET跳过特定结果。
- 【建议】在保障业务逻辑准确的情况下，建议尽量使用UNION ALL来代替UNION。
- 【建议】如果过滤条件只有OR表达式，可以将OR表达式转化为UNION ALL以提升性能。使用OR的SQL语句经常无法优化，导致执行速度慢。例如，将下面语句
  ```
  SELECT * FROM scdc.pub_menu 
  WHERE (cdp= 300 AND inline=301) OR (cdp= 301 AND inline=302) OR (cdp= 302 ANDinline=301);
  ```
  转换为：
  ```
  SELECT * FROM scdc.pub_menu 
  WHERE (cdp= 300 AND inline=301) 
  union all
  SELECT * FROM scdc.pub_menu 
  WHERE (cdp= 301 AND inline=302) 
  union all 
  SELECT * FROM tablename 
  WHERE (cdp= 302 AND inline=301)
  ```
  
- 【建议】当IN(val1, val2, val3...)表达式中字段较多时，建议使用IN (VALUES (val1), (val2),(val3)...)语句进行替换。优化器会自动把IN约束转换为非关联子查询，从而提升查询性能。
- 【建议】在关联字段不存在NULL值的情况下，使用(NOT) EXIST代替(NOT) IN。例如，在下面查询语句中，当T1.C1列不存在NULL值时，可以先为T1.C1字段添加NOT NULL约束，再进行如下改写。
  ```
  SELECT * FROM T1 WHERE T1.C1 NOT IN (SELECT T2.C2 FROM T2);
  ```
  可以改写为：
  ```
  SELECT * FROM T1 WHERE NOT EXISTS (SELECT  * FROM T2 WHERE T1.C1=T2.C2);
  ```
  ![](https://support.huaweicloud.com/centralized-devg-v2-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
  - 如果不能保证T1.C1列的值为NOT NULL的情况下，就不能进行上述改写。
  
  - 如果T1.C1为子查询的输出，要根据业务逻辑确认其输出是否为NOT NULL。
    
- 【建议】通过游标进行翻页查询，而不是使用LIMIT OFFSET语法，避免多次执行带来的资源开销。游标必须在事务中使用，执行完后务必关闭游标并提交事务。