【ClickHouse】ClickHouse 進階（四）優化(4)查詢優化

本文是對《從零到大資料專家的學習指南(全面更新版)》的ClickHouse部分補充。

1 單表查詢

1.1 Prewhere 替代 where

　　Prewhere 和 where 語句的作用相同，用來過濾資料。不同之處在于 prewhere 隻支援MergeTree 族系列引擎的表，首先會讀取指定的列資料，來判斷資料過濾，等待資料過濾之後再讀取 select 聲明的列字段來補全其餘屬性。

當查詢列明顯多于篩選列時使用 Prewhere 可十倍提升查詢性能，Prewhere 會自動優化執行過濾階段的資料讀取方式，降低 io 操作。　　在某些場合下，prewhere 語句比 where 語句處理的資料量更少性能更高。 複制 #關閉 where 自動轉 prewhere(預設情況下， where 條件會自動優化成 prewhere)
set optimize_move_to_prewhere=0; 
# 使用 where
select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID, 
 CounterClass, 
 OS, 
 UserAgent, 
 URL, 
 Referer, 
 URLDomain, 
 RefererDomain, 
 Refresh, 
 IsRobot, 
 RefererCategories, 
 URLCategories, 
 URLRegions, 
 RefererRegions, 
 ResolutionWidth, 
 ResolutionHeight, 
 ResolutionDepth, 
 FlashMajor, 
 FlashMinor, 
 FlashMinor2
from datasets.hits_v1 where UserID='3198390223272470366';

# 使用 prewhere 關鍵字
select WatchID, 
 JavaEnable,
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID, 
 CounterClass, 
 OS, 
 UserAgent, 
 URL, 
 Referer, 
 URLDomain, 
 RefererDomain, 
 Refresh, 
 IsRobot, 
 RefererCategories, 
 URLCategories, 
 URLRegions, 
 RefererRegions, 
 ResolutionWidth, 
 ResolutionHeight, 
 ResolutionDepth, 
 FlashMajor, 
 FlashMinor, 
 FlashMinor2
from datasets.hits_v1 prewhere UserID='3198390223272470366';
 預設情況，我們肯定不會關閉 where 自動優化成 prewhere，但是某些場景即使開啟優化，也不會自動轉換成 prewhere，需要手動指定 prewhere：⚫使用常量表達式⚫使用預設值為 alias 類型的字段⚫包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查詢⚫select 查詢的列字段和 where 的謂詞相同⚫使用了主鍵字段1.2 資料采樣通過采樣運算可極大提升資料分析的性能      

SELECT Title,count() AS PageViews 
FROM hits_v1
SAMPLE 0.1 #代表采樣 10%的資料,也可以是具體的條數
WHERE CounterID =57
GROUP BY Title
ORDER BY PageViews DESC LIMIT 1000      

采樣修飾符隻有在 MergeTree engine 表中才有效，且在建立表時需要指定采樣政策。

1.3 列裁剪與分區裁剪

資料量太大時應避免使用 select * 操作，查詢的性能會與查詢的字段大小和數量成線性表換，字段越少，消耗的 io 資源越少，性能就會越高。

反例：
select * from datasets.hits_v1;
正例：
select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1;      

分區裁剪就是隻讀取需要的分區，在過濾條件中指定。

select WatchID, 
 JavaEnable, 
 Title, 
 GoodEvent, 
 EventTime, 
 EventDate, 
 CounterID, 
 ClientIP, 
 ClientIP6, 
 RegionID, 
 UserID
from datasets.hits_v1
where EventDate='2014-03-23';      

1.4 orderby 結合 where、limit

千萬以上資料集進行 order by 查詢時需要搭配 where 條件和 limit 語句一起使用。

#正例：
SELECT UserID,Age
FROM hits_v1 
WHERE CounterID=57
ORDER BY Age DESC LIMIT 1000
#反例：
SELECT UserID,Age
FROM hits_v1 
ORDER BY Age DESC      

1.5 避免建構虛拟列

如非必須，不要在結果集上建構虛拟列，虛拟列非常消耗資源浪費性能，可以考慮在前端進行處理，或者在表中構造實際字段進行額外存儲。

反例：
SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;
正例：拿到 Income 和 Age 後，考慮在前端進行處理，或者在表中構造實際字段進行額外存儲
SELECT Income,Age FROM datasets.hits_v1;      

1.6 uniqCombined 替代 distinct

　　性能可提升 10 倍以上，uniqCombined 底層采用類似 HyperLogLog 算法實作，能接收 2%左右的資料誤差，可直接使用這種去重方式提升查詢性能。Count(distinct )會使用 uniqExact精确去重。

　　不建議在千萬級不同資料上執行 distinct 去重查詢，改為近似去重 uniqCombined

反例：
select count(distinct rand()) from hits_v1;
正例：
SELECT uniqCombined(rand()) from datasets.hits_v1      

1.7 使用物化視圖

參考第 6 章。

1.8 其他注意事項

（1）查詢熔斷

　　為了避免因個别慢查詢引起的服務雪崩的問題，除了可以為單個查詢設定逾時以外，還可以配置周期熔斷，在一個查詢周期内，如果使用者頻繁進行慢查詢操作超出規定門檻值後将無法繼續進行查詢操作。

（2）關閉虛拟記憶體

　　實體記憶體和虛拟記憶體的資料交換，會導緻查詢變慢，資源允許的情況下關閉虛拟記憶體。

（3）配置 join_use_nulls

　　為每一個賬戶添加 join_use_nulls 配置，左表中的一條記錄在右表中不存在，右表的相應字段會傳回該字段相應資料類型的預設值，而不是标準 SQL 中的 Null 值。

（4）批量寫入時先排序

　　批量寫入資料時，必須控制每個批次的資料中涉及到的分區的數量，在寫入之前最好對需要導入的資料進行排序。無序的資料或者涉及的分區太多，會導緻 ClickHouse 無法及時對新導入的資料進行合并，進而影響查詢性能。

（5）關注 CPU

　　cpu 一般在 50%左右會出現查詢波動，達到 70%會出現大範圍的查詢逾時，cpu 是最關鍵的名額，要非常關注。

2 多表關聯

2.1 準備表和資料

複制 ​

#建立小表
CREATE TABLE visits_v2 
ENGINE = CollapsingMergeTree(Sign)
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate, intHash32(UserID), VisitID)
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from visits_v1 limit 10000;

#建立 join 結果表：避免控制台瘋狂列印資料
CREATE TABLE hits_v2 
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192
as select * from hits_v1 where 1=0;      

2.2 用 IN 代替 JOIN

當多表聯查時，查詢的資料僅從其中一張表出時，可考慮用 IN 操作而不是 JOIN

insert into hits_v2
select a.* from hits_v1 a where a. CounterID in (select CounterID from 
visits_v1); 

#反例：使用 join
insert into table hits_v2
select a.* from hits_v1 a left join visits_v1 b on a. CounterID=b. 
CounterID;      

2.3 大小表 JOIN

　　多表 join 時要滿足小表在右的原則，右表關聯時被加載到記憶體中與左表進行比較，ClickHouse 中無論是 Left join 、Right join 還是 Inner join 永遠都是拿着右表中的每一條記錄到左表中查找該記錄是否存在，是以右表必須是小表。

（1）小表在右

insert into table hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID;      

（2）大表在右

insert into table hits_v2
select a.* from visits_v2 b left join hits_v1 a on a. CounterID=b. 
CounterID;      

2.4 注意謂詞下推（版本差異）

ClickHouse 在 join 查詢時不會主動發起謂詞下推的操作，需要每個子查詢提前完成過濾操作，需要注意的是，是否執行謂詞下推，對性能影響差别很大（新版本中已經不存在此問題，但是需要注意謂詞的位置的不同依然有性能的差異）

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
having a.EventDate = '2014-03-17';

Explain syntax
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
having b.StartDate = '2014-03-17';

insert into hits_v2
select a.* from hits_v1 a left join visits_v2 b on a. CounterID=b. 
CounterID
where a.EventDate = '2014-03-17';

insert into hits_v2
select a.* from (
 select * from 
 hits_v1 
 where EventDate = '2014-03-17'
) a left join visits_v2 b on a. CounterID=b. CounterID;      

2.5 分布式表使用 GLOBAL

　　兩張分布式表上的 IN 和 JOIN 之前必須加上 GLOBAL 關鍵字，右表隻會在接收查詢請求的那個節點查詢一次，并将其分發到其他節點上。如果不加 GLOBAL 關鍵字的話，每個節點都會單獨發起一次對右表的查詢，而右表又是分布式表，就導緻右表一共會被查詢 N²次（N是該分布式表的分片數量），這就是查詢放大，會帶來很大開銷。

2.6 使用字典表

　　将一些需要關聯分析的業務建立成字典表進行 join 操作，前提是字典表不宜太大，因為字典表會常駐記憶體

2.7 提前過濾

　　通過增加邏輯過濾可以減少資料掃描，達到提高執行速度及降低記憶體消耗的目的