全部產品
Search

搜尋本產品

    PolarDB：如何對JSON類型進行高效分析

    文件中心

    PolarDB：如何對JSON類型進行高效分析

    更新時間：Sep 04, 2024

    本文介紹了PolarDB IMCI為應對海量結構化與半結構化資料分析情境，通過整合列式JSON、虛擬列、秒級加減列、表列數擴充及列存索引等系列功能而構建出的擴充Realtime Compute方案，以及該方案的應用案例。

    背景

    隨著應用情境多樣化與快速迭代，業務系統常採用半結構化資料類型進行儲存與分析。PolarDB列存索引（In Memory Column Index，IMCI）功能推出了完備的虛擬列與列式JSON功能等，可以快速處理巨量資料和多種資料類型（包括結構化資料與半結構化資料等），並提供高效的資料分析、查詢與Realtime Compute能力，適用於資料分析、資料倉儲與擴充Realtime Compute等應用情境。

    針對海量半結構化資料計算與分析，本文將以半結構化資料JSON為例，首先簡述傳統資料庫與資料倉儲的解決方案，然後詳細描述PolarDB IMCI列式JSON、虛擬列、秒級加減列與表列數擴充等技術點，最後給出PolarDB IMCI列式JSON即時分析與擴充Realtime Compute解決方案。

    方案

    業務系統採用JSON類型主要由於半結構化資料的靈活性和高效分析的需求，因此，靈活性與高效能就成為衡量海量半結構化資料分析的解決方案好壞的關鍵計量。

    傳統資料庫解決方案

    傳統關係型資料庫（MySQL、PostgreSQL與ClickHouse等）在處理JSON資料時經常將原始JSON資料編碼成位元據並儲存到表的JSON類型列中，查詢時使用JSON函數對整個JSON列資料進行即時解析與計算等。

    JSON作為半結構化資料類型，可以按需增刪改JSON屬性。當業務需求變更時，業務系統只需要針對新JSON列資料動態增刪改相關屬性即可，而不需要修改表結構，有效降低維護和管理表結構的成本。但業務查詢時需要讀取完整JSON列資料並進行即時解析，不僅佔用大量IO資源，而且可能存在重複解析和計算問題。此外也無法針對JSON列資料指定欄位建立和使用二級索引等。

    select product.item->"$.name"
from product, purchase
where product.id = purchase.item->"$.id"
group by product.item->"$.name";

    傳統資料庫執行上述SQL時，當使用Nested Loop Join運算元時，每一張product表的每一行均需要重複讀取purchase表的item整列資料，同時反覆解析JSON列資料並抽取出指定欄位，使得查詢效率不佳。

    傳統數倉解決方案

    資料倉儲的處理流程通常包括以下步驟：

    1. 資料幫浦（Extraction）：從各個資料來源中抽取需要的資料，包括資料庫、檔案、Web服務等，並進行清洗、轉換和過濾。

    2. 資料轉換（Transformation）：對抽取的資料進行轉換，使其符合資料倉儲的資料模型和規範。包括資料清洗、資料整合、資料轉換、資料增強和資料彙總等操作。

    3. 資料載入（Loading）：將經過轉換的資料載入到資料倉儲中，包括維度資料表和事實表。

    4. 資料管理（Management）：對資料倉儲中的資料進行管理，包括資料備份、資料恢複、資料安全等操作。

    5. 資料分析（Analysis）：通過資料倉儲中的資料進行多維分析，包括查詢、報表、資料採礦等操作。

    業務系統的生產資料一般會經過中間ETL任務按需處理後再匯入到資料倉儲。

    在處理JSON資料時，為了提高查詢效能，數倉往往在ETL任務中預先解析JSON資料且按需計算出對應值，然後作為表的單獨一列插入到表中，即將JSON資料一些屬性按需處理後構成大寬表。查詢時不再需要讀取和解析整個JSON列資料，直接讀取對應普通列即可，節省大量IO資源。此外也可以針對該列建立和使用索引，有效提高查詢效能。

    但當業務需求變更時，業務系統按需增刪改JSON資料的屬性後，數倉需要修改中間ETL作業和表結構來適配上遊的生產資料，比如重新發布ETL作業、DDL加列或刪列等。因此，資料倉儲往往需要同時維護ETL作業邏輯和業務表結構，而頻繁發布ETL作業常常會影響上遊資料消費和下遊結果入倉，且在不支援INSTANT DDL時修改大表結構代價相對比較高，容易影響正常查詢業務。

    總體來說，傳統數倉解決方案雖然能夠提供高效查詢，但缺乏靈活性，維持成本高。

    IMCI技術方案

    在應對海量半結構化資料的分析情境時，傳統資料庫與資料倉儲均無法同時滿足查詢效能和靈活架構，因此業界迫切需要新解決方案：PolarDB IMCI。

    為此，PolarDB IMCI 開發列式JSON、虛擬列、秒級加減列與表列數擴充等一系列相關功能，下面將依次描述各個技術點。

    列式JSON

    半結構化資料是介於結構化資料和非結構化資料之間的一種資料形式，具有部分結構化的特點，但不像結構化資料那樣具有明確的資料模式。半結構化資料可以通過標籤、標記、中繼資料等方式進行描述和組織，但其結構與組織方式也可以隨著資料內容的變化而動態調整。半結構化資料通常存在於Web頁面、XML、JSON、NoSQL資料庫等情境中，其靈活性和易擴充性使其成為巨量資料時代中不可或缺的一部分。

    PolarDB MySQL版本身是一個關係型資料庫管理系統，其儲存的資料通常是結構化資料，但也原生支援儲存和查詢半結構化資料，如XML和JSON格式資料。PolarDB IMCI也全面支援JSON資料類型及其列式函數，採用二進位JSON格式來儲存半結構化資料，支援通過列式JSON函數來實現JSON文檔的解析、查詢、修改和刪除等操作，與MySQL文法完全相容。

    PolarDB IMCI採用精簡二進位方式儲存JSON列存資料，且使用RapidJSON庫解析JSON資料，處理過程中按需讀取資料且利用列存壓縮技術等有效減少IO量，同時充分利用SIMD和向量化及並行等加速運算。

    以實際測試資料為例展示列存中JSON用法及其行列存效能對比：

    1. 建立表並添加JSON列及其列索引。

    create table produce (
 id bigint(20) NOT NULL,
 attributes json DEFAULT NULL
) comment='columnar=1';

    1. 使用列式JSON函數進行查詢。

    select count(*)
from produce
where attributes->"$.delivery.width" > 10 and attributes->"$.delivery.height" > 10 and attributes->"$.delivery.weight" > 10;

    列存執行計畫：

    Project | Exprs: temp_table1.COUNT(0)
 HashGroupby | OutputTable(1): temp_table1 | Grouping: None | Output Grouping: None | Aggrs: COUNT(0)
 CTableScan | InputTable(0): produce | Pred: ((JSON_EXTRACT(produce.attributes, "$.delivery.width") > "10(json)") AND (JSON_EXTRACT(produce.attributes, "$.delivery.height") > "10(json)") AND (JSON_EXTRACT(produce.attributes, "$.delivery.weight") > "10(json)"))

    在千萬級produce表中PolarDB行列存時間對比：

    儲存方式

    SQL耗時

    行存

    9.29 sec

    IMCI（32核）

    0.14 sec

    測試實驗表明PolarDB列存可以高效分析JSON資料，接近行存兩個數量級。由於資料集與查詢模式均不同，線上業務的效能收益可能存在一定差異，需要以實際情況為準。

    虛擬列

    虛擬列（Virtual Columns）作為一種特殊類型的列，其值不是通過插入或更新的方式進行儲存，而是根據表中其他列的值動態計算、合并或者篩選得出。虛擬列可以用於查詢和索引，但不能被直接修改或刪除。虛擬列提供了一個快速存取和處理資料的方法，而無需在每次查詢時重新計算這些資料，因此常用來最佳化查詢和簡化操作。

    PolarDB IMCI實現完整虛擬列功能，支援兩種Generated Column：Virtual Generated Column（預設）與Stored Generated Column。其中Virtual只會將Generated Column計算後值持久化到列存，但不會持久化到行存，每次行存讀取時會重新Realtime Compute；Stored則會將Generated Column計算後值持久化到行存和列存，但會佔用更多磁碟空間。 在PolarDB IMCI生態中推薦使用預設Virtual Generated Column，在節省磁碟空間同時還有列存高效能。

    虛擬列文法：

    col_name data_type [GENERATED ALWAYS] AS (expr)
 [VIRTUAL | STORED] [NOT NULL | NULL]
 [UNIQUE [KEY]] [[PRIMARY] KEY]
 [COMMENT 'string']

    以實際測試資料為例展示列存中虛擬列用法及其行列存效能對比：

    1. 建立表並添加虛擬列及其列索引。

    create table produce (
 id bigint(20) NOT NULL,
 attributes json DEFAULT NULL,
 `delivery_volume` double GENERATED ALWAYS AS (((json_extract(`attributes`,'$.delivery.width') * json_extract(`attributes`,'$.delivery.height')) * json_extract(`attributes`,'$.delivery.weight'))) VIRTUAL
) comment='columnar=1';

    1. 分別使用普通列和虛擬列進行查詢。

      • 普通列查詢

        select count(*)
from produce
where (attributes->"$.delivery.width" * attributes->"$.delivery.height" * attributes->"$.delivery.weight") > 1000;

        列存執行計畫：

        Project | Exprs: temp_table1.COUNT(0)
 HashGroupby | OutputTable(1): temp_table1 | Grouping: None | Output Grouping: None | Aggrs: COUNT(0)
 CTableScan | InputTable(0): produce | Pred: ((CAST JSON_EXTRACT(produce.attributes, "$.delivery.width")/JSON as DOUBLE(38, 31)) * (CAST JSON_EXTRACT(produce.attributes, "$.delivery.height")/JSON as DOUBLE(38, 31)) * (CAST JSON_EXTRACT(produce.attributes, "$.delivery.weight")/JSON as DOUBLE(38, 31)) > 1000.000000)

        在千萬級produce表普通列PolarDB行列存時間：

        儲存方式

        SQL耗時

        行存

        13.43 sec

        IMCI（1核）

        5.72 sec

        IMCI（32核）

        0.24 sec

      • 虛擬列查詢

        select count(*)
from produce
where delivery_volume > 1000;

        列存執行計畫：

        Project | Exprs: temp_table1.COUNT(0)
 HashGroupby | OutputTable(1): temp_table1 | Grouping: None | Output Grouping: None | Aggrs: COUNT(0)
 CTableScan | InputTable(0): produce | Pred: (produce.delivery_volume > 1000.000000)

        在千萬級produce表虛擬列PolarDB行列存時間：

        儲存方式

        SQL耗時

        行存

        14.30 sec

        IMCI（1核）

        0.03 sec

        IMCI（32核）

        0.01 sec

    測試實驗表明PolarDB列存的虛擬列功能可以有效提高查詢效能。由於資料集與查詢模式均不同，線上業務的效能收益可能存在一定差異，需要以實際情況為準。

    總之，PolarDB MySQL版的虛擬列是一種靈活且強大的功能，尤其處理半結構化資料（如JSON類型資料等）時可以直接將不規則的資料存放區為結構化資料，避免中間ETL額外處理邏輯，並且可以使用傳統的SQL查詢語言進行查詢與分析。虛擬列有助於簡化複雜的計算和查詢，提高架構靈活性；不僅可以避免行存資料冗餘，而且在虛擬列上建立列存索引且充分利用列存pruner機制進行過濾，切實提高查詢效能。

    秒級加減列

    當半結構化資料因業務需求而增刪JSON列時，業務表則按需通過DDL加列或刪列來修改表結構，此時高效增刪列就成為必不可少的功能。 半結構化資料發生結構變化時，無需每次都更改表結構，可以在查詢頻率不高時直接通過JSON列式函數Realtime Compute。尤其在PolarDB IMCI的JSON列存版本中，大部分情況下Realtime Compute也能滿足查詢效能。

    PolarDB IMCI實現列存表的秒級加減虛擬列INSTANT DDL功能，可以在瞬間完成加減虛擬列，不會阻塞讀寫，基本不會影響正常查詢業務。

    秒級加虛擬列功能：

    alter table produce add column delivery_volume DOUBLE AS (attributes->"$.delivery.width" * attributes->"$.delivery.height" * attributes->"$.delivery.weight");

    秒級刪虛擬列功能：

    alter table produce drop column delivery_volume;

    列數擴充

    當半結構化資料相關屬性通過虛擬列轉換為大寬表的列時，大寬表的列數會隨著半結構化資料屬性增加而不斷擴大。而原生MySQL的最大列數限制，一般取決於表的儲存引擎的限制，例如InnoDB儲存引擎支援最大列數為1017列（約1K列）。 對於行存而言，目前表最大列數基本滿足絕大多數業務需求。設計關係型資料庫表結構時一般盡量避免使用大寬表，因為過多的列往往加重IO和記憶體負擔而影響效能，例如即使只需要少部分列資料時仍然要讀取整個行而導致大量無效IO，一般會考慮通過拆分表或使用關聯表等方式來最佳化表結構。 對於列存來說大寬表反而成為查詢利器，能夠避免表關聯；由於列存是按列進行儲存，有更好壓縮效果，且讀取指定列時只需要讀取對應列即可，能夠有效減少IO量。

    PolarDB IMCI處理半結構化資料時經常會將半結構化資料中一些屬性按需轉換為表的單獨虛擬列，若屬性數目過多時就可能突破表的最大列數限制。因此PolarDB IMCI在原生MySQL基礎上對InnoDB和列存表的最大列數進行擴充，目前支援最大列數4089列（約4K列）；

    即時分析

    針對半結構化資料分析，PolarDB IMCI實現列式JSON與列存虛擬列等技術，本小節將以GitHub即時事件數目據（2023年7月份）來驗證PolarDB IMCI JSON即時資料分析能力。

    GitHub即時事件JSON資料可以從GH Archive擷取，如wget，下載2023年7月份每個小時的資料；下載後解析並插入github_events表中。

    根據GitHub event types定義github_events表：

    create table github_events (id bigint, type varchar(16), public bit, payload json, repo json, actor json, org json, created_at datetime);

    從Everything You Always Wanted To Know About GitHub選擇並改寫出兩條測試SQL：

    查詢一周內最流行程式設計語言：

    SELECT
    repo_language AS language,
    count(*) AS total
FROM
    github_events
WHERE
    created_at >= "2023-07-25 00:00:00"
    AND created_at <= "2023-07-31 23:59:59"
    AND repo_language IS NOT NULL
GROUP BY
    repo_language
ORDER BY
    total DESC
LIMIT 10;

    給Linux倉庫所有粉絲的倉庫按star數進行排名：

    SELECT repo_name, count(*) AS stars
FROM github_events
WHERE (type = 'WatchEvent') AND (actor_login IN
(
    SELECT actor_login
    FROM github_events
    WHERE (type = 'WatchEvent') AND (repo_name IN ('torvalds/linux')) AND created_at >= "2023-07-31 00:00:00" AND created_at <= "2023-07-31 23:59:59"
)) AND (repo_name NOT IN ('torvalds/linux')) AND created_at >= "2023-07-31 00:00:00" AND created_at <= "2023-07-31 23:59:59"
GROUP BY repo_name
ORDER BY stars DESC
LIMIT 10;

    建立github_events表列索引，並結合上述SQL給其添加actor_login、repo_name與repo_language等虛擬列。

    alter table github_events add column actor_login varchar(256) generated always as (json_unquote(json_extract(`actor`,'$.login'))) virtual, add column repo_name varchar(256) generated always as (json_unquote(json_extract(`repo`,'$.name'))) virtual, add column repo_language varchar(32) generated always as (json_unquote(json_extract(`payload`,'$.pull_request.base.repo.language'))) virtual, comment 'columnar=1';

    配置行存緩衝為500 GB，列存緩衝為128 GB，在熱資料情況下測試結果如下：

    儲存方式

    SQL1耗時

    SQL2耗時

    行存

    45.01 sec

    8.57 sec

    IMCI（1核）

    0.79 sec

    0.54 sec

    IMCI（32核）

    0.04 sec

    0.07 sec

    如上表所見，PolarDB IMCI列式JSON即時分析效能遠高於行存，可以有效應對海量半結構化資料的分析情境。

    擴充Realtime Compute

    結合上述列式JSON、虛擬列、秒級加減列與列數擴充等一系列新功能，PolarDB IMCI提供出一套基于海量半結構化資料分析情境的自動化解決方案：擴充Realtime Compute。

    Realtime Compute作為一種即時資料處理技術，主要是基於連續的資料流進行Realtime Compute與分析。而擴充Realtime Compute可以理解為一種輕量級的Realtime Compute，其主要關注於快速處理資料流並提供即時的計算結果，同時儘可能地減少計算資源使用和降低系統複雜度。與傳統的Realtime Compute系統相比，擴充Realtime Compute更加註重輕量級、快速響應與自動化程度高等方面。

    PolarDB IMCI擴充Realtime Compute主要是通過SQL語句在表結構中用計算運算式或函數來定義資料流處理邏輯並記錄為虛擬列，然後擴充Realtime Compute架構會自動根據業務資料流Realtime Compute出結果並持久化到列存表中，查詢時則應用列存索引快速讀取結果值。整個擴充Realtime Compute流程均已內建到PolarDB IMCI，使用者只需要通過SQL DDL來定義不同資料流處理邏輯（即虛擬列）即可；當業務需求發生變化時同樣也只需要通過SQL DDL來增加修改虛擬列。

    具體到海量半結構化資料的分析情境，使用者可以根據業務需求用JSON函數和JSON列資料屬性定義虛擬列（即資料流處理邏輯），並給該虛擬列添加列索引即可，隨後業務持續的資料流Realtime Compute和儲存將由PolarDB IMCI自動完成，不斷更新大寬表。業務查詢時可以直接使用指定虛擬列，不但可以應用列存索引而且還能避免重新讀取和解析完整JSON列資料等，有效提高查詢效率。即使查詢時直接使用JSON函數方式，PolarDB最佳化器也會根據JSON函數和JSON列來查詢是否存在匹配的虛擬列，若匹配則會優先選擇該虛擬列以提高查詢效能。 當業務需求變更時，業務系統按需增刪改JSON列資料屬性後，使用者只需使用INSTANT DDL來秒級加減列即可，不需要類似傳統數倉那樣額外維護ETL作業邏輯，同時秒級加減列功能可以瞬間完成表結構更改。不僅可以靈活應對業務需求變化，基本沒有額外維持成本，也不會影響正常業務。若新需求的查詢頻率不高則可以不更改表結構，查詢時可以直接用列存版本JSON函數即時解析，也能夠滿足大部分業務需求。

    總之，應對海量半結構化資料的分析情境時，PolarDB IMCI擴充Realtime Compute具有傳統資料庫的靈活性與資料倉儲的高效能。

    案例

    視頻平台

    某視頻平台是中國最受歡迎的線上視頻平台之一，提供電影、電視劇與綜藝節目及直播等視頻或功能，使用者可以通過付費會員獲得更好的觀影體驗。

    在海量會員基礎上，某視頻平台每天會員交易資料量迅速膨脹。某視頻平台會員交易業務的資料獨立為即時表，主要用於業務補償與校正、即時監控訂單扭轉狀態、自動補單與發送權益等，實現無人工處理，自動化處理權益延時到賬等。

    會員交易業務原資料庫系統採用的MySQL分庫分表方案，每一庫表基於MySQL叢集一寫多讀部署方案，通過增加分區數和唯讀節點的方式擴充資料庫叢集來應對業務發展需求，節點間採用Binlog同步。 為了應對快速迭代，交易業務大量採用半結構化JSON類型。隨著業務快速發展，現有資料庫結構描述難以支撐大表高並發JSON查詢，只能通過不斷增加資料庫和分區數來應對，因此不僅營運複雜和影響業務，其成本也逐步增長。

    簡化營運和降本增效成為某視頻平台會員交易業務資料庫新架構的主要目標。新資料庫結構描述採用PolarDB HTAP一體化解決方案，充分利用擴充Realtime Compute與列存來提升海量JSON資料分析效能，同時有效簡化營運。

    金融電商平台

    某金融電商平台是一家總部位於新加坡，成立於2016年的金融科技公司，在東南亞地區提供消費金融服務。該公司提供的服務包括分期付款、信用貸款、虛擬信用卡、電商平台等，在印尼、菲律賓、馬來西亞、泰國和越南等國家擁有數百萬使用者，並已經成為當地領先的消費金融服務提供者之一。

    該平台的訂單業務系統主要分為交易處理與資料分析兩大類，業務事務系統大量採用JSON資料來儲存各類業務屬性，而業務分析系統則需要計算與分析大量業務屬性。

    訂單業務原資料庫系統主要採用的MySQL叢集處理業務事務，用ClickHouse叢集進行業務分析，Apache Flink用於訂閱MySQL資料流並將JSON資料按需轉換為結構化資料進而構造出大寬表，再即時插入到ClickHouse叢集。由於現有資料庫結構描述複雜性，業務經常遇到中間連結遺失資料、分析即時性不高與查詢效能不佳及營運複雜等問題。尤其因業務需求更改而增刪改JSON屬性時，資料庫業務需要不斷修改Flink處理邏輯與更改ClickHouse表結構，而ClickHouse無法做到秒級加減列，在修改大表時不僅耗時間長度且影響查詢效能等。隨著業務快速發展，系統穩定性、簡化營運、分析效能與節約成本成為架構師的當務之急。

    一體化即時交易處理和即時資料分析的PolarDB HTAP雲原生資料庫成為不二之選。新資料庫方案具備極簡架構，由MySQL、DTS、Flink與ClickHouse等多套系統簡化為一套PolarDB。新方案利用PolarDB IMCI擴充Realtime Compute代替Flink實現半結構化資料到結構化資料的自動轉換、秒級加減列功能簡化營運、列數擴充支撐業務極速發展與列存提升分析效能等。