引言
PawSQL对TPC-H基准的Query 9进行的自动性能优化后，Query9的性能提升了1195.14%。本文将根据PawSQL的优化过程，探讨其优化 SQL 性能的有效策略与实际效果。通过对比优化前后的查询结构、索引设计及执行计划，阐述如何实现显著的性能提升。
本文的案例可在线查看：
TPC-H Query 9介绍：TPC-H 的第9个查询统计每个国家每年所有被订购零件的总利润。其查询特性如下：
包含分组、排序、聚集操作 存在多表连接和子查询 使用了全匹配的LIKE 操作符
1️⃣ 查询重写优化
原始查询：
SELECT profit.nation, profit.o_year, SUM(profit.amount) AS sum_profitFROM (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 amountFROM part, supplier, lineitem, partsupp, orders, nationWHERE supplier.s_suppkey = lineitem.l_suppkeyAND partsupp.ps_suppkey = lineitem.l_suppkeyAND partsupp.ps_partkey = lineitem.l_partkeyAND part.p_partkey = lineitem.l_partkeyAND orders.o_orderkey = lineitem.l_orderkeyAND supplier.s_nationkey = nation.n_nationkeyAND part.p_name LIKE '%dim%') AS profitGROUP BY profit.nation, profit.o_yearORDER 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_profitFROM part AS profit_part, supplier AS profit_supplier,lineitem AS profit_lineitem, partsupp AS profit_partsupp,orders AS profit_orders, nation AS profit_nationWHERE profit_supplier.s_suppkey = profit_lineitem.l_suppkeyAND profit_partsupp.ps_suppkey = profit_lineitem.l_suppkeyAND profit_partsupp.ps_partkey = profit_lineitem.l_partkeyAND profit_part.p_partkey = profit_lineitem.l_partkeyAND profit_orders.o_orderkey = profit_lineitem.l_orderkeyAND profit_supplier.s_nationkey = profit_nation.n_nationkeyAND profit_part.p_name LIKE '%dim%'GROUP BY profit_nation.n_name, o_yearORDER 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公众号👇👇👇
