使用IMCI分析歸檔冷資料

當業務資料量持續增長，部分歷史資料（如日誌、流水、歷史訂單）訪問頻率降低但仍需保留分析時，儲存成本成為主要負擔。您可以使用本功能將PolarDB MySQL版中的冷資料歸檔至成本更低的阿里雲Object Storage Service，並結合列存索引（IMCI）對這些歸檔資料進行高效能的分析查詢，實現降本增效。

適用範圍

在使用本功能前，請確保您的環境滿足以下條件：

產品版本：企業版，且為叢集版系列。
核心版本：MySQL 8.0.2，且核心小版本需為8.0.2.2.30及以上。
歸檔格式：僅對歸檔為ORC格式的資料生效。
IMCI節點：您的叢集需先添加列存索引唯讀節點。

配置冷資料歸檔並使用IMCI查詢

本章節將以一個普通表為例，引導您完成從開啟列存索引、歸檔資料到執行加速查詢的全過程。

流程概述

為表開啟IMCI與執行資料歸檔：為目標表添加列存索引，使其具備被IMCI加速的能力。並將表內資料移轉至OSS並儲存為ORC格式。
查詢歸檔資料：通過叢集地址自動路由或固定訪問IMCI節點，將分析查詢定向到IMCI節點執行。

步驟一：為表開啟IMCI與執行資料歸檔

為表添加列存索引，這是使用IMCI加速查詢的前提。您可以在資料歸檔前或資料歸檔時進行操作。

資料歸檔前

建立列存索引：在CREATE TABLE語句末尾添加comment='columnar=1'。

CREATE TABLE t1(
  a1 INT PRIMARY KEY, 
  a2 INT, 
  a3 INT, 
  a4 INT
) ENGINE=InnoDB COMMENT='columnar=1';

歸檔冷資料：

ALTER TABLE t1 ENGINE = ORC STORAGE OSS;

資料歸檔時

建立資料表：

CREATE TABLE t2(
  a1 INT PRIMARY KEY, 
  a2 INT, 
  a3 INT, 
  a4 INT
) ENGINE=InnoDB;

歸檔冷資料：

ALTER TABLE t2 ENGINE = ORC STORAGE OSS comment='columnar=1';

步驟二：查詢歸檔資料

將分析查詢請求路由到列存索引唯讀節點節點執行，以加速OSS歸檔資料分析。

對於已開啟列存索引的ORC歸檔表，以及包含ORC歸檔分區的混合分區表，通過叢集地址訪問時，系統會自動將查詢轉寄到列存索引唯讀節點執行，通常無需在SQL語句中額外添加Hint。

方法一：（推薦）使用叢集地址自動路由

說明

自動路由依賴叢集中已添加列存索引唯讀節點。

需要注意的是，這裡的自動路由並非基於最佳化器代價（cost）在行存與列存之間擇優分流，而是直接短路路由：只要目標表滿足上述條件（已開啟列存索引且為ORC歸檔表或含ORC歸檔分區的混合分區表），通過叢集地址訪問的查詢就會被直接轉寄到列存索引唯讀節點執行，不經過行列代價對比。

您可以使用叢集地址串連資料庫，並直接執行查詢。

SELECT COUNT(*) FROM t1;

方法二：固定訪問IMCI節點

您也可以在控制台上新增一個自訂叢集地址並僅選擇列存索引唯讀節點，然後使用該地址串連資料庫。在此串連上執行的查詢將預設嘗試使用列存索引。

該方式適用於希望將歸檔資料分析查詢固定發送到列存索引唯讀節點的情境。

處理分區表的混合查詢

對於分區表，您可以只歸檔部分歷史分區，實現冷熱資料分離。查詢時，IMCI能夠智能地對未歸檔的InnoDB分區和已歸檔的OSS分區進行混合查詢。

為分區表添加列存索引：僅支援為整表添加列存索引，不支援為單個分區獨立設定。

CREATE TABLE t_partition(
  a1 INT PRIMARY KEY,
  a2 INT,
  a3 INT,
  a4 INT
) COMMENT='columnar=1'
PARTITION BY RANGE(a1) (
  PARTITION p1 VALUES LESS THAN (20),
  PARTITION p2 VALUES LESS THAN (40),
  PARTITION p3 VALUES LESS THAN (60),
  PARTITION p4 VALUES LESS THAN MAXVALUE
);

歸檔指定分區：歸檔單個或多個分區。

ALTER TABLE t_partition CHANGE PARTITION p1 ENGINE = ORC FORCE STORAGE OSS;

執行混合查詢。
當查詢需要同時訪問已歸檔的OSS分區和未歸檔的InnoDB分區時，您需要先在會話中開啟混合查詢開關。
```
-- 開啟混合查詢支援
SET hybrid_partition_query_mix_engine_enabled = ON;

-- 執行查詢
SELECT COUNT(*) FROM t_partition;
```

分區表的路由規則

對於包含ORC歸檔分區的混合分區表，系統以整張分區表為粒度識別執行路徑。只要該分區表已開啟列存索引且包含ORC歸檔分區，通過叢集地址訪問時，相關查詢會自動路由到列存索引唯讀節點執行。即使查詢經過分區裁剪，或通過PARTITION子句僅訪問未歸檔的InnoDB分區，也仍按該規則處理。

SELECT COUNT(*) FROM t_partition;

如果希望當前會話不使用列存索引執行查詢，可以關閉use_imci_engine。設定後，查詢不會觸發上述列存索引自動路由，系統會按行存執行路徑處理。

SET use_imci_engine = OFF;

SELECT COUNT(*) FROM t_partition;

如需恢複使用列存索引，可重新開啟該參數。

SET use_imci_engine = ON;

效能參考

以下資料基於TPC-H 100 GB標準測試集，在32核256 GB的PolarDB MySQL版叢集上進行測試。結果顯示，將資料歸檔至OSS後，通過IMCI進行分析查詢，相比直接在InnoDB行存上查詢，整體加速比約為35倍。對於全表彙總、寬表掃描等典型分析型查詢（AP），加速效果尤為顯著。

說明

本文的TPC-H的實現基於TPC-H的基準測試，並不能與發行的TPC-H基準測試結果相比較，本文中的測試並不完全符合TPC-H的所有要求。