SQL优化评测 - PawSQL针对TPCH的优化评测

2024年1月15日 · 阅读需 10 分钟

Optimize your SQL Queries by Clicks!

概述

TPCH (TPC Benchmark H) 是一项针对决策支持系统的数据库基准测试。它由TPC (Transaction Processing Performance Council) 于1994年首次发布。TPCH测试由一组22个复杂的业务查询和若干数据维护查询组成,用于衡量关系数据库管理系统在复杂的分析查询下的性能。这些查询模拟了一个批发供应商的数据仓库中的真实查询,涵盖订单分析、供应链管理、部分销售分析等方面。数据库厂商会使用TPCH测试自己产品,评估不同服务器、存储配置、并行处理、资源调度对数据库性能的影响。

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

测试基准

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

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

环境准备

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

硬件环境: 2C4G
MySQL 8.0.17
参数配置（官方默认）

数据量

表名	行数	引擎
customer	15000	INNODB
lineitem	600572	INNODB
nation	25	INNODB
orders	150000	INNODB
part	20000	INNODB
partsupp	80000	INNODB
region	5	INNODB
supplier	1000	INNODB

索引情况

优化结果

针对TPCH的22个查询语句，对其中的21个查询有优化建议；推荐了 8 个重写优化，推荐了 21 个索引;
优化建议有性能提升的查询有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如下

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

请注意，PawSQL可以判断各个OR分支是否时互斥的，互斥时将其重写为UNION ALL，避免一次去重操作，本案例既是如此。

重写后的SQL为：

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进行索引推荐

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优化服务。

联系我们

网址: https://www.pawsql.com

邮件：service@pawsql.com

Twitter: https://twitter.com/pawsql

扫描关注PawSQL公众号 PawSQL

概述​

测试基准​

环境准备​

数据量​

索引情况​

优化结果​

单SQL优化情况​

Q19优化解析​

Q19原SQL如下​

PawSQL对Q19的自动优化​

Q19性能验证​

关于PawSQL​

联系我们​

概述