1️⃣ 引言
PawSQL对TPC-H基准的Q2进行的自动性能优化后，性能提升了160250.60%。本文将深入探讨 PawSQL 对 TPC-H 基准测试中 Q2 查询的优化过程，重点分析查询重写、索引优化和执行计划改进的具体策略。通过详细的定量分析，评估这些优化策略对性能提升的实际效果。
本文的案例可在线查看：https://www.pawsql.com/statement/1837384704930025474
2️⃣ 原始查询分析
select supplier.s_name, supplier.s_addressfrom supplier, nationwhere supplier.s_suppkey in (select partsupp.ps_suppkeyfrom partsuppwhere partsupp.ps_partkey in (select part.p_partkeyfrom partwhere part.p_name like 'green%')and partsupp.ps_availqty > (select 0.5 * sum(lineitem.l_quantity)from lineitemwhere lineitem.l_partkey = partsupp.ps_partkeyand lineitem.l_suppkey = partsupp.ps_suppkeyand lineitem.l_shipdate >= date '1997-01-01'and lineitem.l_shipdate < date '1997-01-01' + interval '1' YEAR))and supplier.s_nationkey = nation.n_nationkeyand nation.n_name = 'ALGERIA'order by supplier.s_name
3️⃣ 查询重写优化
3.1 子查询转换
PawSQL 通过将 IN 子查询转换为更高效的 EXISTS 子查询，优化了SQL 结构：
select /*QB_1*/ supplier.s_name, supplier.s_addressfrom supplier, nationwhere exists (select /*QB_3*/ partsupp.ps_suppkeyfrom partsupp, partwhere part.p_name like 'green%'and part.p_partkey = partsupp.ps_partkeyand partsupp.ps_availqty > (select /*QB_2*/ 0.5 * sum(lineitem.l_quantity)from lineitemwhere lineitem.l_partkey = partsupp.ps_partkeyand lineitem.l_suppkey = partsupp.ps_suppkeyand 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_nationkeyand 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 在处理复杂查询优化时展现了强大的能力，以下是关键结论：
查询重写：简化了查询结构，帮助优化器生成更高效的执行计划。
索引设计：精心设计的索引策略显著减少数据访问量和处理行数。
执行计划优化：有效减少全表扫描，调整连接顺序，显著提升性能。
🌟关于PawSQL
关注PawSQL公众号👇👇👇
