PawSQL对TPCH的优化评测(性能平均提升276%)

2024-08-20 15:42:49 浏览数 (2)

概述

TPCH (TPC Benchmark H) 是由TPC发布的一项针对决策支持系统的数据库基准测试。它由一组22个复杂的业务查询和若干数据维护查询组成, 模拟了一个批发供应商的数据仓库中的真实查询，用于衡量关系数据库管理系统在复杂的分析查询下的性能。

数据库厂商会使用TPCH测试自己以及竞争对手的产品，“评估”不同产品、不同服务器、存储配置、并行处理、资源调度对数据库性能的影响。

本文将在硬件配置、数据库参数保持不变的情况下，仅仅从查询重写和索引推荐的角度，评估PawSQL的优化建议对于TPCH测试集的性能提升作用。

测试基准

为了更好地模拟真实场景，我们把测试基准建立在比较高的基础之上。

  1. 我们使用TPC官方提供的工具进行测试数据的生成；
  2. 我们采用TPC官方提供的工具进行查询语句的生成，并且确保每个过滤条件能够产生大量的数据进行后续的聚集运算；
  3. 我们针对主键和外键都建立了相应的索引，确保表关联时的性能在保持在一定基准上。

环境准备

由于本次评测主要针对PawSQL的优化建议，所以我们降低硬件配置，使用MySQL数据库，同时采用默认参数配置。

  • 硬件环境: 2C4G
  • MySQL Community 8.0.17
  • 参数配置（官方默认）
  • PawSQL优化配置(默认配置)

数据量

索引情况

优化结果

  • 针对TPCH的22个查询语句，对其中的21个查询有优化建议；推荐了 8个重写优化，推荐了18个索引;
  • 优化建议有性能提升的查询有18个，占比86%；性能提升超过50%的查询有12个，占比60%；
  • 平均性能提升 276.40%，提升最大的Query性能提升近15倍。

单SQL优化情况

单SQL的优化情况可以通过链接获取，我们将以Query-19为例介绍PawSQL对其的自动优化。

Query编号

优化前执行时间（ms）

优化后执行时间（ms）

重写优化

推荐索引

执行计划变化

性能提升百分比

Query-9

385.408

24.312

0

1

Yes

1484.69%

Query-11

345.284

26.994

0

1

Yes

1178.71%

Query-19

27.894

3.26

1

2

Yes

753.64%

Query-4

31.451

6.472

0

2

Yes

385.51%

Query-17

297.865

75.647

0

1

Yes

293.73%

Query-14

25.082

9.354

1

2

Yes

168.07%

Query-18

14.676

5.766

1

3

Yes

154.43%

Query-1

183.749

76.813

1

2

Yes

139.21%

Query-6

131.095

64.19

0

1

Yes

104.23%

Query-20

76.326

39.922

0

2

Yes

91.19%

Query-5

894.588

468.449

0

2

Yes

90.97%

Query-13

154.03

98.645

1

3

Yes

56.15%

Query-15

54.335

42.194

0

1

Yes

28.79%

Query-8

161.492

138.446

0

1

Yes

16.65%

Query-10

77.355

70.005

0

2

Yes

10.51%

Query-12

480.841

440.795

1

2

Yes

9.09%

Query-7

506.672

477.108

0

1

Yes

6.2%

Query-22

369.674

364.289

0

1

Yes

1.48%

Query-3

445.12

445.958

0

0

No

-0.19%

Query-16

156.083

184.76

1

2

Yes

-15.51%

Query-2

669.726

855.372

2

2

Yes

-21.7%

Query-21

1085.156

3466.57

0

1

Yes

-68.7%

案例查询Q19的优化解析

TPCH测试集中Q19语句查询对一些空运或人工运输零件三个不同种类的所有订单的总折扣收入,是用数据挖掘工具产生格式化代码的一个例子。Q19语句的特点是带有分组、排序、聚集、IN子查询操作并存的三表连接操作。

Q19原SQL如下
代码语言：javascript复制
select sum(lineitem.l_extendedprice * (1 - lineitem.l_discount)) as revenue
from lineitem, part
where part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#52'
    and part.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
    and lineitem.l_quantity >= 4
    and lineitem.l_quantity <= 4   10
    and part.p_size between 1 and 5
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
    or part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#11'
    and part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
    and lineitem.l_quantity >= 18
    and lineitem.l_quantity <= 18   10
    and part.p_size between 1 and 10
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
    or part.p_partkey = lineitem.l_partkey
    and part.p_brand = 'Brand#51'
    and part.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
    and lineitem.l_quantity >= 29
    and lineitem.l_quantity <= 29   10
    and part.p_size between 1 and 15
    and lineitem.l_shipmode in ('AIR', 'AIR REG')
    and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
PawSQL对Q19的自动优化

PawSQL对其的自动化优化主要分为两部分，

  • 第一步：应用"OR条件重写为UNION"优化规则将其重写为UNION ALL

请注意重写为的是`UNION ALL`而不是`UNION`，PawSQL判断各个OR分支是否是互斥的，从而将其重写为`UNION ALL`，避免一次去重操作。

重写后的SQL为：

代码语言：javascript复制
select sum(PawDT_1698819584185.l_extendedprice * (1 - PawDT_1698819584185.l_discount)) as revenue
from (
  select /*QB_3*/ lineitem.l_discount, lineitem.l_extendedprice
  from lineitem, part
  where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#51'
  and part.p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
  and lineitem.l_quantity >= 29
  and lineitem.l_quantity <= 29   10
  and part.p_size between 1 and 15
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
     union all 
select /*QB_2*/ lineitem.l_discount, lineitem.l_extendedprice
from lineitem, part
where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#52'
  and part.p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
  and lineitem.l_quantity >= 4
  and lineitem.l_quantity <= 4   10
  and part.p_size between 1 and 5
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
     union all 
select /*QB_1*/ lineitem.l_discount, lineitem.l_extendedprice
from lineitem, part
where part.p_partkey = lineitem.l_partkey
  and part.p_brand = 'Brand#11'
  and part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
  and lineitem.l_quantity >= 18
  and lineitem.l_quantity <= 18   10
  and part.p_size between 1 and 10
  and lineitem.l_shipmode in ('AIR', 'AIR REG')
  and lineitem.l_shipinstruct = 'DELIVER IN PERSON'
) as PawDT_1698819584185
  • 第二步：基于改写后的SQL进行索引推荐
代码语言：javascript复制
CREATE INDEX PAWSQL_IDX2001589724 ON tpch.part(P_BRAND,P_CONTAINER,P_PARTKEY);
-- PART 在查询块 QB_1 中(DRIVE): MATCHING(part.p_brand = 'Brand#11'), RANGE_SCAN(part.p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK'))
CREATE INDEX PAWSQL_IDX1523603249 ON tpch.lineitem(L_PARTKEY,L_SHIPINSTRUCT,L_QUANTITY);
-- LINEITEM 在查询块 QB_1 中(DRIVEN): MATCHING(part.p_partkey = lineitem.l_partkey and lineitem.l_shipinstruct = 'DELIVER IN PERSON'), RANGE_SCAN(lineitem.l_quantity >= 18)
Q19优化的性能验证

经过性能验证，执行时间从27.9ms降低到2.26ms，性能提升了753%。

关于PawSQL

PawSQL专注数据库性能优化的自动化和智能化，支持MySQL，PostgreSQL，openGauss，Oracle等，提供的SQL优化产品包括

  • PawSQL Cloud，在线自动化SQL优化工具，支持SQL审查，智能查询重写、基于代价的索引推荐，适用于数据库管理员及数据应用开发人员，
  • PawSQL Advisor，IntelliJ 插件, 适用于数据应用开发人员，可以IDEA/DataGrip应用市场通过名称搜索“PawSQL Advisor”安装。
  • PawSQL Engine, 是PawSQL系列产品的后端优化引擎，可以以docker镜像的方式独立安装部署，并通过http/json的接口提供SQL优化服务。
索引 性能 优化 测试 配置

0 人点赞