使用IMCI高性能分析归档至OSS的冷数据-云原生数据库 PolarDB-阿里云-云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

当业务数据量持续增长，部分历史数据（如日志、流水、历史订单）访问频率降低但仍需保留分析时，存储成本成为主要负担。您可以使用本功能将PolarDB MySQL版中的冷数据归档至成本更低的阿里云对象存储OSS，并结合列存索引（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的所有要求。

使用IMCI分析归档冷数据