TPC-H测试基准及其SQL场景理解与特点

1024点线面

已于 2024-03-07 14:32:43 修改

阅读量1.3k

点赞数 18

文章标签： sql 测试工具大数据性能测试

于 2024-03-07 14:31:58 首次发布

本文链接：https://blog.csdn.net/yuand7/article/details/136531976

版权

TPC-H介绍与SQL理解

测试基准介绍

TPC-H是交易处理效能委员会（ Transaction ProcessingPerformance Council ）制定的基准程序之一，它由一套面向业务的临时查询和并发数据修改组成。

TPC- H 基准模拟了决策支持系统中的数据库操作，测试数据库系统复杂查询的响应时间，以每小时执行的查询数(TPC-H QphH@Size)作为度量指标。

TPC-H是根据真实的生产运行环境来建模，模拟了一套销售系统的数据仓库。其共包含8个基本关系，数据量可设定从1G~10T不等。其基准测试共包含了22个查询，主要评价指标各个查询的响应时间，即从提交查询到结果返回所需时间。其测试结果可综合反映系统处理查询时的能力。

测试数据量

数据量的大小对查询速度有直接的影响，TPC- H 中使用SF 描述数据量，1 SF 对应1 GB 单位，SF对应的数据量只是8个表的总数据量不包括索引等空间占用，准备数据时需预留更多空间。TPC-H标准规定，测试数据库的比例因子必须从下列固定值中选择：1,10,30,100,1000,3000,10000 (相当于1GB，10GB，30GB，100GB，1TB，3TB，10TB)。数据库的大小缺省定义为1（例如SF＝1；近似于1GB）。

各表数据量说明（实测统计），1TB数据 (也就是sf等于1000，不含索引)

表名	数据条目数
customer	15000w
lineitem	5999989709
nation	25
orders	150000w
part	20000w
partsupp	80000w
region	5
supplier	1000w

SQL查询含义解释和特点

TPC- H 测试围绕22 个SELECT 语句展开。每个SELECT严格定义，遵守SQL- 92语法，并且不允许用户修改。这些SELECT 语句的复杂程度超过大多数实际的OLTP 应用，一个SELECT 执行时间少则几十秒，多则达15 小时以上，22 个查询语句执行一遍需数个小时。

Q1：价格统计报告查询

业务场景

Q1语句是查询lineItems的一个定价总结报告。在单个表lineitem上查询某个时间段内，对已经付款的、已经运送的等各类商品进行统计，包括业务量的计费、发货、折扣、税、平均价格等信息。

特征

Q1语句的特点是：带有分组、排序、聚集操作并存的单表查询操作。这个查询会导致表上的数据有95%到97%行被读取到。

Q2: 最小代价供货商查询

Q2语句查询获得最小代价的供货商。得到给定的区域内，对于指定的零件（某一类型和大小的零件），哪个供应者能以最低的价格供应它，就可以选择哪个供应者来订货。
Q2语句的特点是：带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

Q3: 运送优先级查询

Q3语句查询得到收入在前10位的尚未运送的订单。在指定的日期之前还没有运送的订单中具有最大收入的订单的运送优先级（订单按照收入的降序排序）和潜在的收入（潜在的收入为l_extendedprice * (1-l_discount)的和）。

Q3语句的特点是：带有分组、排序、聚集操作并存的三表查询操作。

Q4: 订单优先级查询

Q4语句查询得到订单优先级统计值。计算给定的某三个月的订单的数量，在每个订单中至少有一行由顾客在它的提交日期之后收到。

Q4语句的特点是：带有分组、排序、聚集操作、子查询并存的单表查询操作。子查询是相关子查询。

Q5: 某地区供货商为公司带来的收入查询

Q5语句查询得到通过某个地区零件供货商而获得的收入（收入按sum(l_extendedprice * (1 -l_discount))计算）统计信息。可用于决定在给定的区域是否需要建立一个当地分配中心。

Q5语句的特点是：带有分组、排序、聚集操作、子查询并存的多表连接查询操作。

Q6: 预测收入变化查询

Q6语句查询得到某一年中通过变换折扣带来的增量收入。这是典型的“what-if”判断，用来寻找增加收入的途径。预测收入变化查询考虑了指定的一年中折扣在“DISCOUNT-0.01”和“DISCOUNT＋0.01”之间的已运送的所有订单，求解把l_quantity小于quantity的订单的折扣消除之后总收入增加的数量。

Q6语句的特点是：带有聚集操作的单表查询操作。查询语句使用了BETWEEN-AND操作符，有的数据库可以对BETWEEN-AND进行优化。

Q7: 货运盈利情况查询

Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。

Q7语句的特点是：带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。

Q8: 国家市场份额查询

Q8语句是查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。

Q8语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。

TPC-H标准定义了Q8语句等价的变形SQL，与上述查询语句格式上基本相同，主要是目标列使用了不同的表达方式，在此不再赘述。

Q9: 产品类型利润估量查询

Q9语句是查询每个国家每一年所有被定购的零件在一年中的总利润。

Q9语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。

Q10: 货运存在问题的查询

Q10语句是查询每个国家在某时刻起的三个月内货运存在问题的客户和造成的损失。

Q10语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前10行（通常依赖于应用程序实现）。

Q11: 库存价值查询

Q11语句是查询库存中某个国家供应的零件的价值。

Q11语句的特点是：带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。

Q12: 货运模式和订单优先级查询

Q12语句查询获得货运模式和订单优先级。可以帮助决策：选择便宜的货运模式是否会导致消费者更多的在合同日期之后收到货物，而对紧急优先命令产生负面影响。

Q12语句的特点是：带有分组、排序、聚集操作并存的两表连接查询操作。

Q13: 消费者订单数量查询

Q13语句查询获得消费者的订单数量，包括过去和现在都没有订单记录的消费者。

Q13语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

TPC-H标准定义了Q13语句等价的变形SQL，变形的SQL把子查询部分变为视图，然后基于视图做查询，这种做法的意义在于有些数据库不支持如上语法，但存在等价的其他语法，如MySQL就不支持。

Q14: 促销效果查询

Q14语句查询获得某一个月的收入中有多大的百分比是来自促销零件。用以监视促销带来的市场反应。

Q14语句的特点是：带有分组、排序、聚集、子查询、左外连接操作并存的查询操作。

Q15: 头等供货商查询

Q15语句查询获得某段时间内为总收入贡献最多的供货商（排名第一）的信息。可用以决定对哪些头等供货商给予奖励、给予更多订单、给予特别认证、给予鼓舞等激励。

Q15语句的特点是：带有分排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。

TPC-H标准定义了Q15语句等价的变形SQL，与原查询语句格式上不相同，原查询语句首先定义了视图，然后用表与视图连接；变形的SQL定了WITH语句，然后用WITH的对象与表进行连接。

Q16: 零件/供货商关系查询

Q16语句查询获得能够以指定的贡献条件供应零件的供货商数量。可用于决定在订单量大，任务紧急时，是否有充足的供货商。
Q16语句的特点是：带有分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作。

Q17: 小订单收入查询

Q17语句查询获得比平均供货量的百分之二十还低的小批量订单。对于指定品牌和指定包装类型的零件，决定在一个七年数据库的所有订单中这些订单零件的平均项目数量（过去的和未决的）。如果这些零件中少于平均数20％的订单不再被接纳，那平均一年会损失多少呢？所以此查询可用于计算出如果没有没有小量订单，平均年收入将损失多少（因为大量商品的货运，将降低管理费用）。
Q17语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。