INSERT ON DUPLICATE KEY UPDATE

🪐 TiDB 技术问题 性能调优
1
  • 【TiDB 版本】：v2.1.13
  • 【问题描述】：upsert语句耗时 使用INSERT ON DUPLICATE KEY UPDATE处理600万的数据大概需要4小时左右 有没有更好的替换方式？ jdbcurl: 已设置rewriteBatchedStatements=true 目前采用的是1千笔一个批次
2

" INSERT ON DUPLICATE KEY UPDATE " 这种方式写入的时候，再出现冲突的时候会更新冲突行，同时还会有可能再次发生冲突，建议根据日志信息综合排查下在更新过程中事务冲突是否严重。另外 batch size 可以设置的小一些：建议 100 - 500 一个批次。

3

方便提供下 SQL 语句信息吗？另外提到的一千笔一个批次是一个事务里一千条 SQL 语句吗？

4

一千笔一个批次是一个事务里一千条 SQL 语句
connection.setAutoCommit(false);
preparedStatement.addBatch();
preparedStatement.executeBatch();

主键冲突很频繁，基本占全表600万中的90%
但是我们业务场景就是需要更新

我试过500一个批次， 整个作业跑完更耗时
后来换成replace into 语句发现快了些， 从4小时降到3小时， 感觉还是太慢了， :sob:

5

方便提供下脱敏的表结构和具体的 SQL 语句吗？

6

表结构： CREATE TABLE ods_s ( DATA_DT date NOT NULL, CID varchar(32) NOT NULL’, S_01 decimal(12,6) DEFAULT NULL, S_02 decimal(12,6) DEFAULT NULL, S_03 decimal(12,6) DEFAULT NULL, S_04 decimal(12,6) DEFAULT NULL, S_05 decimal(12,6) DEFAULT NULL, S_06 decimal(12,6) DEFAULT NULL, S_07 decimal(12,6) DEFAULT NULL, S_08 decimal(12,6) DEFAULT NULL, S_09 decimal(12,6) DEFAULT NULL, S_10 decimal(12,6) DEFAULT NULL, S_11 decimal(12,6) DEFAULT NULL, S_12 decimal(12,6) DEFAULT NULL, S_13 decimal(12,6) DEFAULT NULL, S_14 decimal(12,6) DEFAULT NULL, S_15 decimal(12,6) DEFAULT NULL, S_16 varchar(200) DEFAULT NULL , S_17 varchar(200) DEFAULT NULL , S_18 varchar(200) DEFAULT NULL , S_19 varchar(200) DEFAULT NULL , S_20 varchar(200) DEFAULT NULL , S_21 varchar(200) DEFAULT NULL , S_22 varchar(200) DEFAULT NULL , S_23 varchar(200) DEFAULT NULL , S_24 varchar(200) DEFAULT NULL , S_25 varchar(200) DEFAULT NULL , S_26 varchar(200) DEFAULT NULL , S_27 varchar(200) DEFAULT NULL , S_28 varchar(200) DEFAULT NULL , S_29 varchar(200) DEFAULT NULL , S_30 varchar(200) DEFAULT NULL , S_DATE date DEFAULT NULL’, SX_DAY timestamp NULL DEFAULT NULL’, TX_DAY timestamp NULL DEFAULT NULL’, FK_DAY timestamp NULL DEFAULT NULL’, STAY_DAYS date DEFAULT NULL’, FST_TIME decimal(12,6) DEFAULT NULL’, SH decimal(12,6) DEFAULT NULL, MAX int(11) DEFAULT NULL, WEIGHT decimal(12,6) DEFAULT NULL’, AVG_L1M decimal(12,6) DEFAULT NULL’, AVG_L6M decimal(12,6) DEFAULT NULL, L12M decimal(12,6) DEFAULT NULL, L1M int(11) DEFAULT NULL, L3M int(11) DEFAULT NULL, HIS decimal(12,6) DEFAULT NULL, FSTDAY decimal(12,6) DEFAULT NULL, GRP_1 varchar(200) DEFAULT NULL, GRP_2 varchar(200) DEFAULT NULL, GRP_3 varchar(200) DEFAULT NULL, GRP_4 varchar(200) DEFAULT NULL, GRP_5 varchar(200) DEFAULT NULL, GRP_6 varchar(200) DEFAULT NULL, GRP_7 varchar(200) DEFAULT NULL, GRP_8 varchar(200) DEFAULT NULL, GRP_9 varchar(200) DEFAULT NULL, B_1 decimal(12,6) DEFAULT NULL, B_2 decimal(12,6) DEFAULT NULL, B_3 decimal(12,6) DEFAULT NULL, B_4 decimal(12,6) DEFAULT NULL, B_5 decimal(12,6) DEFAULT NULL, B_6 decimal(12,6) DEFAULT NULL, B_7 decimal(12,6) DEFAULT NULL, B_8 decimal(12,6) DEFAULT NULL, B_9 decimal(12,6) DEFAULT NULL, CREATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, UPDATE_TIME timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (CID) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

SQL语句： replace INTO %s (XXX, XXX…) VALUES(XXX, XXX, …)

7

收到，之前的 INSERT ON DUPLICATE KEY UPDATE 这种方式的语句是 ON DUPLICATE KEY UPDATE 后面是 CID 字段吧，这个具体的语句麻烦给下样例，辛苦，多谢。

8

之前使用的upsert语句： INSERT INTO %s (DATA_DT,XXX, …) VALUES(XXX, XXX, …) ON DUPLICATE KEY UPDATE DATA_DT=VALUES(DATA_DT),CID=VALUES(CID),XXX=VALUES(XXX)…

9

收到，我们分析下，多谢。