Redshift關於SORTKEY排序鍵、DISTKEY分配鍵的總結

官方文件



個人理解：所謂排序鍵，就是類似傳統關係型資料庫的索引，而複合排序鍵就對應傳統關係型資料庫的複合索引，用複合索引時要按前導列的規則使用



SORTKEY排序鍵 and DISTKEY分配鍵
建立表時，您也可以將其一個或多個列定義為SORTKEY排序鍵。初次向空表中載入資料時，行以排序順序儲存在磁碟上。有關排序鍵列的資訊會被傳遞給查詢計劃程式，後者藉助該資訊構建能夠充分利用資料排序方式的計劃。排序可實現範圍限制謂詞的高效處理。Amazon Redshift 將列式資料儲存在 1 MB 的磁碟資料塊中。每個資料塊的最小值和最大值作為後設資料的一部分儲存。如果查詢使用範圍限制謂詞，則查詢處理器可在表掃描期間藉助該最小值和最大值快速跳過大量的資料塊。例如，假設某張表儲存著按日期排序的五年資料，且查詢指定了一個月的日期範圍。在這種情況下，您可以從掃描中消除多達 98% 的磁碟塊。如果資料未排序，則該查詢就不得不掃描更多（也可能是全部）的磁碟資料塊。
備註：分佈鍵和排序鍵是定義資料物理儲存方式的表屬性。分配和排序鍵使用不正確通常會導致DISK FULL磁碟空間已滿的錯誤
要定義排序型別，請在您的 CREATE TABLE 或 CREATE TABLE AS 語句中使用 INTERLEAVED 或 COMPOUND 關鍵字。預設為 COMPOUND。建議使用預設的 COMPOUND，除非您的表沒有使用 INSERT、UPDATE 或 DELETE 定期更新。
複合排序鍵:複合鍵由排序鍵定義中列出的所有列組成（順序即為其排列順序）。當查詢的篩選條件應用了使用排序鍵字首的條件（如篩選條件和聯接）時，複合排序鍵最為有用。當查詢只依賴於輔助排序列而不引用主列時，複合排序的效能優勢會下降。COMPOUND 是預設的排序型別。
交錯排序鍵:交錯排序為排序鍵中的每個列或列的子集賦予相同的權重。如果多個查詢使用不同的列作為篩選條件，則通常可以使用交錯排序方式來提高這些查詢的效能。當查詢對輔助排序列使用限制性謂詞時，與複合排序相比，交錯排序可顯著提高查詢的效能。 不要在具有單調遞增屬性的列（例如，身份列、日期或時間戳）上使用交錯排序鍵。交錯排序的優勢隨著受限制排序列數量的增加而增大。交錯排序對於大型表更為有效。在您向已包含資料的排序表中不斷新增行的過程中，效能會逐漸下降。複合排序和交錯排序都會出現這種效能下降，但交錯表受到的影響更大。VACUUM 可恢復排序順序，但對於交錯表，該操作可能需要花費更長的時間，因為合併新的交錯資料可能涉及到修改每一個資料塊。


建立表時，您可以指定四種分配模式之一：AUTO、EVEN、KEY 或 AUTO。如果未指定分配模式，Amazon Redshift 使用自動 (AUTO) 分配。
AUTO 分配:在自動 (AUTO) 分配模式下，Amazon Redshift 基於表資料大小分配最佳分配模式。
EVEN 分配:不管任意特定列中的值是什麼，領導節點都以輪詢方式向所有切片分配行。當表不參與聯接時，適合使用 EVEN 分配。
KEY 分配:根據一列中的值分配行。領導節點會將匹配的值放置到同一個節點切片上。如果基於聯接鍵分配一對錶，領導節點會根據聯接列中的值在切片上並置行。這樣一來，共同列的匹配值將實際儲存在一起。
ALL 分配:向每個節點分配整個表的副本。EVEN 分配或 KEY 分配只將表中的部分行放置在每個節點上，ALL 分配需要以叢集中節點數量為倍數的儲存空間，因此，它需要長得多的時間來載入、更新或插入資料到多個表中。ALL 分配只適用於移動相對緩慢的表；即更新不頻繁、不廣泛的表。由於在查詢過程中重新分配小表的成本很低，因此將小維度表定義為 DISTSTYLE ALL 沒有顯著好處。

Amazon Redshift 叢集是一組節點。叢集中的每個節點擁有自己的作業系統、專用記憶體和專用磁碟儲存。一個節點是領導節點，它負責將資料和查詢處理任務分配到計算節點。計算節點的磁碟儲存分成一系列切片。每個節點的切片數取決於叢集的節點大小。例如，每個 DS2.XL 計算節點有兩個切片，每個 DS2.8XL 計算節點有 16 個切片。所有節點均參與並行查詢的執行，處理儘可能跨切片均勻分佈的資料。
將資料載入到表中時，Amazon Redshift 會根據表的分配模式將表中的行分配到各個節點切片。作為查詢計劃的一部分，最佳化程式確定需要將資料塊放置在何處，以最好地執行查詢。然後，在查詢執行時，查詢最佳化程式根據執行任何聯接和聚合的需要將行重新分配到計算節點。重新分配可能涉及將特定的行傳送到節點以進行聯接，或將整個表廣播到所有節點。選擇表分配模式的目的是透過在執行查詢前將資料放在需要的位置來最大程度地減小重新分配步驟的影響。資料分配有兩個主要目標：
1、將工作負載均勻地分配到叢集中的節點上。不均勻的分配或資料分配偏斜會導致某些節點執行的工作比其他節點多，從而影響查詢效能。
2、儘量減少查詢執行時的資料移動。如果參與聯接或聚合的行已在節點上與其在其他表中聯接行並置，則最佳化程式在查詢執行時不必重新分配過多的資料。



SALES表，LISTID為分配鍵，LISTID 和 SELLERID為多列複合排序鍵。
create table sales(
salesid integer not null,
listid integer not null,
sellerid integer not null,
buyerid integer not null,
eventid integer not null encode mostly16,
dateid smallint not null,
qtysold smallint not null encode mostly8,
pricepaid decimal(8,2) encode delta32k,
commission decimal(8,2) encode delta32k,
saletime timestamp,
primary key(salesid))
distkey(listid)
COMPOUND sortkey(listid,sellerid)

查詢某個schema下的哪些表有排序鍵或分配鍵
select "schema","table",diststyle,sortkey1,sortkey_num,tbl_rows,skew_sortkey1,skew_rows from SVV_TABLE_INFO where schema='SS' order by "table"

查詢某表的哪些列是排序鍵或分配鍵，如果是 複合排序鍵則排序鍵中的所有列將具有一個 正值且該值指示列在排序鍵中的位置。如果是 交錯排序鍵則排序鍵中的所有列將具有 一個正值或負值且該值的絕對值指示列在排序鍵中的位置。

select schema_name,table_name,column_name,ordinal_position,data_type,distkey,sortkey from SVV_REDSHIFT_COLUMNS where schema_name='SS'
and table_name='TT1' and (distkey='true' or sortkey<>0)


建立一張表，指定了sortkey和distkey,怎麼判斷查詢語句使用到了sortkey?
在查詢語句前面加一個explain就可以得到redshift根據metadata生成的query plan。



曾經遇到的問題，下面sql語句很慢，怎麼讓它快一點？
update tf.all_china_stock_daily set non_linear_fct4 = b.cov_XY from #TF_alpha_1_final1 as b
where tf.all_china_stock_daily.tradedate = b.tradedate and tf.all_china_stock_daily.osid = b.osid;

分析和處理思路：all_china_stock_daily表的osid、tradedate欄位是sort key，而語句中where條件前面是tradedate後面是osid，順序和sort key相反，所以把語句調整，where條件後面先用osid再用tradedate，sql語句就變快了