TPCH 22條SQL語句分析

　　使用TPC-H進行性能測試，需要有很多工作配合才能獲得較高性能，如建立索引，表資料的合理分布（使用表空間和聚簇技術）等。

　　本文從查詢優化技術的角度，對TPC-H的22條查詢語句和主流資料庫執行每條語句對應的查詢執行計劃進行分析，目的在于了解各個主流資料庫的查詢優化技術，以TPC-H執行個體進一步掌握查詢優化技術，對比主流資料庫的實作情況對查詢優化技術融會貫通。

參考：關系資料庫性能測試規範TPC及工具hammerdb、benchmarksql一覽

Q1語句是查詢lineItems的一個定價總結報告。在單個表lineitem上查詢某個時間段内，對已經付款的、已經運送的等各類商品進行統計，包括業務量的計費、發貨、折扣、稅、平均價格等資訊。

Q1語句的特點是：帶有分組、排序、聚集操作并存的單表查詢操作。這個查詢會導緻表上的資料有95%到97%行被讀取到。

Q1的查詢語句如下：

Q2語句查詢獲得最小代價的供貨商。得到給定的區域内，對于指定的零件（某一類型和大小的零件），哪個供應者能以最低的價格供應它，就可以選擇哪個供應者來訂貨。

Q2語句的特點是：帶有排序、聚集操作、子查詢并存的多表查詢操作。查詢語句沒有從文法上限制傳回多少條元組，但是TPC-H标準規定，查詢結果隻傳回前100行（通常依賴于應用程式實作）。

Q2的查詢語句如下：

Q3語句查詢得到收入在前10位的尚未運送的訂單。在指定的日期之前還沒有運送的訂單中具有最大收入的訂單的運送優先級（訂單按照收入的降序排序）和潛在的收入（潛在的收入為l_extendedprice * (1-l_discount)的和）。

Q3語句的特點是：帶有分組、排序、聚集操作并存的三表查詢操作。查詢語句沒有從文法上限制傳回多少條元組，但是TPC-H标準規定，查詢結果隻傳回前10行（通常依賴于應用程式實作）。

Q3的查詢語句如下：

Q4語句查詢得到訂單優先級統計值。計算給定的某三個月的訂單的數量，在每個訂單中至少有一行由顧客在它的送出日期之後收到。

Q4語句的特點是：帶有分組、排序、聚集操作、子查詢并存的單表查詢操作。子查詢是相關子查詢。

Q4的查詢語句如下：

Q5語句查詢得到通過某個地區零件供貨商而獲得的收入（收入按sum(l_extendedprice * (1 -l_discount))計算）統計資訊。可用于決定在給定的區域是否需要建立一個當地配置設定中心。

Q5語句的特點是：帶有分組、排序、聚集操作、子查詢并存的多表連接配接查詢操作。

Q5的查詢語句如下：

Q6語句查詢得到某一年中通過變換折扣帶來的增量收入。這是典型的“what-if”判斷，用來尋找增加收入的途徑。預測收入變化查詢考慮了指定的一年中折扣在“DISCOUNT-0.01”和“DISCOUNT＋0.01”之間的已運送的所有訂單，求解把l_quantity小于quantity的訂單的折扣消除之後總收入增加的數量。

Q6語句的特點是：帶有聚集操作的單表查詢操作。查詢語句使用了BETWEEN-AND操作符，有的資料庫可以對BETWEEN-AND進行優化。

Q6的查詢語句如下：

Q7語句是查詢從供貨商國家與銷售商品的國家之間通過銷售獲利情況的查詢。此查詢确定在兩國之間貨運商品的量用以幫助重新談判貨運合同。

Q7語句的特點是：帶有分組、排序、聚集、子查詢操作并存的多表查詢操作。子查詢的父層查詢不存在其他查詢對象，是格式相對簡單的子查詢。

Q7的查詢語句如下：

Q8語句是查詢在過去的兩年中一個給定零件類型在某國某地區市場佔有率的變化情況。

Q8語句的特點是：帶有分組、排序、聚集、子查詢操作并存的查詢操作。子查詢的父層查詢不存在其他查詢對象，是格式相對簡單的子查詢，但子查詢自身是多表連接配接的查詢。

Q8的查詢語句如下：

TPC-H标準定義了Q8語句等價的變形SQL，與上述查詢語句格式上基本相同，主要是目标列使用了不同的表達方式，在此不再贅述。

Q9語句是查詢每個國家每一年所有被定購的零件在一年中的總利潤。

Q9語句的特點是：帶有分組、排序、聚集、子查詢操作并存的查詢操作。子查詢的父層查詢不存在其他查詢對象，是格式相對簡單的子查詢，但子查詢自身是多表連接配接的查詢。子查詢中使用了LIKE操作符，有的查詢優化器不支援對LIKE操作符進行優化。

Q9的查詢語句如下：

Q10語句是查詢每個國家在某時刻起的三個月内貨運存在問題的客戶和造成的損失。

Q10語句的特點是：帶有分組、排序、聚集操作并存的多表連接配接查詢操作。查詢語句沒有從文法上限制傳回多少條元組，但是TPC-H标準規定，查詢結果隻傳回前10行（通常依賴于應用程式實作）。

Q10的查詢語句如下：

Q11語句是查詢庫存中某個國家供應的零件的價值。

Q11語句的特點是：帶有分組、排序、聚集、子查詢操作并存的多表連接配接查詢操作。子查詢位于分組操作的HAVING條件中。

Q11的查詢語句如下：

Q12語句查詢獲得貨運模式和訂單優先級。可以幫助決策：選擇便宜的貨運模式是否會導緻消費者更多的在合同日期之後收到貨物，而對緊急優先指令産生負面影響。

Q12語句的特點是：帶有分組、排序、聚集操作并存的兩表連接配接查詢操作。

Q12的查詢語句如下：

TPC-H标準定義了Q12語句等價的變形SQL，與上述查詢語句格式上基本相同，主要是目标列使用了不同的表達方式，在此不再贅述。

1 SF，Scale Factor ，資料庫的比例因子。TPC-H标準規定，測試資料庫的比例因子必須從下列固定值中選擇：1，10,30,100,1000,3000,10000 (相當于1GB，10GB,30GB,100GB,1000GB,3000GB,10000GB)。資料庫的大小預設定義為1（例如：SF＝1；近似于1GB）。

Q13語句查詢獲得消費者的訂單數量，包括過去和現在都沒有訂單記錄的消費者。

Q13語句的特點是：帶有分組、排序、聚集、子查詢、左外連接配接操作并存的查詢操作。

Q13的查詢語句如下：

TPC-H标準定義了Q13語句等價的變形SQL，與上述查詢語句格式上不相同，上述語句使用子查詢作為查詢的對象，變形的SQL把子查詢部分變為視圖，然後基于視圖做查詢，這種做法的意義在于有些資料庫不支援如上文法，但存在等價的其他文法，如MySQL就不支援如上文法，需要使用如下等價形式。

Q14語句查詢獲得某一個月的收入中有多大的百分比是來自促銷零件。用以監視促銷帶來的市場反應。

Q14語句的特點是：帶有分組、排序、聚集、子查詢、左外連接配接操作并存的查詢操作。

Q14的查詢語句如下：

TPC-H标準定義了Q14語句等價的變形SQL，與上述查詢語句格式上基本相同，主要是目标列使用了不同的表達方式，在此不再贅述。

Q15語句查詢獲得某段時間内為總收入貢獻最多的供貨商（排名第一）的資訊。可用以決定對哪些頭等供貨商給予獎勵、給予更多訂單、給予特别認證、給予鼓舞等激勵。

Q15語句的特點是：帶有分排序、聚集、聚集子查詢操作并存的普通表與視圖的連接配接操作。

Q15的查詢語句如下：

TPC-H标準定義了Q15語句等價的變形SQL，與上述查詢語句格式上不相同，上述查詢語句首先定義了視圖，然後用表與視圖連接配接；變形的SQL定了WITH語句，然後用WITH的對象與表進行連接配接。變形SQL的語句如下：

Q16語句查詢獲得能夠以指定的貢獻條件供應零件的供貨商數量。可用于決定在訂單量大，任務緊急時，是否有充足的供貨商。

Q16語句的特點是：帶有分組、排序、聚集、去重、NOT IN子查詢操作并存的兩表連接配接操作。

Q16的查詢語句如下：

Q17語句查詢獲得比平均供貨量的百分之二十還低的小批量訂單。對于指定品牌和指定包裝類型的零件，決定在一個七年資料庫的所有訂單中這些訂單零件的平均項目數量（過去的和未決的）。如果這些零件中少于平均數20％的訂單不再被接納，那平均一年會損失多少呢？是以此查詢可用于計算出如果沒有沒有小量訂單，平均年收入将損失多少（因為大量商品的貨運，将降低管理費用）。

Q17語句的特點是：帶有聚集、聚集子查詢操作并存的兩表連接配接操作。

Q17的查詢語句如下：

Q18語句查詢獲得比指定供貨量大的供貨商資訊。可用于決定在訂單量大，任務緊急時，驗證否有充足的供貨商。

Q18語句的特點是：帶有分組、排序、聚集、IN子查詢操作并存的三表連接配接操作。查詢語句沒有從文法上限制傳回多少條元組，但是TPC-H标準規定，查詢結果隻傳回前100行（通常依賴于應用程式實作）。

Q18的查詢語句如下：

Q19語句查詢得到對一些空運或人工運輸零件三個不同種類的所有訂單的總折扣收入。零件的選擇考慮特定品牌、包裝和尺寸範圍。本查詢是用資料挖掘工具産生格式化代碼的一個例子。

Q19語句的特點是：帶有分組、排序、聚集、IN子查詢操作并存的三表連接配接操作。

Q19的查詢語句如下：

Q20語句查詢确定在某一年内，找出指定國家的能對某一零件商品提供更有競争力價格的供貨貨。所謂更有競争力的供貨商，是指那些零件有過剩的供貨商，超過供或商在某一年中貨運給定國的某一零件的50％則為過剩。

Q20語句的特點是：帶有排序、聚集、IN子查詢、普通子查詢操作并存的兩表連接配接操作。

Q20的查詢語句如下：

Q21語句查詢獲得不能及時交貨的供貨商。

Q21語句的特點是：帶有分組、排序、聚集、EXISTS子查詢、NOT EXISTS子查詢操作并存的四表連接配接操作。查詢語句沒有從文法上限制傳回多少條元組，但是TPC-H标準規定，查詢結果隻傳回前100行（通常依賴于應用程式實作）。

Q21的查詢語句如下：

Q22語句查詢獲得消費者可能購買的地理分布。本查詢計算在指定的國家，比平均水準更持肯定态度但還沒下七年訂單的消費者數量。能反應出普通消費者的的态度，即購買意向。

Q22語句的特點是：帶有分組、排序、聚集、EXISTS子查詢、NOT EXISTS子查詢操作并存的四表連接配接操作。

Q22的查詢語句如下：

花若盛開，蝶自飛來，你若精彩，幸福開懷！2020年12月11日-18日