大表创建索引 | OceanBase最佳实践 6

在实际应用中，创建索引可以显著提高复杂查询的效率，尤其是在大表上执行频繁查询操作时，以及在数据分析过程中也能加快数据聚合与分析过程。

OceanBase 数据库通过分布式架构，利用多台 OBServer 协同工作来完成索引构建任务。系统会根据数据分区及负载情况灵活分配资源，提高了资源利用率，并且支持全局索引与局部索引，提高了灵活性。

一、V4.x OceanBase 大表建索引最佳实践

在 OceanBase 数据库 V4.x 版本中，针对大表创建索引的操作会消耗大量系统资源，这可能对磁盘空间和 CPU 使用产生显著影响。此外，可能会遇到 OceanBase 的一些限制或已知问题。因此，提前规划资源以避免不必要的麻烦。

本文将基于实践经验，详细阐述大表创建索引时的注意事项。

（一）注意事项和说明

在进行大表索引创建时，请注意以下几点，以优化资源使用并避免潜在问题：

1、磁盘空间

在 V4.2.2 及以后的版本中，局部索引支持根据分区调度索引构建，这意味着局部索引构建所需的最大临时空间与其最大分区的数据量有关。

在 V4.2.3 及以后的版本中，如果执行创建索引的并行度大于等于 8，系统默认会对创建索引过程中产生的临时文件进行压缩，减少创建索引磁盘空间的占用；如果并行度小于 8，且磁盘空间不足时，可通过修改配置项 _ob_ddl_temp_file_compress_func 的值来开启压缩创建索引过程中产生的临时文件。

压缩过程会需要额外的 CPU 资源，所以您需要权衡 CPU 和磁盘资源的情况判断是否需要开启压缩。

#开启压缩，使用指定的压缩算法对创建索引过程中产生的临时文件进行压缩alter system set _ob_ddl_temp_file_compress_func = 'ZSTD';alter system set _ob_ddl_temp_file_compress_func = 'LZ4';#关闭压缩alter system set _ob_ddl_temp_file_compress_func = 'NONE’#根据并行度（大于等于8开启压缩）判断是否开启压缩，压缩算法使用主表压缩算法alter system set _ob_ddl_temp_file_compress_func = 'AUTO’

如果您使用的是 V4.2.1 或更早版本，请参考下文中“创建索引的临时空间估算”部分；如果您使用的是 V4.2.2 版本，请查看下文中“局部索引的临时空间估算”。

① 创建索引的临时空间估算

在索引创建时，会出现空间放大的问题。我们可以用最坏的情况来估算，公式大致为索引表的原始数据量乘以 5。最复杂的部分是如何估算索引表的原始数据量。

我们有两种方式来估算：第一种是评估表的现有统计信息；第二种是通过其他内部表进行简单估算。

🚩 方式一：按照统计信息估算

根据统计信息计算索引空间的步骤如下：

具体 SQL 语句如下：

-- 查找表级统计信息SELECT NUM_ROWS, AVG_ROW_LEN FROM ALL_TAB_STATISTICS WHERE OWNER = 'xxx' AND TABLE_NAME = 'xxx' AND OBJECT_TYPE = 'TABLE';
-- 查找列级统计信息SELECT TABLE_NAME, COLUMN_NAME, AVG_COL_LEN FROM ALL_TAB_COL_STATISTICS WHERE TABLE_NAME = 'xxx' AND OWNER = 'xxx';

🚩 方式二：按照内部表估算

可以使用内部表获得主表上的原始数据量，具体 SQL 语句如下：

SELECT SUM(original_size) FROM __all_virtual_tablet_sstable_macro_info WHERE tablet_id IN (SELECT tablet_id FROM __all_virtual_tablet_to_table_history WHERE table_id = xxx);

将查出的 Size 除以副本数，即可得出主表的未压缩数据量。然后，根据主表的未压缩数据量及索引表各字段占主表的百分比，大致估算索引表未压缩的字段。

② 局部索引表的临时空间估算

局部索引表的临时空间与正在构建的分区数量的数据量总和相关。通常情况下，单个分区的所有建索引线程都会被占满，因此一般只需考虑最大分区的磁盘空间使用量。对于单个分区，可以使用创建索引的临时空间估算方法，只需将数据量范围设定在最大分区的数据量内。

2、关闭合并

为避免因快照点保留而导致主表空间放大的问题，建议关闭合并。

3、CPU

假设当前集群中可以分配 N 个 CPU 给建索引，可以将建索引的并行度设置为 N。

在 V4.0 及之后的版本中，执行 DDL 默认是串行的，可以通过 SESSION 级参数来控制并行度。选择并行度的原则如下：

并行度的设置方法如下：

在调整并行度后，也需要将相应的 parallel_servers_target 调整：

SET GLOBAL parallel_servers_target = 100; -- 该值设置应大于所有并行度之和

4、IO

一般情况下，如果 IO 没有瓶颈，则不需要开启 IO 隔离。

可以通过 IO 隔离限制建索引等 DDL 的 IO 流量，以防在 DDL 过程中影响前台业务流量。

对于 DDL，补数据的方式一般分为两种：通过 DAG（例如删列等）和通过 inner SQL（例如建索引等）。通过 DAG 调度执行时，可以通过 function 级别的隔离关系进行限流，即使用 function 中绑定的 "DDL" 组；而通过 inner SQL 执行时，由于实际执行 inner SQL 的线程仍为 PX 线程，因此隔离方式与用户的隔离方式一致，即使用当前用户绑定的组资源。

以建索引为例，创建 DDL_GROUP 并将当前用户映射到 DDL_GROUP 上，指定 DDL_GROUP 可使用的资源，从而限制建索引任务的资源占用，配置方式如下（以 root 用户为例）：

/* 创建 plan */CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('DDL_PLAN','plan for ddl');/* 创建 group */CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'DDL_GROUP', COMMENT => 'DDL');/* 创建 directive */CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'DDL_PLAN', GROUP_OR_SUBPLAN => 'DDL_GROUP', COMMENT => 'DDL', MAX_IOPS => 60);/* 创建映射关系 */CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('USER', 'root', 'DDL_GROUP');CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('FUNCTION', 'DDL', 'DDL_GROUP');/* plan 生效 */SET GLOBAL resource_manager_plan = 'DDL_PLAN';

上述配置将执行 DDL 任务的 root 用户和 DDL function 映射到 DDL_GROUP，并将 DDL_GROUP 可使用的资源定义为 max_iops=60。此时，DDL 任务占用的 IOPS 资源最大为当前 unit 的 60%。可以通过 IO 诊断表查看隔离效果。

5、内存

建索引时占用内存较多的模块包括：

如果您使用的是 OceanBase V4.2.1 及之前的版本，已知上述模块占用内存较多；V4.2.1 之后的版本如果需要获取更好性能，建议也检查上述模块的内容占用情况。

① 临时文件元数据内存估算

每 1G 的临时文件空间占用 3.75M 的临时文件元数据内存。在 V4.2.1 版本中，已知该模块的内存上限为租户内存的 70%。如果发现此模块内存不足，建议增加租户内存。

② Channel 的 Range 信息

在 V4.2.1 及之前版本的计算方式为：

🧮 并行度 * 并行度 * 分区数 * 1.5KB

在 V4.2.1 之后的版本计算方式为：

🧮 并行度 * 分区数 * 1.5KB

③ 估算 DTL（数据传输层）的缓冲区

计算公式为：

🧮 并行度 * 并行度 * 64KB * 3

（二）最佳实践操作步骤

为了确保在 OceanBase 中成功创建大表索引，以下是一些最佳实践的操作步骤，您可根据您实际业务情况选择最优方式进行操作：

1、评估表的现有统计信息：

运行 SQL 查询以获取表的行数和平均行长，确保获取最新的统计信息。

-- 获取表级统计信息SELECT NUM_ROWS, AVG_ROW_LENFROM ALL_TAB_STATISTICSWHERE OWNER = 'xxx' AND TABLE_NAME = 'xxx' AND OBJECT_TYPE = 'TABLE';

2、临时空间计算：

使用统计信息或内部表进行索引空间的估算，确保预留足够的临时空间。

-- 获取列级统计信息SELECT TABLE_NAME, COLUMN_NAME, AVG_COL_LENFROM ALL_TAB_COL_STATISTICSWHERE TABLE_NAME = 'xxx' AND OWNER = 'xxx';

SELECT SUM(original_size)FROM __all_virtual_tablet_sstable_macro_infoWHERE tablet_id IN (SELECT tablet_id FROM __all_virtual_tablet_to_table_history WHERE table_id = xxx);

3、资源规划：

根据预估的空间和内存需求，调整 ObServer 的内存设置。确保 CPU 和 IO 资源可以满足并行度的需求。计算内存需求：

临时文件压缩配置：如果您使用的 OceanBase 数据库是 V4.2.3 及以上，那么您如果执行创建索引的并行度大于等于 8，系统默认会对创建索引过程中产生的临时文件进行压缩。

如果并行度小于 8，且磁盘空间不足时，可以使用以下命令开启压缩以减少磁盘空间占用：

-- 开启压缩并使用指定的压缩算法ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'ZSTD'; -- 或者 'LZ4'
-- 关闭压缩ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'NONE';
-- 根据并行度（大于等于8开启压缩）判断是否开启压缩，压缩算法使用主表压缩算法ALTER SYSTEM SET _ob_ddl_temp_file_compress_func = 'AUTO';

4、配置参数：

根据需求设置并行度，确保在租户 CPU 的限制之内。

-- 对于 MySQL 模式SET SESSION _FORCE_PARALLEL_DDL_DOP = 8;
-- 对于 Oracle 模式ALTER SESSION FORCE PARALLEL DDL PARALLEL 8;
-- 从 V4.3.0 版本后设置 DDL 操作的并行度ALTER /*+ PARALLEL(2) */ TABLE xxx;

5、关闭合并：

在创建索引前调整合并相关参数，确保主表空间不会因快照点保留而放大。

ALTER SYSTEM SET minor_freeze_times = 500;ALTER SYSTEM SET major_freeze_duty_time = 'disable';

（三）验证结果

二、V3.X OceanBase 大表建索引最佳实践

在 OceanBase 数据库 V3.x 版本中，对大表创建索引会占用大量系统资源，可能对磁盘空间、CPU 使用造成较大影响，也可能会触碰到 OceanBase 数据库的一些限制或已知问题。因此，需要提前做好准备，避免不必要的麻烦。本文基于实践经验，对大表创建索引的注意事项进行详细说明。

（一）注意事项和说明

在大表创建索引过程中，请注意以下几点，以优化资源使用和避免潜在问题：

1、超时时间设置

对大表创建全局索引是一个非常耗时的过程，需要调整超时时间，避免因超时而失败。

调整超时时间可以参考如下 SQL 语句的示例：

alter system set global_index_build_single_replica_timeout = '168h';

2、存储空间配置

存储空间需要参考两个标准：磁盘空间和内存。

① 配置磁盘空间

V3.x 版本全局索引是整张表数据全部构建，局部索引是按照一批分区构建。所以全局索引占用的临时空间量跟整张表的数据量有关，而局部索引跟当时同时构建的分区的数据量总和有关。

如果需要构建全局或局部索引表，需要预留出足够的磁盘空间。

🚩 设置全局索引表临时空间

全局索引表占用的临时空间量与整张表的数据量有关。估算临时空间方法有两种：

✅ 查询索引列的平均长度，加总后乘以表行数的 5 倍。查询索引列的平均列长的 SQL 语句是：

select sum(length(c1)) / count(1) from t1;

✅ 用索引表所有字段的长度与主表字段总长度进行比例估算，乘以表的压缩数据量和压缩比（经验值为 4）。

-- 查询表数据量select sum(data_size) from __all_virtual_meta_table where table_id = xxx and role = 1;

🚩 设置局部索引表使用空间

局部索引表与构建的分区数据量总和有关，通常只需估算最大分区的磁盘空间使用情况。

② 配置内存

创建索引任务的内存使用与其并行线程数有关，其内存计算根据索引类型的不同而不同。

调整内存参数以支持数据排序以参考如下示例：

ob_sql_work_area_percentage * 租户内存大小 = 主表分区数 * 索引表分区数 * 16M

🚩 配置全局索引内存空间

全局索引构建任务的内存使用与分区数量有关，可以按以下公式示例来计算任务内存使用：

内存使用量 = 主表分区数 * 索引表分区数 * 8M

🚩 配置局部索引内存空间

局部索引的构建任务，其内存估算公式为：

内存使用量 = 线程数 * 128M

局部索引的内存使用来源于 500 租户，无需调整参数。

3、关闭合并

关闭合并以避免因保留快照点带来的主表空间放大问题。建议设置如下参数：

-- `minor_freeze_times` 参数调到足够大，比如 500alter system set minor_freeze_times = 500;-- `major_freeze_duty_time` 设置为 `disable`alter system set major_freeze_duty_time = disable;

4、配置 CPU

优化建索引任务的 CPU 资源使用，避免影响业务。参考以下两种方式：

① 预留足够的 CPU 资源给业务，示例如下：

-- 在剩余的 CPU 资源中，留一半的资源空间用于建索引。parallel_servers_target = (物理 CPU 核数 - 业务峰值 CPU 数) / n

② 将建索引任务转移到副本上，减轻主节点负载，执行以下 SQL：

select svr_ip, count(1) from __all_virtual_sys_task_status where comment like 'build index task%' group by svr_ip;

表 __all_virtual_sys_task_status 中任务进入 job=3 阶段表示为排序阶段。具体的可以观察 __all_virtual_sys_task_status 表中 task_type = 'create index' 类型任务所在的机器，将业务流量从这些机器上切走。

5、配置 IO 资源

OceanBase V3.x 版本根据用户响应时间（RT）的变化自动限制建索引的 IO，无需特殊配置。

（二）最佳实践步骤

根据以上注意事项，您可以按照以下步骤大表建索引：

1、调整超时时间：

alter system set global_index_build_single_replica_timeout = '168h';

2、调整磁盘空间与预留空间。

3、关闭合并：

alter system set minor_freeze_times = 500;alter system set major_freeze_duty_time = disable;

4、优化 CPU 配置，确保业务有足够的 CPU 资源。

5、调整内存参数：

ob_sql_work_area_percentage * 租户内存大小 = 主表分区数 * 索引表分区数 * 16M

6、开始建索引操作。

（三）验证结果

通过以上措施，您可以更有效地在 OceanBase 数据库 V3.x 中为大表创建索引，从而提高系统性能和资源利用率。