各位，今年 ClickHouse 最王炸的功能來啦，沒錯，就是期待已久的 Projection (投影) 功能。ClickHouse 現在的功能已經非常豐富強大了，但是社群用現實告訴我們，還可以進一步做的更好：）
不知道你有沒有碰到過這些情況:
- MergeTree 隻支援一種排序規則
建表的時候，Order By 同時決定了主鍵稀疏索引和資料的排序，假設 :
Order BY A,B,C
那麼通常過濾查詢 Where A 會很快，但是 Where C 會慢一些。
- 物化視圖不夠智能
針對固定的查詢主題，我們會基于一張底表建構許多物化視圖，以幫助更進一步提升查詢性能、提升QPS、降低資源開銷。
物化視圖雖然效果顯著，但是卻不夠智能。物化視圖本質上一張獨立的表，通過原表的觸發器，實時的向視圖表寫入資料。
既然物化視圖也是獨立的表，那麼自然就會存在與原表資料一緻性的問題。如果物化視圖很多，維護起來也是一個問題。
Projection 功能的出現，完美解決了上述的問題。Projection 的概念出自 《C-Store: A Column-oriented DBMS》這篇論文，作者是2015年圖靈獎獲得者、Vertica 之父，Mike Stonebraker。
Projection 意指一組列的組合，可以按照與原表不同的排序存儲，并且支援聚合函數的查詢。
來自快手的 Amos Bird(鄭天祺) 借鑒了這個思想，在 ClickHouse 中實作了 Projection 的功能，并貢獻到社群。
ClickHouse Projection 可以看做是一種更加智能的物化視圖，它有如下特點:
-
part-level 存儲
相比普通物化視圖是一張獨立的表，Projection 物化的資料就儲存在原表的分區目錄中，支援明細資料的普通Projection 和 預聚合Projection
-
無感使用，自動命中
可以對一張 MergeTree 建立多個 Projection ，當執行 Select 語句的時候，能根據查詢範圍，自動比對最優的 Projection 提供查詢加速。如果沒有命中 Projection , 就直接查詢底表。
- 資料同源、同生共死
因為物化的資料儲存在原表的分區，是以資料的更新、合并都是同源的，也就不會出現不一緻的情況了
這麼幹講可能還比較抽象，直接來看用例吧，這裡直接使用官方的測試資料集 hits_100m_obfuscated，這張表有 1億 資料:
SELECT count(*) FROM hits_100m_obfuscated Query id: 813ba930-d299-47d8-9ac3-6d7dbde075b1 ┌───count()─┐ │ 100000000 │ └───────────┘ 1 rows in set. Elapsed: 0.004 sec.
Order By 是:
ENGINE = MergeTree PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID), EventTime)
在沒有 Projection 的時候，查詢非主鍵 WatchID:
SELECT WatchID FROM hits_100m_obfuscated WHERE WatchID = 5814563137538961516 Query id: 20110b52-cac0-43b7-baf6-1931b94864a6 ┌─────────────WatchID─┐ │ 5814563137538961516 │ └─────────────────────┘ 1 rows in set. Elapsed: 0.262 sec. Processed 100.00 million rows, 800.00 MB (380.95 million rows/s., 3.05 GB/s.)
結果全表掃描了 800MB 共 1億行資料。
現在建立一個 Projection ，為特定的 Where 字段加速，按查詢的需求生成有别于主鍵的，另外一種排序規則:
ALTER TABLE hits_100m_obfuscated ADD PROJECTION p1 ( SELECT WatchID,Title ORDER BY WatchID )
注意，隻有在建立 PROJECTION 之後，再被寫入的資料，才會自動物化。
對于曆史資料，需要手動觸發物化，例如現在我們就需要執行：
alter table hits_100m_obfuscated MATERIALIZE PROJECTION p1
MATERIALIZE PROJECTION 是一個異步的 Mutation 操作，可以通過下面的語句查詢狀态：
SELECT table, mutation_id, command, is_done FROM system.mutations AS m WHERE is_done = 0 Query id: 7ddc855a-acb5-4ca9-8c48-ad4f5a7b234e ┌─table────────────────┬─mutation_id─────┬─command───────────────────┬─is_done─┐ │ hits_100m_obfuscated │ mutation_99.txt │ MATERIALIZE PROJECTION p1 │ 0 │ └──────────────────────┴─────────────────┴───────────────────────────┴─────────┘ 1 rows in set. Elapsed: 0.005 sec.
這個時候，如果我們去分區目錄，你會看到一個 tmp 臨時分區，正在物化 PROJECTION 的資料:
等到 p1 PROJECTION 生成好了之後，我們再去看分區目錄:
會看到在原有 MergeTree 的分區下，多了一個 p1.proj 的子目錄，進入子目錄，你會發現和 MergeTree 的存儲格式是一樣的：
cd /data/default/hits_100m_obfuscated/201307_1_96_4_107/p1.proj [[email protected] p1.proj]# ll total 5187772 -rw-r-----. 1 clickhouse clickhouse 278 Sep 8 23:43 checksums.txt -rw-r-----. 1 clickhouse clickhouse 69 Sep 8 23:43 columns.txt -rw-r-----. 1 clickhouse clickhouse 9 Sep 8 23:43 count.txt -rw-r-----. 1 clickhouse clickhouse 10 Sep 8 23:43 default_compression_codec.txt -rw-r-----. 1 clickhouse clickhouse 97672 Sep 8 23:43 primary.idx -rw-r-----. 1 clickhouse clickhouse 4508224709 Sep 8 23:43 Title.bin -rw-r-----. 1 clickhouse clickhouse 293016 Sep 8 23:43 Title.mrk2 -rw-r-----. 1 clickhouse clickhouse 803340103 Sep 8 23:43 WatchID.bin -rw-r-----. 1 clickhouse clickhouse 293016 Sep 8 23:43 WatchID.mrk2
當查詢命中某個 PROJECTION 的時候，就會直接用分區子目錄中的資料，來提供查詢。
再有了 p1 PROJECTION 之後，再次執行同樣的查詢，記得首先要設定參數開啟這項功能：
SET allow_experimental_projection_optimization = 1;
執行查詢:
SELECT WatchID FROM hits_100m_obfuscated WHERE WatchID = 5814563137538961516 Query id: 38d2aa48-45da-4487-ab80-1cd02ee08ce2 ┌─────────────WatchID─┐ │ 5814563137538961516 │ └─────────────────────┘ 1 rows in set. Elapsed: 0.006 sec. Processed 8.19 thousand rows, 65.54 KB (1.41 million rows/s., 11.27 MB/s.)
效果驚人，從 800MB 的 1億 行全表掃描，縮減到 65KB 的 8k 行掃描，時間也加快了 40 多倍。
除了明細資料的查詢，PROJECTION 也支援預聚合，在沒有優化的情況下，下面的查詢也會全表掃描:
SELECT UserID, SearchPhrase, count() FROM hits_100m_obfuscated GROUP BY UserID, SearchPhrase LIMIT 10 Query id: 42c941e0-c15a-4206-9c1b-7350a5a67984 ┌───────────────UserID─┬─SearchPhrase─────────────────────────────────────────────────┬─count()─┐ │ 64240392369242065 │ │ 1 │ │ 2542641703475366060 │ galaxy s4 activerstovmamasumi x2 │ 3 │ │ 14973463213479722228 │ │ 17 │ │ 6604743450870066038 │ │ 1 │ │ 325929602194382277 │ вес гриппи игре aventity of wars 2 в в играть │ 1 │ │ 5481644077966220011 │ как леченский рецепты как почему конкая лето москва отдых на │ 1 │ │ 5965198553492672379 │ │ 1 │ │ 119657425828985633 │ │ 1 │ │ 8462750442030450647 │ рулонасточный+статив зомбинет магазин на айресу батл │ 1 │ │ 7510587892824469257 │ sia 265 сезон 6 серии │ 1 │ └──────────────────────┴──────────────────────────────────────────────────────────────┴─────────┘ 10 rows in set. Elapsed: 2.190 sec. Processed 100.00 million rows, 2.44 GB (45.66 million rows/s., 1.11 GB/s.)
現在建立另外一個聚合 PROJECTION:
ALTER TABLE hits_100m_obfuscated ADD PROJECTION agg_p2 ( SELECT UserID, SearchPhrase, count() GROUP BY UserID, SearchPhrase )
由于曆史資料已經存在，也要手動觸發一下物化：
alter table hits_100m_obfuscated MATERIALIZE PROJECTION agg_p2
物化好了之後，再次執行相同的查詢:
SELECT UserID, SearchPhrase, count() FROM hits_100m_obfuscated GROUP BY UserID, SearchPhrase LIMIT 10 Query id: 258e556e-ea5b-43f0-980a-997c02abc233 ┌───────────────UserID─┬─SearchPhrase─────────────────────────────────────────────────┬─count()─┐ │ 64240392369242065 │ │ 1 │ │ 2542641703475366060 │ galaxy s4 activerstovmamasumi x2 │ 3 │ │ 14973463213479722228 │ │ 17 │ │ 6604743450870066038 │ │ 1 │ │ 325929602194382277 │ вес гриппи игре aventity of wars 2 в в играть │ 1 │ │ 5481644077966220011 │ как леченский рецепты как почему конкая лето москва отдых на │ 1 │ │ 5965198553492672379 │ │ 1 │ │ 119657425828985633 │ │ 1 │ │ 8462750442030450647 │ рулонасточный+статив зомбинет магазин на айресу батл │ 1 │ │ 7510587892824469257 │ sia 265 сезон 6 серии │ 1 │ └──────────────────────┴──────────────────────────────────────────────────────────────┴─────────┘ 10 rows in set. Elapsed: 1.847 sec. Processed 24.07 million rows, 1.58 GB (13.04 million rows/s., 856.09 MB/s.)
資料掃描範圍減少了四分之三。
現在 ClickHouse 也提供了 PROJECTION 的系統表，可以看到相關的存儲資訊：
SELECT name, partition, formatReadableSize(bytes_on_disk) AS bytes, formatReadableSize(parent_bytes_on_disk) AS parent_bytes, parent_rows, rows / parent_rows AS ratio FROM system.projection_parts Query id: 2887b0e1-b984-4274-862c-0b59c68693c5 ┌─name───┬─partition─┬─bytes──────┬─parent_bytes─┬─parent_rows─┬──────ratio─┐ │ agg_p2 │ 201307 │ 490.40 MiB │ 14.06 GiB │ 100000000 │ 0.24070565 │ │ p1 │ 201307 │ 4.95 GiB │ 18.53 GiB │ 100000000 │ 1 │ └────────┴───────────┴────────────┴──────────────┴─────────────┴────────────┘
PROJECTION 本質也是在用空間換時間，還是還很劃算的。
PROJECTION 也支援删除的 DDL:
ALTER TABLE hits_100m_obfuscated DROP PROJECTION p1 ALTER TABLE hits_100m_obfuscated DROP PROJECTION agg_p2
除了通過 ALTER 建立，也能在 CREATE TABLE 的時候建立，例如：
CREATE TABLE xxx ( `event_key` String, `user` UInt32, `dim1` String, PROJECTION p1 ( SELECT groupBitmap(user), count(1) GROUP BY dim1 ) ) ENGINE = MergeTree() ORDER BY (event_key, user)
通過剛才的例子，你能發現在查詢時， PROJECTION 的使用是無感的，ClickHouse 會根據送出的 SQL 語句自動比對。
那麼你肯定會好奇，比對的規則是什麼呢？有這麼幾條原則：
1. 設定了 SET allow_experimental_projection_optimization = 1
2. 傳回的資料行小于基表總數
3. 查詢覆寫的分區 part 超過一半
4. Where 必須是 PROJECTION 定義中 GROUP BY 的子集
5. GROUP BY 必須是 PROJECTION 定義中 GROUP BY 的子集
6. SELECT 必須是 PROJECTION 定義中 SELECT 的子集
7. 比對多個 PROJECTION 的時候，選取讀取 part 最少的
如果你不知道查詢是否比對了 PROJECTION ，有兩種方式可以校驗：
1. 使用 explain ，例如：
EXPLAIN SELECT WatchID FROM hits_100m_obfuscated WHERE WatchID = 5814563137538961516 Query id: bf008e69-fd68-4928-83f6-a57a2d84e286 ┌─explain───────────────────────────────────────────────────────────────────┐ │ Expression ((Projection + Before ORDER BY)) │ │ SettingQuotaAndLimits (Set limits and quota after reading from storage) │ │ ReadFromStorage (MergeTree(with 0 projection p1)) │ └───────────────────────────────────────────────────────────────────────────┘
看到 MergeTree(with 0 projection p1) 就代表這條 SQL 查詢會命中 PROJECTION
2. 檢視執行日志:
(SelectExecutor): Choose normal projection p3 (SelectExecutor): projection required columns: dim1, dim3, event_time, dim2, event_key, user (SelectExecutor): Key condition: (column 0 in ['dim12', 'dim12'])
看到 Choose xxx projection 就代表這條 SQL 查詢已經命中 PROJECTION
利用 PROJECTION ，我們隻需面對一張底表查詢就行了，既擁有原來物化視圖的性能，又免去了維護成本和資料一緻性的問題，簡直無敵啊。
好了，今天的分享就到這裡，再有了 PROJECTION 之後，可以說 ClickHouse 更加的如虎添翼了。在原有的一些場景下，我們可以告别 ETL和物化視圖了。
———————————————————————————
這是一本可幫助讀者深度了解并全面掌握ClickHouse運作原理并進行實踐開發的工具書，涵蓋了ClickHouse的時代背景、發展曆程、核心概念、基礎功能、運作原理、實踐指導等多個次元的内容，尤其是在ClickHouse最核心的部分——MergeTree表引擎與分布式方面，書中對其實作原理和應用技巧進行了詳細解讀。
———————————————————————————
文末贈書：
最近新朋友比較多，為了回饋BigData公衆号粉絲，特來贈書一本,《ClickHouse原了解析和應用實踐》，9月22日20:00分開獎，關注公衆号，回複我要學習參與抽獎