背景

最近在做的業(yè)務(wù)中，用戶相關(guān)的數(shù)據(jù)不斷增長，給系統(tǒng)帶來了不小的壓力，在 SQL優(yōu)化實(shí)戰(zhàn)-千萬量級(jí)后的慢查 一文中也總結(jié)了一些針對(duì)慢查的解決方案。但每次活動(dòng)下來，都會(huì)有幾百上千萬的用戶相關(guān)數(shù)據(jù)產(chǎn)生，單純的sql優(yōu)化已經(jīng)無法解決，本文站在前人肩膀上，總結(jié)了海量數(shù)據(jù)情景下的解決方案。

分區(qū)&分庫分表

目前業(yè)務(wù)中使用的是MySQL，針對(duì)關(guān)系型數(shù)據(jù)庫，可以采用分區(qū)或者分庫分表的策略。首先看一下其各自的實(shí)現(xiàn)原理及優(yōu)缺點(diǎn)：

（1）分區(qū)

• 分區(qū)原理：分區(qū)表是由多個(gè)相關(guān)的底層表實(shí)現(xiàn)，存儲(chǔ)引擎管理分區(qū)的各個(gè)底層表和管理普通表一樣，只是分區(qū)表在各個(gè)底層表上各自加上一個(gè)相同的索引（分區(qū)表要求所有的底層表都必須使用相同的存儲(chǔ)引擎）。
• 分區(qū)優(yōu)點(diǎn)：它對(duì)用戶屏蔽了sharding的細(xì)節(jié)，即使查詢條件沒有sharding column，它也能正常工作（只是這時(shí)候性能一般）。
• 分區(qū)缺點(diǎn)：連接數(shù)、網(wǎng)絡(luò)吞吐量等資源都受到單機(jī)的限制；并發(fā)能力遠(yuǎn)遠(yuǎn)達(dá)不到互聯(lián)網(wǎng)高并發(fā)的要求。（主要因?yàn)殡m然每個(gè)分區(qū)可以獨(dú)立存儲(chǔ)，但是分區(qū)表的總?cè)肟谶€是一個(gè)MySQL示例）。
• 適用場(chǎng)景：并發(fā)能力要求不高；數(shù)據(jù)不是海量（分區(qū)數(shù)有限，存儲(chǔ)能力就有限）。

（2）分庫分表

互聯(lián)網(wǎng)行業(yè)處理海量數(shù)據(jù)的通用方法：分庫分表。 分庫分表中間件全部可以歸結(jié)為兩大類型：

• CLIENT模式；
• PROXY模式；

CLIENT模式代表有阿里的TDDL，開源社區(qū)的sharding-jdbc（sharding-jdbc的3.x版本即sharding-sphere已經(jīng)支持了proxy模式）。架構(gòu)如下：

PROXY模式代表有阿里的cobar，民間組織的MyCAT。架構(gòu)如下：

無論是CLIENT模式，還是PROXY模式。幾個(gè)核心的步驟是一樣的：SQL解析，重寫，路由，執(zhí)行，結(jié)果歸并。

分庫分表實(shí)現(xiàn)（MYSQL）

針對(duì)分區(qū)與分庫分表的適用場(chǎng)景，選擇分庫分表的實(shí)現(xiàn)方案。結(jié)合實(shí)際業(yè)務(wù)：學(xué)生（user表）定期參加體能測(cè)試（detect表），每一次體測(cè)之后，保留對(duì)應(yīng)檢測(cè)數(shù)據(jù)（data表），因此，數(shù)據(jù)data表中的核心數(shù)據(jù)：

分庫分表第一步也是最重要的一步，即sharding column的選取，sharding column選擇的好壞將直接決定整個(gè)分庫分表方案最終是否成功。sharding column的選取跟業(yè)務(wù)強(qiáng)相關(guān)。

• 選擇方法：分析你的API流量，將流量比較大的API對(duì)應(yīng)的SQL提取出來，將這些SQL共同的條件作為sharding column。
• 選擇示例：例如一般的OLTP系統(tǒng)都是對(duì)用戶提供服務(wù)，這些API對(duì)應(yīng)的SQL都有條件用戶ID，那么，用戶ID就是非常好的sharding column。

在上述學(xué)生體測(cè)業(yè)務(wù)中，我們需要匯總統(tǒng)計(jì)一次體測(cè)任務(wù)中，所有學(xué)生各項(xiàng)的體測(cè)結(jié)果，所以按照上述的原則，需要根據(jù)體測(cè)任務(wù)ID，即detect_id進(jìn)行分表，以盡量減少在統(tǒng)計(jì)一次體測(cè)任務(wù)的數(shù)據(jù)時(shí)的跨表查詢；但實(shí)際業(yè)務(wù)中，在學(xué)生端也有縱向?qū)Ρ鹊男枨?，即學(xué)生需要查看自己所有參加過的體測(cè)任務(wù)中的數(shù)據(jù)，這樣的話，按照detect_id分表，再以u(píng)ser_id作為查詢條件，就需要跨表查詢，效率會(huì)很低。因此，最終方案是：不同字段冗余分表。

（1）冗余全量表

每個(gè)sharding列對(duì)應(yīng)的表的數(shù)據(jù)都是全量的。以用戶體測(cè)數(shù)據(jù)為例：分別使用三個(gè)獨(dú)立的sharding column，即data_id（數(shù)據(jù)ID），detect_id（體測(cè)任務(wù)ID），user_id（學(xué)生ID）。

（2）冗余關(guān)系表選擇

只有一個(gè)sharding column的分庫分表的數(shù)據(jù)是全量的，其他分庫分表只是與這個(gè)sharding column的關(guān)系表。實(shí)際使用中可能會(huì)冗余更多常用字段，如學(xué)生姓名、體測(cè)任務(wù)名稱等。

（3）冗余全量表 VS 冗余關(guān)系表

• 速度對(duì)比：冗余全量表速度更快，冗余關(guān)系表需要二次查詢，即使有引入緩存，還是多一次網(wǎng)絡(luò)開銷；
• 存儲(chǔ)成本：冗余全量表需要幾倍于冗余關(guān)系表的存儲(chǔ)成本；
• 維護(hù)代價(jià)：冗余全量表維護(hù)代價(jià)更大，涉及到數(shù)據(jù)變更時(shí)，多張表都要進(jìn)行修改。

選擇冗余全量表還是索引關(guān)系表，這是一種架構(gòu)上的權(quán)衡，兩者的優(yōu)缺點(diǎn)明顯，在我們的業(yè)務(wù)中采用冗余全量表的方式。

非關(guān)系型數(shù)據(jù)庫（ClickHouse）

上面提到的都是條件中有sharding column的SQL執(zhí)行。但是，總有一些查詢條件是不包含sharding column的，同時(shí)，我們也不可能為了這些請(qǐng)求量并不高的查詢，無限制的冗余分庫分表。另外，在分表前，我們會(huì)事先定義好分表的數(shù)量，隨著業(yè)務(wù)擴(kuò)張，單表數(shù)據(jù)達(dá)到大幾千萬甚至上億，對(duì)于MySQL而言，還是不大友好的，再去增加分表數(shù)量，也是不大現(xiàn)實(shí)的。因此，專業(yè)的事情最好還是使用專業(yè)的工具-ClickHouse。

ClickHouse 是近年來備受關(guān)注的開源列式數(shù)據(jù)庫，主要用于數(shù)據(jù)分析(OLAP)領(lǐng)域。目前國內(nèi)社區(qū)火熱，各個(gè)大廠紛紛跟進(jìn)大規(guī)模使用:

• 今日頭條內(nèi)部用 ClickHouse 來做用戶行為分析，內(nèi)部一共幾千個(gè) ClickHouse 節(jié)點(diǎn)，單集群最大 1200 節(jié)點(diǎn)，總數(shù)據(jù)量幾十 PB，日增原始數(shù)據(jù) 300TB 左右。
• 騰訊內(nèi)部用 ClickHouse 做游戲數(shù)據(jù)分析，并且為之建立了一整套監(jiān)控運(yùn)維體系。
• 攜程內(nèi)部從 18 年 7 月份開始接入試用，目前 80% 的業(yè)務(wù)都跑在 ClickHouse 上。每天數(shù)據(jù)增量十多億，近百萬次查詢請(qǐng)求。
• 快手內(nèi)部也在使用 ClickHouse，存儲(chǔ)總量大約 10PB， 每天新增 200TB， 90% 查詢小于 3S。

在 1 億數(shù)據(jù)集體量的情況下，ClickHouse 的平均響應(yīng)速度是 Vertica 的 2.63 倍、InfiniDB 的 17 倍、MonetDB 的 27 倍、Hive 的 126 倍、MySQL 的 429 倍以及Greenplum 的 10 倍。

ClickHouse更多內(nèi)容參考：https://juejin.cn/post/7120519057761107999

在 OLAP 數(shù)據(jù)庫中，可變數(shù)據(jù)通常不受歡迎。ClickHouse 也不歡迎可變數(shù)據(jù)。然而現(xiàn)實(shí)情況，更新情況不可避免。比如，學(xué)生在體測(cè)過程中，是可以進(jìn)行重復(fù)測(cè)試的，即需要進(jìn)行更新數(shù)據(jù)。以下是關(guān)于clickhouse更新的解決方案：

參考：https://zhuanlan.zhihu.com/p/485645089

（1）Alter/Update Table

ClickHouse團(tuán)隊(duì)在2018年發(fā)布了UPDATE和DELETE，但是它不是原生的UPDATE和DELETE語句，而是被實(shí)現(xiàn)為ALTER TABLE UPDATE語句，如下所示：

ALTER TABLE [db.]table UPDATE column1 = expr1 [, ...] WHERE filter_expr;

如更新檢測(cè)結(jié)果，ALTER UPDATE語句如下：

ALTER TABLE UPDATE detect_result=1 WHERE detect_id = 1 and user_id=4;

需要注意的是，ClickHouse的更新是一個(gè)異步的操作。當(dāng)用戶執(zhí)行一個(gè)如上的Update操作獲得返回時(shí)，ClickHouse內(nèi)核其實(shí)只做了兩件事情：

• 檢查Update操作是否合法；
• 保存Update命令到存儲(chǔ)文件中，喚醒一個(gè)異步處理merge和mutation的工作線程；

異步線程的工作流程極其復(fù)雜，總結(jié)其精髓描述如下：先查找到需要update的數(shù)據(jù)所在datapart，之后對(duì)整個(gè)datapart做掃描，更新需要變更的數(shù)據(jù)，然后再將數(shù)據(jù)重新落盤生成新的datapart，最后用新的datapart做替代并remove掉過期的datapart。

這就是ClickHouse對(duì)update指令的執(zhí)行過程，可以看出，頻繁的update指令對(duì)于ClickHouse來說將是災(zāi)難性的。(當(dāng)然，我們可以通過設(shè)置，將這個(gè)異步的過程變成同步的過程，詳細(xì)請(qǐng)看：Synchronicity of ALTER Queries，然而同步阻塞就會(huì)比較嚴(yán)重）。

（2）Incremental Log

Incremental log的思想是什么了？比如對(duì)于用戶瀏覽統(tǒng)計(jì)表中的一條數(shù)據(jù)，如下所示：

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ 
│ 4324182021466249494 │         5 │      146 │    1 │ 
└─────────────────────┴───────────┴──────────┴──────┘

現(xiàn)在有更新了：用戶又瀏覽了一個(gè)頁面，所以我們應(yīng)該改變pageview從5到6，以及持續(xù)時(shí)間從146到185。那么按照Incremental log的思想，再插入兩行:

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ 
│ 4324182021466249494 │         5 │      146 │   -1 │ 
│ 4324182021466249494 │         6 │      185 │    1 │ 
└─────────────────────┴───────────┴──────────┴──────┘

第一個(gè)是刪除行。它和我們已經(jīng)得到的行是一樣的只是Sign被設(shè)為-1。第二個(gè)更新行，所有數(shù)據(jù)設(shè)置為新值。之后我們有三行數(shù)據(jù):

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ 
│ 4324182021466249494 │         5 │      146 │    1 │ 
│ 4324182021466249494 │         5 │      146 │   -1 │ 
│ 4324182021466249494 │         6 │      185 │    1 │ 
└─────────────────────┴───────────┴──────────┴──────┘

那么對(duì)于count,sum,avg的計(jì)算方法如下：

-- number of sessions
count() -> sum(Sign)  
-- total number of pages all users checked 
sum(PageViews) -> sum(Sign * PageViews)  
-- average session duration, how long user usually spent on the website 
avg(Duration) -> sum(Sign * Duration) / sum(Sign)

這就是Incremental log方法，這種方法的不足之處在于：

• 首先需要獲取到原數(shù)據(jù)，那么就需要先查一遍CK，或者將數(shù)據(jù)保存到其他存儲(chǔ)中便于檢索查詢，然后我們才可以針對(duì)原數(shù)據(jù)插入一條 ‘delete’ rows；
• Sign operations在某些計(jì)算場(chǎng)景并不適合，比如min、max、quantile等其他場(chǎng)景；
• 額外的寫入放大：當(dāng)每個(gè)對(duì)象的平均更新次數(shù)為個(gè)位數(shù)時(shí)，更適合使用。

針對(duì)Incremental log方式的寫入方案存儲(chǔ)開銷問題，clickhouse提供了CollapsingMergeTree，使用CollapsingMergeTree，“刪除”行和舊的“刪除”行將在合并過程中折疊。但是，注意這個(gè)引擎，只是解決了寫放大問題，并不是說查詢模式就不是Incremental Log這種，我們還是需要通過對(duì)sign的特殊計(jì)算方式，達(dá)到效果。

（3）Insert+xxxMergeTree

用Insert加特定引擎，也可以實(shí)現(xiàn)更新效果。該方法適用于xxxMergeTree，如ReplacingMergeTree或AggregatingMergeTree。但是了，更新是異步的。因此剛插入的數(shù)據(jù)，并不能馬上看到最新的結(jié)果，因此并不是準(zhǔn)實(shí)時(shí)的。

比如使用AggregatingMergeTree，用法如下：

CREATE TABLE IF NOT EXISTS whatever_table ON CLUSTER default (     
  user_id UInt64,
  gender SimpleAggregateFunction(anyLast, Nullable(Enum('女' = 0, '男' = 1))),
  ...
)
ENGINE = AggregatingMergeTree() partition by toYYYYMMDD(reg_date) ORDER BY user_id;

就以上建標(biāo)語句展開分析，AggregatingMergeTree會(huì)將除主鍵（user）外的其余列，配合anyLast函數(shù)，替換每行數(shù)據(jù)為一種預(yù)聚合狀態(tài)。其中anyLast聚合函數(shù)聲明聚合策略為保留最后一次的更新數(shù)據(jù)。

實(shí)時(shí)性: 非準(zhǔn)實(shí)時(shí)。

優(yōu)點(diǎn)在于：
ClickHouse提供的這些mergeTree引擎，可以幫助我們達(dá)到最終一致性。
缺點(diǎn)在于：
xxxMergeTree并不能保證任何時(shí)候的查詢都是聚合過后的結(jié)果，并且也沒有提供標(biāo)志位用于查詢數(shù)據(jù)的聚合狀態(tài)與進(jìn)度。因此，為了確保數(shù)據(jù)在查詢前處于已聚合的狀態(tài)，還需手動(dòng)下發(fā)optimize指令強(qiáng)制聚合過程的執(zhí)行。

（4）Insert+xxxxMergeTree+Final

用xxxMergeTree是異步的，如何達(dá)到準(zhǔn)實(shí)時(shí)的效果了？ClickHouse提供了FINAL關(guān)鍵字來解決這個(gè)問題。當(dāng)指定FINAL后，ClickHouse會(huì)在返回結(jié)果之前完全合并數(shù)據(jù)，從而執(zhí)行給定表引擎合并期間發(fā)生的所有數(shù)據(jù)轉(zhuǎn)換。

用法

首先Insert數(shù)據(jù)：

INSERT INTO test_a (*) VALUES (1, 'a', 1) ;

查詢時(shí)，加入final關(guān)鍵字，如下所示：

SELECT COUNT()FROM test_a FINAL

優(yōu)缺點(diǎn)

對(duì)上述語句，explain后，查詢執(zhí)行計(jì)劃如下所示：

Expression ((Projection + Before ORDER BY))
  Aggregating
    Expression (Before GROUP BY)
      SettingQuotaAndLimits (Set limits and quota after reading from storage)
        Expression (Remove unused columns after reading from storage)
          MergingFinal (Merge rows for FINAL)
            Expression (Calculate sorting key expression)
              ReadFromStorage (MergeTree with final)

從執(zhí)行計(jì)劃可以看出代價(jià)比較高：

• 是一個(gè)串行過程；
• 會(huì)進(jìn)行分區(qū)合并；

因此，這個(gè)FINAL，也不宜頻繁的使用。

總結(jié)

本文結(jié)合業(yè)務(wù)，尋求海量數(shù)據(jù)的解決方案?，F(xiàn)有業(yè)務(wù)使用的是MySQL數(shù)據(jù)庫，且數(shù)據(jù)量暫時(shí)可控，因此目前采用分庫分表的策略。同時(shí)，也在為日益膨脹的數(shù)據(jù)做準(zhǔn)備，擬采用ClickHouse，并使用Insert+ReplacingMergeTree及查詢中去重的方案解決其更新問題。最后，歡迎有經(jīng)驗(yàn)的伙伴多多指點(diǎn)！