騰龍【大資料clickhouse】clickhouse 常用查詢最佳化策略詳解

騰龍娛樂公司註冊網址官網[k578.net]比如，當查詢列明顯多於篩選列時使用 Prewhere 可十倍提升查詢效能， Prewhere 會自動最佳化

執行過濾階段的資料讀取方式，降低 io 操作。某些場景下， prewhere 語句比 where 語句處理的資料量更少效能更高。

舉例來說，我們在使用mysql查詢一條資料，如下面的這條sql，在不考慮索引的情況下，正常的執行過程是，先掃描表的資料，然後過濾出name='xxx'的符合條件的記錄；

select u.* from user u where u.name = 'xxx'

在clickhouse中，預設情況下，使用where 過程也是類似，但使用了Prewhere 時，並不需要進行全表的掃描了，這樣就大大提升了查詢的效能；

預設情況下，prewhere自動開啟

看下面的查詢sql,使用explain檢視下

EXPLAIN SYNTAX

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查詢

關閉 where 自動轉 prewhere

開啟下面的配置，可以關閉prewhere功能

set optimize_move_to_prewhere=0; 

再次查詢，可以發現prewhere就關閉了；

預設情況，肯定不會關閉 where ，引擎會 自動最佳化為  prewhere ，但某些場景即使開啟優

化，也不會自動轉換成 prewhere ，需要手動指定 prewhere，如下：

使用常量表示式；

使用預設值為 alias 型別的欄位；

包含了 arrayJOIN，globalIn，globalNotIn 或者 indexHint 的查詢；

select 查詢的列欄位和 where 的謂詞相同；

使用了主鍵欄位；

比如下面的sql，在這種情況下就不會使用 prewhere ；

2.2  使用資料取樣替代全量查詢或統計

在某些情況下，比如資料量為TB級別的甚至更大的情況下，不需要精確對資料進行統計而是一個預估值時，可以透過取樣運算，這樣可極大提升資料分析的效能；

SAMPLE 0.1 # 代表取樣 10% 的資料 , 也可以是具體的條數

SELECT Title,count(*) AS PageViews 

FROM hits_v1

SAMPLE 0.1

WHERE CounterID =57

GROUP BY Title

ORDER BY PageViews DESC LIMIT 1000;

可以發現使用這種方式計算統計結果時效能上有明顯的提升；

2.3  列裁剪與分割槽裁剪

列裁剪（減少非必要的查詢欄位）

表的資料量太大時，應儘量避免使用 select * 操作，一般來說，查詢的效能會與查詢的欄位大小和數量成線性增長，欄位越少，消耗的 io 資源越少，效能就會越高；

比如下面這樣的sql，就是需要儘量避免的（小編測試在當前伺服器下執行介面直接崩掉了）；

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';

2.4  orderby 結合 where、limit

千萬量級以上的資料集進行 order by 查詢時，最好搭配 where 條件和 limit 語句一起使用，說到底，就是對查詢的結果集數量進行限制，資料量減少了怎麼都好說；

SELECT 

    UserID,Age

FROM 

    hits_v1 

WHERE 

    CounterID=57

ORDER BY Age DESC LIMIT 1000;

2.5  避免構建虛擬列

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

比如下面的sql，Income/Age 這個就是一個虛擬列，表中並不存在這個欄位，而是處於業務的需要構建出來的，不僅是clickhouse，甚至mysql中這種寫法也應該是儘量要避免的；

SELECT Income,Age,Income/Age as IncRate FROM datasets.hits_v1;

2.6  使用uniqCombined 替代 distinct

使用uniqCombined 替代 distinct ，效能可提升 10 倍以上，uniqCombined 底層採用類似 HyperLogLog 演算法實現，能接收 2%左右的資料誤差，可直接使用這種去重方式提升查詢效能；

Count(distinct )會使用 uniqExact精確去重，不建議在千萬級不同資料上執行 distinct 去重查詢，改為近似去重 uniqCombined；

比如查詢時可以使用下面這種方式

SELECT uniqCombined(rand()) from datasets.hits_v1;

2.7  其他單表查詢中需要注意的事項

（一）查詢熔斷

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

（二）關閉虛擬記憶體

實體記憶體和虛擬記憶體的資料交換，會導致查詢變慢，資源允許的情況下建議關閉虛擬記憶體；

（三）配置 join_use_nulls

為每一個賬戶新增 join_use_nulls 配置，左表中的一條記錄在右表中不存在，右表的相應欄位會返回該欄位相應資料型別的預設值，而不是標準 SQL 中的 Null 值；

（四）批次寫入時先排序

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

（五）關注 CPU

cpu 一般在 50%左右會出現查詢波動，達到 70%會出現大範圍的查詢超時，cpu 是最關

鍵的指標，因此運維過程中要非常關注。

三、關於多表關聯查詢相關最佳化策略

前置準備

依次執行下面的sql，從源表中複製出兩個新表，並從源表中匯入部分資料

#建立小表

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;

3.1  使用in代替join

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

這個和clickhouse的自身的關聯查詢資料載入機制有關，比如A join B時，需要先把B載入到記憶體中，然後從A中查詢的結果跟記憶體中B的資料進行匹配，如果B表的資料量非常大，很有可能造成記憶體被耗盡；

————————————————

版權宣告：本文為CSDN博主「逆風飛翔的小叔」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處連結及本宣告。

原文連結：https://blog.csdn.net/congge_study/article/details/128271948