南大通用GBase 8c常见的SQL调优手段

Plan Hint调优

Plan Hint为用户提供了直接影响执行计划生成的手段，用户可以通过指定join顺序、join、scan方法、指定结果行数等多个手段来进行执行计划的调优，以提升查询的性能。
使用方法：

/*+ <plan hint>*/

如果同时指定多个hint，之间需要使用空格分割，如：

select /*+ <plan_hint1> <plan_hint2> */ * from t1

Plan Hint支持的范围：

• 指定Join顺序的Hint - leading hint：
• 指定Join方式的Hint：
• 指定结果集行数的Hint：
• 指定Scan方式的Hint，仅支持常用的tablescan，indexscan和indexonlyscan的hint：
• 指定子链接块名的Hint：

Plan Hint调优示例

stu和stu_info两个表关联查询，默认使用Hash Join方式进行表关联。

使用Hint强制改变执行计划，让两个表通过nestloop进行关联查询

使用select * 查询stu_info表，指定code和stu_id的查询条件，默认使用Seq Scan方式获取数据。

使用Hint方式指定查询时使用stu_info表的idx_stu_info_code这个索引，改变执行计划

算子级优化示例

基表扫描时，对于点查或者范围扫描等过滤大量数据的查询，如果使用SeqScan全表扫描会比较耗时，可以在条件列上建立索引选择IndexScan进行索引扫描提升扫描效率。

执行耗时：248ms

对stu表的name表创建索引

执行耗时：25ms

SQL改写

避免使用SELECT * 

实际业务可能只需要表的几列数据，使用SELECT * 会导致内存，CPU，网络IO等资源浪费。SELECT * 不会走覆盖索引扫描，部分SQL语句效率下降明显。

使用exists代替in

select st.id,st.name from stu st where st.age=5 and st.id not in (
select stu_id from stu_info where code in ('a','b','c','d')
);

正解：

select st.id,st.name from stu st where st.age=5 and not exists (
select 1 from stu_info si where si.stu_id=st.id and si.code in ('a','b','c','d')
);

使用连接查询代替子查询

select * from stu
where id in (select stu_id from stu_info where code='a')

正解：

select st.* from stu st
inner join stu_id si on si.user_id = st.id and si.code='a'

批量操作

当有多条加工数据需要写入表中时，采取批量写入可以减少I/O消耗

insert into stu values (1,'bill',15);
insert into stu values (2,'frank',16);

正解：

insert into stu values (1,'bill',15),(2,'frank',16);

创建适当索引

CREATE INDEX idx_stu_name ON stu(name);

需要注意点：

索引并非越多越好，太多索引会引起update和insert效率下降；

分析查询语句，使用联合索引；

注意索引区分度和索引冗余；

避免在大量null字段上创建索引；

以及其他常见优化方法，如使用limit限制数据返回行；分页优化，通过主键或者其他索引列先找出符合条件行主键，再通过主键过滤查询；避免在where字句中使用函数等表达式；对大表数据使用分区表，按照分区进行查询操作。