从TPCH测试学习性能优化技巧

一、目标

TPCH是由TPC(Transaction Processing Performance Council)事务处理性能委员会公布的一套针对数据库决策支持能力的测试基准，通过模拟数据库中与业务相关的复杂查询考察数据库的综合处理能力，获取数据库操作的响应时间。

TPCH基准模型中定义了一个数据库模型，容量可以在1GB~10000GB的8个级别中进行选择。数据库模型包括CUSTOMER、LINEITEM、NATION、ORDERS、PART、PARTSUPP、REGION和SUPPLIER 共8张数据表，以及22条SQL查询语句，涉及内容广泛丰富，可以较完整地测试数据库的运算性能。

TPCH的SQL中不乏一些多层嵌套的复杂查询，执行性能较差。对于这些查询，如果能采用更合理的存储方案，设计低复杂度算法并配合并行等手段，将获得更优的性能。但遗憾的是，由于理论体系的限制，很多想法无法用SQL实现，而SQL程序员也因此不关注这些性能优化方法，经常只能忍受数据库的低速运算。

本系列文章将通过这8张表及22条SQL讨论数据运算时的性能优化技巧，仔细分析每一条语句，发现其运算和数据特征，设计更合理的算法加以实现。由于SQL难以实现这些算法和存储结构，我们将使用集算器组表来存储数据，并用SPL实现这些算法，同时与Oracle上的SQL对比性能。希望读者能够通过这些内容学会各种情况下适用的高性能计算技术。

二、环境

CPU：2颗Intel3014，主频1.7G，每个CPU内核数6个。硬盘(SSD)：1T 561MB/s(读) 523MB/s(写) 接口：SATA 6.0Gb/s内存：64G。

操作系统：Linux CentOS 7

三、数据准备

为了回避硬盘读取数据能力的差异，我们将Oracle数据表的数据和SPL组表文件均存储在同一块SSD固态硬盘上。

1. TPCH原始数据生成

利用TPCH官方工具生成8张表的原始数据，数据量总规模100G，各文件大小见后表。

2. 数据导入Oracle

数据导入Oracle比较简单，在Oracle数据库中创建一个数据库名为tpch，运行TPCH工具生成的dss.ddl和dss.ri文件来创建8张数据表以及表的主键、外键关系。然后运行ctl文件将原始数据分别导入各表中。例如导入lineitem表数据的ctl文件如下：

LOAD DATA INFILE '/home/tpch_2_17_0/tbls/lineitem.tbl'

INTO TABLE LINEITEM

(L_ORDERKEY terminated by '|',

L_PARTKEY terminated by '|',

L_SUPPKEY terminated by '|',

L_LINENUMBER terminated by '|',

L_QUANTITY terminated by '|',

L_EXTENDEDPRICE terminated by '|',

L_DISCOUNT terminated by '|',

L_TAX terminated by '|',

L_RETURNFLAG terminated by '|',

L_LINESTATUS terminated by '|',

L_SHIPDATE date "yyyy-mm-dd" terminated by '|',

L_COMMITDATE date "yyyy-mm-dd" terminated by '|',

L_RECEIPTDATE date "yyyy-mm-dd" terminated by '|',

L_SHIPINSTRUCT terminated by '|',

L_SHIPMODE terminated by '|',

L_COMMENT terminated by '|')

将此脚本存成lineitem.ctl文件，然后在linux命令行执行：

>sqlldr 数据库用户名/用户密码@tpch control="/home/sjr/oracle/ctl/lineitem.ctl"

即可，最后可在日志里查看导入数据执行时间。其它数据表的数据导入与此类似，不再赘述。

3. 生成SPL组表

SPL组表也由原始数据文件tbl来生成，每张表生成一个组表文件，用表的主键作为组表的维。对于数据量很小的 region 和 nation 表，使用集文件来存取数据可以获得更好的性能；对于数据量较大的表需要使用sortx函数对主键排序，关于sortx函数使用的jvm配置请看《JVM 参数调整对 sortx 的影响》http://c.raqsoft.com.cn/article/1562723138966

运行SPL脚本生成所需要的组表，下文介绍生成各组表的SPL脚本。

3.1 region/nation 集文件

以region表为例：

用文本文件创建游标取数并按主键排序后再写入集文件，排序时的第二个参数可根据内存大小来确定，省略不写也能工作。nation表与此类似，仅字段名不同。

3.2 customer/supplier/part/orders 组表

以customer表为例：

 

用文本文件创建游标取数并按主键排序后再写入组表，注意组表创建时要指名主键字段(前面加#)，排序时的第二个参数可根据内存大小来确定，省略不写也能工作。

其它各表类似，只要改文件名、排序的主键名及数据结构信息即可，不再赘述。

3.3 partsupp/lineitem 组表

partsupp和lineitem分别是part和orders的子表，对于主子表的存储，组表有特殊的要求，以lineitem表为例说明。lineitem表的主键是L_ORDERKEY和L_LINENUMBER，L_ORDERKEY与orders表中的O_ORDERKEY相对应，L_LINENUMBER是orders表中某一个订单的明细项编号，这样的表数据结构称为主子表。

注意这里A2在创建组表时和前面不同，后面多了一个参数，表明该组表将会按L_ORDREKEY分段，不会将L_ORDERKEY字段值相同的记录分到两段去，才能保证正确的并行计算结果，我们会在后面的例子中看到这种存储方案的效果。

类似地，partsupp可以看作part表的子表，则生成组表的代码是这样：

四、数据准备时间及文件大小

数据导入Oracle与产生组表的时间对比见下表，只比较数据量最大的lineitem、orders及partsupp三张表。

原始文件大小、SPL组表文件大小见下表。

Oracle表数据都是存在库文件里，没有单独的表文件。但是导入数据后的库文件比原始数据的总和要大，可以推断每个表所占的硬盘空间要比原始数据文件大。

TPCH原始数据导入Oracle库表的时间，要比导入到SPL组表的时间多5倍以上(即使加上了排序时间)；而且所占硬盘空间也比组表要大一倍多(组表有压缩，有序后更容易压缩)。所以，为了获得更好的查询性能而换用组表来存储数据，是非常值得的，既省时又省空间，数据准备工作变得更快速。

最后感谢每一个认真阅读我文章的人，礼尚往来总是要有的，虽然不是什么很值钱的东西，如果你用得到的话可以直接拿走：

​

这些资料，对于【软件测试】的朋友来说应该是最全面最完整的备战仓库，这个仓库也陪伴上万个测试工程师们走过最艰难的路程，希望也能帮助到你！