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

1️⃣ 引言

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

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

2️⃣ 原始查询分析

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️⃣ 🔍 索引优化策略

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 在处理复杂查询优化时展现了强大的能力，以下是关键结论：

1. 查询重写：简化了查询结构，帮助优化器生成更高效的执行计划。

2. 索引设计：精心设计的索引策略显著减少数据访问量和处理行数。

3. 执行计划优化：有效减少全表扫描，调整连接顺序，显著提升性能。

🌟关于PawSQL

关注PawSQL公众号👇👇👇