GaussDB
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的子查询。
- 点赞
- 收藏
- 关注作者
评论(0)