PawSQL优化案例分析：TPC-H Query 9性能提升1195.14%

引言

PawSQL对TPC-H基准的Query 9进行的自动性能优化后，Query9的性能提升了1195.14%。本文将根据PawSQL的优化过程，探讨其优化 SQL 性能的有效策略与实际效果。通过对比优化前后的查询结构、索引设计及执行计划，阐述如何实现显著的性能提升。

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

TPC-H Query 9介绍：TPC-H 的第9个查询统计每个国家每年所有被订购零件的总利润。其查询特性如下：

• 包含分组、排序、聚集操作
• 存在多表连接和子查询

• 使用了全匹配的LIKE 操作符

1️⃣ 查询重写优化

原始查询：‍

SELECT profit.nation, profit.o_year, SUM(profit.amount) AS sum_profit
FROM (
    SELECT nation.n_name AS nation, EXTRACT(YEAR FROM orders.o_orderdate) AS o_year,
           lineitem.l_extendedprice * (1 - lineitem.l_discount) - partsupp.ps_supplycost * lineitem.l_quantity AS amount
    FROM part, supplier, lineitem, partsupp, orders, nation
    WHERE supplier.s_suppkey = lineitem.l_suppkey
      AND partsupp.ps_suppkey = lineitem.l_suppkey
      AND partsupp.ps_partkey = lineitem.l_partkey
      AND part.p_partkey = lineitem.l_partkey
      AND orders.o_orderkey = lineitem.l_orderkey
      AND supplier.s_nationkey = nation.n_nationkey
      AND part.p_name LIKE '%dim%'
) AS profit
GROUP BY profit.nation, profit.o_year
ORDER BY profit.nation, profit.o_year DESC;

优化后的查询：‍

SELECT profit_nation.n_name AS nation, EXTRACT(YEAR FROM profit_orders.o_orderdate) AS o_year,
       SUM(profit_lineitem.l_extendedprice * (1 - profit_lineitem.l_discount) - profit_partsupp.ps_supplycost * profit_lineitem.l_quantity) AS sum_profit
FROM part AS profit_part, supplier AS profit_supplier,
     lineitem AS profit_lineitem, partsupp AS profit_partsupp,
     orders AS profit_orders, nation AS profit_nation
WHERE profit_supplier.s_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_partkey = profit_lineitem.l_partkey
  AND profit_part.p_partkey = profit_lineitem.l_partkey
  AND profit_orders.o_orderkey = profit_lineitem.l_orderkey
  AND profit_supplier.s_nationkey = profit_nation.n_nationkey
  AND profit_part.p_name LIKE '%dim%'
GROUP BY profit_nation.n_name, o_year
ORDER BY profit_nation.n_name, o_year DESC;

重写优化要点：

• 消除子查询：将子查询的逻辑提升到主查询中，简化结构。
• 表别名重命名：提高查询的可读性与可维护性。

• 保持计算逻辑：确保查询的业务逻辑未发生变化。

2️⃣ 🔍索引优化策略

PawSQL 提出的索引优化方案：

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);
CREATE INDEX PAWSQL_IDX0327029402 ON tpch.part(P_NAME);

索引优化分析：

• lineitem 表索引：针对连接条件和日期过滤的字段，减少 I/O 操作。
• supplier 表索引：覆盖连接和查询字段，避免回表，提高检索效率。

• part 表索引：通过 LIKE 操作符优化 p_name 的模糊匹配。

3️⃣ 执行计划对比

优化前的执行计划：

• 嵌套循环连接：广泛使用 Nested loop 连接，效率较低。
• 全表扫描：对 lineitem 表进行全表扫描，涉及 60,175 行数据。

• 依赖主键索引：主要依赖主键索引连接各表。

优化后的执行计划：

• 连接顺序优化：仍使用嵌套循环，但连接顺序优化。
• 索引扫描：利用新创建的 PAWSQL_IDX0327029402 索引，扫描 part 表时，处理行数从 60,175 减少至 114。

• lineitem 表精确查找：避免全表扫描，使用PAWSQL_IDX0485218972 进行精准匹配，进一步降低处理行数。

最终，处理的总行数从 60,175 降低到 3,513 行。

4️⃣ 性能提升分析

• 执行时间：从 260 毫秒减少到 20 毫秒。
• 性能提升：达到 1195.14% 的提升。

• 主要因素：索引优化显著改善了数据访问方式，减少了处理的行数。

🌟关于PawSQL

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

关注PawSQL公众号👇👇👇