- 【TiDB 版本】:v2.1.13
- 【问题描述】:upsert语句耗时 使用INSERT ON DUPLICATE KEY UPDATE处理600万的数据大概需要4小时左右 有没有更好的替换方式? jdbcurl: 已设置rewriteBatchedStatements=true 目前采用的是1千笔一个批次
" INSERT ON DUPLICATE KEY UPDATE " 这种方式写入的时候,再出现冲突的时候会更新冲突行,同时还会有可能再次发生冲突,建议根据日志信息综合排查下在更新过程中事务冲突是否严重。另外 batch size 可以设置的小一些:建议 100 - 500 一个批次。
方便提供下 SQL 语句信息吗?另外提到的一千笔一个批次是一个事务里一千条 SQL 语句吗?
一千笔一个批次是一个事务里一千条 SQL 语句
connection.setAutoCommit(false);
preparedStatement.addBatch();
preparedStatement.executeBatch();
主键冲突很频繁,基本占全表600万中的90%
但是我们业务场景就是需要更新
我试过500一个批次, 整个作业跑完更耗时
后来换成replace into 语句发现快了些, 从4小时降到3小时, 感觉还是太慢了,
方便提供下脱敏的表结构和具体的 SQL 语句吗?
表结构:
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, …)
收到,之前的 INSERT ON DUPLICATE KEY UPDATE 这种方式的语句是 ON DUPLICATE KEY UPDATE 后面是 CID 字段吧,这个具体的语句麻烦给下样例,辛苦,多谢。
之前使用的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)…
收到,我们分析下,多谢。