SQL查询最佳实践

根据数据库的SQL执行机制以及大量的实践总结发现：通过一定的规则调整SQL语句， 在保证结果正确的基础上，能够提高SQL执行效率。

• 使用union all代替union

union在合并两个集合时会执行去重操作，而union all则直接将两个结果集合并、不执行去重。执行去重会消耗大量的时间，因此，在一些实际应用场景中，如果通过业务逻辑已确认两个集合不存在重叠，可用union all替代union以便提升性能。

• join列增加非空过滤条件

若join列上的NULL值较多，则可以加上is not null过滤条件，以实现数据的提前过滤，提高join效率。

• 合理选择执行计划

GBase 8c支持LightProxy、FQS、Stream、RemoteQuery 4种分布式执行计划，并且采用CBO（Cost-Based Optimization）基于成本的优化器来对同一条SQL语句的不同执行计划所产生的多种执行路径进行成本评估，从中选择执行成本最低执行计划进行执行。

RemoteQuery分布式计划，CN需要从DN拉取相关数据，节点间通信开销较大，RemoteQuery计划的优先级最低。当remote query、Stream执行计划均能满足SQL执行出正确结果时，执行器应优先选择stream执行计划。

Remote Query算子用来从数据节点上拉取业务数据，并和其他算子一起完成整个的执行计划。如对于包含关联操作的SQL语句，Remote Query算子将从数据节点上拉取数据，最终在协调节点上完成关联操作。

stream支持扩展数据类型，如gist和其它非缺省数据类型。

stream支持外部表和disable trigger 场景insert。

stream执行计划支持单表for update，在进行多表for update时，需要支持Remote Query执行方式。

stream 支持 with recursive功能。with recursive 是SQL中递归查询的功能，它允许用户在一个查询中多次引用同一查询并不断地生成临时表，实现递归查询的功能。

对于one time filter 计划，不需要添加local stream节点。

对于列存表的查询、插入操作，支持使用stream执行计划。

支持列存表使用stream执行计划的SMP功能。

针对INSERT INTO table_name VALUES(RANDOM()[,values...]);、INSERT INTO table_name SELECT RANDOM()[,values...];这两种insert场景，优化器支持默认使用Remote Query执行计划。

支持with recursive语句默认使用Remote Query执行计划。

在涉及复杂查询场景时，使用并行查询，将增加查询效率，提高性能。

• not in转not exists

not in语句需要使用nestloop anti join来实现，而not exists则可以通过hash anti join来实现。在join列不存在null值的情况下，not exists和not in等价。因此在确保没有null值时，可以通过将not in转换为not exists，通过生成hash join来提升查询效率。

如下所示，如果t2.d2字段中没有null值(t2.d2字段在表定义中not null)查询可以修改为

select * from t1 where not exists(select * from t2 where t1.c1 = t2.c1);

产生的计划如下：

postgres=# explain select * from t1 where not exists(select * from t2 where t1.c1 = t2.c1); QUERY PLAN

------------------------------------------------------------------

Hash Anti Join (cost=58.35..107.44 rows=1074 width=8) Hash Cond: (t1.c1 = t2.c1)

-> Seq Scan on t1 (cost=0.00..31.49 rows=2149 width=8)

-> Hash (cost=31.49..31.49 rows=2149 width=4)

-> Seq Scan on t2 (cost=0.00..31.49 rows=2149 width=4) (5 rows)

• 选择hashagg。

查询中GROUP BY语句如果生成了groupagg+sort的plan性能会比较差，可以通过加大work_mem的方法生成hashagg的plan，因为不用排序而提高性能。

• 尝试将函数替换为case语句。

函数调用性能较低，如果出现过多的函数调用导致性能下降很多，可以根据情况把可下推函数的函数改成CASE表达式。

• 避免对索引使用函数或表达式运算。

• 对索引使用函数或表达式运算会停止使用索引转而执行全表扫描。

• 尽量避免在where子句中使用!=或<>操作符、null值判断、or连接、参数隐式转换。

• 对复杂SQL语句进行拆分。

对于过于复杂并且不易通过以上方法调整性能的SQL可以考虑拆分的方法，把SQL 中某一部分拆分成独立的SQL并把执行结果存入临时表，拆分常见的场景包括但不限于：

• 作业中多个SQL有同样的子查询，并且子查询数据量较大。

• Plan cost计算不准，导致子查询hash bucket太小，比如实际数据1000W

• 行，hash bucket只有1000。

• 函数（如substr,to_number）导致大数据量子查询选择度计算不准。

• 多DN环境下对大表做broadcast的子查询。