作者简介：谭源洋，瑞蓝创数据库工程师
原创内容未经授权不得随意使用，转载请联系小编并注明来源
熟悉 MySQL 租户的同学对 INSERT ... ON DUPLICATE KEY UPDATE 再熟悉不过：不存在则插入，主键 / 唯一键冲突时执行更新。 但在 OceanBase 中，这条 SQL 的底层实现，随版本发生了变化，默认行为不一定是原地 UPDATE。
4.2.5.6版本之前版本
INSERT ON DUP KEY UPDATE修改普通列，会默认拆分为delete+insert
4.2.5.7版本
1、准备环境
observer版本：4.2.5.7
MySQL租户
表结构
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_test | CREATE TABLE `t_test` (
`id` int(11) NOT NULL,
`name` varchar(10) DEFAULT NULL,
`value` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)
2、先插入一条数据，再修改数据
VALUES (2, 'US', 20001);
INSERT INTO t_test(id, name, value)
VALUES (2, 'USA', 20002)
ON DUPLICATE KEY UPDATE name = VALUES(name), value = VALUES(value);
3、解析clog
"DmlFlag": "DELETE",
"ModifyCount": 1,
"AccChecksum": 408231420,
"Version": 1789702517101371,
"Flag": 0,
"SeqNo": "{branch:0, seq:10915028}",
"NewRowSize": 25,
"OldRowSize": 29,
"ColumnCnt": 3
},
"RowHeader": "{mutator_type_str_:\"MUTATOR_ROW\", mutator_type_:0, tablet_id_:{id:200011}}",
"NORMAL_ROW": {
"RowKey": "{\"INT\":2}",
"TableVersion": 1789702512408456,
"NewRow Cols": {
"0": "{len: 8, flag: 0, null: 0, ptr: 0x7ffcfb2fd250, hex: 0200000000000000, int: 2}",
"1": "{len: 3, flag: 0, null: 0, ptr: 0x7fb8e0353d91, hex: 555341, cstr: USA}",
"2": "{len: 8, flag: 0, null: 0, ptr: 0x7ffcfb2fd2c0, hex: 224E000000000000, int: 20002}"
},
"OldRow Cols": {},
"DmlFlag": "INSERT",
"ModifyCount": 2,
"AccChecksum": 1152602979,
"Version": 1789702517101371,
"Flag": 0,
"SeqNo": "{branch:0, seq:10915029}",
"NewRowSize": 30,
"OldRowSize": 0,
"ColumnCnt": 3
此时发现，解析出的clog，这个SQL变成了delete+insert
4、修改参数
ALTER SYSTEM SET _enable_insertup_direct_update = 'true' tenant='test_mysql';
5、修改完后再次修改数据并解析clog
INSERT INTO t_test(id, name, value) VALUES (2, 'NEW', 200001) ON DUPLICATE KEY UPDATE name = VALUES(name), value = VALUES(value);
此时可看到是update语句
4.3.5版本
在该版本通过上述方式操作同理
"ModifyCount": 3,
"AccChecksum": 424335685,
"Version": 1789722744393694,
"Flag": 0,
"SeqNo": "{branch:0, seq:18432521}",
"NewRowSize": 30,
"OldRowSize": 30,
"ColumnCnt": 3
最终结论
INSERT ON DUP KEY UPDATE在修改普通列时，在4.2.5.7以前版本暂时没有参数可控制行为，统一为delete+insert
在4.2.5.7,4.3.5版本，打开_enable_insertup_direct_update参数后，变为update
END
瑞蓝创 OceanBase OBCP V4 精英训练营
点击下方图片立即了解详情
▼ 点击「阅读原文」，了解更多产品技术文章