数据库性能优化实验

cjx · 2025 年3 月 26 日 15:11

GaussDB数据库性能优化实验

该实验旨在指导用户通过管理员账号查询系统性能指标，掌握使用WDR报告生成性能报告并分析。定位到Top SQL后，掌握查看SQL执行计划的方法，并应用不同的方式干预执行计划从而优化执行效率和成本。

界面常用操作说明： 收起

![|553x125](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps170.jpg)

全屏：点击全屏，实验手册和操作桌面进入全屏，再次点击退出全屏。

复制粘贴：作为媒介，支持从本地复制内容粘贴至云主机上，或者从云主机上复制内容到本地。双击或者选择左边手册内容也可以复制所选内容。

实验报告：可以查看实验步骤完成情况、实验问题列表、过程记录。

新手引导：查看更多功能区指引。

手册中代码复制，请点击命令框右上角拷贝代码按钮，鼠标挪到粘贴位置，右键粘贴

![|476x89](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps171.jpg)

一、实验配置 收起

1.预置实验环境

点击实验桌面左上角，账号下方的预置实验环境按钮，进行基础的华为云服务预置

![|516x338](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps172.jpg)

2.操作前提：登录华为云

进入【实验操作桌面】，打开浏览器进入华为云登录页面。选择【IAM用户登录】模式，于登录对话框中输入系统为您分配的华为云实验账号和密码登录华为云，如下图所示：

【实验操作桌面】是什么？

注意：账号信息详见实验手册上方，切勿使用您自己的华为云账号登录。

![|516x303](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps173.jpg)

![|552x274](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps174.jpg)

3.购买 GaussDB 实例

1）进入云数据库GaussDB服务，点击左侧的【服务列表】，选择其中【数据库】下的【云数据库GaussDB】。

![|553x244](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps175.jpg)

![|552x292](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps176.jpg)

2）进入云数据库GaussDB，点击页面右上角的【购买数据库实例】按钮。

3）进入购买页后，根据以下参数信息购买数据库。

主要参数如下，其他请保持默认。

• 计费模式 ：选择【按需计费】

• 区域和项目 ：默认的【华北-北京四】

• 实例名称 ：可以设置为【gauss-hccdp】

• 产品类型 ：基础版

• 数据库引擎版本 ：选择最新版即可

• 实例类型 ：集中式版

• 部署形态 ：1主2备

• 可用区 ：选择默认的可用区一、可用区二、可用区三，如果这些可用区购买完了，可以自行选择其他可用区。

• 性能规格 ：选择 “通用型（1:4） | 4 vCPUs | 16 GB”，如果购买完了，选择其他最小的规格即可，如独享型（1:4） | 4 vCPUs | 16 GB。

• 存储类型、存储空间、磁盘加密 ：保持默认

• 虚拟私有云、内网安全组 ：选择已预置的资源vpc-hce和subnet-hce

• 安全组 ：选择已预置的sg-hce

• 数据库端口 ：使用默认的端口8000

• 管理员密码 ：设置符合安全要求的root用户密码，并记住以便后用，其他选择默认

4）确认信息无误后，点击【提交】。

![|552x258](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps177.jpg)

5）任务提交成功后，点击【返回云数据库GaussDB列表】。

![|554x205](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps178.jpg)

6）此时数据库进入创建状态，在运行状态处显示为【创建中】。

![|553x144](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps179.jpg)

注意：gaussDB实例创建需要20分钟，请耐心等待

7）当运行状态处于【正常】，表示数据库已创建完成。

![|553x172](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps180.jpg)

4. gsql客户端工具配置以及http下载

点击“服务列表>数据库>云数据库GaussDB”。

获取数据库实例的内网地址,点击GaussDB实例名称，进入信息页面：

![|553x197](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps181.jpg)

滑动到节点列表处，就可以看到主节点IP:

![|553x253](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps182.jpg)

获取ECS地址,首先进入弹性服务器ECS服务:

![|553x188](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps183.jpg)

然后在ECS服务器列表可以看到预置的ECS，IP地址信息可以在IP地址列被拷贝：

![|553x194](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps184.jpg)

打开桌面的xfe终端

ssh root@EIP

获取密码方式如下，点击如图的眼睛图标，密码就可以被拷贝：

![|178x131](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps185.jpg)

下载httpd并启动

yum -y install httpd

systemctl start httpd

下载gsql客户端，并解压。

wget https://dbs-download.obs.cn-north-1.myhuaweicloud.com/GaussDB/1660794000209/GaussDB_opengauss_client_tools.zip

unzip GaussDB_opengauss_client_tools.zip

cd /root/GaussDB_opengauss_client_tools/Centralized/Euler2.5_X86_64

cp GaussDB-Kernel-V500R002C10-EULER-64bit-gsql.tar.gz /opt

cd /opt/

tar -zxvf GaussDB-Kernel-V500R002C10-EULER-64bit-gsql.tar.gz

二、实验步骤 收起

一）系统性能指标查询

步骤 1 登录并连接数据库，其中数据库的IP及用户密码请根据实际情况替换（GaussDB的IP以主节点IP登录）。

cd /opt/

source gsql_env.sh

执行以下命令连接数据库，xxx.xx.xx.x是GaussDB主节点的IP，数据库默认的是postgres, 端口号也是创建时的默认值 8000，用户是默认是root, yourpassword是设置的数据库密码，请根据实际情况替换。

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

备注：GaussDB主节点的IP的获取方式见上面“一、实验配置第4节”。

步骤 2 查看当前操作系统的负载状态。

SELECT id,name,value FROM dbe_perf.os_runtime;

由上述信息可见，当前实例规格为4vCPU，16G物理内存。

一般而言，CPU占用率的计算方式为BUSY_TIME/( BUSY_TIME+ IDLE_TIME) * 100%，按上述示例中数据计算为24.83%，系统负载并不高。

步骤 3 查看系统级的时间消耗。

SELECT * FROM dbe_perf.instance_time;

DB_TIME表示作业在多核下的有效时间花销，CPU_TIME表示CPU的时间花销。重点关注DB_TIME和CPU_TIME。其余为SQL语句执行过程中各部分的时间消耗。

步骤 4 查看共享内存上下文分配和使用率。

SELECT * FROM dbe_perf.shared_memory_detail WHERE level=2 ORDER BY usedsize DESC LIMIT 10;

示例展示了level=2的共享内存上下文使用前十位的内容。

步骤 5 查看会话级负载强度。

SELECT * FROM dbe_perf.session_stat LIMIT 10;

示例展示了当前节点的会话状态信息。

步骤 6 查看会话级时间细分。

SELECT * FROM dbe_perf.session_time ORDER BY value DESC LIMIT 10;

示例展示了当前节点会话线程的运行时间信息，及各执行阶段所消耗时间前十位。

步骤 7 获取实例工作线程、辅助线程列表。

SELECT * FROM dbe_perf.THREAD_WAIT_STATUS LIMIT 5;

THREAD_WAIT_STATUS视图可以检测当前实例中工作线程（backend thread）以及辅助线程（auxiliary thread）的阻塞等待情况

步骤 8 获取各功能模块中IO、LOCK、LWLOCK、STATUS四类事件的等待次数，等待失败次数，等待时间等维度的统计信息。

SELECT * FROM dbe_perf.WAIT_EVENTS ORDER BY total_wait_time DESC LIMIT 5;

示例展示了当前等待时间最长的5个等待事件。

步骤 9 查询数据库级别的活跃连接数，负载强度，块读写性能，行活动，死锁，临时下盘文件等信息。

SELECT * FROM dbe_perf.STAT_DATABASE LIMIT 2;

示例展示了数据库当前读、写、死锁等状态信息。

二）WDR报告生成与分析

步骤 1 查看WDR相关配置项。

SELECT name,setting FROM pg_settings WHERE name LIKE ‘%wdr%’;

![|553x139](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps186.jpg)

enable_wdr_snapshot表示快照功能已开启；

wdr_snapshot_interval表示快照收集间隔为60分钟；

wdr_snapshot_query_timeout表示快照执行SQL的超时时间为100秒；

wdr_snapshot_retention_days表示快照保留天数为8天。

步骤 2 查看WDR报告相关系统表。

WDR的数据表保存在snapshot这个schema下以snap_开头的表，其数据来源于dbe_perf这个schema内的视图。

SELECT relname FROM pg_class WHERE relname LIKE ‘%snap_%’;

![|553x324](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps187.jpg)

![|440x834](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps188.jpg)

![|435x825](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps189.jpg)

![|438x564](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps190.jpg)

步骤 3 查询数据库节点信息。

\x

![|184x40](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps191.jpg)

select create_wdr_snapshot();

步骤 4 手工收集快照

select * from pg_node_env;

步骤 5 查看最新的10个snapshot序号。

SELECT * FROM snapshot.snapshot ORDER BY start_ts DESC LIMIT 10;

说明：根据步骤1中查到的wdr_snapshot_interval的值，快照的默认收集时间间隔是60分钟。所以对于刚购买完成的实例（购买成功后未满1小时），通常只能查到1个快照。

步骤 6 生成WDR报告。

\a\t

\o /root/index.html

其中，\a: 不显示表行列符号， \t: 不显示列名，\o: 指定输出文件。

生成报告语句格式示例：

SELECT generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name);

参数说明：

begin_snap_id：要查看的某段时间性能的开始的snapshot的id（表snapshot.snaoshot中的snapshot_id）。

end_snap_id：结束snapshot的id，默认end_snap_id大于begin_snap_id（表snapshot.snaoshot中的snapshot_id）。

report_type：指定生成report的类型，可取值summary、detail、all。

report_scope：指定生成report的范围，可取值cluster、node。

node_name：在report_scope指定为single node时，需要把该参数指定为对应节点的名称，取pg_node_env中node_name字段值。在report_scope为cluster时，该值可以指定为省略或者为NULL。

示例，生成集群级别的报告，示例中begin_snap_id和end_snap_id选择步骤4中查到的snapshot（其中的数值替换成实际步骤5查询到的snapshot_id数值）：

SELECT generate_wdr_report(34, 35, ‘all’, ‘cluster’);

SELECT generate_wdr_report(34, 35, ‘all’, ‘node’, ‘dn_6001_6002_6003’);

执行如下命令关闭输出选项及格式化输出命令。

\o \t \a

步骤 7 使用http查看WDR报告，查看WDR报表主要内容。

① 前往华为云控制台，点击服务列表-点击虚拟私有云VPC

![|442x388](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps192.jpg)

点击访问控制-安全组-在预置好sg-hccdp安全组后面的配置规则

![|553x135](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps193.jpg)

选择入方向规则-添加规则，输入优先级为1，端口80，源地址0.0.0.0/0, 点击确认，放开该端口

![|553x233](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps194.jpg)

![|553x136](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps195.jpg)

② 回到ecs界面，将html文件挪到/var/www/html里

mv /root/index.html /var/www/html/

步骤 8 WDR报告解读。（报告及对应SQL）

打开桌面浏览器输入以下地址注意替换EIP地址为实际ECS服务器的IP地址：

http://EIP:80/index.html

![|553x338](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps196.jpg)

Workload Diagnosis Report

![|553x208](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps197.jpg)

![|514x636](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps198.jpg)

![|512x349](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps199.jpg)

Instance Efficiency Percentages

实例的效率百分比，目标值是100%，即越接近100%，数据库运行越健康。

![|553x228](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps200.jpg)

Buffer Hit：即数据库请求的数据在buffer中命中的比例，该指标越高代表GaussDB在buffer中查询到目标数据的概率越高，数据读取性能越好。

Effective CPU：即有效的CPU使用比例，该指标偏小则说明CPU的有效使用偏低，处于等待状态的比例可能较高。

WalWrite NoWait：即WAL日志写入时不等待的比例，该指标接近100%，说明buffer容量充足，可以满足WAL写操作的需求，若指标值偏小则可能需要调大buffer容量。

Soft Parse：即SQL软解析的比例，该指标接近100%，说明当前执行的SQL基本都可以在Buffer中找到，若指标值偏小则说明存在大量硬解析，需要分析原因，对DML语句进行适度优化。

Non-Parse CPU：即非解析占用的CPU比例，该指标接近100%，说明SQL解析并没有占用较多的CPU时间。

![|435x825](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps201.jpg)

![|438x784](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps202.jpg)

Wait Events

![|553x264](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps203.jpg)

![|436x481](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps204.jpg)

Configuration Settings

![|553x98](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps205.jpg)

![|437x255](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps206.jpg)

Wait Classes by Total Wait Time

![|553x234](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps207.jpg)

![|445x524](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps208.jpg)

Host CPU

![|553x103](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps209.jpg)

![|435x559](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps210.jpg)

![|443x831](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps211.jpg)

![|437x47](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps212.jpg)

Database requests：即每秒IO请求次数，包括请求次数总和、读请求次数、写请求次数.

Database(blocks)：即每秒block请求数量，包含请求的block总和数量、读block的数量和写block的数量.

Database(MB): 即将block换算成容量(MB)[如：blocks * 8/1024]，增加数据的可读性。

Redo requests和Redo(MB) 分别表示每秒redo的写请求次数和redo写的数据量。

![|437x359](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps213.jpg)

![|435x428](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps214.jpg)

Memory Statistics

![|436x373](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps215.jpg)

![|438x78](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps216.jpg)

Time Model

![|345x342](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps217.jpg)

![|435x253](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps218.jpg)

SQL Statistics

![|553x162](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps219.jpg)

![|438x820](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps220.jpg)

![|433x298](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps221.jpg)

SQL Detail

![|553x97](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps222.jpg)

![|433x113](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps223.jpg)

三）执行计划干预-创建索引

步骤 1 登录并连接数据库，其中数据库的IP及用户密码请根据实际情况替换。

退出数据库在ecs服务器上进行操作，在系统root/目录下准备相应的数据

cd /root

wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com/gaussdb/lab_data.zip

unzip lab_data.zip

wget https://sandbox-expriment-files.obs.cn-north-1.myhuaweicloud.com/gaussdb/employees.tar.gz

tar -zxvf employees.tar.gz

mv home/user/rar/employees/ .

cd /opt/

source gsql_env.sh

登录数据库postgres，xxx.xx.xx.x是GaussDB主节点的IP，数据库默认的是postgres, 端口号也是创建时的默认值 8000，用户是默认是root, yourpassword是设置的数据库密码，请根据实际情况替换。

gsql -h xxx.xx.xx.x -d postgres -p 8000 -U root -W yourpassword -r

步骤 2 创建表

CREATE TABLE salaries (

emp_no int NOT NULL,

salary int NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,from_date)

);

CREATE TABLE employees (

emp_no int NOT NULL,

birth_date date NOT NULL,

first_name varchar(14) NOT NULL,

last_name varchar(16) NOT NULL,

gender char(2) NOT NULL,

hire_date date NOT NULL,

PRIMARY KEY (emp_no)

);

CREATE TABLE departments (

dept_no char(4) NOT NULL,

dept_name varchar(40) NOT NULL,

PRIMARY KEY (dept_no));

CREATE TABLE titles (

emp_no int NOT NULL,

title varchar(50) NOT NULL,

from_date date NOT NULL,

to_date date DEFAULT NULL,

PRIMARY KEY (emp_no,title,from_date),

CONSTRAINT titles_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE

) ;

CREATE TABLE dept_manager (

emp_no int NOT NULL,

dept_no char(4) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,dept_no),

CONSTRAINT dept_manager_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

CONSTRAINT dept_manager_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE

) ;

CREATE TABLE dept_emp (

emp_no int NOT NULL,

dept_no char(4) NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL,

PRIMARY KEY (emp_no,dept_no),

CONSTRAINT dept_emp_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,

CONSTRAINT dept_emp_ibfk_2 FOREIGN KEY (dept_no) REFERENCES departments (dept_no) ON DELETE CASCADE

);

步骤 3 上传数据文件至/root/employees下，并导入数据。

\copy salaries from ‘/root/employees/salaries.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

\copy employees from ‘/root/employees/employees.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

\copy titles from ‘/root/employees/titles.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

\copy departments from ‘/root/employees/departments.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

\copy dept_manager from ‘/root/employees/dept_manager.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

\copy dept_emp from ‘/root/employees/dept_emp.csv’ with ( FORMAT ‘csv’, DELIMITER ‘,’, ENCODING ‘utf8’);

步骤 4 HR想查看employees系统中，1999后入职人员薪资低于40000的人员名单，针对这些人员进行调薪。

因为薪资salaries表中，存储着每位员工各个时期的薪资情况，因此需要通过最大的日期，将每个员工最新的薪资情况进行查询，并同employees表进行关联，将入职时间在1999年后员工查询出来。

SELECTe.first_name,e.last_nameFROM

employees e join

salaries son s.emp_no = e.emp_noWHEREe.hire_date > '1999-01-01’and s.to_date='9999-01-01’AND s.salary < 40000GROUP BYe.first_name,e.last_name;

步骤 5 查看执行计划。

\timing onEXPLAIN SELECTe.first_name,e.last_nameFROM

employees e join

salaries son s.emp_no = e.emp_noWHEREe.hire_date > '1999-01-01’and s.to_date='9999-01-01’AND s.salary < 40000GROUP BYe.first_name,e.last_name;

步骤 6 存在employees、salaries表的全表扫描，考虑创建索引。

create index idx_hire_date on employees(hire_date);

create index idx_sal on salaries(salary,to_date);

Create index idx_name on employees(first_name,last_name);

步骤 7 再次对比执行计划。

EXPLAIN SELECT e.first_name, e.last_name FROM employees e join salaries s on s.emp_no = e.emp_no WHERE e.hire_date > ‘1999-01-01’ and s.to_date=‘9999-01-01’ AND s.salary < 40000 GROUP BY e.first_name,e.last_name;

四）执行计划干预-分区剪枝

分区剪枝是指数据库在扫描分区表时，根据查询条件只扫描目标数据所在分区。通过分区剪枝可以大大减少从磁盘检索的数据量，提高查询性能。分区剪枝分为静态剪枝和动态剪枝。

分区剪枝是被动触发的，一般情况下，当查询SQL的条件带有分区列且能确定扫描分区时，数据库会进行分区剪枝。如果分区列条件使用静态谓词，数据库进行静态剪枝；如果分区列条件使用绑定变量，数据库进行动态剪枝。静态剪枝发生在SQL解析过程中，在SQL执行前，数据库就知道了需要扫描的分区。

本实验以TPCC业务表为例，通过分析分区表剪枝的基本行为、触发条件、剪枝前后行为对比，了解数据库是如何通过分区剪枝提升分区表的查询性能。

步骤 1 创建range分区表bmsql_stock。

DROP TABLE IF EXISTS bmsql_stock;

CREATE TABLE bmsql_stock (

s_w_id INTEGER NOT NULL,

s_i_id INTEGER NOT NULL,

s_quantity INTEGER,

s_ytd INTEGER,

s_order_cnt INTEGER,

s_remote_cnt INTEGER,

s_data VARCHAR(50),

s_dist_01 CHAR(24),

s_dist_02 CHAR(24),

s_dist_03 CHAR(24),

s_dist_04 CHAR(24),

s_dist_05 CHAR(24),

s_dist_06 CHAR(24),

s_dist_07 CHAR(24),

s_dist_08 CHAR(24),

s_dist_09 CHAR(24),

s_dist_10 CHAR(24)

)PARTITION BY RANGE (s_w_id)

(

PARTITION stock_p1 VALUES LESS THAN (35),

PARTITION stock_p2 VALUES LESS THAN (70),

PARTITION stock_p3 VALUES LESS THAN (MAXVALUE)

);

步骤 2 导入数据。

INSERT INTO bmsql_stock SELECT generate_series(1,100),generate_series(1,999);

步骤 3 查看语句计划，判断分区是否发生静态剪枝。

剪枝是被动触发的，可以通过查看SQL的执行计划来判断是否发生了剪枝。若只扫描了部分分区，则表明发生了剪枝行为；若扫描了全部分区，则表明未发生剪枝行为。

a. 查看如下SQL的执行计划。

EXPLAIN SELECT * FROM bmsql_stock WHERE s_w_id = ‘59’ AND s_i_id = ‘23’;

表bmsql_stock的分区列为s_w_id，由于查询SQL带有条件s_w_id = ‘59’，通过静态剪枝，可以只访问指定分区的数据，跳过其他分区的处理。Iterations值为1，表示数据库只访问了单个分区，Selected Partitions值为2，表示访问了第2个分区。从分区定义可以看出，s_w_id = '59’落在了第2个分区stock_p2，与静态剪枝结果对应。

b. 将条件改成s_w_id > ‘59’ AND s_i_id = ‘23’，查看执行计划。

EXPLAIN SELECT * FROM bmsql_stock WHERE s_w_id > ‘59’ AND s_i_id = ‘23’;

可以看到，数据库基于条件s_w_id > '59’进行静态剪枝，确定访问了分区2和分区3，对应分区名为stock_p2和stock_p3。

c. 将条件改成s_i_id = ‘23’，查看执行计划。

EXPLAIN SELECT * FROM bmsql_stock WHERE s_i_id = ‘23’;

由于分区键s_w_id上不带有任何条件，数据库不会进行分区剪枝，会访问全部的3个分区。

步骤 4 验证静态剪枝的触发条件

分区列使用静态谓词，若符合剪枝条件，可以触发静态剪枝。对于RANGE分区，符合剪枝条件的静态谓词包括范围表达式（>、>=、=、<=、<）、IN查询，以及由此组合的布尔表达式（AND、OR）；对于LIST分区和HASH分区，范围表达式仅支持等号查询，其他场景与RANGE分区一致。需要注意的是，当分区列发生类型转换时，无法触发静态剪枝。

EXPLAIN SELECT * FROM bmsql_stock WHERE s_w_id = 59.1;

由于分区列需要转换为numeric类型与59.1比较，此时无法触发静态剪枝。

步骤 5 通过语句计划，判断一级分区是否发生动态剪枝

与静态剪枝类似，当SQL符合动态剪枝条件时，数据库会自动进行动态剪枝。可以通过查看SQL的执行计划来判断是否发生了动态剪枝。需要注意的是，由于动态剪枝在SQL执行阶段进行，打印生成的计划中，是无法看到具体剪枝分区的。

a. 查看如下PBE的执行计划。

DEALLOCATE p1;PREPARE p1 AS SELECT * FROM bmsql_stock WHERE s_w_id = $1 AND s_i_id = $2;EXPLAIN EXECUTE p1(59, 23);

查询SQL带有条件s_w_id = $1，在SQL解析阶段，数据库可以根据这个条件确定只需要扫描部分分区（可能是1个，也可能是0个），但无法确定具体扫描的分区数和分区编号，所以会进行动态剪枝。可以看到，Iterations和Selected Partitions都标记为PART，表示只扫描了部分分区。

b. 将条件改成s_w_id > $1 AND s_i_id = $2，查看执行计划。

DEALLOCATE p1; --清理缓存的PEB

DEALLOCATE p1;PREPARE p1 AS SELECT * FROM bmsql_stock WHERE s_w_id < $1 AND s_i_id = $2;EXPLAIN EXECUTE p1(59, 23);

可以看到分区列条件s_w_id > $1和s_w_id = $1剪枝结果是类似的，数据库只能确定扫描部分分区，具体扫描哪些分区，只有在执行阶段才能确定。比如$1绑参为80，则只会扫描分区stock_p3；$1绑参为20，则扫描所有3个分区。

c. 将条件改成s_i_id = $1，查看执行计划。

DEALLOCATE p1; --清理缓存的PEB

DEALLOCATE p1;PREPARE p1 AS SELECT * FROM bmsql_stock WHERE s_i_id = $1;EXPLAIN EXECUTE p1(23);

步骤 6 验证动态剪枝的触发条件。

分区列条件有绑定变量时，若符合剪枝条件，可以触发动态剪枝。动态剪枝的触发条件与静态剪枝类似，同样包括范围表达式（>、>=、=、<=、<）、IN查询，以及由此组合的布尔表达式（AND、OR）。需要注意的是，一条SQL的查询计划中Iterations和Selected Partitions标记为PART并不代表一定进行了动态剪枝，只是数据库认为这条SQL”可能”进行动态剪枝，是否真正进行剪枝只能在绑参后才能确定。

分区列发生类型转换，仍然可以触发动态剪枝。

DEALLOCATE p1; --清理缓存的PEB

DEALLOCATE p1;PREPARE p1 AS SELECT * FROM bmsql_stock WHERE s_w_id = $1;EXPLAIN ANALYZE EXECUTE p1(59.1);

当分区列的条件有绑定变量时，在SQL解析过程中，数据库无法确定需要扫描的分区。若符合剪枝条件，数据库可以通过动态剪枝，在执行阶段只扫描部分分区，提高查询性能。

步骤 7 总结。

典型TPCC业务场景都是通过PBE调用SQL的，通过调整表的分区方式、修改查询条件等，可以让尽可能多的TPCC业务查询触发动态剪枝，从而对大数据量查询进行性能优化。

五）执行计划干预-分区表改造

步骤 1 公司管理者想查看公司某时间段中，各部门的薪资状况。

该SQL需要将每个部门的所有人员的薪资进行求和，因此使用sum函数进行累加，并通过部门表和员工部门表进行关联，将每个部门的员工进行累加；使用salaries表中的薪资时间，进行时间段（如1990-1999年间）的查找。

SELECT

b.dept_name,

sum( a.salary ) AS de_sal FROM

salaries a,

departments b,

dept_emp e WHERE

a.from_date BETWEEN ‘1990-01-01’

AND ‘1999-01-01’

AND e.dept_no = b.dept_no

AND a.emp_no = e.emp_no GROUP BY

b.dept_name ORDER BY

de_sal;

步骤 2 查看执行计划。

EXPLAIN SELECT

b.dept_name,

sum( a.salary ) AS de_sal FROM

salaries a,

departments b,

dept_emp e WHERE

a.from_date BETWEEN ‘1990-01-01’

AND ‘1999-01-01’

AND e.dept_no = b.dept_no

AND a.emp_no = e.emp_no GROUP BY

b.dept_name ORDER BY

de_sal;

步骤 3 分析执行计划。

根据条件，扫描的结果还是不少。因为根据条件筛选出这么多记录，在同salaries表进行关联后，效率会慢。查看salaries表，数据量较大，并且其中有时间排序，再观察SQL，其中按照from_date作为条件进行过滤，因此可以考虑将salaries表中，按照from_date字段进行分区表改造，这样按照年份，可以将每年的数据落在一个分区。

CREATE TABLE salaries_new

( emp_no int NOT NULL,

salary int NOT NULL,

from_date date NOT NULL,

to_date date NOT NULL

)

partition by range(from_date)

(

partition p01 values less than (‘1985-01-01’),

partition p02 values less than (‘1986-01-01’),

partition p03 values less than (‘1987-01-01’),

partition p04 values less than (‘1988-01-01’),

partition p05 values less than (‘1989-01-01’),

partition p06 values less than (‘1990-01-01’),

partition p07 values less than (‘1991-01-01’),

partition p08 values less than (‘1992-01-01’),

partition p09 values less than (‘1993-01-01’),

partition p10 values less than (‘1994-01-01’),

partition p11 values less than (‘1995-01-01’),

partition p12 values less than (‘1996-01-01’),

partition p13 values less than (‘1997-01-01’),

partition p14 values less than (‘1998-01-01’),

partition p15 values less than (‘1999-01-01’),

partition p16 values less than (‘2000-01-01’),

partition p17 values less than (‘2001-01-01’),

partition p18 values less than (‘2001-02-01’),

partition p19 values less than (‘2001-03-01’),

partition p20 values less than (‘2001-04-01’),

partition p21 values less than (‘2001-05-01’),

partition p22 values less than (‘2001-06-01’),

partition p23 values less than (‘2001-07-01’),

partition p24 values less than (‘2001-08-01’),

partition p25 values less than (‘2001-09-01’),

partition p26 values less than (‘2001-10-01’),

partition p27 values less than (‘2001-11-01’),

partition p28 values less than (‘2001-12-01’),

partition p29 values less than (‘2002-01-01’),

partition p30 values less than (‘2002-02-01’),

partition p31 values less than (‘2002-03-01’),

partition p32 values less than (‘2002-04-01’),

partition p33 values less than (‘2002-05-01’),

partition p34 values less than (‘2002-06-01’),

partition p35 values less than (‘2002-07-01’),

partition p36 values less than (‘2002-08-01’),

partition p37 values less than (‘2002-09-01’),

partition p38 values less than (‘2002-10-01’),

partition p39 values less than (‘2002-11-01’),

partition p40 values less than (‘2002-12-01’),

partition pmax values less than (‘3000-01-01’)

);

Insert into salaries_new select * from salaries;

步骤 4 对比执行计划。

Explain SELECT b.dept_name, sum( a.salary ) AS de_sal FROM salaries_new a, departments b, dept_emp e WHERE a.from_date BETWEEN ‘1990-01-01’ AND ‘1999-01-01’ AND e.dept_no = b.dept_no AND a.emp_no = e.emp_no GROUP BY b.dept_name ORDER BY de_sal;

六）执行计划干预-统计信息

在数据库中，统计信息是优化器生成计划的源数据。精确的统计信息是生成最优计划的前提条件。如果没有收集统计信息或者统计信息陈旧，可能会造成执行计划严重劣化，从而导致性能问题。在GaussDB数据库中，可以通过ANALYZE命令或者AUTOANALYZE进程收集表中各列统计信息，从而给出相对准确的代价估算结果。

本实验通过分析收集统计信息前后SQL执行代价对比，了解数据库是如何利用统计信息提升查询的代价估算准确率的，同时也掌握利用统计信息进行SQL性能调优的方法。

步骤 1 在数据库中创建业务表part、supplier、lineitem、partsupp、orders和nation。

注意：lineitem表在上一个实验中已创建，可跳过。

CREATE TABLE NATION ( N_NATIONKEY INTEGER NOT NULL,

N_NAME CHAR(25) NOT NULL,

N_REGIONKEY INTEGER NOT NULL,

N_COMMENT VARCHAR(152));

CREATE TABLE PART ( P_PARTKEY INTEGER NOT NULL,

P_NAME VARCHAR(55) NOT NULL,

P_MFGR CHAR(25) NOT NULL,

P_BRAND CHAR(10) NOT NULL,

P_TYPE VARCHAR(25) NOT NULL,

P_SIZE INTEGER NOT NULL,

P_CONTAINER CHAR(10) NOT NULL,

P_RETAILPRICE DECIMAL(15,2) NOT NULL,

P_COMMENT VARCHAR(23) NOT NULL );

CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER NOT NULL,

S_NAME CHAR(25) NOT NULL,

S_ADDRESS VARCHAR(40) NOT NULL,

S_NATIONKEY INTEGER NOT NULL,

S_PHONE CHAR(15) NOT NULL,

S_ACCTBAL DECIMAL(15,2) NOT NULL,

S_COMMENT VARCHAR(101) NOT NULL);

CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER NOT NULL,

PS_SUPPKEY INTEGER NOT NULL,

PS_AVAILQTY INTEGER NOT NULL,

PS_SUPPLYCOST DECIMAL(15,2) NOT NULL,

PS_COMMENT VARCHAR(199) NOT NULL );

CREATE TABLE ORDERS ( O_ORDERKEY INTEGER NOT NULL,

O_CUSTKEY INTEGER 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 INTEGER NOT NULL,

O_COMMENT VARCHAR(79) NOT NULL);

CREATE TABLE LINEITEM ( L_ORDERKEY INTEGER NOT NULL,

L_PARTKEY INTEGER NOT NULL,

L_SUPPKEY INTEGER NOT NULL,

L_LINENUMBER INTEGER 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);

步骤 2 上传数据文件至/root/目录下，并导入数据。

\copy orders FROM ‘/root/orders.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

\copy supplier FROM ‘/root/supplier.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

\copy part FROM ‘/root/part.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

\copy partsupp FROM ‘/root/partsupp.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

\copy nation FROM ‘/root/nation.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

\copy lineitem FROM ‘/root/lineitem.csv’ WITH( FORMAT ‘csv’, DELIMITER ‘,’, ignore_extra_data ‘true’, ENCODING ‘utf8’);

步骤 3 查询语句执行计划。

EXPLAIN ANALYZE SELECT nation, o_year, sum(amount) AS sum_profitFROM

(

SELECT

n_name AS nation,

extract(year FROM o_orderdate) AS o_year,

l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount

FROM

part, supplier, lineitem, partsupp, orders, nation

WHERE

s_suppkey = l_suppkey

AND ps_suppkey = l_suppkey

AND ps_partkey = l_partkey

AND p_partkey = l_partkey

AND o_orderkey = l_orderkey

AND s_nationkey = n_nationkey

AND p_name LIKE ‘%green%’

) AS profitGROUP BY nation, o_yearORDER BY nation, o_year DESC;

![|553x248](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps224.jpg)

步骤 4 收集统计信息。

analyze lineitem;

analyze partsupp;

步骤 5 重新查询执行计划。

![|553x232](file:///C:\Users\sinochen\AppData\Local\Temp\ksohtml1936\wps225.jpg)

执行时间由1965ms减少至1576ms(具体时间因为不同的环境可能有所不同)。实例创建完成后，打开数据库对SELECT的审计，以支持检测实验者的历史执行记录。

------恭喜您完成本实验----