更新时间:2024-05-07 GMT+08:00

SQL编写

DDL

  • GaussDB中,建议DDL(建表、COMMENT等)操作统一执行。在批处理作业中尽量避免DDL操作,避免大量并发事务对性能的影响。
  • 在非日志表(unlogged table)使用完后,立即执行数据清理(TRUNCATE)操作。因为在异常场景下,GaussDB不保证非日志表(unlogged table)数据的安全性。
  • 临时表和非日志表的存储方式建议和基表相同。
  • 索引字段的总长度不超过50字节。否则,索引大小会膨胀比较严重,带来较大的存储开销,同时索引性能也会下降。
  • 不要使用DROP…CASCADE方式删除对象,除非已经明确对象间的依赖关系,以免误删。

数据加载和卸载

  • 在INSERT语句中显式设置插入的字段列表。例如:
    1
    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有关,生成的执行计划时不能提前计算结果值,导致查询性能严重劣化。
  • 使用下面表1 时间相关的宏替换now函数来获取当前时间。因为now函数生成的执行计划无法下推,导致查询性能严重劣化。
    表1 时间相关的宏

    宏名称

    描述

    示例

    CURRENT_DATE

    获取当前日期,不包含时分秒。

    1
    2
    3
    4
    5
    gaussdb=# SELECT CURRENT_DATE;
    date
    ------------
    2018-02-02
    (1 row)
    

    CURRENT_TIME

    获取当前时间,不包含年月日。

    1
    2
    3
    4
    5
    gaussdb=# SELECT CURRENT_TIME;
    timetz
    --------------------
    00:39:34.633938+08
    (1 row)
    

    CURRENT_TIMESTAMP(n)

    获取当前日期和时间,包含年月日时分秒。

    说明:

    n表示存储的毫秒位数。

    1
    2
    3
    4
    5
    gaussdb=# SELECT CURRENT_TIMESTAMP(6);
    timestamptz
    -------------------------------
    2018-02-02 00:39:55.231689+08
    (1 row)
    
  • 尽量避免标量子查询语句的出现。标量子查询是出现在SELECT语句输出列表中的子查询,在下面例子中,“SELECT COUNT(*) FROM films f WHERE f.did = s.id”部分即为一个标量子查询语句。
    1
    SELECT id, (SELECT COUNT(*) FROM films f WHERE f.did = s.id) FROM staffs_p1 s;
    

    标量子查询往往会导致查询性能严重劣化,在应用开发过程中,应当根据业务逻辑,对标量子查询进行等价转换,将其写为表关联。

  • 在WHERE子句中,应当对过滤条件进行排序,把选择读较小(筛选出的记录数较少)的条件排在前面。
  • WHERE子句中的过滤条件,尽量符合单边规则。即把字段名放在比较条件的一边,优化器在某些场景下会自动进行剪枝优化。形如col op expression,其中col为表的一个列,op为‘=’、‘>’的等比较操作符,expression为不含列名的表达式。例如,
    1
    SELECT id, from_image_id, from_person_id, from_video_id FROM face_data WHERE current_timestamp(6) - time < '1 days'::interval;
    

    改写为:

    1
    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语句经常无法优化,导致执行速度变慢。例如,下面语句的转换。
    1
    2
    SELECT * FROM scdc.pub_menu 
    WHERE (cdp= 300 AND inline=301) OR (cdp= 301 AND inline=302) OR (cdp= 302 ANDinline=301);
    

    转换为:

    1
    2
    3
    4
    5
    6
    7
    8
    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约束,再进行改写。
    1
    SELECT * FROM T1 WHERE T1.C1 NOT IN (SELECT T2.C2 FROM T2);
    

    可以改写为:

    1
    SELECT * FROM T1 WHERE NOT EXISTS (SELECT  * FROM T2 WHERE T1.C1=T2.C2);
    
    • 如果不能保证T1.C1列的值为NOT NULL的情况下,不能进行上述改写。
    • 如果T1.C1为子查询的输出,要根据业务逻辑确认其输出是否为NOT NULL。
  • 通过游标进行翻页查询,而不使用LIMIT OFFSET语法,避免多次执行带来的资源开销。游标必须在事务中使用,执行完后务必关闭游标并提交事务。