武乐飞-2017021846-计算机科学与技术-复杂分析型查询性能调整及分析.docx

复杂分析性查询性能调整及分析 TPC-H（商业智能计算测试）是TPC的重要测试标准之一，主要用来模拟真实商业的应用环境。TPC Benchmark H(TPC-H)是一个决策支持的基准，它由一系列面向商务应用的查询和并行数据修改组成。基准里选择的查询和组成数据库的数据在商业上都具有广泛的代表性并且易于实现。本基准阐明了决策支持系统的三个方面： ·分析大量的数据； ·执行高复杂度的查询； ·回答关键的、经常需要回答的商业问题。 现利用TPC-H测试产生的数据集对postgresql-9.6数据库进行查询性能测试，选取三个由TPC-H测试提供的查询语句，进行查询性能的调整，产生极好和极差的效果。 一 测试环境 操作系统:CentOS6.7（虚拟机环境下） 处理器：双核单个 内存：2G 硬盘：120G tpch数据集大小：10G、20G 数据库：postgresql-9.6 查询语句：Q10、Q15、Q18 二 10G数据集测试 1 .Q10此查询标记那些可能对货运给他们的零件有问题的顾客 select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders,lineitem,nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate = date 1993-10-01 and o_orderdate date 1993-10-01 + interval 3 month and l_returnflag = R and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address,c_comment order by revenue desc limit 20 此查询根据在一个季度中那些有返回零件的顾客中对收入产生影响，造成损失的前20名顾客。这个查询只考虑在特定季度中定购的零件。查询结果列出顾客姓名，地址，国别，电话，帐册，意见信息和收入损失。按收入损失降序排列。收入损失定义为对所有具有资格的项目，按收入损失降序排列，返回查询结果的前20行。此查询涉及四个表的连接查询，返回结果中涉及一个聚集函数，并且含有分组操作和排序操作。 1）不经任何优化处理的执行 在不经过任何优化和性能处理时执行Q10三次，每次执行前执行一次缓存清理，三次执行的结果如下所示， 三次执行的平均时间为213146ms. cpu开销：10%20% 内存开销：89%-97% io开销：30%-45%左右 执行分析：对customer、nation、orders和lineitem进行了全表扫描，其中，对customer表的扫描共使用10753ms，对orders表的扫描共使用15148ms，对lineitem表的全表扫描共使用102574ms，对orders表和lineitem表的查询结果进行hash链接时共使用142889ms，对orders表和customer表的查询结果进行hash链接时共使用182819ms，另外查询中，共执行了2次sort操作，其中一次top-N heapsort排序，一次external merge排序，二者用时不大，根据上述分析，得出两次链接操作和一次对lineitem的全表扫描用时较多，查询性能优化可以从这里着手，查询计划如下图所示： 查询计划分析：上述查询过程花时总共213146ms， 2）根据上面的对查询计划的分析，准备采用以下的优化方案： 在lineitem中的l_returnflag和l_orderkey字段上建立索引； 在orders中的o_orderdate、o_custkey和o_orderkey字段上建立索引； 在customer表中的c_custkey字段上建立索引； 针对查询中存在外部查询问题对数据库的work_mem参数进行参数调优. 3）尝试为lineitem、orders和customer表增加前述相关索引 运行结果如下： 内存开销：97% cpu开销：平均8% io开销：平均25% 执行计划分析：为上述个字段添加索引后，执行效率竟然大幅下降，查看执行计划得知，添加索引后，对lineitem的全表扫描变为了对lineitem的Bitmap Heap Scan的扫描方式，消耗了215