跳到主要内容

1 篇博文 含有标签「TPC-H」

查看所有标签

SQL优化案例 - TPCH Q9 性能提升1195.14%

· 阅读需 5 分钟
PawSQL Team
PawSQL Team
Optimize your SQL Queries by Clicks!

引言​

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 行。

Image

4️⃣ 性能提升分析​

  • 执行时间：从 260 毫秒减少到 20 毫秒。
  • 性能提升：达到 1195.14% 的提升。
  • 主要因素：索引优化显著改善了数据访问方式，减少了处理的行数。

🌐 关于PawSQL​

PawSQL专注于数据库性能优化自动化和智能化，提供的解决方案覆盖SQL开发、测试、运维的整个流程，广泛支持包括KingbaseES 在内的多种主流商用和开源数据库，为开发者和企业提供一站式的创新SQL优化解决方案。有效解决了数据库SQL性能及质量问题，提升了数据库系统的稳定性、应用性能和基础设施利用率，为企业节省了大量的运维成本和时间投入。

PawSQL架构