mysql批处理查询_棘手的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...

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!

EDIT: 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.)

解决方案

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.

Update:

Select any existing id's out of the database (ideally a single select for all known data).

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.

Allocate new id's to any records without an id.

bulk replace data in the database ( inserting multiple lines with a single statement ).