TPC-H Q2 从秒级到毫秒的SQL优化案例分享：PawSQL优化策略及性能提升探秘

PawSQL

发布于 2024-10-10 19:30:28

29500

代码可运行

文章被收录于专栏：PawSQL，在线的自动化SQL优化工具！PawSQL，在线的自动化SQL优化工具！

运行总次数：0

代码可运行

1️⃣ 引言

PawSQL对TPC-H基准的Q2进行的自动性能优化后，性能提升了160250.60%。本文将深入探讨 PawSQL 对 TPC-H 基准测试中 Q2 查询的优化过程，重点分析查询重写、索引优化和执行计划改进的具体策略。通过详细的定量分析，评估这些优化策略对性能提升的实际效果。

本文的案例可在线查看：https://www.pawsql.com/statement/1837384704930025474

2️⃣ 原始查询分析

Q2 查询涉及 6 个表（supplier, nation, partsupp, part, lineitem），结构复杂，包含多层嵌套子查询和多个连接条件。查询的主要目标是找出特定国家（如 ALGERIA）中，供应某类零件（名称以 "green" 开头）且具有最低成本的供应商。

select supplier.s_name, supplier.s_address
from supplier, nation
where supplier.s_suppkey in (
    select partsupp.ps_suppkey
    from partsupp
    where partsupp.ps_partkey in (
    select part.p_partkey
    from part
    where part.p_name like 'green%')
    and partsupp.ps_availqty > (select 0.5 * sum(lineitem.l_quantity)
                        from lineitem
                        where lineitem.l_partkey = partsupp.ps_partkey
                           and lineitem.l_suppkey = partsupp.ps_suppkey
                           and lineitem.l_shipdate >= date '1997-01-01'
                           and lineitem.l_shipdate < date '1997-01-01' + interval '1' YEAR))
    and supplier.s_nationkey = nation.n_nationkey
    and nation.n_name = 'ALGERIA'
order by supplier.s_name

3️⃣ 查询重写优化

3.1 子查询转换

PawSQL 通过将 IN 子查询转换为更高效的 EXISTS 子查询，优化了SQL 结构：

select /*QB_1*/ supplier.s_name, supplier.s_address
from supplier, nation
where exists (
  select /*QB_3*/ partsupp.ps_suppkey
  from partsupp, part
  where part.p_name like 'green%'
  and part.p_partkey = partsupp.ps_partkey
  and partsupp.ps_availqty > (select /*QB_2*/ 0.5 * sum(lineitem.l_quantity)
                      from lineitem
                      where lineitem.l_partkey = partsupp.ps_partkey
                         and lineitem.l_suppkey = partsupp.ps_suppkey
                         and lineitem.l_shipdate >= date '1997-01-01'
                         and lineitem.l_shipdate < date '1997-01-01' + interval '1' YEAR)
  and partsupp.ps_suppkey = supplier.s_suppkey)
  and supplier.s_nationkey = nation.n_nationkey
  and nation.n_name = 'ALGERIA'
order by supplier.s_name

3.2 重写优化要点

消除子查询嵌套：简化查询结构，提升执行效率。
条件合并：将 part 和 partsupp 表的条件合并，减少中间结果集的大小。
保留相关子查询：优化子查询结构，为后续执行计划优化创造更多可能性。

4️⃣ 🔍 索引优化策略

PawSQL 建议为 Q2 查询创建以下索引：

CREATE INDEX PAWSQL_IDX0357178651 ON tpch.nation(N_NAME,N_NATIONKEY,N_REGIONKEY);
CREATE INDEX PAWSQL_IDX0327029402 ON tpch.part(P_NAME);
CREATE INDEX PAWSQL_IDX0485218972 ON tpch.lineitem(L_PARTKEY,L_SUPPKEY,L_SHIPDATE);
CREATE INDEX PAWSQL_IDX0214365528 ON tpch.supplier(S_NATIONKEY,S_SUPPKEY,S_NAME,S_ADDRESS);

索引优化分析：

nation 表索引：加速对 nation.n_name 的查找，支持快速定位国家。
part 表索引：优化对 part.p_name 的模糊匹配查询，提升过滤效率。
lineitem 表索引：覆盖连接条件和日期范围过滤，减少全表扫描。
supplier 表索引：支持与 nation 表的连接及最终结果的排序输出。

5️⃣ 执行计划对比分析

5.1 优化前的执行计划

嵌套循环连接：多次使用嵌套循环，导致性能瓶颈。
part 表全表扫描：处理 2000 行数据，效率低下。
lineitem 表全表扫描：每次处理 60,175 行数据，处理量巨大。

5.2 优化后的执行计划

索引查找与范围扫描：使用新创建的索引进行高效检索。
- part 表：通过 PAWSQL_IDX0327029402 索引，扫描行数从 2000 行降至 16 行。
- lineitem 表：借助 PAWSQL_IDX0485218972 索引，显著减少处理行数。
- nation 和 supplier 表：使用新索引进行精确匹配，提升连接效率。

5.3 关键改进

nation 表：由全表扫描改为索引查找，处理行数从 25 行减少到 1 行。
part 表：由全表扫描变为索引范围扫描，处理行数从 2000 行降至 16 行。
lineitem 表：通过索引查找，处理行数从 60,175 行降至约 1 行。

6️⃣ 性能提升量化分析

执行时间：从 1433.535 毫秒降至 0.894 毫秒。
性能提升：约 160250.60% 的提升。
主要贡献因素：通过索引优化，数据访问方式得到极大改善，处理行数大幅减少。

7️⃣ 额外优化建议

清理冗余索引：建议移除 lineitem 表上的多余索引，以优化 DML 操作性能。
常量过滤条件优化：对于常用过滤条件（如 nation.n_name = 'ALGERIA'），优先使用索引匹配。

8️⃣ 结论 🏆

PawSQL 在处理复杂查询优化时展现了强大的能力，以下是关键结论：

查询重写：简化了查询结构，帮助优化器生成更高效的执行计划。
索引设计：精心设计的索引策略显著减少数据访问量和处理行数。
执行计划优化：有效减少全表扫描，调整连接顺序，显著提升性能。

🌟关于PawSQL

PawSQL专注于数据库性能优化自动化和智能化，提供的解决方案覆盖SQL开发、测试、运维的整个流程，广泛支持MySQL、PostgreSQL、OpenGauss、Oracle等主流商用和开源数据库，以及openGauss，人大金仓、达梦等国产数据库，为开发者和企业提供一站式的创新SQL优化解决方案；有效解决了数据库SQL性能及质量问题，提升了数据库系统的稳定性、应用性能和基础设施利用率，为企业节省了大量的运维成本和时间投入。