请教一个 mysql 批量插入的问题

[mawerss1]

背景：

数据库是 mysql 5.7，
表结构：
id
data
modified_time

data 是一个很长的 json 串，需求是批量插入每次最多要几千条，插入前比较 modified_time 和插入的数据比较，时间戳比较新的插入，否则失败

现在我的做法是用 insert into on ON DUPLICATE KEY UPDATE，但是如何比较这个时间就不会了，试了用触发器来写，但是批量插入如果有一条时间对不上的话，整个 sql 就不会再执行了，没办法跳过，请教大家有更好的做法吗

[encro]

一条一条插入，然后采用事务批量提交看看，性能也可以的。

[mawerss1]

@encro 试了一下，速度差距还是很大的

[hhyvs111]

不要在数据库里弄触发器之类的，后期很难维护，建议在逻辑层去判断这个时间。

[encro]

采用事务提交后，应该几秒钟可以插入几千条吧，你 modified_time 没有索引？

其实这个方案不好，因为 modified_time 可能存在同一秒有多条数据吧。

不知道你具体的需求场景是什么，脱离需求场景谈 SQL 优化很不靠谱。

[zjq123]

弄张临时内存表 insert into xx select from a join b ... where

[littlewing]

multi query

[jswh]

一次几千应该也不多吧，先把 id 对应的 time 一次都拿出来，在代码里比较确定要不要插入就好了。

[akira]

先插入到一个表，然后再把数据更新过来

[mawerss1]

@encro 你说的对，场景是同步订单表，商家业务

[mawerss1]

@encro 同步过来的数据查询也很频繁

[mawerss1]

@jswh 主要还考虑延迟问题，这个业务场景要求延迟越低越好

[encro]

采用事务后，如果几千条数据，插入更新下应该几秒钟搞定了。前提是建立了合理的索引。当然如果你 data 非常大可能久一点。

你这个需求还是不明确，INSERT ... ON DUPLICATE KEY UPDATE Statement 的意思如果唯一索引不存在就插入否则更新。并不能指定其他条件。这篇文章讲得很清楚了 https://blog.csdn.net/analogous_love/article/details/71085001。官方语法也没有提供扩展 https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html。

所以除非你是根据 ID 来的，或者定了一个唯一索引。且实际不是你说的 “比较新的插入，否则失败”。

我理解你的需求应该是“如果数据有更新，那么执行更新，否则忽略这个数据更新。”
如果是我理解的，那么方案是：
update order set data=new_data where id=xxx and modified_time < new_time; (只需要 id 主键即可，性能最好)
比如你的目的表数据不全部来自来源表，那么可能需要加一个唯一索引字段 from_id
update order set data=new_data where from_id=xxx and modified_time < new_time;（需要对 from_id 建立唯一索引）

你首先要做的是，确定性能问题出在查找数据时的读取磁盘速度慢还是写入磁盘慢。

[MoYi123]

直接不检查直接插入，查找时候找时间戳最大的那条，定时删除过期数据，这样的话需要经常重建索引。如果量特别大可以考虑时序数据库。

[mawerss1]

@encro 还是我没有说清楚，场景是一个接受数据的推送库，这个库是没有任何操作权限，只能查询，要查询推送库里的数据到业务库，这两个库表结构是不同的要对数据做些处理，另外还有别的应用对业务库插入，主要还要新增插入，只靠 update 不行，所以用了 insert on dup key，看来还是要在程序里查询 modified 时间进行比较，然后决定是 insert 还是 update

[mawerss1]

@MoYi123 这样数据量太大了吧，现在的数据量单库预计一千万到两千万，每天还要新增

[ASpiral]

建个中间表，把数据都插进去；写个存储过程，把中间表的数据有选择性地插入目标表，再清空中间表；不知这样会不会比较快…

[wind3110991]

碰什么都不要碰触发器，坑死你，而且你的 mysql 版本也太低了，是大厂吗

[llussy]

@wind3110991 大厂都是 8.0 吗？我们还是 5.6 呢

[polymerdg]

1000 條不多 事務 查出來 在代碼里比较后插入即可

[encro]

@mawerss1
执行两次，
第一次找出已有的 update，
第二次不存在的 insert。
记得采用事务。

[anjuyiyu]

id + 时间做一个临时表？
另：这里是否可以考虑下时间分片做日结表？

[Aresxue]

1.别用触发器；
2.考虑做分表；
3.在代码里做好数据的清洗，最好是先删除数据库中较老时间的数据然后再插入，能不用 update 就尽量规避；
4.注意调整 max_allowed_packet 的限制。

[mawerss1]

@Aresxue update 有什么问题吗