棘手的MySQL批处理查询 [英] Tricky MySQL batch query
问题描述
我有一个事件数据库，每天晚上都会更新.一个事件具有跨越三个(或更多)表的信息.最近，更新的数量使我的MySQL引擎非常缓慢，以至于在插入新事件时其他查询被冻结.为了加快处理速度，我想进行一系列的批处理查询，而不必分别进行每个批处理查询，我觉得这是开销的很大一部分.
I have a database of events which gets updated every night. A single event has information across three (or more) tables. Recently the volume of updates has caused my MySQL engine to be very slow to the point that my other queries get frozen while new events are being inserted. In order to speed things up I want to make a series of batch queries instead of having to do each one separately which I feel like is a large part of the overhead.
问题是因为数据分布在多个表中，这是我插入单个事件所要做的:
The problem is because the data is spread across several tables this is what I have to do to insert a single event:
(in Mysql) INSERT INTO 'locations' (...) VALUES (...) ON DUPLICATE KEY UPDATE ...
(in php get the last inserted id into variable $locationID)
(in Mysql) INSERT INTO 'event_info' (...) VALUES ($locationID, ...) ON DUPLICATE KEY UPDATE...
(in php get the last inserted id into variable $eventID)
(in Mysql) INSERT INTO 'event_times' (...) VALUES ($eventID, ...) ON DUPLICATE KEY UPDATE...
我并不是在设计表时寻求帮助，但是如您所见，插入一个事件至少需要三个插入，每个插入都取决于从上一个插入的ID.这就是为什么我不知道从哪里开始将其变成批处理请求的原因.将这个流程设计成批处理请求的任何帮助都将非常棒，谢谢！
I'm not looking for help in designing the tables, but as you can see to insert a single event requires at least three inserts each of which depends on getting the id from the previous one. This is why I didn't know where to begin for making this into a batch request. Any help for designing this process into a batch request would be awesome, thanks!
我以前可能已经有位置或事件信息，这就是为什么ON DUPLICATE KEY UPDATE部分存在的原因，因此，如果它已经在数据库中，我会得到旧的ID.在插入之前，我不知道它是新数据还是数据库中已经存在. (因此，除非引起我的误解，否则我无法做预分配ID的操作，因为每次都会假定一个新ID.)
I might already have the location or the event info previously and that is why the ON DUPLICATE KEY UPDATE part is there so that if it was already in the database I get the old id. I don't know until the insert if it is new data or if it already exists in the database. (because of that unless I am misunderstanding, I can't do things that preallocate the ids since this assume a new id every time.)
推荐答案
请勿使用自动递增列-在插入之前预先分配参考ID.这样，您可以使用批量插入并删除依赖项.
Don't use auto incrementing columns - preallocate your reference id's before inserting. That way you can use a bulk insert and remove the dependency.
更新:
-
从数据库中选择任何现有的ID(最好是对所有已知数据进行一次选择).
Select any existing id's out of the database (ideally a single select for all known data).
使用任何已知ID丰富要插入的数据. (为每个项目计算一个键，该键将与数据库中表的主键相对应，并使用该键更新数据库中ID的项目)-您最终希望将数据拆分成您知道的项目数据库，因此具有已知的ID-以及数据库中不存在的数据，因此需要分配键.我假设您的表具有一个主键，而不仅仅是ID-否则数据库将如何知道您已经在数据库中拥有数据.
Enrich data to insert with any known id's. ( calculate a key for each item, which would correspond with the primary key for your table in the database, use that to update the item with the id from the database ) - you want to ultimately split the data into items which you know about in the database, and thus have an known id - and data which doesn't exist in the database, and thus needs a key allocating. I'm assuming your table has a primary key which isn't just the id - otherwise how else would the database know you already have the data in the database.
为没有ID的任何记录分配新ID.
Allocate new id's to any records without an id.
批量替换数据库中的数据(用一条语句插入多行).
bulk replace data in the database ( inserting multiple lines with a single statement ).
这篇关于棘手的MySQL批处理查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
