慢SQL咨询

🪐 TiDB 技术问题 性能调优
1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
TiKV,32C 64G

【现象】 业务和数据库现象
3个慢SQL

【TiDB 版本】
5.7.25-TiDB-v4.0.10

1.insert超过1s，单表数据约2000万；
insert into t_invoice_kj_record (msg_id, tax_disk, real_tax_disk, kpr_id, fplxdm, kplx, tspz, xhdwsbh, ghdwmc, ghdwsbh, ghdwdz, ghdwdh, ghdwyh, ghdwzh, hjje, hjse, jshj, bz, qdbz, kpjh, sign ) values (…)

执行计划
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:419.505µs, loops:1, prepare:282.66µs, insert:136.845µs, commit_txn: {prewrite:1.718988742s, wait_prewrite_binlog:229ns, get_commit_ts:275.08µs, commit:3.312319ms, region_num:2, write_keys:2, write_byte:538} 3.8310546875 KB N/A

2.insert … on duplicate key update 慢，单表数据在500+
insert into t_plugin_online_record (disk_type, tax_disk, disk_num, version, xhdwsbh, xhdwmc, plugin_version) values (xx) on duplicate key update disk_type=1,disk_num=’’,version=’’,xhdwsbh=’’,xhdwmc=’’,plugin_version=’’,last_time=now();

执行计划：
id task estRows operator info actRows execution info memory disk
Insert_1 root 0 N/A 0 time:36.579234809s, loops:2, prepare:3.982522ms, check_insert:{total_time:36.575252287s, mem_insert_time:408.598µs, prefetch:36.574843689s, rpc:{BatchGet:{num_rpc:6, total_time:22.632265ms}}}, commit_txn: {prewrite:881.108µs, wait_prewrite_binlog:6.997531ms, region_num:1, write_keys:1, write_byte:149, txn_retry:1} 1.0927734375 KB N/A

表结构：
CREATE TABLE t_plugin_online_record (
id bigint(20) NOT NULL AUTO_INCREMENT,
disk_type tinyint(4) NOT NULL COMMENT ‘税盘类型’,
tax_disk varchar(12) COMMENT ‘税盘编号’,
disk_num varchar(3) COMMENT ‘开票机号’,
version varchar(15) COMMENT ‘版本号’,
xhdwsbh varchar(20) COMMENT ‘税号’,
xhdwmc varchar(100) COMMENT ‘纳税人名称’,
ip varchar(40) DEFAULT ‘’ COMMENT ‘IP’,
plugin_version varchar(10) DEFAULT ‘’ COMMENT ‘版本号1’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
last_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
PRIMARY KEY (id),
UNIQUE KEY uniq_tax_disk (tax_disk)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=2327025 COMMENT=‘状态记录表’;

3.根据id update 慢，id为主键，单表数据约2000万
update t_plugin_transfer set status=1,result=’’ where id=19704790
执行计划：
id task estRows operator info actRows execution info memory disk
Update_2 root 0 N/A 0 time:775.147µs, loops:1, , commit_txn: {prewrite:2.177618ms, wait_prewrite_binlog:1.236378809s, get_commit_ts:346.903µs, commit:1.613045ms, region_num:1, write_keys:1, write_byte:3650} 4.44921875 KB N/A └─Point_Get_1 root 1 table:t_plugin_transfer, handle:19704790 1 time:618.636µs, loops:2, Get:{num_rpc:1, total_time:559.972µs} N/A N/A

针对on duplicate key update官方不建议，但是一些批量数据处理场景还是要需要，如果拆分成查询是否存在数据来插入或更新，这样效率会大打折扣。
对于插入耗时超过1s，在大量并发插入情况下，5.x版本的id可以设置auto-random，还有其它什么建议。

2

  1. t_invoice_kj_record
    insert 操作并不慢，耗时 time:419.505µs ；
    针对这块，我有个疑问：
    a. 是否有热点问题？
    b. 对于batch insert，是否有足够的优化？

  2. t_plugin_online_record 表数据量很小了才500…
    但是场景是期望能够支持到 insert 和 update，我查阅执行计划慢的原因：
    time:36.579234809s 总耗时
    check_insert:{total_time:36.575252287s, mem_insert_time:408.598µs, prefetch:36.574843689s, rpc:{BatchGet:{num_rpc:6, total_time:22.632265ms}}}
    检查插入的过程，就耗掉一大半了… 其中 rpc 回调 就 执行了 6次，执行时间 22.632265ms
    疑问：

a. 是否有热点问题？
4.X 的版本也是聚簇索引的，但是是隐式的，如果判断有热点问题，建议切换成 auto_random

b. 业务场景上是否有足够的优化空间？ 对于唯一标识是否存在，肯定是知晓的，那么在处理层面就可以单纯切换成 batch insert 和 batch update，并且这个事务也不会太大，可以解决这个慢的问题

  1. t_plugin_transfer 是什么类型的结构？
    按照你给出的计划来看，并不慢
    commit_txn: {prewrite:2.177618ms, wait_prewrite_binlog:1.236378809s, get_commit_ts:346.903µs, commit:1.613045ms, region_num:1, write_keys:1, write_byte:3650}
    都是以ms 为单位的…

还有更大的疑问，目前环境的整体的配置是否依据了官方的要求？

3

1.t_invoice_kj_record
a.这个表就是简单插入和更新，主键是递增的，应该有热点，除通过设置auto_random外，请问是否还有其它方式；
b.目前不涉及批量插入，如果涉及，一般会考虑批量插入。

2.t_plugin_online_record
这个表有id主键，还有一个唯一键，感觉去掉id主键，也可以减少冲突。
另外还有一种业务场景，每次600多万业务数据整合，根据唯一键判断是插入还是更新，这个不用insert … on duplicate key update应该会比较慢，是否有好的建议。

3.t_plugin_transfer表结构
CREATE TABLE t_plugin_transfer (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘id’,
tenant_id varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘租户id’,
request_type tinyint(4) NOT NULL DEFAULT ‘1’ COMMENT ‘请求类型’,
msg_id varchar(48) NOT NULL COMMENT ‘消息id（每次请求唯一）’,
disk_type tinyint(4) NOT NULL COMMENT ‘税盘类型’,
tax_disk varchar(12) NOT NULL COMMENT ‘税盘编号’,
record longtext NOT NULL COMMENT ‘转发报文’,
sign char(32) NOT NULL DEFAULT ‘’ COMMENT ‘MD5签名’,
status tinyint(4) DEFAULT ‘0’ COMMENT ‘处理状态’,
result varchar(4000) DEFAULT ‘’ COMMENT ‘错误原因或响应结果’,
create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘修改时间’,
PRIMARY KEY (id),
KEY idx_msg_id (tenant_id,msg_id)
) ENGINE=InnoDB AUTO_INCREMENT=633008 COMMENT=‘请求转发记录表’;

4.TiDB是DBA搭建，应该是按照官方的要求支持OLTP和OLAP，目前只能看到配置是32C64G，2个节点，没有使用TiFlash，TiCDC不清楚。

4

  • t_plugin_online_record 按照你的描述，应该是有系统唯一主键和 业务唯一键，这个不会成为冲突的来源，识别业务应该是必须的。除了数据库没其他的中间件可以作为辅助么？优化下业务模式，减少查询会更高效

  • t_invoice_kj_record 如果想要高性能，要么采用官方提供的 auto_random，要么自己采用 snowflake 的 算法进行优化，满足 高位调换的原则，也可以支持动态打散； AUTO_INCREMENT 适用于性能要求不高的场景，一般都是为了兼容迁移

  • 建议咨询 DBA 看看有没有什么方式支持你… :star_struck:

5

是有redis、kafka可以选择，这个主要做业务记录，数据不大，只是需要的时候会查询，一般就是插入或更新，才选择的insert … on duplicate key update，后续可以修改auto_random或者先查是否存在再决定是插入还是更新。

对于每次600多万业务数据整合，根据唯一键判断是插入还是更新，这种批量插入更新场景，感觉速度会慢不少。

6

每次600多万业务数据整合 是个什么场景？

7

6个月应收数据整合

8

那也是 N 个业务数据 + 维度 合并后的一个结果吧

还好拉:custard:

如果这块的数据会一直留存，建议开tiflash，进行查询的提速；
不过，你用的版本太低了，开 tiflash 建议 5.4.X 坑比较少

9

可能描述有问题，就是每次拿6个月数据（放在A表）跟已经整合的业务表B比较，根据两表唯一键来使A表数据插入或更新B表数据，A表相对B表差异一般在万级别、跨月的时候是百万级别），B表新增或更新的数据最高在百万级别、最低万级别。

10

合并数据有两个概念：

  1. 业务数据更新

  2. 统计数据更新

如果是第一种呢，不论放到啥数据库都会有点问题，处理过程少不了需要各种优化了，你会遇到如下的困难：

  • 处理不够快速，时效性不够好

  • 超大事务，容易 OOM

如果是第二种呢，类似于指标结果了，一般都是 append ，upsert 不常见

11

upsert业务，MySQL之前1万左右数据upsert在8分钟左右，百万级别upsert在50分钟左右，TiDB这种场景可能会更长

12

该主题在最后一个回复创建后60天后自动关闭。不再允许新的回复。