TPCH分析详解及其调优

1、TPC-H简介

TPC-H可以说是世界上最为流行的OLAP workload的benchmark程序，是评估OLAP数据库最常见的工具。因此，分析型数据库及HTAP数据库都会将TPC-H的测试性能作为重要的评测指标。那么 OceanBase 做TPC-H 测试又有哪些小窍门呢？本文将为你分享。

2. TPC-H分析

2.1 TPC-H Schema

TPC-H是用来评估在线分析处理的基准程序，主要模拟了供应商和采购商之间的交易行为，其中包含针对8张表的22条分析型查询。

TPC-H模型是典型的雪花模型，一共有8张表，其中nation（国家）和region（区域）两张表的数据量是固定的，其余6张表的数据量跟比例因子SF（Scale Factor）相关，可以指定为1,100,1000等，分别代表1 GB、100GB、1000GB，根据指定的SF确定每张表的数据量。例如tpch1g的part表，其数据量=基础数据量200000*1=200000（行），再例如tpch100g的part表，其数据量=200000*100=20000000（行）。

• 8张表的说明：

part表示零件信息，主键为p_partkey，取值范围1~SF*200000，与partsupp关联。

supplier表示供应商信息，主键为s_suppkey，取值范围1~SF*10000，和partsupp、customer、nation关联。

partsupp表示供应商零件信息，主键为ps_partkey、ps_suppkey，与part、supplier、lineitem关联。

customer表示消费者信息，主键为c_custkey，取值范围1~SF*150000，与orders表关联。

orders表示订单信息，主键为o_orderkey，取值范围1~SF*1500000与lineitem表关联。

lineitem表示在售商品信息，主键为l_orderkey,l_linenumber，这是数据量最大的一张表。

nation表示国家信息，主键为n_nationkey，有25个国家，这是固定值。

region表示地区信息，主键为r_regionkey，固定有5个地区。

• 表间关联说明：

lineitem的l_orderkey跟orders表的o_orderkey是一一对应关系，订单上的商品都在lineitem表中，每个订单(l_orderkey)有1-7(l_linenumber)种商品，两张表数据量是1:(1-7)。

orders表的o_custkey信息都在customer表中，但不是一一对应关系，也就是说订单上的所有消费者信息都在customer表中，但不是所有消费者都购买了商品，大概2/3的消费者有订单。

part和partsupp中的partkey是一一对应关系，每个零件都有4个供应商，两张表数据量是1:4。

supplier和partsupp中的suppkey是一一对应关系，每个供应商供应80种零件，两张表数据量是1:80。

lineitem的partkey和suppkey都在partsupp中。

每个region有5个nation，每个nation中，supplier和customer的比例大概为1:15。

2.2 OceanBase TPC-H schema设计

• 建立分区表

OceanBase的TPC-H测试，在lineitem、orders、part、partsupp、supplier、customer都建了分区表，分区键就是上图中各表的主键，nation表和region表，因为都是小表，所以直接单分区表即可。

• 分区数规则

mysqlmode下建议采用key分区(partition_id = hash(value) % part_number)

为了充分利用CPU，分区数也要结合CPU数，建议值大概是cpu*server，比如1:1:1的32c的OceanBase集群，分区数是96

• 建立索引

为lineitem和orders两张表建立索引，可以提高检索效率。

• 建立tablegroup

多表关联查询的时候，可以利用表组创建一组具有相同分布特征的表，让有相同partition id的表分区在同一台observer上，这样具有相同分布特征的表在进行连接操作时，可以在本地进行，避免跨节点的数据请求。

在TPC-H的测试模型中，lineitem和orders两张表、partsupp和part两张表，是符合建立tablegroup条件的

• 并发查询parallel

OceanBase的查询是支持并发线程查询的，因此在TPC-H测试中，可以在22条SQL中添加parallel(para_num)的Hint来提高并发查询速度，para_num是并发线程数，建议并发数的数值与可用 CPU 总数相同。

2.3 TPC-H SQL

TPC-H标准共有22条SQL，全都是查询，主要考验数据库的如下数据分析能力：

• Aggregation
• Join
• Expression Calculation
• Subqueries
• Parallelism and Concurrency

Aggregation

以Q1为例，这条语句是带有分组、排序、聚集操作并存的单表查询操作，这个查询会导致表上的数据有95%～97%行被读取到，因此Aggregation的能力对于SQL的整体性能而言非常关键。

TPC-H的22条标准SQL中，主要用到了HASH GROUP BY和MERGE GROUP BY这两种分组聚合算法。

SELECT  /*+   TPCH_Q1 parallel(96) */
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    lineitem
where
    l_shipdate <= date'1998-12-01' - interval '90' day
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

Join

以Q9为例，这条语句是带有分组、排序、聚集、子查询操作并存的查询操作。虽然有子查询，但子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，而子查询自身是多表连接的查询，子查询中过滤条件很少，6张表做hash join，重点考察HASH JOIN的能力。

TPC-H的22条标准SQL中，用到了HASH JOIN、NESTED-LOOP JOIN、MERGE JOIN、MERGE SEMI JOIN 四种Join算法。

SELECT  /*+   TPCH_Q9 parallel(96) */ NATION, O_YEAR, SUM(AMOUNT) AS SUM_PROFIT
FROM (SELECT N_NAME AS NATION,
             TO_CHAR(O_ORDERDATE, 'YYYY') 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%%') PROFIT
GROUP BY NATION, O_YEAR ORDER BY NATION, O_YEAR DESC;

Expression Calculation

以Q19为例，这条语句是带有分组、排序、聚集、IN子查询操作并存的三表连接操作，含有大量的expression calculation，像in查询、between ...and... 等就考察数据库的expression能力，并行执行、semi-join、向量化等优化都能有效提升expression calculation能力。

SELECT /*+   TPCH_Q19 parallel(96) */ Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#12'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 1
         AND l_quantity <= 1 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#23'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 10
             AND l_quantity <= 10 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#34'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 20
             AND l_quantity <= 20 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' );

Subqueries

以Q20为例，这条语句是带有排序、聚集、IN子查询、普通子查询操作并存的两表连接操作，在条件中使用相关子查询的聚集结果作为外层的过滤条件，可以通过下推部分表+条件到子查询中的方式来完成提前的过滤。

TPC-H的标准SQL中，Subquery很多，有相关子查询和非相关子查询，AP类数据库对相关子查询的处理很多都是将其展开为不同形式的Join，比如in子查询经常会转为semi-join，还有将过滤条件下推到子查询等优化方法。

SELECT /*+   TPCH_Q20 parallel(96) */ s_name,
       s_address
FROM   supplier,
       nation
WHERE  s_suppkey IN (SELECT ps_suppkey
                     FROM   partsupp
                     WHERE  ps_partkey IN (SELECT p_partkey
                                           FROM   part
                                           WHERE  p_name LIKE 'green%')
                            AND ps_availqty > (SELECT 0.5 * SUM(l_quantity)
                                               FROM   lineitem
                                               WHERE  l_partkey = ps_partkey
                                                      AND l_suppkey = ps_suppkey
                                                      AND l_shipdate >= DATE
                                                          '1993-01-01'
                                                      AND l_shipdate < DATE
                                                          '1993-01-01' + interval '1' year
                                              ))
       AND s_nationkey = n_nationkey
       AND n_name = 'ALGERIA'
ORDER  BY s_name;

Parallelism and Concurrency

TPC-H的SQL都需要PX（并行执行）来提升性能，以Q5为例，带有分组、排序、聚集操作、子查询并存的多表连接查询操作，采用并行都能明显提升性能。

SELECT  /*+   TPCH_Q5 parallel(96)  */ N_NAME, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE
FROM CUSTOMER, ORDERS, LINEITEM, SUPPLIER, NATION, REGION
WHERE C_CUSTKEY = O_CUSTKEY AND
      L_ORDERKEY = O_ORDERKEY AND
      L_SUPPKEY = S_SUPPKEY AND
      C_NATIONKEY = S_NATIONKEY AND
      S_NATIONKEY = N_NATIONKEY AND
      N_REGIONKEY = R_REGIONKEY AND
      R_NAME = 'ASIA' AND
      O_ORDERDATE >= DATE'1994-01-01' AND
      O_ORDERDATE < DATE'1994-01-01' + interval '1' year
GROUP BY N_NAME
ORDER BY REVENUE DESC;

除了上述五类能力，还有table scan、数据处理（向量化）、filter（filter下压优化）、数据重分布（exchange）等通用能力，在每条SQL都有应用，如果你感兴趣，可以去了解下TPC-H 标准SQL的执行计划。

3. TPCH调优

3.1 调优参数

使用TPC-H进行性能测试，需要有很多工作配合才能获得较高性能，如建立索引、表数据的合理分布（分区打散）等，还有一些参数的调优。

3.2 TPC-H测试的注意事项

• 注意Query的parallel大小
• Load Data导数据

数据源和OBServer在一台物理机

OBServer需要有secure_file_priv的权限

Load Data需直连OBServer

• 导入数据后需要合并

————————————————————————————————————————————

社区版官网论坛

社区版项目网站提 Issue

欢迎持续关注 OceanBase 技术社区，我们将不断输出技术干货内容，与千万技术人共同成长！！！

搜索🔍钉钉群，或扫描下方二维码，还可进入 OceanBase 技术答疑群，有任何技术问题在里面都能找到答案哦～