本文不详细讨论 Oracle 对 UPDATE 的实现方式，如果你有兴趣，作者觉得你可以通过 dump 对应行的 dba 信息查看是否原地更新，或者借助 logminer + 附加日志的方式查看 redo 日志中的 redo entry。
考虑到存储引擎、表结构、索引设置、触发器、约束以及应用程序的具体情况，数据库可能对 UPDATE 采用不同的操作方式，直觉上觉得Oracle 这样的存储结构，在原地 UPDATE 的效率应该更高，尤其在 IO 资源稀缺的年代，DBA 们绞尽脑汁为了减少一个物理 IO 的情况下。
借助 Gemini 的结论，Oracle 的可能行为如下：
行迁移： 当更新操作导致行数据过大或者行的位置需要发生变化时，Oracle 可能会将整行数据移动到新的数据块中，这个过程类似于先删除再插入。
索引重建： 在某些情况下，Oracle 可能会触发索引重建，这个过程会涉及到对索引的完全重建，可以看作是先删除旧索引，再插入新索引。
分区表操作： 当对分区表进行更新操作时，如果更新涉及到跨分区的数据移动，Oracle 可能会执行类似于 DELETE+INSERT 的操作。
不过本文要讨论的是 OceanBase ，在没有厂商 internal 的文档的前提下，我们能借助的工具，有，但是不多。本文以来 ob_admin 工具来做分析。
ob_admin 的官方介绍及安装：
本次测试的环境信息如下：
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 2453701
Server version: OceanBase 4.2.1.10 (r110020012024120420-760d3c19482ba306e5a10e214a8ec64422bbf1f0) (Built Dec 4 2024 20:33:18)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
OB_SYS_4.2.1 [root@oceanbase:2883] > 首先在 Oracle 租户中，创建一张表，这里开启一下后来者视角，创建一个多种列类型的分区表，有主键，分区键，唯一键，索引，普通无索引的列：
CREATE TABLE "HXDB"."T_UPDATE_CLOG" (
"ID" NUMBER(10) DEFAULT NULL,
"NO_INDEX_COL" VARCHAR2(12) DEFAULT NULL,
"UK_COL" VARCHAR2(12) DEFAULT NULL,
"PART_COL" DATE DEFAULT NULL,
CONSTRAINT "ID_PK" PRIMARY KEY ("ID", "PART_COL"),
CONSTRAINT "UK_CONSTRAINT_COL" UNIQUE ("UK_COL")
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
partition by range("PART_COL")
(partition "p1" values less than (TO_DATE(' 1989-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p2" values less than (TO_DATE(' 1990-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p3" values less than (TO_DATE(' 1991-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p4" values less than (TO_DATE(' 1992-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p5" values less than (TO_DATE(' 1993-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p6" values less than (TO_DATE(' 1994-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p7" values less than (TO_DATE(' 1995-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition "p8" values less than (TO_DATE(' 1996-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));
COMMENT ON TABLE "HXDB"."T_UPDATE_CLOG" IS 'Test for update ';插入测试数据：
insert into hxdb.t_update_clog values (111123110,'col2','c111123110','1989-12-03 00:00:00');
insert into hxdb.t_update_clog values (112123111,'col2','c112123111','1989-12-13 00:00:00');
insert into hxdb.t_update_clog values (113123112,'col2','c113123112','1989-12-21 00:00:00');
insert into hxdb.t_update_clog values (114123113,'col2','c114123113','1989-12-22 00:00:00');
insert into hxdb.t_update_clog values (115123114,'col2','c115123114','1989-12-23 00:00:00');
insert into hxdb.t_update_clog values (116123115,'col2','c116123115','1990-12-03 00:00:00');
insert into hxdb.t_update_clog values (116123116,'col2','c116123116','1990-12-04 00:00:00');
insert into hxdb.t_update_clog values (116123117,'col2','c116123117','1990-12-05 00:00:00');
insert into hxdb.t_update_clog values (116123118,'col2','c116123118','1990-12-06 00:00:00');
insert into hxdb.t_update_clog values (117123119,'col2','c117123119','1991-12-07 00:00:00');
insert into hxdb.t_update_clog values (118123120,'col2','c118123120','1991-12-18 00:00:00');
insert into hxdb.t_update_clog values (119123121,'col2','c119123121','1991-12-21 00:00:00');
insert into hxdb.t_update_clog values (120123122,'col2','c120123122','1991-12-22 00:00:00');
insert into hxdb.t_update_clog values (121123123,'col2','c121123123','1991-12-23 00:00:00');
insert into hxdb.t_update_clog values (122123124,'col2','c122123124','1992-12-03 00:00:00');
insert into hxdb.t_update_clog values (123123125,'col2','c123123125','1992-12-13 00:00:00');
insert into hxdb.t_update_clog values (124123126,'col2','c124123126','1992-12-21 00:00:00');
insert into hxdb.t_update_clog values (125123127,'col2','c125123127','1992-12-22 00:00:00');
insert into hxdb.t_update_clog values (126123128,'col2','c126123128','1992-12-23 00:00:00');
insert into hxdb.t_update_clog values (127123129,'col2','c127123129','1993-12-03 00:00:00');
insert into hxdb.t_update_clog values (127123130,'col2','c127123130','1993-12-13 00:00:00');
insert into hxdb.t_update_clog values (127123135,'col2','c127123135','1993-12-14 00:00:00');
insert into hxdb.t_update_clog values (128123131,'col2','c128123131','1993-12-21 00:00:00');
insert into hxdb.t_update_clog values (129123132,'col2','c129123132','1993-12-22 00:00:00');
insert into hxdb.t_update_clog values (130123133,'col2','c130123133','1993-12-23 00:00:00');
insert into hxdb.t_update_clog values (128123136,'col2','c128123136','1993-12-24 00:00:00');
insert into hxdb.t_update_clog values (129123137,'col2','c129123137','1993-12-25 00:00:00');
insert into hxdb.t_update_clog values (130123138,'col2','c130123138','1993-12-26 00:00:00');
insert into hxdb.t_update_clog values (127123134,'col2','c127123134','1993-12-29 00:00:00');
insert into hxdb.t_update_clog values (131123139,'col2','c131123139','1994-12-03 00:00:00');
insert into hxdb.t_update_clog values (132123140,'col2','c132123140','1994-12-13 00:00:00');
insert into hxdb.t_update_clog values (133123141,'col2','c133123141','1994-12-21 00:00:00');
insert into hxdb.t_update_clog values (134123142,'col2','c134123142','1994-12-22 00:00:00');
insert into hxdb.t_update_clog values (135123143,'col2','c135123143','1994-12-23 00:00:00');
insert into hxdb.t_update_clog values (136123144,'col2','c136123144','1995-12-03 00:00:00');
insert into hxdb.t_update_clog values (137123145,'col2','c137123145','1995-12-13 00:00:00');
insert into hxdb.t_update_clog values (138123146,'col2','c138123146','1995-12-21 00:00:00');
insert into hxdb.t_update_clog values (139123147,'col2','c139123147','1995-12-22 00:00:00');
insert into hxdb.t_update_clog values (140123148,'col2','c140123148','1995-12-23 00:00:00');
insert into hxdb.t_update_clog values (140123149,'col2','c140123149','1995-12-24 00:00:00');
--
commit;接下来要确定这个租户的日志流是哪个，由于 4.x 版本和 3.x 版本的日志流架构发生了非常大的变化，这里需要确认我们执行测试的时候，具体写的是哪个 clog 文件。如下：
OB_SYS_4.2.1 [root@oceanbase:2883] > select a.TENANT_ID,a.DATABASE_NAME,a.TABLE_NAME,a.TABLE_ID,a.PARTITION_NAME,a.TABLET_ID,a.LS_ID,a.SVR_IP,a.ROLE from CDB_OB_TABLE_LOCATIONS a where table_name ='T_UPDATE_CLOG';
+-----------+---------------+---------------+----------+----------------+-----------+-------+--------------+--------+
| TENANT_ID | DATABASE_NAME | TABLE_NAME | TABLE_ID | PARTITION_NAME | TABLET_ID | LS_ID | SVR_IP | ROLE |
+-----------+---------------+---------------+----------+----------------+-----------+-------+--------------+--------+
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p1 | 262685 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p2 | 262686 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p3 | 262687 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p4 | 262688 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p5 | 262689 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p6 | 262690 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p7 | 262691 | 1001 | 172.16.0.147 | LEADER |
| 1004 | HXDB | T_UPDATE_CLOG | 592893 | p8 | 262692 | 1001 | 172.16.0.147 | LEADER |
+-----------+---------------+---------------+----------+----------------+-----------+-------+--------------+--------+
8 rows in set (0.162 sec)从查询结果来看，这个表的每一个 tablet 都在 1001 这个日志流上，那么简单了，我们只需要 dump 这个日志流的 clog 即可。
找到对应日志流的目录，我的环境如下目录：
/data/log1/observer147/clog/tenant_1004/1001/log场景 1：更新主键列，即 ID 列
OB_ORACLE_4.2 [HXDB@HXDB] > update hxdb.t_update_clog set id=id+1 where id=140123149;
Query OK, 1 row affected (0.011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
OB_ORACLE_4.2 [HXDB@HXDB] > commit;
Query OK, 0 rows affected (0.001 sec)ob_admin 的简单用法：
Usage: ob_admin io_bench
ob_admin slog_tool
ob_admin dump_ckpt ## dump slog checkpoint, only support for 4.x
ob_admin dumpsst
ob_admin dump_enum_value
ob_admin dump_key
ob_admin log_tool ## './ob_admin log_tool' for more detail
ob_admin -h127.0.0.1 -p2883 xxx
ob_admin -h127.0.0.1 -p2883 (-sintl/-ssm -mbkmi/-mlocal) [command]
## The options in parentheses take effect when ssl enabled.
ob_admin -S unix_domain_socket_path xxx[admin@observer147 log]$ ob_admin log_tool
succ to open, filename=/home/admin/ob_admin_log/ob_admin.log, fd=3, wf_fd=2
succ to open, filename=/home/admin/ob_admin_log/ob_admin_rs.log, fd=4, wf_fd=2
Usages:
$ob_admin log_tool dump_log log_files ## ./ob_admin log_tool dump_log log_files... ##将log文件中的内容全部打印出来
$ob_admin log_tool dump_tx_format log_files ## ./ob_admin log_tool dump_tx_format log_files ##将log文件中的事务相关内容以json格式打印
$ob_admin log_tool dump_filter 'filter_conditions' log_files ## ./ob_admin log_tool dump_filter 'tx_id=xxxx;tablet_id=xxx' '$path'## 按照过滤条件将log文件中的事务相关内容打印,目前支持按照事务id(tx_id=xxxx),tablet_id(tablet_id=xxxx)进行过滤，多个条件之间以;隔开
$ob_admin log_tool stat log_files ## ./ob_admin log_tool stat 1
$ob_admin log_tool dmp_meta log_files ## ./ob_admin log_tool dump_meta 1
一些注意事项:
1. 为避免在clog目录生成一些ob_amdin的输出文件，强烈建议使用绝对路径
2. log_files 支持绝对路径、相对路径
3. log_files 支持同时解析多个文件
4. 支持解析归档文件
5. 如何通过LSN快速定位日志:
1. 获取文件ID: BLOCK_ID=LSN/(64MB-4KB)
2. 根据LSN去输出文件中执行grep操作使用 ob_admin dump 最新的 clog 文件，这里比较琐碎，需要找事务号，tablet id，去找对应的记录，不赘述了，展示结果：
OB_SYS_4.2.1 [root@oceanbase:2883] > select tx_id from gv$ob_sql_audit where QUERY_SQL='update hxdb.t_update_clog set id=id+1 where id=140123149';
+----------+
| tx_id |
+----------+
| 94248239 |
+----------+
1 row in set (0.067 sec)ob_admin 的命令如下：
ob_admin log_tool dump_filter 'tx_id=94248239' /data/log1/observer147/clog/tenant_1004/1001/log/1046
--其中 1046 为当前的 clog 文件输出的结果格式化以后如下：
{
BlockId: 1046: LSN: 70251624347 TxID: 94248239 scn: 1735060894337093699 TxBlockHeader: {
org_cluster_id: 1709740693,
log_entry_no: 0,
tx_id: {
txid: 94248239
},
scheduler: "172.16.0.147:2882"
}### < TxRedoLog >: {
txctxinfo: {
LOG_TYPE: 1,
mutator_size_: 617,
ctx_redo_info_.cluster_version_: 17180000522
}
MutatorMeta: 0x7ffc73a46ad8 data_crc = bc04c342 meta_size = 28 data_size = 589 row_count = 5 MutatorRows: {
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 262691
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735060891761981,
create_timestamp: 1735060891762162,
create_schema_version: 1735057916462984
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262691
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "140123149"
} {
"DATETIME": "1995-12-24 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f428e2,
hex: 010000 C00D1C5A08,
int: 601824345588760577,
num_digit0: 140123149
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f428ea,
hex: 000056 FE91E90200,
int: 819763200000000
}
2: NOP 3: NOP
}
OldRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f4290c,
hex: 010000 C00D1C5A08,
int: 601824345588760577,
num_digit0: 140123149
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42914,
hex: 000056 FE91E90200,
int: 819763200000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7ff774f4291c,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7ff774f42920,
hex: 63313430313233313439,
cstr: c140123149
}
}
DmlFlag: DELETE ModifyCount: 3 AccChecksum: 2621153522 Version: 1735058076972385 Flag: 0 SeqNo: 1735060891761981 NewRowSize: 41 OldRowSize: 55 ColumnCnt: 4
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262691
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "140123150"
} {
"DATETIME": "1995-12-24 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42990,
hex: 010000 C00E1C5A08,
int: 601824349883727873,
num_digit0: 140123150
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42998,
hex: 000056 FE91E90200,
int: 819763200000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7ff774f429a0,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7ff774f429a4,
hex: 63313430313233313439,
cstr: c140123149
}
}
OldRow Cols: {}
DmlFlag: INSERT ModifyCount: 2 AccChecksum: 382774143 Version: 1735058076972385 Flag: 0 SeqNo: 1735060891761981 NewRowSize: 55 OldRowSize: 0 ColumnCnt: 4
}
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 1152921504606855944
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735060891761982,
create_timestamp: 1735060891762249,
create_schema_version: 1735057916178264
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 1152921504606855944
}
}
NORMAL_ROW: {
RowKey: {
"VARCHAR": "c140123149",
collation: "gbk_bin",
coercibility: "IMPLICIT"
} {
"NULL": "NULL"
} {
"NULL": "NULL"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7ff774f42a4a,
hex: 63313430313233313439,
cstr: c140123149
}
1: null 2: null 3: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42a54,
hex: 010000 C00E1C5A08,
int: 601824349883727873,
num_digit0: 140123150
}
4: NOP
}
OldRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7ff774f42a78,
hex: 63313430313233313439,
cstr: c140123149
}
1: null 2: null 3: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42a82,
hex: 010000 C00D1C5A08,
int: 601824345588760577,
num_digit0: 140123149
}
4: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7ff774f42a8a,
hex: 000056 FE91E90200,
int: 819763200000000
}
}
DmlFlag: UPDATE ModifyCount: 3 AccChecksum: 345615453 Version: 1735058076455018 Flag: 0 SeqNo: 1735060891761982 NewRowSize: 45 OldRowSize: 53 ColumnCnt: 5
}
}
}### < TxCommitInfoLog >: {
Size: 40 Members: {
LOG_TYPE: 64,
scheduler_: "172.16.0.147:2882",
participants_: [{
id: 1001
}],
upstream_: {
id: -1
},
is_sub2pc_: false,
is_dup_tx_: false,
can_elr_: true,
incremental_participants_: [],
cluster_version_: 17180000522,
app_trace_id_str_: "",
app_trace_info_: "",
prev_record_lsn_: {
lsn: 18446744073709551615
},
redo_lsns_: [],
xid_: {
gtrid_str: "",
bqual_str: "",
format_id: 1,
gtrid_str_.ptr(): "data_size:0, data:",
bqual_str_.ptr(): "data_size:0, data:",
g_hv: 0,
b_hv: 0
}
}
}### < TxCommitLog >: {
Size: 30 Members: {
LOG_TYPE: 256,
commit_version_: {
val: 18446744073709551615,
v: 3
},
checksum_: 2517641975,
incremental_participants_: [],
multi_source_data_: [],
trans_type_: 0,
tx_data_backup_: {
start_log_ts_: {
val: 18446744073709551615,
v: 3
}
},
prev_lsn_: {
lsn: 18446744073709551615
},
ls_log_info_arr_: []
}
}
}能看到 UPDATE 操作被拆成了 DELETE +INSERT 操作，其中有 oldrow 每一列的 ptr 位置，也有 newrow 每一列的新的 ptr 位置，以及最终对 UPDATE 的 summary 操作。注意这里的 ptr 位置的变化，NOP 等关键字的意义。以前搞 Oracle 的会觉得猜这些 tag 具体指的是什么意思很有意思，现在老了猜不透了。
场景 2：更新没有索引的普通列，即 NO_INDEX_COL 列
结果格式化如下：
{
BlockId: 1046: LSN: 70252927376 TxID: 94251127 scn: 1735061981417951916 TxBlockHeader: {
org_cluster_id: 1709740693,
log_entry_no: 0,
tx_id: {
txid: 94251127
},
scheduler: "172.16.0.147:2882"
}### < TxRedoLog >: {
txctxinfo: {
LOG_TYPE: 1,
mutator_size_: 259,
ctx_redo_info_.cluster_version_: 17180000522
}
MutatorMeta: 0x7fffec40fe88 data_crc = fbd3c7b7 meta_size = 28 data_size = 231 row_count = 2 MutatorRows: {
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 262691
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735061924046181,
create_timestamp: 1735061924047689,
create_schema_version: 1735057916462984
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262691
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "140123148"
} {
"DATETIME": "1995-12-23 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f548ec80ad7,
hex: 010000 C00C1C5A08,
int: 601824341293793281,
num_digit0: 140123148
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f548ec80adf,
hex: 00 A07EE07DE90200,
int: 819676800000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7f548ec80ae7,
hex: 636 F6C33,
cstr: col3
}
3: NOP
}
OldRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f548ec80b05,
hex: 010000 C00C1C5A08,
int: 601824341293793281,
num_digit0: 140123148
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f548ec80b0d,
hex: 00 A07EE07DE90200,
int: 819676800000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7f548ec80b15,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f548ec80b19,
hex: 63313430313233313438,
cstr: c140123148
}
}
DmlFlag: UPDATE ModifyCount: 1 AccChecksum: 3939639371 Version: 1735058076972385 Flag: 0 SeqNo: 1735061924046181 NewRowSize: 45 OldRowSize: 55 ColumnCnt: 4
}
}
}### < TxCommitInfoLog >: {
Size: 40 Members: {
LOG_TYPE: 64,
scheduler_: "172.16.0.147:2882",
participants_: [{
id: 1001
}],
upstream_: {
id: -1
},
is_sub2pc_: false,
is_dup_tx_: false,
can_elr_: true,
incremental_participants_: [],
cluster_version_: 17180000522,
app_trace_id_str_: "",
app_trace_info_: "",
prev_record_lsn_: {
lsn: 18446744073709551615
},
redo_lsns_: [],
xid_: {
gtrid_str: "",
bqual_str: "",
format_id: 1,
gtrid_str_.ptr(): "data_size:0, data:",
bqual_str_.ptr(): "data_size:0, data:",
g_hv: 0,
b_hv: 0
}
}
}### < TxCommitLog >: {
Size: 30 Members: {
LOG_TYPE: 256,
commit_version_: {
val: 18446744073709551615,
v: 3
},
checksum_: 2675778131,
incremental_participants_: [],
multi_source_data_: [],
trans_type_: 0,
tx_data_backup_: {
start_log_ts_: {
val: 18446744073709551615,
v: 3
}
},
prev_lsn_: {
lsn: 18446744073709551615
},
ls_log_info_arr_: []
}
}
}在这个输出结果里，只有 UPDATE 操作。
场景 3：更新唯一索引列，即 UK_COL 列
输出如下：
{
BlockId: 1046: LSN: 70253319830 TxID: 94252135 scn: 1735062308516902353 TxBlockHeader: {
org_cluster_id: 1709740693,
log_entry_no: 0,
tx_id: {
txid: 94252135
},
scheduler: "172.16.0.147:2882"
}### < TxRedoLog >: {
txctxinfo: {
LOG_TYPE: 1,
mutator_size_: 616,
ctx_redo_info_.cluster_version_: 17180000522
}
MutatorMeta: 0x7ffcd5a557a8 data_crc = 7 a304afc meta_size = 28 data_size = 588 row_count = 5 MutatorRows: {
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 262691
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735062306424405,
create_timestamp: 1735062306425259,
create_schema_version: 1735057916462984
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262691
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "139123147"
} {
"DATETIME": "1995-12-22 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae07dd,
hex: 010000 C0CBD94A08,
int: 597529369702825985,
num_digit0: 139123147
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae07e5,
hex: 0040 A7C269E90200,
int: 819590400000000
}
2: NOP 3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f6384ae07ed,
hex: 63313339313233313530,
cstr: c139123150
}
}
OldRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae0811,
hex: 010000 C0CBD94A08,
int: 597529369702825985,
num_digit0: 139123147
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae0819,
hex: 0040 A7C269E90200,
int: 819590400000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7f6384ae0821,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f6384ae0825,
hex: 63313339313233313437,
cstr: c139123147
}
}
DmlFlag: UPDATE ModifyCount: 1 AccChecksum: 2315192800 Version: 1735058076972385 Flag: 0 SeqNo: 1735062306424405 NewRowSize: 51 OldRowSize: 55 ColumnCnt: 4
}
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 1152921504606855944
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735062306424406,
create_timestamp: 1735062306425343,
create_schema_version: 1735057916178264
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 1152921504606855944
}
}
NORMAL_ROW: {
RowKey: {
"VARCHAR": "c139123147",
collation: "gbk_bin",
coercibility: "IMPLICIT"
} {
"NULL": "NULL"
} {
"NULL": "NULL"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f6384ae08ca,
hex: 63313339313233313437,
cstr: c139123147
}
1: null 2: null 3: NOP 4: NOP
}
OldRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f6384ae08f0,
hex: 63313339313233313437,
cstr: c139123147
}
1: null 2: null 3: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae08fa,
hex: 010000 C0CBD94A08,
int: 597529369702825985,
num_digit0: 139123147
}
4: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae0902,
hex: 0040 A7C269E90200,
int: 819590400000000
}
}
DmlFlag: DELETE ModifyCount: 1 AccChecksum: 2652555665 Version: 1735058076455018 Flag: 0 SeqNo: 1735062306424406 NewRowSize: 37 OldRowSize: 53 ColumnCnt: 5
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 1152921504606855944
}
}
NORMAL_ROW: {
RowKey: {
"VARCHAR": "c139123150",
collation: "gbk_bin",
coercibility: "IMPLICIT"
} {
"NULL": "NULL"
} {
"NULL": "NULL"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f6384ae0972,
hex: 63313339313233313530,
cstr: c139123150
}
1: null 2: null 3: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae097c,
hex: 010000 C0CBD94A08,
int: 597529369702825985,
num_digit0: 139123147
}
4: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f6384ae0984,
hex: 0040 A7C269E90200,
int: 819590400000000
}
}
OldRow Cols: {}
DmlFlag: INSERT ModifyCount: 0 AccChecksum: 77130762 Version: 1735058076455018 Flag: 0 SeqNo: 1735062306424406 NewRowSize: 53 OldRowSize: 0 ColumnCnt: 5
}
}
}### < TxCommitInfoLog >: {
Size: 40 Members: {
LOG_TYPE: 64,
scheduler_: "172.16.0.147:2882",
participants_: [{
id: 1001
}],
upstream_: {
id: -1
},
is_sub2pc_: false,
is_dup_tx_: false,
can_elr_: true,
incremental_participants_: [],
cluster_version_: 17180000522,
app_trace_id_str_: "",
app_trace_info_: "",
prev_record_lsn_: {
lsn: 18446744073709551615
},
redo_lsns_: [],
xid_: {
gtrid_str: "",
bqual_str: "",
format_id: 1,
gtrid_str_.ptr(): "data_size:0, data:",
bqual_str_.ptr(): "data_size:0, data:",
g_hv: 0,
b_hv: 0
}
}
}### < TxCommitLog >: {
Size: 30 Members: {
LOG_TYPE: 256,
commit_version_: {
val: 18446744073709551615,
v: 3
},
checksum_: 2435649795,
incremental_participants_: [],
multi_source_data_: [],
trans_type_: 0,
tx_data_backup_: {
start_log_ts_: {
val: 18446744073709551615,
v: 3
}
},
prev_lsn_: {
lsn: 18446744073709551615
},
ls_log_info_arr_: []
}
}
}这个能看到，update 操作后面有 delete 及 insert 操作，但是这次是 INSERT 了 0 个记录。
场景 4：更新分区键，即 PART_COL 列
输出结果如下：
{
BlockId: 1046: LSN: 70253938997 TxID: 94253645 scn: 1735062823009645290 TxBlockHeader: {
org_cluster_id: 1709740693,
log_entry_no: 0,
tx_id: {
txid: 94253645
},
scheduler: "172.16.0.147:2882"
}### < TxRedoLog >: {
txctxinfo: {
LOG_TYPE: 1,
mutator_size_: 617,
ctx_redo_info_.cluster_version_: 17180000522
}
MutatorMeta: 0x7ffe76bced98 data_crc = 1 d0d668 meta_size = 28 data_size = 589 row_count = 5 MutatorRows: {
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 262689
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735062821124691,
create_timestamp: 1735062821124859,
create_schema_version: 1735057916462984
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262689
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "130123138"
} {
"DATETIME": "1993-12-26 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77a7c,
hex: 010000 C08285C107,
int: 558874625384120321,
num_digit0: 130123138
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77a84,
hex: 0000 DD215DB00200,
int: 756864000000000
}
2: NOP 3: NOP
}
OldRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77aa6,
hex: 010000 C08285C107,
int: 558874625384120321,
num_digit0: 130123138
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77aae,
hex: 0000 DD215DB00200,
int: 756864000000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77ab6,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77aba,
hex: 63313330313233313338,
cstr: c130123138
}
}
DmlFlag: DELETE ModifyCount: 1 AccChecksum: 576618654 Version: 1735058076744501 Flag: 0 SeqNo: 1735062821124691 NewRowSize: 41 OldRowSize: 55 ColumnCnt: 4
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 262689
}
}
NORMAL_ROW: {
RowKey: {
"DECIMAL": "130123138"
} {
"DATETIME": "1993-12-27 00:00:00.000000"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77b2a,
hex: 010000 C08285C107,
int: 558874625384120321,
num_digit0: 130123138
}
1: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77b32,
hex: 0060 B43F71B00200,
int: 756950400000000
}
2: {
len: 4,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77b3a,
hex: 636 F6C32,
cstr: col2
}
3: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77b3e,
hex: 63313330313233313338,
cstr: c130123138
}
}
OldRow Cols: {}
DmlFlag: INSERT ModifyCount: 0 AccChecksum: 4206667041 Version: 1735058076744501 Flag: 0 SeqNo: 1735062821124693 NewRowSize: 55 OldRowSize: 0 ColumnCnt: 4
}
RowHeader: {
mutator_type_str_: "MUTATOR_TABLE_LOCK",
mutator_type_: 1,
tablet_id_: {
id: 49403
}
}
TableLock: {
row_size: 40,
table_id: 18446744073709551615,
table_version: 0,
lock_id: {
obj_type: 2,
obj_id: 1152921504606855944
},
owner_id: {
id: 0
},
mode: 4,
lock_type: 1,
seq_no: 1735062821124694,
create_timestamp: 1735062821124998,
create_schema_version: 1735057916178264
}
RowHeader: {
mutator_type_str_: "MUTATOR_ROW",
mutator_type_: 0,
tablet_id_: {
id: 1152921504606855944
}
}
NORMAL_ROW: {
RowKey: {
"VARCHAR": "c130123138",
collation: "gbk_bin",
coercibility: "IMPLICIT"
} {
"NULL": "NULL"
} {
"NULL": "NULL"
}
TableVersion: 1735057916462984 NewRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77be4,
hex: 63313330313233313338,
cstr: c130123138
}
1: null 2: null 3: NOP 4: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77bee,
hex: 0060 B43F71B00200,
int: 756950400000000
}
}
OldRow Cols: {
0: {
len: 10,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77c12,
hex: 63313330313233313338,
cstr: c130123138
}
1: null 2: null 3: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77c1c,
hex: 010000 C08285C107,
int: 558874625384120321,
num_digit0: 130123138
}
4: {
len: 8,
flag: 0,
null: 0,
ptr: 0x7f3f2cb77c24,
hex: 0000 DD215DB00200,
int: 756864000000000
}
}
DmlFlag: UPDATE ModifyCount: 1 AccChecksum: 1740258124 Version: 1735058076455018 Flag: 0 SeqNo: 1735062821124694 NewRowSize: 45 OldRowSize: 53 ColumnCnt: 5
}
}
}### < TxCommitInfoLog >: {
Size: 40 Members: {
LOG_TYPE: 64,
scheduler_: "172.16.0.147:2882",
participants_: [{
id: 1001
}],
upstream_: {
id: -1
},
is_sub2pc_: false,
is_dup_tx_: false,
can_elr_: true,
incremental_participants_: [],
cluster_version_: 17180000522,
app_trace_id_str_: "",
app_trace_info_: "",
prev_record_lsn_: {
lsn: 18446744073709551615
},
redo_lsns_: [],
xid_: {
gtrid_str: "",
bqual_str: "",
format_id: 1,
gtrid_str_.ptr(): "data_size:0, data:",
bqual_str_.ptr(): "data_size:0, data:",
g_hv: 0,
b_hv: 0
}
}
}### < TxCommitLog >: {
Size: 30 Members: {
LOG_TYPE: 256,
commit_version_: {
val: 18446744073709551615,
v: 3
},
checksum_: 3916520625,
incremental_participants_: [],
multi_source_data_: [],
trans_type_: 0,
tx_data_backup_: {
start_log_ts_: {
val: 18446744073709551615,
v: 3
}
},
prev_lsn_: {
lsn: 18446744073709551615
},
ls_log_info_arr_: []
}
}
}同样存在 update，insert，delete，INSERT 为 0 。
总结
从 dump 的结果来看，只要列上存在索引或者主键，以及如果是分区键，这些列的更新都会伴随 delete 及 insert 操作，所不同的是操作的记录数。这个部分需要进一步的分析差异。而一个完全没有索引的列的更新则是一个纯 update 操作，是一个 memtable 的直接操作。
好了，这些测试并没有什么意义。