作者介紹
楊 博， 嗶哩嗶哩高級開發工程師。
王智博， 嗶哩嗶哩資深開發工程師。
一、業務背景
1.什麽是直播公會
直播公會是指從事網路直播表演的組織、制作、行銷等經營活動和主播的簽約、推廣、代理等經紀活動的機構。
B站為外部公會提供了主播全生命周期的管理系統，包含主播的入退會管理、主播營收數據分析、主播開播看播數據分析、直播監控、營收賬單結算等功能子模組。
同時為了鼓勵、引導外部公會有針對性對潛力主播進行孵化扶持，公會系統協同B站內部多業務方，提供一系列業務激勵、流量策略，滿足公會日常營運需求。
2.公會業務對於數據查詢場景分析
1）數據場景
公會系統主要的服務物件是外部公會營運人員，在日常公會營運工作中，通常關註以下數據：
主播日維度準即時營收數據
主播日維度準即時看播數據
主播日維度準即時垂直業務數據
公會日維度準即時營收數據
主播T+1任務進度
主播T+1月、年維度營收數據
主播T+1月、年維度看播數據
主播T+1月、年維度垂直業務數據
公會月、年維度準即時營收數據
且對數據更新頻率敏感，通常需要準即時的數據進行觀測。
3.查詢數據量預估
截至2023年12月，B站公會主播量級為百萬級，為滿足各項業務數據查詢需求，公會側儲存日粒度的主播業務數據，日均數據在百萬級。
主播維度年度數據聚合：
預估數據量：1*365 = 365
公會維度日、周、月、年度數據聚合（以最大公會主播數據量級20萬記）：
預估數據量：
日- 200000
周-200000 * 7 = 1,400,000
月-200000 * 30 = 6,000,000
年-200000 * 365 = 73,000,000
二、技術方案選型/演進
1.數據方案選型：從MySQL到ClickHouse
1）MySQL數據查詢壓力
基於上述「查詢數據量預估」，在索引設計合理的情況下，公會維度數據按月聚合已需要掃描至少6,000,000行數據。
按照資料庫配置4核8G，此時查詢平均時間已達到20s+水平，無法滿足業務要求。
2）MySQL數據儲存壓力
在公會主播百萬級數據壓力下，儲存一年必要的熱數據為：
1000000 * 365 = 365,000,000
雖然可透過分庫分表等方式緩解數據儲存壓力，但是在實際的查詢場景中，分庫分表存在著天然的數據查詢硬傷，無法靈活滿足OLAP需求。
3）數據查詢QPS分析
對公會數據查詢介面進行流量分析，需要查詢準即時全量公會數據的QPS最高不超過20，透過梳理業務場景，針對大公會進行數據分流，即使未來公會業務大力發展，任然可以將介面整體QPS控制在50以下。
4）難點
按照公會實際情況，主播與經紀人1：N關系，經紀人與其所屬管理員1：N關系，並且經紀人、管理員進行數據查詢時需要進行數據隔離，因此無法對匯總數據進行緩存或提前計算等操作。
基於上述特性，數據量大、單次查詢掃描行數多、查詢QPS可控、數據頻繁更新，選擇ClickHouse作為數據處理工具支撐公會業務。
2.技術方案演進：業務場景流量隔離， OLAP業務重構數據鏈路
1）流量隔離
透過梳理業務場景，將數據查詢分為以下三類：
外部公會後台準即時查詢長時間跨度公會維度數據概覽： 此場景下，需要即時匯總登入使用者許可權範圍內的數據概覽，無法進行數據預熱的同時需要進行大量數據計算。
外部公會後台查詢主播詳細數據： 僅需查詢單主播數據，需要掃描的數據行數可控。
T+1指令碼查詢數據計算任務進度： 透過定時任務，T+1計算主播維度、公會維度任務進度，查詢姿勢可控、篩選條件較少，但QPS較高。
公會服務透過查詢特性，將主播維度、短時間跨度、查詢簡單但QPS高的業務透過MySQL業務寬表進行支撐；將經紀人、管理員、公會維度等需要掃描大量數據的業務透過ClickHouse支持。
2）針對ClickHouse特性進行全域流量管控
公會數據異步匯出中心
針對ClickHouse支持QPS較低的問題，公會服務針對數據匯出場景進行全域限流，限制同時數據匯出任務數量，控制匯出任務查詢頻率等方式，支持公會大數據量場景下的數據匯出功能。
三、ClickHouse表設計與最佳化
如上文所述，我們運用ClickHouse支持直播公會場景的大數據量分析查詢，同時需要ClickHouse支持數據更新寫入（UPSERT）的功能。最初，我們采用ClickHouse的ReplacingMergeTree表引擎，借助其繼承的MergeTree表引擎能力和去重特性，來支持數據的查詢和更新。
針對直播公會場景的更新需求、數據查詢模式、數據過濾方式以及數據量規模等場景特征，我們的ClickHouse表采用了以下技術設計：
設定去重鍵(record_date, uid)為主鍵索引和sharding key，設定mtime為版本，利用ReplacingMergeTree的合並機制和final修飾詞，來完成即時更新。
按月分區(substring(record_date, 1, 6))，減少檔的數量，降低查詢的IO尋址消耗。
使用復制表，增強數據可靠性。
使用冷熱儲存策略，冷熱數據使用不同儲存介質，提升熱數據的讀寫效能。
ReplacingMergeTree引擎的建表語句範例：
CREATETABLE bili_live.ads_guild(`id` Int64,`uid` Int64,`guild_id` Int64,`record_date`String,`mtime` DateTime,...)ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{layer}-{shard}/bili_live/ads_guild', '{replica}', mtime)PARTITIONBYsubstring(record_date, 1, 6)ORDERBY (record_date, uid)TTL ...SETTINGS index_granularity = 8192, storage_policy = 'hot_and_cold'
ReplacingMergeTree會使用primary key(預設為order by鍵)做排序，並根據指定的version欄位（上面SQL中的mtime欄位）做去重，每個key只保留最新的那條數據。
1.ReplacingMergeTree的問題
ReplacingMergeTree表引擎雖然支持了數據更新，但由於受限於其實作原理，ReplacingMergeTree存在以下問題：
基於Merge-On-Read機制實作數據更新，使用單執行緒做數據合並，效率低。
主鍵索引設定不夠靈活，去重鍵必須和索引鍵保持一致，去重鍵不一定是業務常用的過濾欄位，限制了主鍵索引的作用。
final修飾詞導致無法使用跳數索引做數據過濾。
final修飾詞導致無法使用prewhere的最佳化。
以上問題的存在使得ReplacingMergeTree表在查詢時數據過濾效果不佳，並行度不夠，最終導致查詢效能受限，查詢延遲不能很好地滿足業務需求。
2.Unique Engine
為了解決ReplacingMergeTree的上述問題，我們新增了一款Unique Engine表引擎，主要思路是在寫入過程中透過標記刪除(Delete-On-Insert)完成更新操作。下面我們比較一下ReplacingMergeTree引擎和Unique Engine引擎的查詢執行模式，看看為什麽Unique Engine引擎在查詢效能方面更優。
從上圖可以看到，ReplacingMergeTree雖然也是多執行緒讀取數據，但由於要實作相同key數據的去重（僅保留最新version的數據），最終需要在單執行緒中完成數據合並，這樣就會影響查詢效能，增加了查詢延遲。 而Unique Engine的數據讀取和去重都是多執行緒並行完成的，不存在單執行緒合並的環節。
UniqueEngine引擎的建表語句範例：
CREATETABLE bili_live.ads_guild(`id` Int64,`uid` Int64,`guild_id` Int64,`record_date`String,`mtime` DateTime, ...INDEX id_idx idTYPE bloom_filter(0.025) GRANULARITY 1,INDEX uid_idx uid TYPE bloom_filter(0.025) GRANULARITY 1)ENGINE = ReplicatedUniqueMergeTree('/clickhouse/tables/{layer}-{shard}/bili_live/ads_guild', '{replica}', mtime)PARTITIONBYsubstring(record_date, 1, 6)ORDERBY (record_date, guild_id)UNIQUEKEY (record_date, uid)TTL ...SETTINGS index_granularity = 8192, storage_policy = 'hot_and_cold', enable_unique_key_bucket = 1, unique_key_deduplicate_level = 1, unique_key_index_type = 1
從建表SQL可以看到，我們增加了一個unique key，數據會基於unique key做去重，每個key僅保留version（上面SQL中的mtime欄位）最新的那條數據。
除了查詢執行模式上的優勢，Unique Engine同時解決了上文提到的ReplacingMergeTree的幾個問題：
拆分去重鍵和主鍵索引： 設定(record_date, guild_id)為主鍵索引，以便使用主鍵索引做過濾。
添加跳數索引： 由於查詢不再需要添加final修飾詞，所以能夠利用跳數索引跳過不需要掃描的數據。
prewhere最佳化： 由於查詢不再需要添加final修飾詞，所以能夠利用prewhere最佳化減少需要讀取的數據量。
四、Unique Engine設計原理與實作
上一節我們闡述了如何透過引入Unique Engine引擎解決ReplacingMergeTree引擎的問題，下面我們將詳細介紹Unique Engine的原理與實作。
針對分析型場景支持數據更新這一問題，行業內常見的設計思路有兩種：Merge-On-Read和Delete-On-Insert。例如，HBase和ClickHouse的ReplacingMergeTree就是在查詢過程中透過Merge-On-Read做數據更新；而StarRocks和ByConity則都是透過Delete-On-Insert在寫入過程中做標記刪除。
由於在我們大部份有更新需求的場景下，數據量都相對較小，對數據寫入的延遲要求較低），而對數據查詢延遲的要求比較高，所以經過詳細評估後，我們采用了Delete-On-Insert的設計思路。
1.方案設計
為了支持Delete-On-Insert機制，我們在ClickHouse data part內部新增了三種數據結構：
unique key minmax index： 用於儲存unique key的最大值和最小值，透過極值判斷快速跳過不需要掃描的data part。
unique key index： 用於儲存所有unique key的唯一值，並進行了hash分桶，以加快其載入的速度。
delete bitmap： 用於標記data part內部刪除的數據，記錄其對應的行號。
在寫入過程中，掃描歷史數據，並依次檢查unique key minmax index和unique key index，如果發現歷史數據的data part中有相同的unique key，且未被刪除，則在delete bitmap中記錄其行號，標記刪除。在查詢和合並的過程中，依賴delete bitmap過濾刪除數據，避免了merge on read單執行緒做合並的問題。
對於unique key index，我們支持了map、unordered_map、StringHashMap三種型別，其中unordered_map占用記憶體相對較小，但在常駐記憶體的模式下，如果unique key的數量非常多的話，還是會占用很大的記憶體，影響業務的正常查詢，所以我們場景中使用的是非常駐記憶體模式。
由於data part內部的行號是遞增數值型別，所以非常適合使用bitmap型別來儲存刪除的行號，這樣不僅占用空間小，檢索速度還非常快。
2.寫入合並沖突
寫入和合並是兩條獨立的鏈路，它們是很有可能同時發生的。在合並期間進行寫入操作，如果合並未感知到寫入的標記刪除，就會引發沖突，造成數據重復的問題。
如圖所示，在part_1_1_0和part_2_2_0合並的過程中，寫入了part_3_3_0，雖然part_3_3_0已經標記了part_1_1_0中的unique key 'jack'需要刪除，但由於merge任務在標記刪除之前，已經完成了對part_1_1_0的讀取，所以導致最後的結果中，出現了重復的數據。
為了解決這個沖突，我們采用了cas原子性變更part狀態+commit lock的方案，以保證冪等性更新。在寫入過程中，如果發現歷史part正處於合並中，會對歷史part做兩份標記，一份是part級別，另一份是table級別，part級別是為了即時讀取的準確性，table級別是為了最終的數據去重。
再回到剛才的問題，part_3_3_0在寫入的過程中，發現part_1_1_0正在合並，則會對part_1_1_0做兩份標記，part級別和table級別都會記錄unique key 'jack'的刪除資訊。當part_1_1_0和part_2_2_0合並到臨時檔後，在送出的時候，會發現table級別有關於part_1_1_0的標記資訊，則會對unique key 'jack'做最後的刪除，保證了數據去重。
3.去重加速
1）並列比較
在寫入過程中，新寫入的data part需要與歷史data part進行比較，以篩選出並標記需刪除的數據。傳統的序列比較方法效率低，影響了寫入效能。為了提高寫入效率，我們將歷史數據部份進行分組，並采用多執行緒並列比較的方式，以此來提升數據寫入的速度。
2）Hash分桶
unique key index是行級索引，占用儲存空間較大。如果unique key index常駐記憶體，則記憶體消耗過大，在我們的實際場景中不可行；而如果unique key index非常駐記憶體，則每次寫入在做去重前都需要將unique key index載入進記憶體。為了減少每次寫入需要載入的unique key index數據量，我們對Unique index進行了hash分桶，每次寫入時僅載入可能與寫入數據重復的分桶，從而提升整體寫入效能。
綜上所述，我們透過並列比較和unique key index分桶載入的方式實作unique key的去重，從而在記憶體占用和寫入速度之間達到一個平衡，以保障冪等性寫入。
4.復制表副本去重
為使用ClickHouse副本特性(比如備份數據，同時提供讀寫能力等)，我們支持了Unique Engine的副本功能。Unique Engine會對歷史的data part進行即時更新，所以副本之間是需要同步更新的資訊，為降低對zookeeper的影響，目前采用的是副本二次去重，即拉取的data part會與歷史的data part重新進行比較，以保證冪等性寫入。同為shard的兩個副本節點，為保證兩個副本的數據一致性，只能由一個節點完成合並，另一個節點只能去拉取合並後的data part。
5.重復數據的過濾
ClickHouse內部的過濾篩選是透過bit column實作的，1代表滿足篩選條件，0代表不滿足。ClickHouse的prewhere過濾結果就是透過bit column表示的。delete bitmap的作用是過濾掉已刪除的數據，因此在進行數據過濾時，我們將delete bitmap轉換成為bit column進行過濾。
對於存在prewhere的查詢，我們將delete bitmap bit列與prewhere bit列進行交集操作，這樣不僅能夠過濾無效的數據，還可以結合prewhere的能力，提升整體的查詢效能。對於不存在prewhere的查詢和合並，則直接依賴於delete bitmap的bit column進行過濾，獲取有效的數據集。
五、Unique Engine在直播公會場景的落地效果
1.查詢效能對比
上圖是七種業務查詢在不同表引擎下的耗時對比圖，如圖所示，受益於Unique Engine的特性(靈活的主鍵索引、不受final限制的跳數索引等)，查詢效能均得到大幅提升，整體提升了大約10x~20x。
使用ReplacingMergeTree表引擎，部份查詢延遲無法保證在亞秒內，而且日均掃描數據量達60TB+。
使用UniqueMergeTree表引擎，最大耗時可以保證在亞秒內，p90查詢效能提升了5x，日均掃描數據量減少了6x，集群資源節省了50%。
2.寫入效能對比
在寫入方面，使用UniqueMergeTree表引擎後，相較於ReplacingMergeTree表引擎有10x的效能損耗，而且去重比較的歷史數據量越大，去重效能越差。
針對MySQL2ClickHouse的數據寫入場景，由於整體數據量相對較小，大多數都在千萬級別以內。在我們的場景中，寫入帶來資源消耗比較小，平均寫入延遲依然在亞秒級別。
六、Unique Engine寫入效能最佳化方案
如上文所述，雖然我們做了各種最佳化（如並行比較，hash分桶），但Unique Engine相較ReplacingMergeTree仍然有10+倍的寫入效能下降。因此，在Unique Engine落地直播公會場景後，我們對unique engine寫入效能最佳化做了進一步的探索。透過做寫入效能分析，我們發現效能瓶頸主要在兩個地方：一個是unique key index的載入；另一個是unique key的比較。
unique key index的載入：就是將unique key index從磁盤反序列化到記憶體中。雖然使用了hash分桶，但在某些寫入場景下，hash後的key比較離散，導致幾乎每次都需要把data part中的所有unique key載入到記憶體中。
unique key的比較：就是比較新寫入的data part和歷史的data part的unique key。新寫入的所有keys都需要和歷史存在的所有keys比較。比如有4個歷史data part，新寫入的data part有10個unique key，那麽意味著至少需要比較4 * 10 = 40次。當寫入數據或歷史數據較多時，key的比較次數巨大，從而影響整體寫入效能。
圍繞上述寫入效能瓶頸，我們對Unique Engine的寫入做了進一步最佳化。主要思路是將unique key儲存到LevelDB，在數據寫入時，對新寫入的數據做排序後構建有序key iterator，同時對LevelDB中的歷史數據也構建有序key iterator，然後對兩個有序key iterators做順序比較，利用LevelDB索引跳過不需比較的數據。這樣不僅可以借助LevelDB的特性(如有序性，索引、block)縮小unique key的載入範圍，減少無效的載入，還可以減少unique key的比較次數，從而大幅提升寫入效能。
我們實作了上述寫入最佳化，經測試驗證，上述最佳化可將寫入延遲降低數倍，效能提升效果如下：
另外，最佳化前Unique Engine寫入耗時與歷史數據成正比，而最佳化後的版本由於利用LevelDB的索引跳過無需比較的數據，所以寫入效能大體上是和新寫入的數據量成正比的，所以線上上數據量較大的場景下，效能提升效果會更為明顯。
七、下一步的工作
1.LevelDB Unique Key Index全覆蓋
目前線上執行的大部份Unique Engine場景使用的還是Map型別的Unique Key Index，我們將推進LevelDB Unique Key Index的全覆蓋，從而大幅提升ClickHouse更新場景的寫入效能。
2.接入鏈路最佳化
目前MySQL到ClickHouse的寫入鏈路較長，需要經過Canal，Kafka，Flink等元件才能最終寫入到ClickHouse，不僅增加了數據鏈路的復雜性和運維成本，同時也使得使用者的業務接入門檻較高，阻礙了業務場景的拓展。我們計劃采用Flink CDC，配合上層接入服務支持，實作MySQL到ClickHouse數據同步鏈路的一站式構建配置，降低業務接入門檻，進而拓展更多業務場景。
作者丨楊博、王智博
來源丨公眾號：嗶哩嗶哩技術（ID：bilibili-TC）
dbaplus社群歡迎廣大技術人員投稿，投稿信箱： [email protected]
