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

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

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

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

插入

MySQL

Data

请教

23 条回复 • 2020-02-24 19:34:01 +08:00

encro

2020-02-21 14:58:24 +08:00

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

mawerss1

2020-02-21 15:36:30 +08:00

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

hhyvs111

2020-02-21 15:46:17 +08:00 via iPhone

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

encro

2020-02-21 15:57:24 +08:00

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

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

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

zjq123

2020-02-21 16:04:29 +08:00 via iPad

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

littlewing

2020-02-21 16:10:25 +08:00

multi query

jswh

2020-02-21 16:12:51 +08:00

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

akira

2020-02-21 18:07:45 +08:00

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

mawerss1

2020-02-21 19:02:14 +08:00

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

mawerss1

2020-02-21 19:03:23 +08:00

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

mawerss1

2020-02-21 19:06:19 +08:00

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

encro

2020-02-21 20:49:29 +08:00

采用事务后，如果几千条数据，插入更新下应该几秒钟搞定了。前提是建立了合理的索引。当然如果你 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

2020-02-21 22:14:08 +08:00

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

mawerss1

2020-02-21 23:04:30 +08:00 via iPhone

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

mawerss1

2020-02-21 23:11:30 +08:00 via iPhone

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