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 | -> Streaming (type: GATHER) | 15 | 12 | 29.65 2 | -> Seq Scan on t1 | 15 | 12 | 28.77 3 | -> Materialize [2, SubPlan 1] | 270 | 4 | 14.37 4 | -> Streaming(type: BROADCAST) | 90 | 4 | 14.22 5 | -> Seq Scan on t2 | 30 | 4 | 14.14 (5 rows) Predicate Information (identified by plan id) ----------------------------------------------- 2 --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 | -> Streaming (type: GATHER) | 3 | 12 | 29.99 2 | -> Hash Right Anti Join (3, 5) | 3 | 12 | 29.86 3 | -> Streaming(type: REDISTRIBUTE) | 30 | 4 | 15.49 4 | -> Seq Scan on t2 | 30 | 4 | 14.14 5 | -> Hash | 29 | 12 | 14.14 6 | -> Seq Scan on t1 | 30 | 12 | 14.14 (6 rows) Predicate Information (identified by plan id) ----------------------------------------------- 2 --Hash Right Anti Join (3, 5) Hash Cond: (t2.d2 = t1.c1) (2 rows) --删除。 gaussdb=# DROP TABLE t1,t2; gaussdb=# DROP SCHEMA IF EXISTS no_in_to_no_exists_test CASCADE;
- 选择hashagg。
查询语句中如果存在GROUP BY条件则生成的计划(Plan)中可能存在排序操作,即计划中包含GroupAgg+Sort算子,导致性能较差。可以通过设置GUC参数work_mem增大可用内存,生成带有HashAgg的计划(Plan)避免排序操作从而提升性能。work_mem设置请联系管理员。
- 尝试将函数替换为case语句。
GaussDB函数调用性能较低,如果出现过多的函数调用导致性能下降很多,可以根据情况把可下推函数的函数改成CASE表达式。
- 避免对索引使用函数或表达式运算。
对索引使用函数或表达式运算会停止使用索引转而执行全表扫描。
- 尽量避免在where子句中使用!=或<>操作符、null值判断、or连接、参数隐式转换。
- 对于高频数据变化的表,在相关SQL语句中添加Hint,以固定执行计划。
高频数据变化的表可能在触发自动ANALYZE之前出现统计信息不是最新的情况,从而导致执行计划选择不优。建议通过在相关SQL中添加Hint来固定执行计划。
- 对复杂SQL语句进行拆分。
对于过于复杂并且不易通过以上方法调整性能的SQL可以考虑拆分的方法,把SQL中某一部分拆分成独立的SQL并把执行结果存入临时表,拆分常见的场景包括但不限于:
- 作业中多个SQL有同样的子查询,并且子查询数据量较大。
- Plan cost计算不准,导致子查询hash bucket太小,比如实际数据1000W行,hash bucket只有1000。
- 函数(如substr、to_number)导致大数据量子查询选择度计算不准。
- 多DN环境下对大表做broadcast的子查询。
其他更多调优点,请参见《开发指南》中“SQL调优指南 > 典型SQL调优点”章节。