在 OceanBase 數據庫運維中，創建索引是高頻操作之一，尤其面對海量數據時，索引構建可能持續數小時甚至更久。實時掌握建索引進度、判斷當前所處階段，不僅能避免運維人員盲目等待，更能及時發現異常並介入處理。
本文基於 OceanBase 2.2.77 版本，從索引創建的核心階段出發，結合系統表查詢實戰，帶大家全方位監控建索引全流程。
一、OceanBase 建索引的四大核心階段
在監控進度前，需明確索引創建的完整生命週期，每個階段對應特定的系統狀態特徵：
1、準備階段：生成索引表的元數據信息，索引表設置成只寫狀態；等待之前未往索引表插入過數據的事務結束，獲取構建快照點。
2、構建階段：基於獲取的快照點掃描主表數據，並寫入到索引表基線SSTable中，而增量事務產生的數據寫入到Memtable中，最後完成基線補全也就是將快照點後的dml同步到索引中。
3、拷貝階段：索引構建是單副本上完成的，構建完成後會通過一致性算法同步到索引表多個副本上。
4、收尾階段：進行數據校驗，對於唯一索引還需要進行唯一性校驗，一切完成後索引表設置成可讀寫狀態，否則設置成不可用狀態。
以下案例版本為2.2.77
二、觀察全局索引創建過程
全局索引主要依靠觀察__all_index_build_stat信息表定位創建到哪個階段了：
該表的status列值對應如下：
- 0 = invalid，創建本地索引時觀察該值為0
- 1 = 單副本構建
- 2 = 多副本拷貝
- 3 = 校驗唯一性
- 4 = 校驗和比對
- 5 = 設置索引生效
- 6 = 構建索引失敗
- 7 = 構建索引完成
三、按階段查詢操作指南
1、準備階段：
- a. 查看建表語句：
mysql> show create table t1;
--------------------+------------------
| Table | Create Table |
+-------+-----------------------------
| t1 | CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`, `k`)
) AUTO_INCREMENT = 12000001 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.0' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by hash(k)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4) |- b. 執行建索引語句：
mysql> create index idx_pad on t1(pad) global;- c. 在等待事務結束時，查詢
__all_index_build_stat表status列為1，快照點snapshot為0：
mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+----------
| gmt_create | gmt_modified | tenant_id | data_table_id | index_table_id | status | snapshot | schema_version |
+----------------------------+----------------------------+---------
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:19:25.503550 | 1014 | 1114904790615059 | 1114904790615064 | 1 | 0 | 1649157565414240 |2、構建階段：
- a. 觀察
__all_index_build_stat，status為1，已獲取快照點snapshot
觀察__all_virtual_table_mgr，已完成memtable創建（table_type=0）
觀察__all_virtual_sys_task_status，每個分區都有一個create index任務
觀察__all_virtual_rebalance_task_stat，每個分區都有一個SQL_BACKGROUND_DIST_TASK任務
觀察__all_index_checksum，暫無checksum信息
mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------
| gmt_create | gmt_modified | tenant_id | data_table_id | index_table_id | status | snapshot | schema_version |
+----------------------------+----------------------------+---------
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:19:47.551521 | 1014 | 1114904790615059 | 1114904790615064 | 1 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+-----------------
| svr_ip | svr_port | tenant_id | table_type | table_id | partition_id | index_id | base_version | multi_version_start | snapshot_version | version | logical_data_version | size | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+-------------------+-----------+-----+-----------+-----------+
| 10.10.10.10 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.11 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.12 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
+-------------+----------+-----------+------------+-----------------
mysql> select * from __all_virtual_sys_task_status;
+----------------------------+--------------+-----------------------
| start_time | task_type | task_id | svr_ip | svr_port | tenant_id | comment | is_cancel |
+----------------------------+--------------+-----------------------
| 2023-04-05 19:19:48.632648 | create index | YB420B973EA3-0005D1DBCF659889 | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:0, task_cnt:5} | 0 |
| 2023-04-05 19:19:48.633941 | create index | YB420B973EA3-0005D1DBCF65988A | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:1, task_cnt:5} | 0 |
| 2023-04-05 19:19:48.635211 | create index | YB420B973EA3-0005D1DBCF65988B | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:2, task_cnt:5} | 0 |
| 2023-04-05 19:19:48.636490 | create index | YB420B973EA3-0005D1DBCF65988C | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:3, task_cnt:5} | 0 |
| 2023-04-05 19:19:48.637767 | create index | YB420B973EA3-0005D1DBCF65988D | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:4}, task_id:4, task_cnt:5} | 0 |
+----------------------------+--------------+-----------------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id | partition_id | partition_count | source | data_source | destination | offline | is_replicate | task_type | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
| 1014 | 1114904790615059 | 0 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 56 | 326548 |
| 1014 | 1114904790615059 | 1 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 1374 | 325151 |
| 1014 | 1114904790615059 | 2 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 2550 | 323916 |
| 1014 | 1114904790615059 | 3 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 3793 | 322623 |
| 1014 | 1114904790615059 | 4 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 5025 | 321342 |
+-----------+------------------+--------------+-----------------+----+----------------+
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
| 0 |
+----------+- b. 觀察
__all_virtual_sys_task_status，有一個 create index任務
觀察__all_virtual_rebalance_task_stat，有一個SQL_BACKGROUND_DIST_TASK任務
觀察__all_index_checksum，發現有5個分區的索引列（pad）、主鍵列（id/k）信息，5x3=15
mysql> select * from __all_virtual_sys_task_status;
+----------------------------+--------------+-----------------------------------------------------+-----------+
| start_time | task_type | task_id | svr_ip | svr_port | tenant_id | comment | is_cancel |
+----------------------------+--------------+-----------------------------------------------------+-----------+
| 2023-04-05 19:19:50.958727 | create index | YB420B973EA3-0005D1DBCF65988E | 10.10.10.10 | 2882 | 1014 | build index task, tenant_id=1014, task_id={ob_job_id:{ob_execution_id:{server:"0.0.0.1:1", execution_id:4611686018427387907, hash:13208655902673732501}, job_id:3}, task_id:0, task_cnt:1} | 0 |
+----------------------------+--------------+-----------------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id | partition_id | partition_count | source | data_source | destination | offline | is_replicate | task_type | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
| 1014 | 1114904790615064 | 0 | 0 | 0.0.0.0:0 | 0.0.0.0:0 | 10.10.10.10:2882 | 0.0.0.0:0 | No | SQL_BACKGROUND_DIST_TASK | Yes | No | 41 | 3646335 |
+-----------+------------------+--------------+-----------------+----+----------------+
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
| 15 |
+----------+- c. 觀察
__all_virtual_sys_task_status，暫無任務信息
觀察__all_virtual_rebalance_task_stat，暫無任務信息
觀察__all_index_checksum，發現多出了全局索引表的checksum信息
mysql> select * from __all_virtual_sys_task_status;
Empty set (0.01 sec)
mysql> select * from __all_virtual_rebalance_task_stat;
Empty set (0.01 sec)
mysql> select count(*) from __all_index_checksum where gmt_create>'2023-04-05 19:19:00' and tenant_id=1014;
+----------+
| count(*) |
+----------+
| 18 |
+----------+- d. 觀察
__all_index_checksum內容（以下內容為重建索引獲取，會存在索引表id不一致情況）
mysql> select * from __all_index_checksum where gmt_create>'2023-04-05 20:10:00' and tenant_id=1014 order by column_id;
+----------------------------+----------------------------+---------
| gmt_create | gmt_modified | execution_id | tenant_id | table_id | partition_id | column_id | task_id | checksum | checksum_method |
+----------------------------+----------------------------+---------
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 | 1014 | 1114904790615059 | 0 | 16 | 0 | 4294189217994805 | 2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 | 1014 | 1114904790615059 | 1 | 16 | 1 | 4293460459368680 | 2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 | 1014 | 1114904790615068 | 0 | 16 | 0 | 21474837339484641 | 2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 | 1014 | 1114904790615059 | 2 | 16 | 2 | 4294471659607893 | 2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 | 1014 | 1114904790615059 | 4 | 16 | 4 | 4291068240165421 | 2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 | 1014 | 1114904790615059 | 3 | 16 | 3 | 4301647762347842 | 2 |
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 | 1014 | 1114904790615059 | 0 | 17 | 0 | 4294529994534407 | 2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 | 1014 | 1114904790615068 | 0 | 17 | 0 | 21478992387389366 | 2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 | 1014 | 1114904790615059 | 4 | 17 | 4 | 4295367552394642 | 2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 | 1014 | 1114904790615059 | 3 | 17 | 3 | 4296764324236270 | 2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 | 1014 | 1114904790615059 | 2 | 17 | 2 | 4299921221083111 | 2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 | 1014 | 1114904790615059 | 1 | 17 | 1 | 4292409295140936 | 2 |
| 2023-04-05 20:10:45.442407 | 2023-04-05 20:10:45.442407 | 4611686018427387911 | 1014 | 1114904790615059 | 2 | 19 | 2 | 4296268846119605 | 2 |
| 2023-04-05 20:10:45.391626 | 2023-04-05 20:10:45.391626 | 4611686018427387911 | 1014 | 1114904790615059 | 3 | 19 | 3 | 4300722872371463 | 2 |
| 2023-04-05 20:10:45.394463 | 2023-04-05 20:10:45.394463 | 4611686018427387911 | 1014 | 1114904790615059 | 1 | 19 | 1 | 4289982707443986 | 2 |
| 2023-04-05 20:10:45.377162 | 2023-04-05 20:10:45.377162 | 4611686018427387911 | 1014 | 1114904790615059 | 4 | 19 | 4 | 4293685226392460 | 2 |
| 2023-04-05 20:10:45.452655 | 2023-04-05 20:10:45.452655 | 4611686018427387911 | 1014 | 1114904790615059 | 0 | 19 | 0 | 4292840718037296 | 2 |
| 2023-04-05 20:11:45.061528 | 2023-04-05 20:11:45.061528 | 4611686018427387911 | 1014 | 1114904790615068 | 0 | 19 | 0 | 21473500370364810 | 2 |
+----------------------------+----------------------------+---------
mysql> select 4294189217994805+4293460459368680+4294471659607893+4291068240165421+4301647762347842;
+-------------------------------------------------------------------
| 4294189217994805+4293460459368680+4294471659607893+4291068240165421+4301647762347842 |
+--------------------------------------------------------------------------------------+
| 21474837339484641 |
+-------------------------------------------------------------------3、拷貝階段：
觀察__all_index_build_stat，status為2，已進入多副本拷貝階段
觀察__all_virtual_table_mgr，發現正在拷貝基線sstable（table_type=1）
觀察__all_virtual_sys_task_status，發現migration任務
觀察__all_virtual_rebalance_task_stat，發現COPY_SSTABLE任務
mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------+------------------+
| gmt_create | gmt_modified | tenant_id | data_table_id | index_table_id | status | snapshot | schema_version |
+----------------------------+----------------------------+---------+------------------+
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:20:53.331745 | 1014 | 1114904790615059 | 1114904790615064 | 2 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------+------------------+
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
| svr_ip | svr_port | tenant_id | table_type | table_id | partition_id | index_id | base_version | multi_version_start | snapshot_version | version | logical_data_version | size | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
| 10.10.10.11 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.10 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.11 | 2882 | 1014 | 1 | 1114904790615064 | 0 | 1114904790615064 | 0 | 1649157587510214 | 1649157587510214 | 298 | 298 | 293289439 | 0 | 2 | 0 | 0 |
| 10.10.10.12 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.10 | 2882 | 1014 | 1 | 1114904790615064 | 0 | 1114904790615064 | 0 | 1649157587510214 | 1649157587510214 | 298 | 298 | 293289439 | 0 | 3 | 0 | 0 |
+-------------+----------+-----------+------------+------------------------+-----------+-----+-----------+-----------+
mysql> select * from __all_virtual_sys_task_status;
+----------------------------+----------------------------+----------------------------------------------+-----------+
| start_time | task_type | task_id | svr_ip | svr_port | tenant_id | comment | is_cancel |
+----------------------------+----------------------------+----------------------------------------------+-----------+
| 2023-04-05 19:20:56.134953 | group partition migration | YB420B277B81-0005C4F9E6B7E579 | 10.10.10.12 | 2882 | 1014 | group partition migration executor: group_task_id=YB420B277B81-0005C4F9E6B7E579 partition_count=1 partition_id=0 key={tid:1114904790615064, partition_id:0, part_cnt:0}, op_type=COPY_GLOBAL_INDEX_OP, src="10.10.10.10:2882", dest="10.10.10.12:2882", | 0 |
| 2023-04-05 19:20:56.137597 | single partition migration | YB420B277B81-0005C4F9C3F84711 | 10.10.10.12 | 2882 | 1014 | single partition migration: group_task_id=YB420B277B81-0005C4F9E6B7E579 key={tid:1114904790615064, partition_id:0, part_cnt:0}, op_type=COPY_GLOBAL_INDEX_OP, src="10.10.10.10:2882", dest="10.10.10.12:2882",dest_memstore_percent=100 | 0 |
+----------------------------+----------------------------+----------------------------------------------+-----------+
mysql> select * from __all_virtual_rebalance_task_stat;
+-----------+------------------+--------------+-----------------+----+----------------+
| tenant_id | table_id | partition_id | partition_count | source | data_source | destination | offline | is_replicate | task_type | is_scheduled | is_manual | waiting_time | executing_time |
+-----------+------------------+--------------+-----------------+----+----------------+
| 1014 | 1114904790615064 | 0 | 0 | 10.10.10.10:2882 | 10.10.10.10:2882 | 10.10.10.12:2882 | 0.0.0.0:0 | No | COPY_SSTABLE | Yes | No | 57 | 452611 |
+-----------+------------------+--------------+-----------------+---4、收尾階段：
觀察__all_index_build_stat，status為5，設置索引表生效
觀察__all_virtual_table_mgr，發現基線sstable（table_type=1）已補齊
補充：查詢select index_status from__all_virtual_table where table_id= 索引表id，為2説明索引表構建成功
mysql> select * from __all_index_build_stat;
+----------------------------+----------------------------+---------+------------------+
| gmt_create | gmt_modified | tenant_id | data_table_id | index_table_id | status | snapshot | schema_version |
+----------------------------+----------------------------+---------+------------------+
| 2023-04-05 19:19:25.503550 | 2023-04-05 19:20:58.114574 | 1014 | 1114904790615059 | 1114904790615064 | 5 | 1649157587510214 | 1649157565414240 |
+----------------------------+----------------------------+---------+------------------+
mysql> select * from __all_virtual_table_mgr where index_id in (select index_table_id from __all_index_build_stat);
+-------------+----------+-----------+------------+------------------+-----------------+-----------+
| svr_ip | svr_port | tenant_id | table_type | table_id | partition_id | index_id | base_version | multi_version_start | snapshot_version | version | logical_data_version | size | is_active | ref | write_ref | trx_count |
+-------------+----------+-----------+------------+------------------+-----------------+-----------+
| 10.10.10.11 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.10 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.11 | 2882 | 1014 | 1 | 1114904790615064 | 0 | 1114904790615064 | 0 | 1649157587510214 | 1649157587510214 | 298 | 298 | 293289439 | 0 | 2 | 0 | 0 |
| 10.10.10.12 | 2882 | 1014 | 0 | 1114904790615064 | 0 | 1114904790615064 | 1649095210272570 | 1649095210272570 | 9223372036854775807 | 0 | 0 | 0 | 1 | 2 | 0 | 0 |
| 10.10.10.10 | 2882 | 1014 | 1 | 1114904790615064 | 0 | 1114904790615064 | 0 | 1649157587510214 | 1649157587510214 | 298 | 298 | 293289439 | 0 | 2 | 0 | 0 |
| 10.10.10.12 | 2882 | 1014 | 1 | 1114904790615064 | 0 | 1114904790615064 | 0 | 1649157587510214 | 1649157587510214 | 298 | 298 | 293289439 | 0 | 2 | 0 | 0 |
+-------------+----------+-----------+------------+-----------------通過以上方法，可實現 OceanBase 建索引全流程的可視化監控，精準掌握每個階段的進度與狀態，為運維決策提供數據支撐。如果在實操中遇到異常場景，歡迎在評論區留言交流，下次見！
更多技術分享可掃碼關注查看
