DB2 SQL应用调优??解释工具db2expln的使用及实例分析

发布于:2021-06-22 11:27:17

1
、准备实验环境


我们创建了一个模拟tpch(数据库工业标准测试)测试的数据库,库中一共有3张数据表,分别是:


part??????产品部件表


supplier??供应商表


partsupp??产品供应商关联表


其中part表中含有200000条数据,partsupp表中含有800000条数据,supplier表中含有10000条数据


1)??我们为如上的3张表分别建立如下的索引:


create index part_idx1 on tpcd.part(p_partkey,p_size);

create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);

create index supp_idx1 on tpcd.supplier(s_suppkey);

?


?


2)??建立索引后,我们收集一下相关的统计信息,在db2cmd中执行如下的命令:


runstats on table tpcd.part with distribution and detailed indexes all;

runstats on table tpcd.partsupp with distribution and detailed indexes all;

runstats on table tpcd.supplier with distribution and detailed indexes all;

?


?


分别对PART,??PARTSUPP,??SUPPLIER运行以下命令,确保runstats已经成功执行:


db2 “select card,npages,stats_time from syscat.tables where tabname=’PART’”

?

CARD?????????????????NPAGES???????????????STATS_TIME

-------------------- -------------------- -----------------------------------------------------------

?200000?????????????????7616???????????????2008-08-21-17.20.22.828000

?


?


其中,CARD为该表的记录数,NPAGES为该表所占有的存储空间(页数),STATS_TIME为收集统计信息的时间。


2、发现问题

1)??我们有如下的一个SQL语句:


?


select

????count(*)

from

?????????tpcd.part,

?????????tpcd.partsupp,

?????????tpcd.supplier

where

?????????p_partkey = ps_partkey

?????????and s_suppkey = ps_suppkey

????and p_size = 30

?????????and ps_suppkey = 9988@

?


?


目前,该SQL的运行速度不理想,我们希望通过调优提高这个SQL语句的执行效率。


首先,我们为了记录这条查询语句执行的时间,运行如下SQL文件,记录一个时间:


文件名:lab.sql


values current timestamp@

?

select???????

??count(*)

from

??tpcd.part,???????

??tpcd.partsupp,????????

??tpcd.supplier

where???????

??p_partkey = ps_partkey?

??and s_suppkey = ps_suppkey???????

??and p_size = 30?????

??and ps_suppkey = 9988@

?

values current timestamp@

?


?


在db2cmd中运行:


db2 -td@ -vf??lab.sql

?


?


得到结果如下:


1

--------------------------

2009-01-04-15.09.25.281000

1?条记录已选择。

?

select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988

1

-----------

1

??1?条记录已选择。

?

values current timestamp

1

--------------------------

2009-01-04-15.09.33.359000

?

??1?条记录已选择。

?


?


通过前后时间对比,我们发现这个SQL运行了大约6秒钟(不同的机器性能可能有差异)。


3、分析问题

1)??为了了解这个SQL的执行过程,我们开始分析它的执行计划,在db2cmd中运行:


db2expln -d tpcd -f lab.sql -t -z @ -g > lab-before.exp

?


可以用文本编辑器打开lab-before.exp,下面,我们详细解读其中的执行计划:如图1所示



?


分析:执行计划是倒树状的结构,首先对part表、partsupp表和supplier表进行索引扫描,然后对part和partsupp表的索引扫描结果进行NLJOIN(嵌套循环连接),再将结果与supplier表的索引扫描结果进行HSJOIN(HASH连接),再进行排序,最后返回查询结果。


其中黄色标记部分,我们发现执行part表的索引扫描花费较大(1261.42个单位),且扫描结果(3810行)与我们的最终期望结果(1)差距较大,执行NLJOIN的花费(7443.88?1261.42?15.1451=6167.31个单位),因此我们认为这里part和partsupp表建立的索引是影响查询效率的因素。


4、解决问题

1)??在仔细分析的问题之后,我们尝试来解决这个问题,我们规划了一个新的索引方案,我们建立新的索引:


drop index part_idx1;

create index part_idx1 on tpcd.part(p_size,p_partkey);

drop index partsupp_idx1;

create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );

drop index supp_idx1;

create index supp_idx1 on tpcd.supplier(s_suppkey);

?


?


我们改变了part表和partsupp表的索引顺序


2)??建立索引后,我们再收集一下相关的统计信息,在db2cmd中执行如下的命令:


runstats on table tpcd.part with distribution and detailed indexes all;

runstats on table tpcd.partsupp with distribution and detailed indexes all;

runstats on table tpcd.supplier with distribution and detailed indexes all;

?


?


3)??下面,我们再执行一下原来的SQL,在db2cmd中执行:


db2 connect to tpcd

db2 ?td@ -vf??lab.sql

1

--------------------------

2009-01-04-16.02.45.078000

??1?条记录已选择。

?

select count(*) from tpcd.part, tpcd.partsupp, tpcd.supplier where p_partkey = ps_partkey and s_suppkey = ps_suppkey an p_size = 30 and ps_suppkey = 9988

1

-----------

1

??1?条记录已选择。

?

values current timestamp

1

--------------------------

2009-01-04-16.02.45.218000

?

??1?条记录已选择。

?


?


?


通过前后时间对比,我们发现这次,这个SQL运行时间在1秒之内?(不同的机器性能可能有差异)。


4)??为了进一步分析这个SQL的执行过程,我们再分析一下SQL的执行计划:


在db2cmd中运行:


db2expln -d tpcd -f lab.sql -t -z @ -g > lab-after.exp

?


?


?


可以用文本编辑器打开lab-after.exp,下面,我们详细解读这个执行计划,如图2所示



?


从执行的总花费(84.817)上我们可以明显的看到优化后的效果。


5、解决方案分析

我们来看实验Sql语句的谓词部分:


p_partkey = ps_partkey

???and s_suppkey = ps_suppkey

?????and p_size = 30

?????????and ps_suppkey = 9988@


?


DB2的sql优化器在执行查询sql语句,根据谓词进行表连接查询,并不依赖于where条件中谓词的顺序,而是根据所建索引来进行先后顺序的连接。


我们再来看优化前的索引:


create index part_idx1 on tpcd.part(p_partkey,p_size);

create index partsupp_idx1 on tpcd.partsupp(ps_partkey, ps_supplycost, ps_suppkey);

create index supp_idx1 on tpcd.supplier(s_suppkey);

?


1)我们目标是尽量增大第一次或前几次join的数据量缩小幅度,所以首先要进行小表的索引扫描和连接。而这里,从业务角度来说,把业务主键放到索引的第一个位置是有意义的,但是对于优化器来说,这毫无意义。优化器会根据索引优化器会首先选择谓词:p_partkey = ps_partkey?对part和partsupp进行NLJOIN,而这两个表是数据量相对大的表。


2)NLJOIN中外表只扫描一次,内表扫描N次,所以内表要尽量的小一些。而这里的内表partsupp有800000条数据。


我们期望优化器做如下处理:


1)优化器首先根据谓词p_size = 30和?ps_suppkey = 9988@进行索引扫描,缩小数据范围。


2)优化器根据谓词s_suppkey = ps_suppkey对supplier和partsupp进行表的NLJOIN。内表(partsupp)是数据量较小的一个表


所以,我们要将p_size和ps_suppkey的索引提前,建立如下索引


create index part_idx1 on tpcd.part(p_size,p_partkey);

create index partsupp_idx1 on tpcd.partsupp(ps_suppkey,ps_partkey, ps_supplycost );

create index supp_idx1 on tpcd.supplier(s_suppkey);

?


?


6、总结

使用db2expln解释工具,能够得到DB2 Sql优化器的详细Sql执行计划,通过其中的花费我们可以结合sql语句及表、索引、连接的结构进行分析,发现并定位问题,然后对sql进行改进,达到优化的目标。

相关推荐

最新更新

猜你喜欢