引言
PawSQL对TPC-H基准的Query 9进行的自动性能优化后，Query9的性能提升了1195.14%。本文将根据PawSQL的优化过程，探讨其优化 SQL 性能的有效策略与实际效果。通过对比优化前后的查询结构、索引设计及执行计划，阐述如何实现显著的性能提升。
本文的案例可在线查看：https://pawsql.com/statement/1837384704875499522
TPC-H Query 9介绍：TPC-H 的第9个查询统计每个国家每年所有被订购零件的总利润。其查询特性如下：
- 包含分组、排序、聚集操作
- 存在多表连接和子查询
- 使用了全匹配的LIKE 操作符
1️⃣ 查询重写优化
原始查询：
代码语言：javascript复制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;优化后的查询：
代码语言：javascript复制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;重写优化要点：
- 消除子查询：将子查询的逻辑提升到主查询中，简化结构。
- 表别名重命名：提高查询的可读性与可维护性。
- 保持计算逻辑：确保查询的业务逻辑未发生变化。
