文档首页/ 云数据库 GaussDB/ 开发指南（分布式_V2.0-10.x）/ 开发设计建议/ 数据库编程规范/ SQL编写
更新时间：2026-10-21 GMT+08:00
查看PDF
分享

SQL编写

DDL

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

详细DDL语法请参见《参考》中“SQL参考 > SQL语法 > DDL语法一览表”章节。

数据加载和卸载

  • 在INSERT语句中显式设置插入的字段列表。例如： 
    1
    		 
    INSERT INTO task(name,id,comment) VALUES ('task1','100','第100个任务');
  • 在批量数据入库、变更之后，或数据增量达到一定阈值时，建议对表执行ANALYZE操作，避免统计信息不准确导致执行计划劣化。

    案例描述：某业务每日导入当天的数据，并在导入后立即执行SQL查询。由于未手动ANALYZE，且自动统计信息收集未及时触发，导致当日新数据未被纳入统计信息，SQL执行时未能选择最优执行计划。

    根本原因：业务插入大量当日数据后，未手动执行ANALYZE，也未触发自动收集统计信息，导致统计信息未及时更新，行数估算与代价计算不准。

    最佳实践：

    • 业务导入当日数据后应及时执行ANALYZE，确保统计信息及时收集。
    • GaussDB支持通过HINT固定执行计划，建议对重要业务SQL使用HINT固定执行计划。
  • 如果要清理表中的所有数据，建议使用TRUNCATE TABLE方式，不要使用DELETE TABLE方式。DELETE TABLE方式删除性能差，且不会释放那些已经删除了的数据占用的磁盘空间。

类型转换

  • 在需要数据类型转换（不同数据类型进行比较或转换）时，使用强制类型转换，以防隐式类型转换结果与预期不符。
  • 在查询中，对常量要显式指定数据类型，不要试图依赖任何隐式的数据类型转换。
  • 若sql_compatibility参数为ORA，在导入数据时，空字符串会自动转化为NULL。如果需要保留空字符串，则需sql_compatibility参数为TD。

详细数据类型介绍请参见《参考》中“SQL参考 > 数据类型”章节。

查询操作

  • 除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函数的输出跟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) EXISTS代替(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语法，避免多次执行带来的资源开销。游标必须在事务中使用，执行完后务必关闭游标并提交事务。

锁

  • 并发写入事务的潜在死锁情况：只要事务涉及多个表的更新或者同一个表相同行的更新时，同时运行的事务就可能在同时尝试写入时变为死锁状态。

    例如，假设事务T1和T2在大致相同的时间开始。

    如果T1开始对表A进行写入且T2开始对表B进行写入，则两个事务均可继续而不会发生冲突，但是，如果T1完成了对表A的写入操作并需要开始对表B进行写入，此时操作的行数正好与T2一致，它将无法继续，因为T2仍保持对表B对应行的锁定，如果此时T2开始更新表A中与T1相同的行数，此时也将无法继续，产生死锁，在锁等待超时时间内，前面事务提交释放锁，后面的事务可以继续执行更新，等待时间超时后，事务抛出错误，有一个事务退出。

    如果T1，T2都对表A进行写入，此时T1更新1-5行的数据，T2更新6-10行的数据，两个事务不会发生冲突，但是，如果T1完成后开始对表A的6-10行数据进行更新，T2完成后开始更新1-5行的数据，此时两个事务无法继续，在锁等待超时时间内，前面事务提交释放锁，后面的事务可以继续执行更新，等待时间超时后，事务抛出错误，有一个事务退出。

  • 分区表业务的潜在死锁情况：
    • 并发执行涉及多个分区的业务时，操作分区的顺序不同时可能导致死锁问题。

      例如，假设事务T1和T2在大致相同的时间开始，事务T1执行查询所有分区的操作，先获取了1-15分区的一级锁，即将对16分区加一级锁时，事务T2执行drop 16分区操作，先持有了该分区的八级锁，执行完成后继续执行drop 15分区的操作，尝试获取15分区的八级锁，造成了死锁。

    • 业务在进行自动扩展分区时，应避免同时进行分区DDL操作，否则可能会触发死锁。
  • 备机读场景下死锁场景：当主机端短时间内生成多条八级锁日志，备机回放线程在连续处理八级锁日志时，先持有relation1的八级锁并尝试获取relation2的八级锁，而查询线程持有relation2的一级锁并试图获取relation1的一级锁，引起了死锁。会产生八级锁的语句类型：ALTER TABLE、DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL，或未声明其他锁模式的LOCK TABLE操作。
  • 并行导入场景：
    • 在对带触发器的表执行并行导入时，如果触发器内部存在向带约束的表（记为t1）插入数据的操作，则可能因并行导入导致死锁问题。具体表现为：多个并行导入进程在操作过程中，t1表因约束条件（如唯一键、外键等）导致不同事务间产生锁等待状态，如果形成循环等待则触发数据库死锁检测机制，最终导致导入失败并抛出deadlock错误。因此当目标表包含触发器，且触发器逻辑中存在向带约束的表插入数据的操作时，不建议使用并行导入。
    • 在对带约束的表（如唯一约束等）执行并行导入时，如果不使用load语法，如果数据文件本身含有违反约束冲突的数据，则可能因并行导入导致死锁问题。具体表现为：多个并行导入线程在操作过程中，t1表因约束条件（如唯一键、外键等）导致不同事务间产生锁等待状态，如果形成循环等待则触发数据库死锁检测机制，最终导致数据部分导入失败，并抛出错误。因此当目标表包含约束条件（如唯一键等），建议使用load进行并行导入。
  • 索引相关操作中的潜在死锁情况：
    • 在线创建/在线重建索引时，如果同时并发执行DDL（删除表、删除索引、重建索引等操作）和DML（插入、删除等操作），则可能触发死锁并报错。
    • 两个会话对同一个表创建CONCURRENTLY索引，会引起死锁问题。
    • 两个会话同时对同一个索引或表进行REINDEX CONCURRENTLY操作，会引起死锁问题。
    • 在线删除索引可能会和其他DDL出现死锁问题，导致在线删除索引失败。具体原因是：在线删除索引持有四级锁，等待并发DDL事务结束；并发DDL持有事务锁，等待在线删除索引释放四级锁。
  • VACUUM场景：
    • 同时执行多个VACUUM FULL可能出现死锁。
    • VACUUM FULL执行期间，应尽量避免与其他DDL并发执行，否则可能会出现死锁。
  • LOCK TABLE提供多种锁级别，但如果对数据库原理和业务理解不足，误用表锁可能触发死锁。
  • 全量物化视图的刷新、删除过程中会给基表加七级锁（阻塞DML，DDL等），若物化视图的定义涉及多张表，需要注意业务逻辑，避免死锁产生。
  • 在线DDL执行期间，应尽量避免与DDL并行执行，否则有一定概率出现死锁。
  • HTAP创建IMCV的DDL语句执行后，会有后台线程进入等待活跃事务结束阶段，以支撑在线加载能力，此时如果有DROP TABLE等需要高级锁的表上DDL语句执行，会和该阶段有死锁冲突。
  • 业务进行压缩操作时（无论是自动调度还是手动调度），应尽可能避免对压缩中的相关表进行DDL操作。特别是在一段长事务中执行DDL，可能会引发死锁或锁等待问题。
  • O兼容模式下，同时设置check_function_bodies值为off，enable_global_plsqlcache值为off的条件下，创建带有自定义类型和自治事务的存储过程或者函数，调用该存储过程或函数时，会触发死锁。
  • 存储过程相关锁操作请参见《参考》中“SQL参考 > 存储过程 > 其他语句 > 锁操作”章节。
  • 在执行流式查询的事务中，锁或MVCC快照可能长时间持有，增加死锁概率或影响写入性能。
  • 查询pg_locks视图获取锁信息时，会锁定主锁表全部分区，导致其他事务无法获取或释放锁，进而影响业务性能。
父主题： 数据库编程规范

相关文档