SQL查询最佳实践（集中式）

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

• 使用UNION ALL代替UNION。

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

• JOIN列增加非空过滤条件。

  若JOIN列上的NULL值较多，则可以加上IS NOT NULL过滤条件，以实现数据的提前过滤，提高JOIN效率。

• 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来提升查询效率。

  建表语句如下：

  gaussdb=# DROP SCHEMA IF EXISTS no_in_to_no_exists_test CASCADE;
  gaussdb=# CREATE SCHEMA no_in_to_no_exists_test;
  gaussdb=# SET CURRENT_SCHEMA=no_in_to_no_exists_test;
  gaussdb=# CREATE TABLE t1(c1 int, c2 int, c3 int);
  gaussdb=# CREATE TABLE t2(d1 int, d2 int NOT NULL, d3 int);

  使用NOT IN实现查询语句如下：

  gaussdb=# SELECT * FROM t1 WHERE c1 NOT IN (SELECT d2 FROM t2);

  其计划如下所示：

  gaussdb=# EXPLAIN SELECT * FROM t1 WHERE c1 NOT IN (SELECT d2 FROM t2);
   id |               operation               | E-rows | E-width |    E-costs     
  ----+---------------------------------------+--------+---------+----------------
    1 | ->  Seq Scan on t1                    |    972 |      12 | 34.312..68.625
    2 |    ->  Seq Scan on t2  [1, SubPlan 1] |   1945 |       4 | 0.000..29.450
  (2 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     1 --Seq Scan on t1
           Filter: (NOT (hashed SubPlan 1))
  (2 rows)

  因为t2.d2字段中没有NULL值（t2.d2字段在表定义中为NOT NULL），所以查询可以等价修改如下：

  1	gaussdb=# SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.c1=t2.d2);

  其生成的计划如下：

  gaussdb=# EXPLAIN SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.c1=t2.d2);
   id |         operation         | E-rows | E-width |    E-costs     
  ----+---------------------------+--------+---------+----------------
    1 | ->  Hash Anti Join (2, 3) |    972 |      12 | 53.763..99.142
    2 |    ->  Seq Scan on t1     |   1945 |      12 | 0.000..29.450
    3 |    ->  Hash               |   1945 |       4 | 29.450..29.450
    4 |       ->  Seq Scan on t2  |   1945 |       4 | 0.000..29.450
  (4 rows)
  
   Predicate Information (identified by plan id) 
  -----------------------------------------------
     1 --Hash Anti Join (2, 3)
           Hash Cond: (t1.c1 = t2.d2)
  (2 rows)

• 选择hashagg。

  查询语句中如果存在GROUP BY条件则生成的计划（Plan）中可能存在排序操作，即计划中包含GroupAgg+Sort算子，导致性能较差。可以通过设置GUC参数work_mem增大可用内存，生成带有HashAgg的计划（Plan）避免排序操作从而提升性能。work_mem设置请联系管理员。

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

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

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

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

• 尽量避免在where子句中使用!=或<>操作符、null值判断、or连接、参数隐式转换。
• 对于高频数据变化的表，在相关SQL语句中添加Hint，以固定执行计划。

  高频数据变化的表可能在触发自动ANALYZE之前出现统计信息不是最新的情况，从而导致执行计划选择不优。建议通过在相关SQL中添加Hint来固定执行计划。

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

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

  • 作业中多个SQL有同样的子查询，并且子查询数据量较大。
  • Plan cost计算不准，导致子查询hash bucket太小，比如实际数据1000万行，hash bucket只有1000。
  • 函数（如substr、to_number）导致大数据量子查询选择度计算不准。
  • 多DN环境下对大表做broadcast的子查询。

其他更多调优点，请参考《开发指南》中“SQL调优指南 > 典型SQL调优点”章节。