今天用clickhouse執行分布式查詢報錯：
SQL 錯誤 [241]: ClickHouse exception, code: 241, host: 192.168.29.124, port: 18123; Code: 241, e.displayText() = DB::Exception: Memory limit (total) exceeded: would use 15.44 GiB (attempt to
allocate chunk of 4294967296 bytes), maximum: 13.95 GiB (version 20.6.3.28 (official build))
查詢的語句如下：
SELECT DISTINCT plate_number FROM
(SELECT DISTINCT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
) AS t0
INNER JOIN
(SELECT DISTINCT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
) AS t1 ON t0.plate_number = t1.plate_number
INNER JOIN
(SELECT DISTINCT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
) AS t2 ON t1.plate_number = t2.plate_number
INNER JOIN
(SELECT DISTINCT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
) AS t3 ON t2.plate_number = t3.plate_number
以上語句的意思是要查詢同時出現在不同時段的車牌，每個時段資料量在10萬左右，在選擇3組join查詢時結果也1到2秒内查詢出結果，但是選擇4組時段join查詢就報記憶體超出限制
也就是說3組子查詢join的話，沒問題，4組甚至更多組就會報出記憶體限制問題。
由于業務上隻要查詢出車牌，是以上面每組子查詢都用了DISTINCT做去重，之是以用DISTINCT不用group by的原因是我不想把語句寫太長，太複雜。
看了下clickhouse的DISTINCT與group by功能上是差不多的(實際上用起來并非如此)，于是我在user.xml中設定如下：
7500000000
也就是設定group by超過7.5g就讓group by走磁盤查詢避免記憶體超出，設定完重新開機，上面這個查詢依然報記憶體超出限制。也就是說這個配置不會對DISTINCT有影響。
于是我隻好把上面的子查詢語句DISTINCT全部修改成group by:
SELECT DISTINCT plate_number FROM
(SELECT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
group by e.plate_number) AS t0
INNER JOIN
(SELECT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
group by e.plate_number) AS t1 ON t0.plate_number = t1.plate_number
INNER JOIN
(SELECT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
group by e.plate_number) AS t2 ON t1.plate_number = t2.plate_number
INNER JOIN
(SELECT e.plate_number FROM base_db.event_local AS e
WHERE (e.snapshot_time >= '2020-11-18 00:00:00') AND (e.snapshot_time <= '2020-11-24 23:59:59')
group by e.plate_number) AS t3 ON t2.plate_number = t3.plate_number
終于，沒有再報記憶體問題，而且這個查詢隻用了2秒。
總結一下：group by與DISTINCT雖說查詢結果一樣，但是在記憶體限制的計算中并不太一樣，max_bytes_before_external_group_by參數配置也隻會對group by有影響，另外我還發現
分布式查詢中除響應節點外其他節點被分發的語句，以上面報錯的語句為例(不是我修改後的)，從t0這個子查詢開始的DISTINCT關鍵字會被去掉，分發節點的查詢被篡改，可能是ck自動優化化語句吧。是以基于記憶體的限制問題來說clickhouse的複雜查詢能不用DISTINCT就不用，我這就掉坑裡了。
以上問題解決辦法就是将DISTINCT修改為group by，并且設定max_bytes_before_external_group_by參數就可以了
如果開發過程中有什麼問題，或者想了解一些clickhouse的原理的同學，推薦朱凱老師這本書
