如何做到毫秒級從百億大表任意維度篩選數據？| 技術頭條

業務背景

隨著閑魚業務的發展，用戶規模達到數億級，用戶維度的數據指標，達到上百個之多。如何從億級別的數據中，快速篩選出符合期望的用戶人群，進行精細化人群運營，是技術需要解決的問題。業界的很多方案往往需要分鐘級甚至小時級才能生成查詢結果。本文提供了一種解決大數據場景下的高效數據篩選、統計和分析方法，從億級別數據中，任意組合查詢條件，篩選需要的數據，做到毫秒級返回。

技術選型分析

從技術角度分析，我們這個業務場景有如下特點：

• 需要支持任意維度的組合（and/or）嵌套查詢，且要求低延遲；
• 數據規模大，至少億級別，且需要支持不斷擴展；
• 單條數據指標維度多，至少上百，且需要支持不斷增加；綜合分析，這是一個典型的 OLAP 場景。

OLTP 與 OLAP

下面簡單對比下 OLTP 和 OLAP：

• 數據量瓶頸：MySQL 比較適合的數據量級是百萬級，再多的話，查詢和寫入性能會明顯下降。因此，一般會採用分庫分表的方式，把數據規模控制在百萬級。
• 查詢效率瓶頸：MySQL 對於常用的條件查詢，需要單獨建立索引或組合索引。非索引欄位的查詢需要掃描全表，性能下降明顯。

綜上分析，我們的應用場景，並不適合採用行存儲資料庫，因此我們重點考慮列存資料庫。

行式存儲與列式存儲

下面簡單對比一下行式存儲與列式存儲的特點：

HybridDB for MySQL 計算規格介紹

HybridDB for MySQL 計算規格對我們的這個場景而言，核心能力主要有：

• 任意維度智能組合索引（使用方無需單獨自建索引）
• 百億大表查詢毫秒級響應
• MySQL BI 生態兼容，完備 SQL 支持
• 空間檢索、全文檢索、複雜數據類型（多值列、JSON）支持

那麼，HybridDB for MySQL 計算規格是如何做到大數據場景下的任意維度組合查詢的毫秒級響應的呢？

• 首先是 HybridDB 的高性能列式存儲引擎，內置於存儲的謂詞計算能力，可以利用各種統計信息快速跳過數據塊實現快速篩選；
• 第二是 HybridDB 的智能索引技術，在大寬表上一鍵自動全索引並根據列索引智能組合出各種謂詞條件進行過濾；
• 第三是高性能 MPP+DAG 的融合計算引擎，兼顧高並發和高吞吐兩種模式實現了基於 Pipeline 的高性能向量計算，並且計算引擎和存儲緊密配合，讓計算更快；
• 第四是 HybridDB 支持各種數據建模技術例如星型模型、雪花模型、聚集排序等，業務適度數據建模可以實現更好的性能指標。

綜合來說，HybridDB for MySQL 計算規格是以 SQL 為中心的多功能在線實時倉庫系統，很適合我們的業務場景，因此我們在此之上構建了我們的人群圈選底層引擎。

業務落地

在搭建了人群圈選引擎之後，我們重點改造了我們的消息推送系統，作為人群精細化運營的一個重要落地點。

消息推送簡介

消息推送（PUSH）是信息觸達用戶最快捷的手段。我們比較常用的 PUSH 方式，是先離線計算好 PUSH 人群、準備好對應 PUSH 文案，然後在第二天指定的時間推送。一般都是周期性的 PUSH 任務。但是臨時性的、需要立刻發送、緊急的 PUSH 任務，就需要 BI 同學介入，每個 PUSH 任務平均約需要佔用 BI 同學半天的開發時間，且操作上也比較麻煩。本次我們把人群圈選系統與原有的 PUSH 系統打通，極大地改善了此類 PUSH 的準備數據以及發送的效率，解放了開發資源。

系統架構

離線數據層：用戶維度數據，分散在各個業務系統的離線表中。我們通過離線 T+1 定時任務，把數據匯總導入到實時計算層的用戶大寬表中。

實時計算層：根據人群的篩選條件，從用戶大寬表中，查詢符合的用戶數量和用戶 ID 列表，為應用系統提供服務。

人群圈選前台系統：提供可視化的操作界面。運營同學選擇篩選條件，保存為人群，用於分析或者發送 PUSH。每一個人群，對應一個 SQL 存儲。類似於： select count(*) from userbigtable where column1> 1 and column2 in ("a","b") and ( column31=1 or column32=2) 同時，SQL可以支持任意欄位的多層 and/or 嵌套組合。 用 SQL 保存人群的方式，當用戶表中的數據變更時，可以隨時執行 SQL，獲取最新的人群用戶，來更新人群。

PUSH 系統：從人群圈選前台系統中獲取人群對應的 WHERE 條件，再從實時計算層，分頁獲取用戶列表，給用戶發送 PUSH。在實現過程中，我們重點解決了分頁查詢的性能問題。

分頁查詢性能優化方案： 在分頁時，當人群的規模很大（千萬級別）時，頁碼越往後，查詢的性能會有明顯下降。因此，我們採用把人群數據增加行號、導出到 MySQL 的方式，來提升性能。表結構如下：

• 批次號：人群每導出一次，就新加一個批次號，批次號為時間戳，遞增。
• 行號：從 1 開始遞增，每一個批次號對應的行號都是從 1 到 N。

我們為"人群 ID"+"批次號"+"行號"建組合索引，分頁查詢時，用索引查詢的方式替換分頁的方式，從而保證大頁碼時的查詢效率。

另外，為此額外付出的導出數據的開銷，得益於 HybridDB 強大的數據導出能力，數據量在萬級別至百萬級別，耗時在秒級至幾十秒級別。綜合權衡之後，採用了本方案。

PUSH 系統改造收益

人群圈選系統為我們的精細化用戶運營提供了強有力的底層能力支撐。同時，圈選人群，也可以應用到其他的業務場景，比如首頁焦點圖定投等需要分層用戶運營的場景，為閑魚業務提供了很大的優化空間。

本文實現了海量多維度數據中組合查詢的秒級返回結果，是一種 OLAP 場景下的通用技術實現方案。同時介紹了用該技術方案改造原有業務系統的一個應用案例，取得了很好的業務結果，可供類似需求或場景的參考。

後續計劃

人群圈選引擎中的用戶數據，我們目前是 T+1 導入的。這是考慮到人群相關的指標，變化頻率不是很快，且很多指標（比如用戶標籤）都是離線 T+1 計算的，因此 T+1 的數據更新頻度是可以接受的。後續我們又基於 HybridDB 構建了更為強大的商品圈選引擎。我們的商品數據相比用戶數據，變化更快。一方面用戶隨時會更新自己的商品，另一方面，由於我們的商品單庫存（售出即下架）的特性，以及其他原因，商品狀態會隨時變更。因此我們的選品引擎，應該儘快感知到這些數據的變化，並在投放層面做出實時調整。我們基於 HybridDB（存儲）和實時計算引擎，構建了更為強大的「馬赫」實時選品系統。

參考資料：HybridDB for MySQL介紹（https://www.aliyun.com/product/petadata）

聲明：本文為作者投稿，版權歸其所有。

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！