SQL優化技術分享:從 321 秒到 0.2 秒的性能飛躍 —— 基于 PawSQL 的 TPCH 查詢優化實戰

在數據庫性能優化領域,TPC-H 測試集是一個經典的基準測試工具,常用于評估數據庫系統的查詢性能。本文將基于 TPCH 測試集中的第 20個查詢,結合 PawSQL 自動化優化工具,詳細分析如何通過 SQL 重寫和索引設計,將查詢性能從 321 秒提升到 0.2 秒,性能提升高達1541倍。

1. 背景介紹:一個典型的多表關聯分析查詢

TPC-H作為業界公認的數據庫性能測試基準,其第20號查詢(Q20)是一個極具挑戰性的復雜分析查詢。這個查詢的業務場景是:識別阿爾及利亞('ALGERIA')地區庫存充足的供應商,具體條件是這些供應商提供的綠色('green%')零件的庫存量(ps_availqty)超過該零件在過去一年內訂單總量的一半。

原始SQL語句如下:

select?s_name, s_address
from?supplier, nation
where?s_suppkey?in?(select?ps_suppkeyfrom?partsuppwhere?ps_partkey?in?(select?p_partkeyfrom?partwhere?p_name?like?'green%')and?ps_availqty?>?(select?0.5?*?sum(l_quantity)from?lineitemwhere?l_partkey?=?ps_partkeyand?l_suppkey?=?ps_suppkeyand?l_shipdate?>=?date?'1997-01-01'and?l_shipdate?<?date?'1997-01-01'?+?interval?'1'?YEAR)
)
and?s_nationkey?=?n_nationkey?
and?n_name?=?'ALGERIA'
order?by?s_name

在實際測試環境中,這個查詢的執行時間達到了驚人的321秒,完全無法滿足業務系統的要求。

2. 性能瓶頸分析:為什么這么慢?

?

從執行計劃可以看出主要性能問題:

  • 子查詢執行次數多:相關子查詢被執行了848次,每次耗時約378ms

  • 表掃描泛濫:對partsupp、part和lineitem表進行了全表掃描

  • 嵌套循環效率低:對lineitem表的訪問在嵌套循環最內層

  • 排序操作代價高:最后需要對結果集進行排序

3. PawSQL的優化方案:系統性解決方案

PawSQL作為專業的SQL優化工具,針對上述問題提供了一套完整的優化方案:

3.1?SQL重寫:從IN到EXISTS

將IN子查詢轉換為EXISTS形式,在有合適索引的情況下,這種改寫通常能讓優化器生成更高效的執行計劃:

where exists (select /*QB_1*/ partsupp.ps_suppkeyfrom partsupp, (...)where exists (select /*QB_4*/ part.p_partkeyfrom partwhere part.p_name like 'green%' and part.p_partkey = partsupp.ps_partkey)and partsupp.ps_availqty > SQ_1742975670803.null_and partsupp.ps_suppkey = supplier.s_suppkeyand SQ_1742975670803.l_partkey = partsupp.ps_partkeyand SQ_1742975670803.l_suppkey = partsupp.ps_suppkey)

?

3.2?SQL重寫:提前聚合計算

將lineitem的聚合計算從子查詢中提取出來,預先計算每個(零件,供應商)組合的總量:

select?0.5?*?sum(l_quantity)?as?null_, l_partkey,l_suppkey
from?lineitem
where?l_shipdate?>=?date?'1997-01-01'and?l_shipdate?<?date?'1997-01-01'?+?interval?'1'?YEAR
group?by?l_partkey, l_suppkey

3.3 智能索引設計

除了SQL重寫外,PawSQL還為優化后的SQL推薦了一系列索引,這些索引的創建為查詢性能的提升提供了有力支持。

-- 加速lineitem表的聚合計算
CREATE INDEX PAWSQL_IDX1406058528 ON lineitem(l_shipdate,l_quantity,l_partkey,l_suppkey);-- 優化nation表查詢
CREATE INDEX PAWSQL_IDX0006674720 ON nation(n_name,n_nationkey);-- 支持supplier表的排序和連接
CREATE INDEX PAWSQL_IDX1461825654 ON supplier(s_name,s_address,s_nationkey);
CREATE INDEX PAWSQL_IDX1670284145 ON supplier(s_nationkey,s_name,s_address);-- 加速part和partsupp表的連接
CREATE INDEX PAWSQL_IDX0450194419 ON part(p_partkey,p_name);
CREATE INDEX PAWSQL_IDX1262756509 ON partsupp(ps_partkey,ps_suppkey,ps_availqty);

3.4 謂詞下推

將過濾條件盡可能下推到數據訪問層,減少中間結果集:

  • nation.n_name = 'ALGERIA'

  • part.p_name like 'green%'

  • lineitem.l_shipdate范圍條件

3.5. 避免排序

通過創建包含s_name的索引,直接利用索引的有序性避免排序操作。

4. 優化效果:性能提升1541倍

?

優化前后的對比令人震撼:

指標優化前優化后提升幅度
執行時間321秒0.208秒154,124%
lineitem表掃描次數848次1次-
lineitem表掃描行數509,285,056行90,514行-
排序操作需要顯式排序利用索引避免排序-

執行計劃對比

  • 優化前:全表掃描→嵌套循環→重復計算

  • 優化后:索引查找→哈希連接→物化視圖

5. 經驗總結:SQL優化最佳實踐

通過這個案例,我們可以總結出以下SQL優化經驗:

  1. 避免關聯子查詢:特別是重復執行的關聯子查詢,考慮改寫為JOIN或提前物化

  2. 索引設計:盡量減少表掃描,同時兼顧避免回表操作

  3. 利用索引有序性:讓索引順序與ORDER BY一致可以避免排序操作

  4. 聚合計算預優化:對于重復的聚合計算,考慮提前計算并存儲

  5. 專業工具輔助:使用PawSQL等專業工具可以快速定位問題并提供優化方案

這個案例生動展示了:即使是極其復雜的分析查詢,通過系統性的優化方法,也能實現從分鐘級到亞秒級的性能飛躍

🌐 關于PawSQL

PawSQL專注于數據庫性能優化自動化和智能化,提供的解決方案覆蓋SQL開發、測試、運維的整個流程,廣泛支持包括MySQL/PostgreSQL/Oracle /openGauss/TDSQL/Oceanbase/達夢DM/金倉等各種主流商用和開源數據庫,為開發者和企業提供一站式的創新SQL優化解決方案。

?

?

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/bicheng/76821.shtml
繁體地址,請注明出處:http://hk.pswp.cn/bicheng/76821.shtml
英文地址,請注明出處:http://en.pswp.cn/bicheng/76821.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

SpringBoot3-web開發筆記(下)

內容協商 實現&#xff1a;一套系統適配多端數據返回 多端內容適配&#xff1a; 1. 默認規則 SpringBoot 多端內容適配。 基于請求頭內容協商&#xff1a;&#xff08;默認開啟&#xff09; 客戶端向服務端發送請求&#xff0c;攜帶HTTP標準的Accept請求頭。 Accept: applica…

Graylog 索引配置詳解與優化建議

Graylog 索引配置詳解與優化建議 &#x1f680; 前言一、索引集基礎信息 &#x1f4da;二、分片&#xff08;Shards&#xff09;與副本&#xff08;Replicas&#xff09;設置 ??1. 分片 (Shards)2. 副本 (Replicas) 三、 字段類型刷新間隔&#xff08;Field Type Refresh Int…

數據結構*包裝類泛型

包裝類 什么是包裝類 在講基本數據類型的時候&#xff0c;有提到過包裝類。 基本數據類型包裝類byteByteshortShortintIntegerlongLongfloatFloatdoubleDoublecharCharacterbooleanBoolean 我們知道&#xff1a;基本數據類型并不是對象&#xff0c;沒有對象所具有的方法和屬…

【JDBC-54.1】MySQL JDBC連接字符串常用參數詳解

在Java應用程序中連接MySQL數據庫時&#xff0c;JDBC連接字符串是建立連接的關鍵。一個配置得當的連接字符串不僅能確保連接成功&#xff0c;還能優化性能、增強安全性并處理各種連接場景。本文將深入探討MySQL JDBC連接字符串的常用參數及其最佳實踐。 1. 基本連接字符串格式…

[ctfshow web入門] web37

信息收集 題目有了變化&#xff0c;include$c if(isset($_GET[c])){$c $_GET[c];if(!preg_match("/flag/i", $c)){include($c);echo $flag;}}else{highlight_file(__FILE__); }解題 通過協議解題 參考[ctfshow web入門] web31 同樣是include&#xff0c;之前的方…

Linux 調試代碼工具:gdb

文章目錄 一、debug vs release&#xff1a;兩種程序形態的本質差異1. 什么是 debug 與 release&#xff1f;2. 核心差異對比 二、為什么需要 debug&#xff1a;從項目生命周期看調試價值1. 項目開發流程中的調試閉環&#xff08;流程圖示意&#xff09;2. Debug 的核心意義與目…

Python設計模式:命令模式

1. 什么是命令模式&#xff1f; 命令模式是一種行為設計模式&#xff0c;它將請求封裝為一個對象&#xff0c;從而使您能夠使用不同的請求、隊列或日志請求&#xff0c;以及支持可撤銷操作。 命令模式的核心思想是將請求的發送者與請求的接收者解耦&#xff0c;使得兩者之間的…

nlp面試重點

深度學習基本原理&#xff1a;梯度下降公式&#xff0c;將損失函數越來越小&#xff0c;最終預測值和實際值誤差比較小。 交叉熵&#xff1a;-p(x)logq(x)&#xff0c;p(x)是one-hot形式。如果不使用softmax計算交叉熵&#xff0c;是不行的。損失函數可能會非常大&#xff0c;…

Leetcode:二叉樹

94. 二叉樹的中序遍歷 class Solution {public List<Integer> inorderTraversal(TreeNode root) {TreeNode cur root;Stack<TreeNode> stack new Stack<>();List<Integer> list new ArrayList<>();while (!stack.isEmpty() || cur ! null) {…

SQL:Constraint(約束)

目錄 &#x1f3af; 什么是 Constraint&#xff1f; MySQL 中常見的約束類型&#xff1a; 1. PRIMARY KEY 2. FOREIGN KEY 3. UNIQUE 4. NOT NULL 5. DEFAULT 6. CHECK&#xff08;MySQL 8.0&#xff09; 7. AUTO_INCREMENT &#x1f3af; 什么是 Constraint&#xf…

數據庫數據恢復——sql server數據庫被加密怎么恢復數據?

SQL server數據庫數據故障&#xff1a; SQL server數據庫被加密&#xff0c;無法使用。 數據庫MDF、LDF、log日志文件名字被篡改。 數據庫備份被加密&#xff0c;文件名字被篡改。 SQL server數據庫數據恢復過程&#xff1a; 1、將所有數據庫做完整只讀備份。后續所有數據恢…

MySQL 用 limit 影響性能的優化方案

一.使用索引覆蓋掃描 如果我們只需要查詢部分字段&#xff0c;而不是所有字段&#xff0c;我們可以嘗試使用索引覆蓋掃描&#xff0c;也就是讓查詢所需的所有字段都在索引中&#xff0c;這樣就不需要再訪問數據頁&#xff0c;減少了隨機 I/O 操作。 例如&#xff0c;如果我們…

【算法筆記】并查集詳解

&#x1f680; 并查集&#xff08;Union-Find&#xff09;詳解&#xff1a;原理、實現與優化 并查集&#xff08;Union-Find&#xff09;是一種非常高效的數據結構&#xff0c;用于處理動態連通性問題&#xff0c;即判斷若干個元素是否屬于同一個集合&#xff0c;并支持集合合…

鴻蒙HarmonyOS埋點SDK,ClkLog適配鴻蒙埋點分析

ClkLog埋點分析系統&#xff0c;是一種全新的、開源的洞察方案&#xff0c;它能夠幫助您捕捉每一個關鍵數據點&#xff0c;確保您的決策基于最準確的用戶行為分析。技術人員可快速搭建私有的分析系統。 ClkLog鴻蒙埋點SDK通過手動埋點的方式實現HarmonyOS 原生應用的前端數據采…

JMeter的關聯

關聯&#xff1a;上一個請求的響應結果和下一個請求的數據有關系 xpath提取器 適用場景 HTML/XML文檔結構化數據&#xff1a; 適用于從HTML或XML文檔中提取結構化數據。例如&#xff0c;提取表格中的數據、列表中的項目等。示例&#xff1a;從HTML表格中提取所有行數據。 …

Spring Security 權限配置詳解

&#x1f31f;Spring Security 權限配置詳解&#xff1a;從基礎到進階 Spring Security 是一個功能強大、可高度自定義的安全框架&#xff0c;主要用于為基于 Spring 的應用程序提供身份驗證和授權功能。 本篇文章將帶你深入理解 Spring Security 的權限配置機制&#xff0c;掌…

pycharm中安裝Charm-Crypto

一、安裝依賴 1、安裝gcc、make、perl sudo apt-get install gcc sudo apt-get install make sudo apt-get install perl #檢查版本 gcc -v make -v perl -v 2、安裝依賴庫m4、flex、bison(如果前面安裝過pypbc的話,應該已經裝過這些包了) sudo apt-get update sudo apt…

【MCAL】AUTOSAR架構下基于SPI通信的驅動模塊詳解-以TJA1145為例

目錄 前言 正文 1.TJA1145驅動代碼中的SPI協議設計 1.1 對SPI Driver的依賴 1.2 對SPI配置的依賴 1.2.1 SpiExternalDevice 1.2.2 Channel_x 1.2.3 Job_x 1.2.4 Sequence N 1.2.5 Sequence M 1.2.6 Sequence L 1.2.7 小結 2.基于Vector驅動代碼的SPI配置 2.1 SPI引…

JavaScript:BOM編程

今天我要介紹的是JS中有關于BOM編程的知識點內容&#xff1a;BOM編程&#xff1b; 介紹&#xff1a;BOM全名&#xff08;Browser Object Model&#xff08;瀏覽器對象模型&#xff09;&#xff09;。 是瀏覽器提供的與瀏覽器窗口交互的接口&#xff0c;其核心對象是 window。與…

Memcached緩存系統:從部署到實戰應用指南

#作者&#xff1a;獵人 文章目錄 一、安裝libevent二、安裝配置memcached三、安裝Memcache的PHP擴展四、使用libmemcached的客戶端工具五、Nginx整合memcached:六、php將會話保存至memcached Memcached是一款開源、高性能、分布式內存對象緩存系統&#xff0c;可應用各種需要緩…