Hive语法差异

建表语句转换

• 表名和模式名去’或者”，数字和特殊字符开头的表名除外。例如：

CREATE TABLE 'ods_gld_gesspsbc_t_rgs_exhibition_sell_flow'

修改为

CREATE TABLE ods_gld_gesspsbc_t_rgs_exhibition_sell_flow

• 字段名中’转换为”或者去掉。例如：

‘id’varchar(32) 修改为id varchar(32)

‘exhibition_id’varchar(32) 修改为exhibition_id varchar(32)

• String类型替换为text。例如：

op_branch_id’string修改为op_branch_id text

• double转换为double precision。例如：

‘benjin’double修改为Benjin double precision

• 删除表定义中storage as等存储参数。例如：

ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde’
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat’
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat’
LOCATION
'hdfs://hdfs-ha/apps/hive/warehouse/ods_import.db/oids_gld_gesspsbs_t_rgs_exhibition_sell_flow’
    TBLPROPERTIES (
      'numFiles’=’1’,
      'numRows’=’0’,
      'rawDataSize’=’0’,
      'totalSize’=’6397150’,
      'transient_lastDdlTime’=’1569923372’);

• NOT NULL ENABLE修改为NOT NULL。
• systimestamp修改为current_timestamp。例如：

default  systimestamp修改为default current_timestamp

 

查询语句

• collect_set函数改写。例如：

concat_ws(',’,collect_set(column_a))

修改为

string_agg(distinct column_a,',')

• 数字开头的表名使用"括起来。例如：

99700010000_pk_fix_dtl_20180101_20181231

修改为

"99700010000_pk_fix_dtl_20180101_20181231"

• 使用空格替换tab符号。
• 部分脚本缺少语句结束分号“；”。
• as后面的'修改为"，例如：

c.yifen as‘一级分行’

修改为

c.yifen as“一级分行”

• 去掉显示提交事务语句commit。例如：

cast('20101231’as date format 'YYYYMMDD’)

修改为

to_date('20101231’,'YYYYMMDD’)

• date_add修改为+运算符。例如：

date_add(to_date('1899-12-31’),cast(a.open_date as int)) open_date

修改为

to_date('1899-12-31’) + cast(a.open_date as int) open_date

• 类型转换中string类型修改为text类型。例如：

cast(f.wrtoff_retrieve_prin as string)

修改为

cast(f.wrtoff_retrieve_prin as text)

• 删除drop语句中的purge关键字。例如：

DROP TABLE fukuan_tmp_result purge;

修改为

DROP TABLE fukuan_tmp_result;

• date_diff转换。例如：

date_diff(time_a, time_b)

转换为

extract(day from (time_a - time_b))

• limit over语法改写。例如：

SELECT
a.conform_IndParty_ID,          --客户号
a.Id_Info_Type_Cd,              --当事人鉴别信息种类代码
a.IndPty_I_Txt, ,               --个人鉴别信息
a.IndPty_Name, ,                --客户名称
a.Home_Address, ,               --家庭住址
a.Home_Tel_Num, ,               --家庭电话
a.Office_Tel_Num,               --家庭电话
a.Mobile_Num,                   --移动电话
a.Sys_Open_Acct_Org_Id,         --开户机构号
a.Agmt_Amt/100000000 Agmt_Amt   --账户余额
  from (
SELECT
row_number() over(partition by a.Conform_Indparty_Id order by a.Home_Address desc, a.Mobile_Num desc) as ttt,
a.conform_IndParty_ID,   
a.Id_Info_Type_Cd,      
a.IndPty_I_Txt,            
a.IndPty_Name,                          
a.Home_Address,        
a.Home_Tel_Num,      
a.Office_Tel_Num,      
a.Mobile_Num,            
a.Sys_Open_Acct_Org_Id,
a.Agmt_Amt
ods_test.jyj_T011_agmt05_1612 a  
)
Where ttt=1;

函数改写

• isdate函数

CREATE OR REPLACE FUNCTION isdate(date_string text) RETURNS BOOLEAN  
AS $$
DECLARE
BEGIN
    If (date_string is NULL) then return FALSE;
        end if;
        PERFORM date_string::timestamp;
        RETURN true;
    EXCEPTION when others then
        RETURN false;
    END;$$
LANGUAGE plpgsql IMUUTABLE;

• 自定义函数，去除末尾end后面得到函数名。

CREATE OR REPLACE function GET_STAIR_BARCH(P_ORG_CD in varchar2)  
   return varchar2 is
   P_SUPER_CD      VARCHAR2(20),
   P_ORG_LEVEL_CD  VARCHAR2(20),
   ORG_CD_P        VARCHAR2(20),
BEGIN
   ORG_CD_P :=P_ORG_CD
   <<CYCLE>>
   SELECT ORG.SUPERIOR_CD,ORG.ORG_LEVEL_CD
     INTO P_SUPER_CD, P_ORG_LEVEL_CD
     FROM analy72.TP_SOU_ORGANIZATION ORG
     WHERE ORG.ORG_CD = ORG_CD_P
   IF (P_ORG_LEVEL_CD NOT IN (‘1’,’0’)) THEN
     ORG_CD _P:= P_SUPER_CD;
     GOTO CYCLE;
   END IF;
   Return(ORC_CD_P);
END /* GET_STAIR_BARCH*/
/

SQL脚本调优

• 使用合适的数据类型。

CREATE TABLE qry_cdm_dtl_tsf1
(
    Tran_date interval,
    sys_seqno TEXT,
    up_fee_mode TEXT,
    tsf_flag TEXT,
    in_acc_flag TEXT,
    in_iden_no TEXT,
    in_cstm_name TEXT,
    out_acc_flag TEXT,
    out_iden_no TEXT,
    out_cstm_name TEXT,
    check_in_date TEXT,
    check_in_seqno TEXT,
    tran_inst TEXT,
    out_open_inst TEXT,
    in_open_inst TEXT,
    start_dt    TEXT,
    end_dt TEXT
)
with (orientation=column)
distribute by hash (sys_sequno);

• 数据量在10W行左右的维度表创建为列存复制表。

CREATE TABLE jgxxb1 with(orientation=column) distribute by replication as select * from jgxxb;

• 数据批量插入后进行analyze操作。

CREATE TABLE qry_cdm_dtl_tsf_tmp1 with(orientation=column) distribute by hash(sys_seqno) as select * from qry_cdm_dtl_tsf_tmp1 where '1899-12-31’::date + tran_date between '20190901’and '20190930’;
--Time: 16411.647 ms
analyze qry_cdm_dtl_tsf_tmp1;

• 对于关联分析的事实表创建为列存hash表，并选择distinct值较多的字段作为分布键。

CREATE TABLE agt_nosf_info1
(
    agt_no text,   
    agt_no text,   
    agt_date text,
    agt_sys_seqno text,
    dfzh text,
    dfhm text,
    dfxh text,
    dfxm text,
    type text  
)  
with (orientation=column)  
distribute by hash (agt_sys_seqno);

• 对表进行简单运算后写入临时表时，临时表的分布键和原表保持一致。

CREATE TABLE tmp_dbr_infol with (orientation=column) distribute by hash (sys_seqno) as select tran_date,sys_seqno,agt_name,agt_paper_type,agt_paper_no from fron_agtinfo_reg group by tran_date,sys_seqno,agt_name,agt_paper_type_agt_paper_no;

• Isdate自定义函数会写大量日志，执行时将日志级别提升为ERROR。

SET log_min_messages to error;

******

SET log_min_messages to warning;

 

GaussDB性能调优

查询流程

From：对from子句中的前两个表执行笛卡尔积，生成虚拟表VT1

On：对VT1应用on筛选器。只有那些使为真的行才被插入VT2

Outer（join）:如果指定了outer join（相对于CROSS JOIN或（INNER JOIN)，保留表（preserved table：左外部连接把左表标记为保留表，右外部连接把右表标记为保留表，完全外部连接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到VT2，生成VT3。如果From子句包含两个含以上的表，则对上一个连接生成的结果表和下一个重复执行步骤1到步骤3，直到处理完所有的表为止。

Where：对VT3应用where筛选器。只有视为true的行才被插入VT4

Group By：按group by子句中的列，列表对VT4的行分组，生成VT5

Cube|Rollup：把超组插入VT5，生成VT6

Having：对VT6应用having筛选器。只有视为ture的组才会被插入VT7

Select：处理select列表，产生VT8

Distinct：将重复的行从VT8中移除，产生VT9

Order By：将VT9中的行按order by子句中的列，列表排序，生成游标（VC10）

Limit：从VC10的开始处选择指定数量或比例的行，生成表VT11，并返回拥有者。

Explain 执行计划

Verbose信息

执行EXPLAIN VERBOSE 命令收集查询语句（SELECT/UPDATE/INSERT/DELETE）的执行计划。

通常采用pretty风格显示执行计划

SET explain_perf_mode=pretty;

 

Plan Information

• id：当前算子在计划中的唯一标记PlanNodeId
• operation：算子名称
• E-rows：当前算子预估的返回行数。
• E-distinct：相关算子DISTINCT值的预估
• E-memory：相关算子的内存估计
• E-width：当前算子预估的返回的元组的宽度
• E-costs：当前算子预估的执行开销

 

Predicate Information

算子上的谓词信息，描述了各个算子承载的具体执行动作

Targerlist Information

• output：各个算子输出信息
• Node/s：描述了算子都在哪些节点上执行，All datanodes表示此算子在所有的DN上执行，否则会输出具体的DN名称。

 

Query summary

• System available mem：系统可用最大内存
• Query Max mem：估算内存消耗最大值
• Query estimated mem：执行内存消耗估值

 

Perf 信息

Explain performance选项可以打印执行中的所有相关信息

• Plan Information：以表格形式显示整个执行过程中的每个算子的执行概要信息
• Predicate Information：算子计算信息，如scan的filter条件，join的join条件
• Memory Information：算子计算过程中内存消耗信息
• Targetlist Information：算子输出列信息
• Datanode Information：算子在每个DN上执行的详细信息
• User Define Profiling：性能profile信息
• Query Summary：Query执行的概要信息

 

性能分析主要关注Plan Information、Datanode Information、Predicate Information这三部分信息。

大集群下，对于复杂SQL，建议使用EXPLAIN ANALYZE打印概要的实际执行信息。

 

Plan Information

相比verbose选项生成的执行计划，新增实际执行的相关信息。

• A-time：算子的实际执行时间，在DN上的输出由[]括起来由逗号分割的两个值，分别表示此算子在不同DN上执行的最短时间和最长时间。
• A-row：算子的实际输出的元组数，为各个DN上算子输出的元组数的总和。
• PeakMemory：算子运行过程中消耗的内存峰值，在DN上的输出由[]括起来由逗号分割的两个值，分别表示此算子在不同DN上执行的最小内存消耗和最大内存消耗。
• A-width：算子每行元组的实际宽度，仅涉及内存使用算子。

 

Memory Infromation

显示整个计划执行时内存的使用情况

• 高内存消耗算子：HashJoin、Sort、Aggregate、Material
• 显示内存信息

节点内存峰值（Query Peak Memory）

算子峰值内存（Peak Memory）

算子估算内存消耗（Estimate Memory）

控制内存（Control Memory）

内存自动扩展次数（Auto Spread Num）

是否存在数据下盘（Early Spilled）

下盘次数（Spill Time）

下盘文件数（Temp File Num）

 

DataNode Information

描述算子在各DN的执行时间、CPU、Buffer的使用情况

• 执行时间（actual time）：如果这个值在各个DN上存在较大差异，可初步判断存在计算倾斜。
• 输出元组数（rows）：结合执行时间进一步佐证是否存在计算倾斜。
• buffer命中率（hit）：主要针对scan算子作数据扫描时。从性能角度来说，buffer命中率越高越好，需要增大集群的shared_buffers（行存）、cstore_buffers（列存）配置参数的取值。
• CPU的执行cycle：在算子执行期间，执行所消耗的CPU cycle。

 

User Define Information

描述算子执行中，关键动作的性能打桩西悉尼

• CN和DN建立连接
• 数据加载
• 数据解压
• 列存的min/max check
• 列存场景的Batch加载
• 算子上的投影和过滤计算

 

不下推计划

Set enable_stream_operaator =off;可以强制生成不下推计划

语句中的不下推因素会在pg_log中打印相关日志

用户自定义函数是不下推的主要因素

FQS执行计划

如果原来生成FWS计划，那么set enable_fast_query_shipping=off;可以强制他生成FQS计划。

FQS计划的特点是DN之间没有数据交换，不需要Broadcast/Redistribute算子参与。

单节点执行的语句

一条语句在单DN上就可以执行完成，这种语句的特点通常是where条件中有关于hash键的等值条件。

 

调优原则

• 基本原则（唯一原则）：资源利用最大化原则
• 资源：CPU、内存、磁盘IO、网络IO
• SQL语句应当尽量高效，节省资源开销
• 以最小的代价实现最大的利益
• 举例：典型点查询
• .seqscan+filter（读取每一条元组和点查询条件进行匹配）
• .indexscan（基于表建立索引表）

Indexscan可以以更小的代价实现相同的查询效果

• SQL语句应当充分利用资源
• 应当尽量充分利用资源，实现性能的极致
• 举例：sort排序

在内存可控的范围内尽量保证数据不下盘，让数据在内存中排序，从而提升排序效率，保证性能的最大收益。

调优流程

静态调优

根据硬件资源和客户的业务特征确定集群部署方案、表定义。

• 表定义：行列存、复制/哈希分布等
• 集群部署方案和表定义一旦确定，后续改动的代价会比较大

 

集群部署有SA协助规划，只关注表定义创建策略

数据分布在DN上，好的表定义要求：

• 表数据均匀分布在各个DN

 防止单个DN数据过多导致集群有效容量下降。

 选择合适分布列避免数据分布倾斜。

• 表scan压力均匀分散在各个DN

 避免单DN的Scan压力过大，形成scan的单节点瓶颈。

 避免把基表上的等值filter中的列作为分布列。

• 减少扫描数据量：通过分区机制实现
• 尽量减少随机IO：通过聚簇/局部聚簇可以实现这点
• 尽量避免数据shuffle

     减少网络压力

 建议选择join-condition或者group by 列作为分布列

 避免用随机数，sequence列作为分布列

 

分布方式

• 复制（Replication）
• 集群中每个DN实例上都有一份全量表数据
• Join操作可减少重分布造成的网络开销
• 存在数据冗余
• 适用于小表、维表
• 哈希（Hash）
• 数据通过Hash方式散列到集群的所有DN实例
• 读写数据可充分利用各个节点IO资源，提升读写速度
• 适用于大数据量大的表

 

存储类型

重要性

• 表设计决定数据的存储形式，进而影响I/O操作及内存使用
• 存储类型是表定义的第一步，客户业务属性决定表的存储类型。

行列存选择依据

存储类型	适用场景
行存	点查询（返回记录少，基于索引的简单查询）； 增删改比较多的场景。
列存	统计分析类查询（GROUP,JOIN多的场景）； 即席查询（查询条件列不确定，行存无法确定索引）。

 

选择分布列

一、基本原则

• 列值应比较离散，以便数据能够均匀分布到各个DN

 通常选择表的主键为分布列。

• 复杂查询场景下，尽量不要选取存在常量等过滤条件，避免DN剪枝后Scan集中到一个DN上。

 点查询的场景下，则应该尽量选择where条件中的等值过滤条件作为分布列

• 选择查询中的连接条件为分布列，以便Join任务能够下推到DN中执行，且减少DN间的通信数据量。
• 判断数据是否存在存储倾斜的方法：table_distribution函数
• 不同DN的数据量：相差5%以上即可视为倾斜；相差10%以上，必须调整分布列

 SELECT * FROM table_distribution(schemaname text , tablename text);

 

局部聚簇（Partial Cluster Key，简称PCK）

列存储下一种通过min/max稀疏索引实现基表快速扫描的一种索引技术

• 适用场景

数据规模：大表

业务特征：大批量数据导入，每次导入数据量远小于DN数*6W

基表相关查询存在大量形如Col op Const约束，其中col为列名，const为常量值，op为操作符=、>、>=、<=、<

尽量选用选择度比较高的简单表达式的列，且把选择度低的约束列放在PCK中的前面

• 使用约束

一张表只能建立一个PCK

 

使用分区表

分区表

• 把逻辑上的一个大表按照某种策略分成几块物理块进行存储时，逻辑上的大表成为分区表，每个物理块则成为一个分区。
• 在查询时，通过分区剪枝技术尽可能减少底层数据扫描
• 改善查询性能
• 增强可用性
• 方便维护
• 适用场景
• 数据规模：大表
• 业务特征：通过剪枝缩小查询范围
• 分区键的选择：可以将数据均匀映射到各个分区的列

 

执行态调优（动态调优）

根据SQL语句执行的实际情况采取针对性干预SQL执行计划的方式来提升性能。

• 常用手段：SQL改写、GUC参数干预、Plan Hint

 

概述

动态调优，即执行态调优，是一个不断分析与尝试的过程。

首先，试跑Query；然后，判断性能是否满足客户需求；

如果不满足客户需求，则需要进一步分析性能瓶颈点；

获取性能瓶颈点之后进行针对性优化，重新试跑，一直到满足性能目标。

 

基本步骤

• 判断查询相关表是否已收集统计信息
• 判断查询语句是否下推
• 收集Perfromance信息进行性能分析，并做针对性优化
• SQL改写优化

 

统计信息

• 可用的统计信息
• 统计信息（表数据特征）
• 表的元组数
• 字段宽度
• NULL记录比率
• DISTINCT值
• MCV值（Most Common Value）
• HB值（直方图，数据分布概率区间）
• 优化器（基于代价的优化（Cost-Based Optimization,简称CBO））
• 数据库根据大量表数据特征，结合代价计算模型，通过代价估算输出的最优执行计划
• 统计信息是查询优化的核心输入，准确的统计信息可以帮助优化器选择最合适的查询计划
• 统计信息如何收集？
• 适用ANALIYZE语法收集整个表或表的若干列的统计信息
• 建议周期性执行ANALYZE或 在表做了大量更新后对表执行ANALYZE

 

函数下推行为的分析

• 函数的易变属性可分为三种，在创建函数时如果不指定函数易变属性，默认为VOLATILE
• IMMUTABLE：相同的入参值，总是返回相同的结果。如 pg_catalog.trunc(numeric)
• STABLE：相同的入参值，同一次表扫描中，函数返回值不变，但在不同SQL语句中返回值可能发生变化。通常，此类函数的输出值收到环境变量的影响，但一条SQL语句执行过程中，此环境变量不会发生变化。如 pg_catalog.now()
• VOLATILE：相同的入参值，函数值随时可能返回不同的结果，即便是同在一次表扫描内，如pg_catalog.random()
• 函数定义时可指定函数下推属性为SHIPPABLE，如果定义为NOT SHIPPABLE，即调用函数的SQL语句不下推。
• 函数的下推行为由其易变属性和下推属性共同决定。

下推属性 易变属性	IMMUTABLE	STABLE	VOLATILE
SHIPPABLE	下推	下推	下推
NOT SHIPPABLE	下推	不下推	不下推

 

Performance分析

• Explain Performance：收集Query的执行信息+分析可能的性能问题+针对性优化
• 重点关注：耗时占整体执行时间高的算子
• 重要执行信息：DataNode Information、Memory Information、Targelist Information
• 常见算子的瓶颈及优化策略
• scan性能瓶颈
• 基表扫描元组数过多：增加索引、使用PCK、使用分区
• 数据在各个DN分布不均衡：调整分布列方式
• Join性能瓶颈
• join方式选择不当：增加索引、使用Plan Hint
• Join内外表选择不当：使用Plan Hint、改写SQL
• Subplan性能瓶颈
• Subplan比Join性能差，建议改写Subplan为join

 

Join性能瓶颈

• join性能提升策略
• 策略1：选择高校的join方式
• 支持的join方式：HashJoin、MergeJoin、Nestloop
• 通常情况下，HashJoin较为高效的Join方式
• 部分特定场景下，Nestloop + IndexScan性能更好

 满足条件：

• INNER JOIN
• JOIN条件为简单等值表达式
• OUTER分支输出结果集特别少
• INNER分支只有表SCAN，且JOIN条件相关列有索引

 

Plan Hint

概述

• Plan Hint为用户提供直接影响执行计划生成的手段，通过对执行计划的调优，提升查询的性能。
• 常用的Hint调优手段
• 指定join顺序
• 指定join、stream、scan方法
• 指定结果行数
• 指定重分布过程中的倾斜信息
• 功能描述

Plan Hint仅支持在SELECT关键字后通过如下形式指定：

/*+<planhint>*/

• 可以同时指定多个Hint，使用空格分隔
• Hint只能Hint当前层的计划，对于子查询计划的Hint，需要在子查询的select关键字后指定Hint

SELECT /*+<planhint1> <planhint2>*/ FROM t1 , (SELECT /*<planhint3>*/ FROM t2) WHERE 1=1;

• 在视图定义时指定Hint，该视图每次被调用时都会使用该Hint信息

Hint语法

Join顺序的Hint

• 仅指定join顺序，不指定内外表顺序

 Leading(join_table_list)

• 同时指定join顺序和内外表顺序，内外表顺序仅在最外层生效。

 Leading（（join_table_list））

注：join_table_list为表join顺序和hint字符串，可以包含当前层的任意个表，可以使用括号指定优先级，表之间使用空格分隔；表只能用单个字符串表示，不能带schema；表如果存在别名，需要优先使用别名来表示该表。

• 示例
• 表t1,t2,t3,t4,t5先join，且未指定表join顺序及内外表顺序。

 Leading(t1 t2 t3 t4 t5)

• 表t1和t2先join，t2做内表；再和t3 join，t3做内表；再和t4 join，t4做内表；再和t5 join，t5做内表。

 Leading((t1 t2 t3 t4 t5))

• 表t2, t3 ,t4先join，不指定内外表；再和t1, t5 join，不指定内外表。

 Leading(t1 (t2 t3 t4) t5)

 

Join方式的hint

• join方法的可选项：Nested Loop、Hash Join、Merge Join
• 语法格式

[no] nestloop|hashjoin|mergejoin(table_list)

• 可选项no表明Hint指定的Join方法不使用
• table_list：Hint表集合的字符串，中间不允许出现括号指定Join的优先级

Hint检测

Plan Hint的结果体现在计划的变化上，通过explain来对比Hint前后的计划变化来判定Hint的效果。

Hint中的错误不会影响语句的执行，只是不能生效，在explain语句时，Hint错误会以WARNING信息的形式显示。

 

Hint的错误、冲突及告警

• 语法错误
• 语法规则树规约失败、报错并指出出错位置
• 语义错误
• 表不存在，存在多个
• Scan Hint中的index不存在
• 表在Leading或Join中出现多次，均会报语义错误
• 子查询提升后，定义的blockname重名
• 子链接提升后hint失效
• 出现在子链接中存在多个表连接，子链接提升后，子链接中的多个表不再作为一个整体出现在join中
• Hint重复或冲突

 Hint重复或冲突时，只有第一个Hint生效，其他Hint均会失效

• Hint重复是指，hint的方法及表名均相同
• Hint冲突是指，table list一样的hint，存在不一样的hint
• Hint未被使用
• 非等值join使用hashjoin hint或mergejoin hint
• 不包含索引的表使用indexscan hint或indexonlyscan hint
• 查询条件上没有合适的过滤条件，无法使用hint指定的index
• 多个表存在等值连接时，仅尝试有等值连接条件的表的连接
• 如果表的分布列与join列相同，则不会生成sream和hint指定的redistribute计划
• 如果表的分布列与join列不同，且另一表分布列与join列相同，不会生成stream和hint指定broadcast的计划
• 在replication对象（基表或者连接表）上hint broadcast或者redistribute
• 如果子链接未被提升，则blockname hint不会被使用

 

执行计划中的关键字说明

1.表访问方式

• Seq Scan

全表顺序扫描。

• Index Scan

优化器决定使用两步的规划：最底层的规划节点访问一个索引，找出匹配索引条件的行的位置，然后上层规划节点真实地从表中抓取出那些行。独立地抓取数据行比顺序地读取它们的开销高很多，但是因为并非所有表的页面都被访问了，这么做实际上仍然比一次顺序扫描开销要少。使用两层规划的原因是，上层规划节点在读取索引标识出来的行位置之前，会先将它们按照物理位置排序，这样可以最小化独立抓取的开销。

如果在WHERE里面使用的好几个字段上都有索引，那么优化器可能会使用索引的AND或OR的组合。但是这么做要求访问两个索引，因此与只使用一个索引，而把另外一个条件只当作过滤器相比，这个方法未必是更优。

索引扫描可以分为以下几类，他们之间的差异在于索引的排序机制。

n ●Bitmap Index Scan

使用位图索引抓取数据页。

n ●Index Scan using index_name

使用简单索引搜索，该方式表的数据行是以索引顺序抓取的，这样就令读取它们的开销更大，但是这里的行少得可怜，因此对行位置的额外排序并不值得。最常见的就是看到这种规划类型只抓取一行，以及那些要求ORDER BY条件匹配索引顺序的查询。因为那时候没有多余的排序步骤是必要的以满足ORDER BY。

2.表连接方式

• Nested Loop

嵌套循环，适用于被连接的数据子集较小的查询。在嵌套循环中，外表驱动内表，外表返回的每一行都要在内表中检索找到它匹配的行，因此整个查询返回的结果集不能太大（不能大于10000），要把返回子集较小的表作为外表，而且在内表的连接字段上建议要有索引。

• (Sonic) Hash Join

哈希连接，适用于数据量大的表的连接方式。优化器使用两个表中较小的表，利用连接键在内存中建立hash表，然后扫描较大的表并探测散列，找到与散列匹配的行。Sonic和非Sonic的Hash Join的区别在于所使用hash表结构不同，不影响执行的结果集。

• Merge Join

归并连接，通常情况下执行性能差于哈希连接。如果源数据已经被排序过，在执行融合连接时，并不需要再排序，此时融合连接的性能优于哈希连接。

3.运算符

• sort

对结果集进行排序。

• filter

EXPLAIN输出显示WHERE子句当作一个"filter"条件附属于顺序扫描计划节点。这意味着规划节点为它扫描的每一行检查该条件，并且只输出符合条件的行。预计的输出行数降低了，因为有WHERE子句。不过，扫描仍将必须访问所有 10000 行，因此开销没有降低；实际上它还增加了一些（确切的说，通过10000 * cpu_operator_cost）以反映检查WHERE条件的额外CPU时间。

• LIMIT

LIMIT限定了执行结果的输出记录数。如果增加了LIMIT，那么不是所有的行都会被检索到。

经验总结：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来提升查询效率。

• 选择hashagg。

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

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

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

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

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

• 尽量避免在where子句中使用!=或<>操作符、null值判断、or连接、参数隐式转换。
• 对复杂SQL语句进行拆分。

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

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

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

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

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