insert或replace耗时久

[EthanWong (Ethan)]

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
tidb v5.1.0

【概述】 场景 + 问题概述
消费mq然后使用insert into/insert into on duplicate key/replace into等方式进行批次写入，批次大小最大100，在dashboard经常发现慢sql，很多insert一条数据的sql耗时也能够达到5~6s

insert into:
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:120.3µs, loops:1, prepare:38.5µs, insert:81.8µs, lock_keys: {time:5.8s, region:3, keys:9, lock_rpc:5.802377927s, rpc_count:3} 2.97 KB N/A

insert into on duplicate key:
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:5.22s, loops:1, prepare:54.5ms, check_insert: {total_time: 5.17s, mem_insert_time: 7.54ms, prefetch: 5.16s, rpc:{BatchGet:{num_rpc:29, total_time:1m11.9s}, scan_detail: {total_process_keys: 54, total_keys: 65, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 801, read_count: 9, read_byte: 173.2 KB}}}}}, lock_keys: {time:5.54ms, region:21, keys:40, lock_rpc:47.81964ms, rpc_count:21} 350.0 KB N/A

replace into:
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:5.98s, loops:1, prefetch: 5.98s, rpc:{BatchGet:{num_rpc:19, total_time:29.2s}, scan_detail: {total_process_keys: 56, total_keys: 128, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 1366, read_count: 1, read_byte: 12.7 KB}}}}, lock_keys: {time:14.1ms, region:20, keys:228, lock_rpc:41.860724ms, rpc_count:20} 221.6 KB N/A

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

[xfworld (魔幻之翼)]

单纯 insert into 呢？
表结构和集群是什么样的配置？

针对出现操作慢的场景，是否有监控数据，能否放一份上来

[Kongdom]

批量写入？那我怀疑是热点，建议组合成一个insert语句

[EthanWong (Ethan)]

第一个就是insert into场景
集群配置：6台TiDB(8C16M) 18台TiKV(16C64G)
大致表结构：CREATE TABLE order_details (
auto_pk bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,
id bigint(20) unsigned DEFAULT NULL COMMENT ‘id’,
detail_no varchar(64) DEFAULT NULL COMMENT ‘’,
vender_id varchar(64) DEFAULT NULL COMMENT ‘’,
order_id varchar(64) DEFAULT NULL COMMENT ‘’,
sku_id varchar(64) DEFAULT NULL COMMENT ‘’,
finish_time datetime NOT NULL COMMENT ‘’,
finish_date int(12) NOT NULL COMMENT ‘’,
uuid varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘’,
create_time datetime DEFAULT NULL COMMENT ‘’,
update_time datetime DEFAULT NULL COMMENT ‘’,
PRIMARY KEY (auto_pk,finish_date) /*T![clustered_index] CLUSTERED */,
UNIQUE KEY uk_uuid (uuid,finish_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=270001 COMMENT=‘xxx’
PARTITION BY RANGE ( finish_date ) (
PARTITION pbefore202201 VALUES LESS THAN (20220101),
PARTITION p202201 VALUES LESS THAN (20220201),
PARTITION p202202 VALUES LESS THAN (20220301),
PARTITION p202203 VALUES LESS THAN (20220401),
PARTITION p202204 VALUES LESS THAN (20220501),
PARTITION p202205 VALUES LESS THAN (20220601),
PARTITION p202206 VALUES LESS THAN (20220701),
PARTITION p202207 VALUES LESS THAN (20220801),
PARTITION p202208 VALUES LESS THAN (20220901),
PARTITION p202209 VALUES LESS THAN (20221001),
PARTITION p202210 VALUES LESS THAN (20221101),
PARTITION p202211 VALUES LESS THAN (20221201),
PARTITION p202212 VALUES LESS THAN (20230101),
PARTITION p202301 VALUES LESS THAN (20230201),
PARTITION p202302 VALUES LESS THAN (20230301),
PARTITION p202303 VALUES LESS THAN (20230401),
PARTITION p202304 VALUES LESS THAN (20230501),
PARTITION p202305 VALUES LESS THAN (20230601),
PARTITION p202306 VALUES LESS THAN (20230701),
PARTITION p202307 VALUES LESS THAN (20230801),
PARTITION p202308 VALUES LESS THAN (20230901),
PARTITION p202309 VALUES LESS THAN (20231001),
PARTITION p202310 VALUES LESS THAN (20231101),
PARTITION p202311 VALUES LESS THAN (20231201),
PARTITION p202312 VALUES LESS THAN (20240101)
)

[EthanWong (Ethan)]

就是批量写入batch提交的一个insert语句

[EthanWong (Ethan)]

需要哪些监控数据？

[xfworld (魔幻之翼)]

AUTO_INCREMENT 可以替换成 auto_random
需要重新建表，不能直接修改
然后你在试试，我估计和热点有关系

[EthanWong (Ethan)]

也有不是AUTO_INCREMENT的表结构，同样写入很慢
CREATE TABLE xxx (
id bigint(20) unsigned NOT NULL COMMENT ‘’,
order_id bigint(20) NOT NULL COMMENT ‘’,
order_state smallint(6) DEFAULT NULL COMMENT ‘’,
sku_id bigint(20) DEFAULT NULL COMMENT ‘’,
adowner_id varchar(50) DEFAULT NULL COMMENT ‘’,
pop_id bigint(20) DEFAULT NULL COMMENT ‘’,
vender_id bigint(20) DEFAULT ‘0’ COMMENT ‘’,
order_time datetime DEFAULT NULL COMMENT ‘’,
finish_time datetime DEFAULT NULL COMMENT ‘’,
finish_date int(10) DEFAULT ‘0’ COMMENT ‘’,
create_time datetime DEFAULT NULL COMMENT ‘’,
modify_time datetime DEFAULT NULL COMMENT ‘’,
PRIMARY KEY (order_id,id) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘xxx’

[EthanWong (Ethan)]

从dashboard看执行计划耗时
insert into: lock_keys算子耗时长
insert into on duplicate key: check_insert算子耗时长
replace into: prefetch算子耗时长

[xfworld (魔幻之翼)]

我的意思，你要高性能，只能是auto_random
其他的主键，无法满足你对性能的要求的，需要手动 spile 和 预处理才行，比较麻烦

你试试先

[h5n1]

看下insert慢时 有没有类似查询系统元数据的比如 列 唯一索引 这类的SQL

[EthanWong (Ethan)]

insert into前是有根据表的唯一索引进行查询的

[h5n1]

那个SQL看下，看代码里貌似check_insert/prefech是batchCheckAndInsert里判断唯一性，具体也不太懂

[EthanWong (Ethan)]

select * from order_details where uuid = #{uuid} and finish_date = #{finishDate};
insert into order_details …
insert into对应的是lock_keys算子耗时长
insert into on duplicate key/replace into对应的是check_insert/prefech算子耗时长，这两种写入时并没有查询，但看起来还是对主键或唯一索引进行了判断