AnalyticDB：基於PolarDB和AnalyticDB構建即時資料倉庫

前提條件

• AnalyticDB for MySQL叢集與PolarDB for MySQL叢集位於同一地區且所屬同一VPC。

• AnalyticDB for MySQL叢集的儲存空間須大於PolarDB MySQL版叢集佔用的儲存空間。

樣本資料

本文樣本的PolarDB for MySQL資料庫名為DB1，並在該庫中建立了一個名為sales_range_columns的分區表。該表以create_date作為分區鍵，共包含12個分區，12條資料。

-- 建立資料庫
CREATE DATABASE IF NOT EXISTS DB1;

-- 建立分區表
CREATE TABLE sales_range_columns (
    dept_no INT(11) NOT NULL AUTO_INCREMENT,
    part_no INT(11) DEFAULT NULL,
    country VARCHAR(20) DEFAULT NULL,
    create_date DATE NOT NULL,
    amount INT(11) DEFAULT NULL,
    PRIMARY KEY (dept_no, create_date)
)
ENGINE=InnoDB
AUTO_INCREMENT=1000001
DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE COLUMNS(create_date) (
    PARTITION p1 VALUES LESS THAN ('2024-01-01') ENGINE = InnoDB,
    PARTITION p2 VALUES LESS THAN ('2024-02-01') ENGINE = InnoDB,
    PARTITION p3 VALUES LESS THAN ('2024-03-01') ENGINE = InnoDB,
    PARTITION p4 VALUES LESS THAN ('2024-04-01') ENGINE = InnoDB,
    PARTITION p5 VALUES LESS THAN ('2024-05-01') ENGINE = InnoDB,
    PARTITION p6 VALUES LESS THAN ('2024-06-01') ENGINE = InnoDB,
    PARTITION p7 VALUES LESS THAN ('2024-07-01') ENGINE = InnoDB,
    PARTITION p8 VALUES LESS THAN ('2024-08-01') ENGINE = InnoDB,
    PARTITION p9 VALUES LESS THAN ('2024-09-01') ENGINE = InnoDB,
    PARTITION p10 VALUES LESS THAN ('2024-10-01') ENGINE = InnoDB,
    PARTITION p11 VALUES LESS THAN ('2024-11-01') ENGINE = InnoDB,
    PARTITION p12 VALUES LESS THAN ('2024-12-01') ENGINE = InnoDB
);

-- 插入12條資料
INSERT INTO sales_range_columns (part_no, country, create_date, amount) VALUES
(101, 'USA', '2024-01-15', 500),
(102, 'UK', '2024-02-20', 700),
(103, 'Germany', '2024-03-10', 600),
(104, 'France', '2024-04-05', 800),
(105, 'Japan', '2024-05-25', 900),
(106, 'China', '2024-06-15', 1000),
(107, 'India', '2024-07-05', 1100),
(108, 'Brazil', '2024-08-20', 1200),
(109, 'Canada', '2024-09-10', 1300),
(110, 'Australia', '2024-10-25', 1400),
(111, 'Italy', '2024-11-15', 1500),
(112, 'Spain', '2024-11-30', 1600);

步驟一：開啟PolarDB for MySQL叢集的Binlog功能

1. 登入PolarDB控制台，在左側導覽列單擊集群列表，選擇叢集所在地區，並單擊目的地組群ID進入叢集詳情頁。

2. 您可以通過以下兩種方式中的任意一種來開啟Binlog：

   • 方式一：

     1. 進入叢集的Binlog管理頁面。

        

     2. 單擊立即開啟按鈕。

     3. 在開啟Binlog對話方塊中，選擇生效方式為立即生效或定時生效。

        若選擇定時生效，請指定具體的生效時間。

        

     4. 單擊确定。

   • 方式二：通過設定參數loose_polar_log_bin的值來開啟Binlog。

     1. 在左側導覽列中選擇配置与管理 > 参数配置。

     2. 找到目標參數loose_polar_log_bin，修改參數值。具體操作請參見修改參數值。

        說明

        • 若您的PolarDB MySQL版叢集版本為MySQL 5.6，當前值修改為ON_WITH_GTID；

        • 若您的PolarDB MySQL版叢集版本為MySQL 5.7或MySQL 8.0，當前值修改為ON。

步驟二：資料同步

您可以選擇以下任意一種方式，將PolarDB for MySQL叢集的庫表結構、全量資料、增量資料同步至AnalyticDB for MySQL。

步驟三：驗證資料一致性

當同步鏈路的狀態為運行中時，可以驗證資料一致性。

步驟四：指定冷熱分離儲存策略

為保證近期資料的查詢效能並降低歷史資料的儲存成本，請按以下步驟對AnalyticDB for MySQL中的表設定冷熱分離：

1. 指定儲存策略。執行以下SQL語句，設定表的儲存策略為mixed（冷熱混合儲存），並將最近6個月資料保留在熱儲存中，其餘歷史資料移轉至冷儲存。

   -- 修改儲存策略
   ALTER TABLE sales_range_columns storage_policy = 'mixed', hot_partition_count = 6;

   變更冷熱階層式存放區策略後，需要該表再次觸發並完成BUILD任務後，新的儲存策略才會生效。

2. 監控遷移進度。執行以下SQL語句，查看冷熱分離任務狀態。

   -- 查看遷移進度
   SELECT 
       table_name,                -- 表名
       source_storage_policy,     -- 原始儲存策略
       dest_storage_policy,       -- 目標儲存策略
       progress,                  -- 進度（百分比）
       status                     -- 當前任務狀態
   FROM 
       information_schema.storage_policy_modify_progress;

   任務狀態（status）：

   • INIT：任務初始化，表示冷熱分離儲存策略已提交但尚未開始執行。

   • RUNNING：任務執行中，表示資料正在從熱儲存向冷儲存遷移。

   • FINISH：任務完成，表示冷熱分離儲存策略已成功應用，資料分布調整完畢。

3. 驗證儲存策略生效。執行以下SQL語句，查看錶當前儲存策略是否生效。

   -- 查看最新表結構
   SHOW CREATE TABLE sales_range_columns;

   查詢結果顯示STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=6，表明冷熱分離儲存策略已成功生效。