25.數(shù)據(jù)查詢與寫入優(yōu)化
25.1.分布式子查詢優(yōu)化
25.1.1.分布式表的IN查詢示例1（普通IN子查詢、IN子查詢?yōu)楸镜乇?#xff09;
25.1.2.分布式表的IN查詢示例2（普通IN子查詢、IN子查詢?yōu)榉植际奖?#xff09;
25.1.3.分布式表的IN查詢示例3（GLOBAL IN子查詢、IN子查詢?yōu)榉植际奖?
25.1.4.使用GLOBAL IN/GLOBAL JOIN注意事項(xiàng)
25.2.外部聚合/排序優(yōu)化
25.3.基于JOIN引擎的優(yōu)化
25.4.SQL優(yōu)化案例
25.4.1.物化視圖提速
25.4.2.查詢優(yōu)化常用經(jīng)驗(yàn)法則
25.4.3.選擇和主鍵不一樣的排序鍵
25.4.4.數(shù)據(jù)入庫(kù)優(yōu)化

25.數(shù)據(jù)查詢與寫入優(yōu)化

25.1.分布式子查詢優(yōu)化

帶子查詢的IN和JOIN有兩個(gè)選項(xiàng)：普通的IN/JOIN、GLOBAL IN / GLOBAL JOIN。
1、 普通的IN/JOIN : 查詢發(fā)送到遠(yuǎn)程的server，在每個(gè)遠(yuǎn)程的server上運(yùn)行IN子查詢或JOIN子句。
2、 GLOBAL IN/GLOBAL JOIN : 首先為GLOBAL IN/GLOBAL JOIN運(yùn)行所有子查詢，將結(jié)果收集在臨時(shí)表中。然后將臨時(shí)表發(fā)送到每個(gè)遠(yuǎn)端server，并在其中使用此臨時(shí)數(shù)據(jù)運(yùn)行查詢。

25.1.1.分布式表的IN查詢示例1（普通IN子查詢、IN子查詢?yōu)楸镜乇?#xff09;

SELECT uniq(UserID) FROM distributed_tableWHERE UserID IN (SELECT UserID FROM local_table_in WHERE CounterID = 34);

上面的查詢語(yǔ)句將被發(fā)送到所有遠(yuǎn)程服務(wù)器上， 并在遠(yuǎn)程服務(wù)器使用本地表運(yùn)行：

SELECT uniq(UserID) FROM local_table WHERE UserID IN (SELECT UserID FROM local_table_in WHERE CounterID=34);

上面的語(yǔ)句需要保證該local_table_in表的所有USERID完全駐留在單個(gè)服務(wù)器上，否則，數(shù)據(jù)可能會(huì)不準(zhǔn)確。

25.1.2.分布式表的IN查詢示例2（普通IN子查詢、IN子查詢?yōu)榉植际奖?#xff09;

SELECT uniq(UserID) FROM distributed_tableWHERE UserID IN (SELECT UserID FROM distributed_table_in WHERE CounterID = 34);

1.上面的查詢語(yǔ)句將被發(fā)送到所有遠(yuǎn)程服務(wù)器上（假設(shè)100臺(tái)服務(wù)器），并被遠(yuǎn)程服務(wù)器使用本地表運(yùn)行：

SELECT uniq(UserID) FROM local_table WHERE UserID IN (SELECT UserID FROM distributed_table_in WHERE CounterID = 34);

2.由于子查詢是分布式表，每個(gè)子查詢分發(fā)至100臺(tái)服務(wù)器，運(yùn)行如下查詢：

SELECT UserID FROM local_table_in WHERE CounterID = 34;

執(zhí)行整個(gè)查詢需要100 * 100 = 10000個(gè)請(qǐng)求。這將導(dǎo)致嚴(yán)重性能問(wèn)題。

考慮使用GLOBAL IN。

25.1.3.分布式表的IN查詢示例3（GLOBAL IN子查詢、IN子查詢?yōu)榉植际奖?

SELECT uniq(UserID) FROM distributed_table WHERE UserID GLOBAL IN (SELECT UserID FROM distributed_table_in WHERE CounterID = 34);

1.在請(qǐng)求服務(wù)器上運(yùn)行子查詢，并將結(jié)果存儲(chǔ)在RAM的臨時(shí)表_data1中。

SELECT UserID FROM distributed_table_in WHERE CounterID = 34;

2.請(qǐng)求服務(wù)器將臨時(shí)表_data1發(fā)送到每個(gè)遠(yuǎn)程的服務(wù)器上，并在每個(gè)服務(wù)器執(zhí)行如下查詢：

SELECT uniq(UserID) FROM local_table WHERE UserID IN _data1;

避免了普通IN導(dǎo)致的連鎖響應(yīng)請(qǐng)求。

25.1.4.使用GLOBAL IN/GLOBAL JOIN注意事項(xiàng)

1.使用GLOBAL IN創(chuàng)建臨時(shí)表，數(shù)據(jù)沒有去重。若要減少網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量， 在子查詢中指定 DISTINCT。
2.使用GLOBAL時(shí)，應(yīng)盡量避免使用大數(shù)據(jù)集。臨時(shí)表將發(fā)送所有遠(yuǎn)程的主機(jī)，特別是在多機(jī)房容災(zāi)的集群架構(gòu)下，數(shù)據(jù)發(fā)送至遠(yuǎn)程數(shù)據(jù)中心性能低下。
3.GLOBAL可能會(huì)導(dǎo)致網(wǎng)絡(luò)超載。不會(huì)限制網(wǎng)絡(luò)帶寬。
4.使用GLOBAL時(shí)，盡量保證副本組駐留在同一個(gè)數(shù)據(jù)中心，保證快速的網(wǎng)絡(luò)數(shù)據(jù)傳輸。
5.為避免GLOBAL導(dǎo)致的數(shù)據(jù)傳輸，可提前將全量的數(shù)據(jù)發(fā)至每個(gè)節(jié)點(diǎn)，并使用普通JOIN/IN。

25.2.外部聚合/排序優(yōu)化

聚合查詢消耗的內(nèi)存超過(guò)max_memory_usage（默認(rèn)10G）設(shè)置的值，導(dǎo)致內(nèi)存溢出。

ClickHouse支持將臨時(shí)數(shù)據(jù)轉(zhuǎn)儲(chǔ)到磁盤以限制GROUP BY期間的內(nèi)存使用。

當(dāng)GROUP BY消耗超過(guò)max_bytes_before_external_group_by設(shè)置的閾值，ClickHouse將中間數(shù)據(jù)轉(zhuǎn)儲(chǔ)到磁盤。默認(rèn)值為0，表示禁用磁盤溢寫。

聚合有兩個(gè)階段，第一階段讀取數(shù)據(jù)并形成中間數(shù)據(jù)。第二階段合并中間數(shù)據(jù)。中間臨時(shí)數(shù)據(jù)轉(zhuǎn)儲(chǔ)到磁盤是發(fā)生在第一階段，如果沒有數(shù)據(jù)轉(zhuǎn)儲(chǔ)，則兩個(gè)階段使用的內(nèi)存相同。

在設(shè)置max_bytes_before_external_group_by值時(shí)，建議將其設(shè)置為max_memory_usage的一半。

當(dāng)使用分布式查詢，為了保證外部聚合時(shí)在遠(yuǎn)程的server端執(zhí)行，設(shè)置 distributed_aggregation_memory_efficient為1。

開啟外部排序設(shè)置max_bytes_before_external_sort,否則可能會(huì)內(nèi)存不足導(dǎo)致查詢異常終止。

當(dāng)啟用外部聚合，如果數(shù)據(jù)沒有轉(zhuǎn)儲(chǔ)到磁盤，此時(shí)，查詢的運(yùn)行速度和沒有外部聚合時(shí)一樣快。 如果中間數(shù)據(jù)轉(zhuǎn)儲(chǔ)到磁盤，則運(yùn)行時(shí)間將延長(zhǎng)數(shù)倍（大約3倍）。
而外部排序，數(shù)據(jù)轉(zhuǎn)儲(chǔ)到磁盤，性能將明顯下降。
三個(gè)參數(shù)分別設(shè)置為20GB、10GB和40GB。

1.在命令行界面設(shè)置：

set max_bytes_before_external_group_by=20000000000; set max_bytes_before_external_sort=10000000000; set max_memory_usage=40000000000;

2.JDBC設(shè)置，在URL添加參數(shù)：

url?max_bytes_before_external_group_by=20000000000&max_memory_usage=40000000000

25.3.基于JOIN引擎的優(yōu)化

如果多次使用相同的表（子查詢），每次都需要重新計(jì)算運(yùn)行子查詢。為此，可使用Join表引擎將數(shù)據(jù)緩存在 RAM中。
語(yǔ)法：

ENGINE=Join(join_strictness,join_type,k1[,k2,...]) 參數(shù)： join_strictness,取值ALL,ANY和ASOF join_type : JOIN的類型，LEFT、RIGHT、INNER等。 k1[,k2,...] : 關(guān)聯(lián)的字段

JOIN引擎使用示例：
創(chuàng)建表：

DROP TABLE id_val; CREATE TABLE id_val(`id` UInt32, `val` UInt32) ENGINE = TinyLog;

插入表數(shù)據(jù)：

INSERT INTO id_val VALUES (1,11)(2,12)(3,13);

創(chuàng)建右連接的JOIN引擎表：

DROP TABLE id_val_join; CREATE TABLE id_val_join(`id` UInt32, `val` UInt8) ENGINE = Join(ANY, LEFT, id);

插入數(shù)據(jù)：

INSERT INTO id_val_join VALUES (1,21)(1,22)(3,23);

表數(shù)據(jù)關(guān)聯(lián)：
SELECT * FROM id_val ANY LEFT JOIN id_val_join USING (id) SETTINGS join_use_nulls = 1;

25.4.SQL優(yōu)化案例

25.4.1.物化視圖提速

在用于插入數(shù)據(jù)的表上，創(chuàng)建多個(gè)物化視圖，每個(gè)物化視圖根據(jù)業(yè)務(wù)需求對(duì)數(shù)據(jù)做轉(zhuǎn)換。

1.物化視圖存儲(chǔ)通過(guò)由相應(yīng)的SELECT查詢轉(zhuǎn)換的數(shù)據(jù)。
2.在數(shù)據(jù)插入期間做查詢轉(zhuǎn)換，壓力分散。
3.僅在插入的單個(gè)數(shù)據(jù)塊中聚合，數(shù)據(jù)不會(huì)進(jìn)一步聚合。
4.數(shù)據(jù)表的引擎可為NULL。

25.4.2.查詢優(yōu)化常用經(jīng)驗(yàn)法則

1.小表放在JOIN的右邊
2.使用子查詢顯示設(shè)置數(shù)據(jù)處理的順序
3.使用IN替換JOIN操作
4.使用字典替換JOIN操作。
5.設(shè)置單射屬性。
6.使用Join引擎緩存表。
7.禁用分布式表的子查詢，使用GLOBAL IN/JOIN替換或者將子查詢的表提前分發(fā)至所有的server作為本地表。
8.使用PREWHERE
9.避免使用SELECT *查詢
10.盡量少用或不用多表關(guān)聯(lián)。

25.4.3.選擇和主鍵不一樣的排序鍵

默認(rèn)情況下，主鍵（由PRIMARY KEY指定）跟排序鍵（ORDER BY)相同，因此，大部分情況下，不需要專門指定一個(gè)PRIMARY KEY子句。

當(dāng)使用SummingMergeTree 和 AggregatingMergeTree時(shí)，可考慮選擇和主鍵不一樣的排序鍵。

CREATE TABLE t_merge_sum (id UInt32, name String, value UInt32 )ENGINE = SummingMergeTree() PRIMARY KEY id ORDER BY (id,name);

1.排序鍵可以修改，主鍵不能修改。
2.預(yù)聚合/增量聚合的key是由排序鍵指定的。業(yè)務(wù)邏輯后期可能會(huì)更改。
3.查詢條件無(wú)需包括排序鍵的所有字段。

添加排序鍵需要注意的：
1.主鍵字段是排序鍵字段的子集。
例如主鍵為（A,B）,則排序鍵以（A,B）開頭，排序鍵可為（A,B）或（A,B,C）等。

2.舊的排序鍵是新的排序鍵的前綴。
修改排序鍵只能增加排序字段，不能減少排序鍵字段，例如可修改排序鍵（A,B）為（A,B,C），但不能修改為（A,C）或（A,C,B）等。

3.排序鍵只能添加新加入表的列，表中已存在的數(shù)據(jù)的列不能添加到排序鍵中。

25.4.4.數(shù)據(jù)入庫(kù)優(yōu)化

分布式表本地表

簡(jiǎn)單	數(shù)據(jù)通過(guò)各節(jié)點(diǎn)的本地表導(dǎo)入
異步發(fā)送	壓力分散
無(wú)一致性校驗(yàn)	原子寫入
ZooKeeper壓力大

1.使用復(fù)制表，能夠保證數(shù)據(jù)原子寫入和去重。
2.限制單個(gè)復(fù)制表INSERT語(yǔ)句執(zhí)行的頻率（建議每秒不超過(guò)1個(gè)）
3.以相當(dāng)大的數(shù)據(jù)塊插入數(shù)據(jù)（默認(rèn)為1048576）。
4.將數(shù)據(jù)INSERT到ClickHouse之前，通過(guò)分區(qū)鍵對(duì)數(shù)據(jù)進(jìn)行分組。
5.增加入庫(kù)的并發(fā)，入庫(kù)性能線性提升。
6.自定義負(fù)載均衡策略，控制數(shù)據(jù)均衡。

總結(jié)

以上是生活随笔為你收集整理的19_clickhouse,数据查询与写入优化,分布式子查询优化,外部聚合/排序优化,基于JOIN引擎的优化,SQL优化案例,物化视图提速,查询优化常用经验法则,选择和主键不一样的排序键,数据入库优化的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。