GreenplumSQL语句开发
Sql开发中最基本的规范要求：
1.代码⾏清晰、整齐、层次分明、结构性强，易于阅读；
2.代码中应具备必要的注释以增强代码的可读性和可维护性；
3.代码应充分考虑执⾏效率，保证代码的⾼效性；
2.1查看表字段名或随机少量数据时，不要使⽤SELECT * FROM TABLENAME
在psql命令窗⼝⽤\d TABLENAME或SELECT * FROM TABLENAME WHERE 1 = 2、SELECT * FROM TABLENAME limit 1等命令查看表结构信息，尽量不要直接执⾏SELECT * FROM TABLENAME ，这样GP数据库会查询出sql所有的记录。
2.2 SELECT⼦句中避免使⽤*
在SELECT⼦句中列出所有的列时，使⽤*很⽅便，但是效率低。因为GP在解析过程中会查询数据字典，将*依次转换成所有的列名。另外在ETL开发过程中使⽤INSERT INTO..SELECT * FROM..语句时，
如果FROM表新增字段时，会造成代码执⾏报错可能。所以，直接在SELECT⼦句中写出想要显⽰的列。
2.3查询总记录数时，尽量不要⽤COUNT(*)，⽽要指定⼀个有索引的字段。
例如主键列为INDEX，使⽤COUNT(INDEX)能利⽤索引。
2.4对分区表进⾏查询时，尽量把分区键作为查询条件的第⼀个条件
2.5⽆条件删除表中数据时，⽤TRUNCATE代替DELETE
2.6查询语句中尽量使⽤表的索引字段，避免做⼤表的全表扫描
例如：WHERE⼦句中有联接的列，即使最后的联接值为⼀个静态值，也不会使⽤索引。
SELECT * FROM EMPLOYEE WHERE FIRST_NAME || '' || LAST_NAME = 'Beill Cliton';
这条语句没有使⽤基于LAST_NAME创建的索引。
当采⽤下⾯这种SQL语句的编写，GP系统就可以采⽤基于LAST_NAME创建的索引。
SELECT *  FROM EMPLOYEE WHERE FIRST_NAME = 'Beill' AND LAST_NAME = 'Cliton';
2.7带通配符（%）的LIKE语句
例如：SQL语句：
SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE '%cliton%';
由于通配符（%）在词⾸出现，所以GP系统不使⽤LAST_NAME的索引。
当通配符出现在字符串其他位置时，优化器就能利⽤索引。例如：
SELECT * FROM EMPLOYEE WHERE LAST_NAME LIKE 'c%';
2.8⽤EXISTS替代IN
在许多基于基础表的查询中，为了满⾜⼀个条件，往往需要对另⼀个表进⾏联接。在这种情况下，使⽤EXISTS(或NOT EXISTS)通常将提⾼查询的效率。
低效：
SELECT * FROM EMP
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'MELB');
⾼效：
SELECT * FROM EMP
WHERE EMPNO > 0
AND EXISTS (SELECT 'X' FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB');
2.9尽可能⽤UNION ALL替换UNION
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION ALL的⽅式被合并， 然后在输出最终结果前进⾏排序。
如果⽤UNION ALL替代UNION，就不需排序，提⾼了查询效率。例如：
低效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95'
UNION
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95';
⾼效：
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONSgreenplum数据库
WHERE TRAN_DATE = '31-DEC-95'
UNION ALL
SELECT ACCT_NUM, BALANCE_AMT
FROM DEBIT_TRANSACTIONS
WHERE TRAN_DATE = '31-DEC-95';
2.10 ORDER BY语句建议
ORDER BY语句对要排序的列没有特别限制，也可以将函数加⼊列中。在ORDER BY语句中使⽤⾮索引项或有计算表达式都将降低查询速度。当ORDER BY 中所有的列定义为⾮空时会⽤到索引，例如：
T1表的ID列存在索引，且⾮空。则以下查询⽤到索引：
SELECT * FROM T1 ORDER BY ID;
2.11避免使⽤NOT
在查询时经常在WHERE⼦句使⽤⼀些逻辑表达式，如⼤于、⼩于、等于以及不等于等等，也可以使⽤AND（与）、OR（或）以及NOT（⾮）。NOT可⽤来对任何逻辑运算符号取反。下⾯是⼀个NOT
⼦句的例⼦：
... WHERE NOT (STATUS ='VALID')
如果要使⽤NOT，则应在取反的短语前⾯加上括号，并在短语前⾯加上NOT运算符。NOT运算符包含在另外⼀个逻辑运算符中，这就是不等于（<>）运算符。换句话说，即使不在查询WHERE⼦句中显式地加⼊NOT词，NOT仍在运算符中，见下例：
... WHERE STATUS <>'INVALID';
再看下⾯这个例⼦：
SELECT * FROM EMPLOYEE WHERE SALARY <> 3000;
对这个查询，可以改写为不使⽤NOT：
SELECT * FROM EMPLOYEE WHERE SALARY < 3000 OR SALARY > 3000;
虽然这两种查询的结果⼀样，但是第⼆种查询⽅案会⽐第⼀种查询⽅案更快些。第⼆种查询对SALARY列使⽤索引，⽽第⼀种查询则不能使⽤索引。
2.12使⽤DECODE函数减少处理时间
使⽤DECODE函数可以避免重复扫描相同记录或重复连接相同的表。例如：
SELECT COUNT(*) ，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE  'SMITH%';
SELECT COUNT(*) ，SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE  'SMITH%';
可以⽤DECODE函数⾼效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO, 0020, 'X', NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO, 0030, 'X', NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO, 0020, SAL, NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO, 0030, SAL, NULL)) D0030_SAL
FROM EMP
WHERE ENAME LIKE 'SMITH%';
类似的，DECODE函数也可以运⽤于GROUP BY 和ORDER BY⼦句中。
2.13如果可以使⽤WHERE条件，尽量不要在HAVING中限制数据
2.14尽量不要使⽤distinct语句对数据去重
DISTINCT语句会引起排序操作，当查询的数据量很⼤时将排序会⼗分消耗数据库资源，从⽽影响查询的效率。请使⽤group by 语句替代distinct语句。
2.15避免在索引列上使⽤计算
WHERE⼦句中，如果索引列是函数的⼀部分，优化器将不使⽤索引⽽使⽤全表扫描。例如：
低效：
SELECT * FROM DEPT WHERE SAL * 12 > 25000;
⾼效：
SELECT * FROM DEPT WHERE SAL > 25000 / 12;
2.16避免在索引列上使⽤IS NULL和IS NOT NULL
避免在索引中使⽤任何可以为空的列，GP将⽆法使⽤该索引。对于单列索引，如果列包含空值，索引中将不存在此记录。对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果⾄少有⼀个列不为空，则记录存在于索引中。例如：
如果唯⼀性索引建⽴在表的A列和B列上，并且表中存在⼀条记录的A，B值为(123，null) ， GP将不接受下⼀条具有相同A，B值（123，null）的记录(插⼊)。然⽽如果所有的索引列都为空，GP将认为整个键值为空，⽽空不等于空。因此可以⽆限条空记录。
因空值不存在于索引列中，所以WHERE⼦句中对索引列进⾏空值⽐较将使GP停⽤该索引。例如：
低效：(索引失效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;
⾼效：(索引有效)
SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;
2.17⼦查询改写成表连接
通常来说，采⽤表连接的⽅式⽐⼦查询更有效率，但并不是所有的⼦查询都可以改写成表连接的形式。只有当连接字段存在唯⼀性时才可以进⾏改写。否则重复字段会产⽣笛卡尔积。
例如：
T1表存在以下数据：
ID        ID2
1          1
2          2
2          3
T2表存在以下数据：
ID        ID2
1          1
2          2
3          2
4          2
2          3
则以下查询结果不同：
⼦查询：
SELECT COUNT(*) FROM T1 WHERE T1.ID IN (SELECT ID FROM T2);  返回值3
表连接：
SELECT COUNT(*) FROM T1, T2 WHERE T1.ID = T2.ID; 返回值5
2.18使⽤索引的第⼀个列
如果索引是建⽴在多个列上，只有在它的第⼀个列(leading column)被WHERE⼦句引⽤时，优化器才会选择使⽤该索引。
2.19减少对表的查询
在含有⼦查询的SQL语句中，要特别注意减少对表的查询。例如：
低效：
SELECT TAB_NAME FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604)
AND DB_VER = (SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);
⾼效：
SELECT TAB_NAME FROM TABLES
WHERE (TAB_NAME, DB_VER) =
(SELECT TAB_NAME, DB_VER FROM TAB_COLUMNS WHERE VERSION = 604);
2.20 SQL语句中：⽤>=替代>
如果在ID列上建有索引，则语句SELECT * FROM EMPLOYEE WHERE ID >= 9 要⽐语句SELECT * FROM EMPLOYEE WHERE ID > 8⾼效。这是由于前者DBMS将直接跳到第⼀个ID等于9的记录⽽后者将⾸先定位到8的记录并且向前扫描到第⼀个DEPT⼤于9的记录。
2.21⼤批量数据导⼊
⼤批量数据插⼊操作：insert into xx select ..from xx操作时，可以使⽤gp外部表导出在导⼊。通常ETL⼯具会提供这样的封装功能来实现批量数据提交。2.22 SQL嵌套层数不能超过两层
SQL嵌套层数过多会影响最优执⾏计划的⽣成，执⾏计划容易变化，最终造成SQL执⾏效率降低，影响数据库的稳定性。因此规范SQL嵌套层数不能超过两层。
2.23 定期使⽤Vacuum analyze tablename 回收垃圾和收集统计信息
2.24 SQL执⾏计划
在提交⼤的查询之前，⾸先使⽤Vacuum analyze tablename收集表统计信息，然后使⽤explain分析执⾏计划、发现潜在优化机会，避免将系统资源熬尽。
2.25 gphdfs外部表
创建gphdfs外部表时注意尽量使⽤text格式，所以需提前在hadoop定义表时注意表的存储格式。发现hadoop中Parquet格式的表在GP中使⽤gphdfs外部表访问时，⼀旦其⽂件个数过多（⼤概20⼏个）会报错，严重会引起数据库异常。
2.26 表增加分区注意事项
GP数据库增加分区时（ETL代码也要注意），如果需要压缩，则⼀定要指定压缩属性，如下例⼦所⽰:（新增分区不会⾃动继承⽗表的压缩属性）
alter table table_xxx  add partition m201604 VALUES('201604')
WITH (appendonly=true, compresslevel=5, orientation=column, compresstype=zlib)
--⾏存储
WITH (appendonly=true, compresslevel=5, orientation=row, compresstype=zlib)
2.27 delete语句使⽤规范
如果可以使⽤truncate语句替代delete语句，则必须使⽤truncate语句（⽐如清空整张表，清空某个分区）。Delete语句执⾏完后，选择合理的时机对该表进⾏vacuum full操作：
vacuum full table_xxx
此操作可以收回delete操作后留下的空闲空间，防⽌delete频繁操作导致表膨胀。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。