计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
弹性伸缩 AS
镜像服务 IMS
专属主机 DeH
函数工作流 FunctionGraph
云手机服务器 CPH
Huawei Cloud EulerOS
网络
虚拟私有云 VPC
弹性公网IP EIP
虚拟专用网络 VPN
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
VPC终端节点 VPCEP
云连接 CC
企业路由器 ER
企业交换机 ESW
全球加速 GA
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
边缘安全 EdgeSec
威胁检测服务 MTD
CDN与智能边缘
内容分发网络 CDN
CloudPond云服务
智能边缘云 IEC
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
云搜索服务 CSS
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
数据可视化 DLV
数据湖工厂 DLF
湖仓构建 LakeFormation
企业应用
云桌面 Workspace
应用与数据集成平台 ROMA Connect
云解析服务 DNS
专属云
专属计算集群 DCC
IoT物联网
IoT物联网
设备接入 IoTDA
智能边缘平台 IEF
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
国际站常见问题
ICP备案
我的凭证
支持计划
客户运营能力
合作伙伴支持计划
专业服务
区块链
区块链服务 BCS
Web3节点引擎服务 NES
解决方案
SAP
高性能计算 HPC
视频
视频直播 Live
视频点播 VOD
媒体处理 MPC
实时音视频 SparkRTC
数字内容生产线 MetaStudio
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
存储容灾服务 SDRS
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
专属分布式存储服务 DSS
容器
云容器引擎 CCE
容器镜像服务 SWR
应用服务网格 ASM
华为云UCS
云容器实例 CCI
管理与监管
云监控服务 CES
统一身份认证服务 IAM
资源编排服务 RFS
云审计服务 CTS
标签管理服务 TMS
云日志服务 LTS
配置审计 Config
资源访问管理 RAM
消息通知服务 SMN
应用运维管理 AOM
应用性能管理 APM
组织 Organizations
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
应用身份管理服务 OneAccess
数据库
云数据库 RDS
文档数据库服务 DDS
数据管理服务 DAS
数据复制服务 DRS
云数据库 GeminiDB
云数据库 GaussDB
分布式数据库中间件 DDM
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
人脸识别服务 FRS
图引擎服务 GES
图像识别 Image
内容审核 Moderation
文字识别 OCR
AI开发平台ModelArts
图像搜索 ImageSearch
对话机器人服务 CBS
华为HiLens
视频智能分析服务 VIAS
语音交互服务 SIS
应用中间件
分布式缓存服务 DCS
API网关 APIG
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
多活高可用服务 MAS
事件网格 EG
企业协同
华为云会议 Meeting
云通信
消息&短信 MSGSMS
云生态
合作伙伴中心
云商店
开发者工具
SDK开发指南
API签名指南
Terraform
华为云命令行工具服务 KooCLI
其他
产品价格详情
系统权限
管理控制台
客户关联华为云合作伙伴须知
消息中心
公共问题
开发与运维
应用管理与运维平台 ServiceStage
软件开发生产线 CodeArts
需求管理 CodeArts Req
部署 CodeArts Deploy
性能测试 CodeArts PerfTest
编译构建 CodeArts Build
流水线 CodeArts Pipeline
制品仓库 CodeArts Artifact
测试计划 CodeArts TestPlan
代码检查 CodeArts Check
代码托管 CodeArts Repo
云应用引擎 CAE
开天aPaaS
云消息服务 KooMessage
云手机服务 KooPhone
云空间服务 KooDrive

统计信息调优

更新时间:2024-07-01 GMT+08:00

统计信息调优介绍

GaussDB(DWS)是基于代价估算生成的最优执行计划。优化器需要根据ANALYZE收集的统计信息行数估算和代价估算,因此统计信息对优化器行数估算和代价估算起着至关重要的作用。通过ANALYZE收集全局统计信息,主要包括:pg_class表中的relpages和reltuples;pg_statistic表中的stadistinct、stanullfrac、stanumbersN、stavaluesN、histogram_bounds等。

实例分析1:未收集统计信息导致查询性能差

在很多场景中,由于查询中涉及到的表或列没有收集统计信息,对查询性能产生很大的影响。

示例表的表结构如下所示:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE TABLE LINEITEM
(
L_ORDERKEY         BIGINT        NOT NULL
, L_PARTKEY        BIGINT        NOT NULL
, L_SUPPKEY        BIGINT        NOT NULL
, L_LINENUMBER     BIGINT        NOT NULL
, L_QUANTITY       DECIMAL(15,2) NOT NULL
, L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL
, L_DISCOUNT       DECIMAL(15,2) NOT NULL
, L_TAX            DECIMAL(15,2) NOT NULL
, L_RETURNFLAG     CHAR(1)       NOT NULL
, L_LINESTATUS     CHAR(1)       NOT NULL
, L_SHIPDATE       DATE          NOT NULL
, L_COMMITDATE     DATE          NOT NULL
, L_RECEIPTDATE    DATE          NOT NULL
, L_SHIPINSTRUCT   CHAR(25)      NOT NULL
, L_SHIPMODE       CHAR(10)      NOT NULL
, L_COMMENT        VARCHAR(44)   NOT NULL
) with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(L_ORDERKEY);

CREATE TABLE ORDERS
(
O_ORDERKEY        BIGINT        NOT NULL
, O_CUSTKEY       BIGINT        NOT NULL
, O_ORDERSTATUS   CHAR(1)       NOT NULL
, O_TOTALPRICE    DECIMAL(15,2) NOT NULL
, O_ORDERDATE     DATE NOT NULL
, O_ORDERPRIORITY CHAR(15)      NOT NULL
, O_CLERK         CHAR(15)      NOT NULL
, O_SHIPPRIORITY  BIGINT        NOT NULL
, O_COMMENT       VARCHAR(79)   NOT NULL
)with (orientation = column, COMPRESSION = MIDDLE) distribute by hash(O_ORDERKEY);

查询语句如下所示:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
explain verbose select
count(*) as numwait 
from
lineitem l1,
orders 
where
o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
order by
numwait desc;

可以通过如下方法确认查询中涉及的表或列是否执行过ANALYZE收集统计信息。

  1. 通过explain verbose执行query分析执行计划时会提示WARNING信息:
    WARNING:Statistics in some tables or columns(public.lineitem(l_receiptdate,l_commitdate,l_orderkey, l_suppkey), public.orders(o_orderstatus,o_orderkey)) are not collected.
    HINT:Do analyze for them in order to generate optimized plan.
  2. 通过在pg_log目录下的日志文件中查找以下类似信息来确认是当前执行的query是否由于没有收集统计信息导致查询性能变差。
    2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] LOG:Statistics in some tables or columns(public.lineitem(l_receiptdate, l_commitdate,l_orderkey,
    .l_suppkey), public.orders(o_orderstatus,o_orderkey)) are not collected.
    2017-06-14 17:28:30.336 CST 140644024579856 20971684 [BACKEND] HINT:Do analyze for them in order to generate optimized plan.

查看和确认涉及的表或列没有执行ANALYZE后,可以通过对WARNING或日志中上报的表或列执行ANALYZE,以解决未收集统计信息导致查询变慢的问题。

实例分析2:设置cost_param对查询性能优化

请参考案例:设置cost_param对查询性能优化

实例分析3:多表join的复杂查询存在中间结果不准调优

现象描述:查询与指定人在前后15分钟内、同一网吧登记上网的人员信息:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
C.WBM,
C.DZQH,
C.DZ,
B.ZJHM,
B.SWKSSJ,
B.XWSJ
FROM
b_zyk_wbswxx A,
b_zyk_wbswxx B,
b_zyk_wbcs C
WHERE
A.ZJHM = '522522******3824'
AND A.WBDM = B.WBDM
AND A.WBDM = C.WBDM
AND abs(to_date(A.SWKSSJ,'yyyymmddHH24MISS') - to_date(B.SWKSSJ,'yyyymmddHH24MISS')) < INTERVAL '15 MINUTES'
ORDER BY
B.SWKSSJ,
B.ZJHM
limit 10 offset 0
;

执行计划如图1所示。该查询实际耗时约12秒。

图1 应用unlogged table案例(一)

优化分析:分析过程如下:

  1. 分析该执行计划发现,扫描节点已使用Index Scan,耗时主要在最外层Nest Loop Join的Join Filter计算中,且该计算执行了字符串的加减法和不等值比较。
  2. 考虑使用unlogged table保存目标人的上网信息,且在插入时处理上网开始时间和终止时间,以避免后续进行时间加减。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    //创建临时unlogged table
    CREATE UNLOGGED TABLE temp_tsw
    (
    ZJHM         NVARCHAR2(18),
    WBDM         NVARCHAR2(14),
    SWKSSJ_START NVARCHAR2(14),
    SWKSSJ_END   NVARCHAR2(14),
    WBM          NVARCHAR2(70),
    DZQH         NVARCHAR2(6),
    DZ           NVARCHAR2(70),
    IPDZ         NVARCHAR2(39)
    )
    ;
    //插入目标人的上网记录,并处理上网开始和结束时间。
    INSERT INTO
    temp_tsw
    SELECT
    A.ZJHM,
    A.WBDM,
    to_char((to_date(A.SWKSSJ,'yyyymmddHH24MISS') - INTERVAL '15 MINUTES'),'yyyymmddHH24MISS'),
    to_char((to_date(A.SWKSSJ,'yyyymmddHH24MISS') + INTERVAL '15 MINUTES'),'yyyymmddHH24MISS'),
    B.WBM,B.DZQH,B.DZ,B.IPDZ
    FROM
    b_zyk_wbswxx A,
    b_zyk_wbcs B
    WHERE
    A.ZJHM='522522******3824' AND A.WBDM = B.WBDM
    ;
    
    //查询和目标人在前后十五分钟内在同一网吧上网的人员信息,比较大小时强制转换为int8
    SELECT
    A.WBM,
    A.DZQH,
    A.DZ,
    A.IPDZ,
    B.ZJHM,
    B.XM,
    to_date(B.SWKSSJ,'yyyymmddHH24MISS') as SWKSSJ,
    to_date(B.XWSJ,'yyyymmddHH24MISS') as XWSJ,
    B.SWZDH
    FROM temp_tsw A,
    b_zyk_wbswxx B
    WHERE
    A.ZJHM <> B.ZJHM
    AND A.WBDM = B.WBDM
    AND (B.SWKSSJ)::int8 > (A.swkssj_start)::int8
    AND (B.SWKSSJ)::int8 < (A.swkssj_end)::int8
    order by
    B.SWKSSJ,
    B.ZJHM
    limit 10 offset 0
    ;
    

    上述查询耗时约7秒,执行计划如图2所示。

    图2 应用unlogged table案例(二)
  3. 分析上述执行计划,发现执行了Hash Join,对大表b_zyk_wbswxx建立了Hash Table。由于该表数据量大,创建过程耗时较长。

    由于temp_tsw中仅包含几百条记录,且temp_tsw和b_zyk_wbswxx均通过wbdm(网吧代码)执行等值连接。因此,如果Join方式改为Nest Loop Join,则扫描节点可以实现Index Scan,性能预计将会提升。

  4. 执行如下语句,将Join方式改为Nest Loop Join。
    1
    SET enable_hashjoin = off;
    

    执行计划如图3所示。查询耗时约3秒。

    图3 应用unlogged table案例(三)
  5. 使用unlogged table保存结果集并用于分页显示。

    如果需要在上层应用页面实现分页显示,需要修改offset值确定显示目标页的结果集。按此实现,每次翻页时均执行上面查询语句,耗时较长。

    为解决上述问题,建议使用unlogged table保存结果集。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    //创建保存结果集的unlogged table
    CREATE UNLOGGED TABLE temp_result
    (
    WBM      NVARCHAR2(70),
    DZQH     NVARCHAR2(6),
    DZ       NVARCHAR2(70),
    IPDZ     NVARCHAR2(39),
    ZJHM     NVARCHAR2(18),
    XM       NVARCHAR2(30),
    SWKSSJ   date,
    XWSJ     date,
    SWZDH    NVARCHAR2(32)
    );
    
    //将结果集插入unlogged table,插入耗时约3秒。
    INSERT INTO
    temp_result
    SELECT
    A.WBM,
    A.DZQH,
    A.DZ,
    A.IPDZ,
    B.ZJHM,
    B.XM,
    to_date(B.SWKSSJ,'yyyymmddHH24MISS') as SWKSSJ,
    to_date(B.XWSJ,'yyyymmddHH24MISS') as XWSJ,
    B.SWZDH
    FROM temp_tsw A,
    b_zyk_wbswxx B
    WHERE
    A.ZJHM <> B.ZJHM
    AND A.WBDM = B.WBDM
    AND (B.SWKSSJ)::int8 > (A.swkssj_start)::int8
    AND (B.SWKSSJ)::int8 < (A.swkssj_end)::int8
    ;
    
    //查询结果集表进行分页显示,分页查询耗时约10ms
    SELECT
    *
    FROM
    temp_result
    ORDER BY
    SWKSSJ,
    ZJHM
    LIMIT 10 OFFSET 0;
    
    注意:

    通过ANALYZE收集全局统计信息,通常会改善查询性能。

    如果遇到性能问题:可以使用plan hint来调整到之前的查询计划,详情请参见使用Plan Hint进行调优

我们使用cookie来确保您的高速浏览体验。继续浏览本站,即表示您同意我们使用cookie。 详情

文档反馈

文档反馈

意见反馈

0/500

标记内容

同时提交标记内容