MySQL-InnoDB: Best-practice-bulk-insert

Ich versuche zu legen 2x 700 000 Datensätze in eine InnoDB-Tabelle und es ist meiner Meinung nach eher langsam.

Ich habe versucht, verschiedene Dinge, und ich bin nicht sicher, was ist der beste Weg, um möglichst effiziente Weise einsetzen.

Create table-sql:

DROP TABLE IF EXISTS `booking_daily_analysis`;
CREATE TABLE IF NOT EXISTS `booking_daily_analysis` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `booking_id` INT NULL,
  `action_id` INT NOT NULL,
  `creative_id` INT NULL,
  `position_id` INT NULL,
  `profile_id` INT NULL,
  `start` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `end` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `hits` INT NOT NULL DEFAULT 0,
  `uniqueHits` INT NOT NULL DEFAULT 0 COMMENT 'contacts van vroeger',
  PRIMARY KEY (`id`,`action_id`)
  #INDEX `booking_id_idx` (`booking_id` ASC),
  #FOREIGN KEY (`booking_id`) REFERENCES `booking` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  #INDEX `creative_id_idx` (`creative_id` ASC),
  #FOREIGN KEY (`creative_id`) REFERENCES `creative` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  #INDEX `position_id_idx` (`position_id` ASC),
  #FOREIGN KEY (`position_id`) REFERENCES `position` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  #INDEX `action_id_idx` (`action_id` ASC),
  #FOREIGN KEY (`action_id`) REFERENCES `action` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,  
  #INDEX `profile_id_idx` (`profile_id` ASC),
  #FOREIGN KEY (`profile_id`) REFERENCES `profile` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8;

Als Sie können sehen, eine Menge von Indizes und foreign keys (innoDb muss einen index für jeden Fremdschlüssel), aber die Indizes langsam das einlegen Weg nach unten, so dass ich versuche Sie nach meinen einfügen, durch eine alter-Anweisung:

START TRANSACTION;
alter table `booking_daily_analysis` add index `booking_id_idx` (`booking_id` ASC), add constraint `fk_booking_id` foreign key (`booking_id`) REFERENCES `booking` (`id`) on delete set null on update cascade;
alter table `booking_daily_analysis` add index `creative_id_idx` (`creative_id` ASC), add constraint `fk_creative_id` foreign key (`creative_id`) references `creative` (`id`) on delete set null on update cascade;
alter table `booking_daily_analysis` add index `position_id_idx` (`position_id` ASC), add constraint `fk_position_id` foreign key (`position_id`) references `position` (`id`) on delete set null on update cascade;
alter table `booking_daily_analysis` add index `action_id_idx` (`action_id` ASC), add constraint `fk_action_id` foreign key (`action_id`) references `action` (`id`) on delete set null on update cascade;
alter table `booking_daily_analysis` add index `profile_id_idx` (`profile_id` ASC), add constraint `fk_profile_id` foreign key (`profile_id`) references `profile` (`id`) on delete set null on update cascade;
COMMIT;

Nicht sicher, ob die Transaktion notwendig ist.

Oben in meinem script habe ich angegeben diese Optionen:

SET foreign_key_checks=0;
SET unique_checks=0;

Unten:

SET unique_checks = 1;
SET foreign_key_checks = 1;

Und die 2x 700 000 insert-Anweisung (nur 2 Zeilen für diese)

START TRANSACTION;
insert into nrc.booking_daily_analysis (id, action_id, start, end, hits, uniqueHits, position_id, booking_id, creative_id, profile_id)
select id, 1, start, end, impressions, contacts, position_id, booking_id, creative_id, new_profile_id from adhese_nrc.temp_ad_slot_ids;
COMMIT;

START TRANSACTION;
-- Insert clicks for click action (click action is 2)
insert into nrc.booking_daily_analysis (id, action_id, start, end, hits, uniqueHits, position_id, booking_id, creative_id, profile_id)
select id, 2, start, end, clicks, 0, position_id, booking_id, creative_id, new_profile_id from adhese_nrc.temp_ad_slot_ids;
COMMIT;

Wie Sie sehen können, der einzige Unterschied besteht in der Einfügung der Maßnahmen-ID (1 -> 2).

Also habe ich mich gefragt, ist dies der Weg zu gehen, oder bin ich hier etwas fehlt?

Neueste Ausgabe von MySQL workbench:

14:32:13    START TRANSACTION   0 row(s) affected   0.000 sec

14:32:13    FIRST INSERT 717718 row(s) affected Records: 717718  @ 11.263 sec

14:32:24    COMMIT  0 row(s) affected   0.020 sec
14:32:24    START TRANSACTION   0 row(s) affected   0.000 sec

14:32:24    SECOND INSERT   717718 row(s) affected Records: 717718 @ 21.268 sec

14:32:46    COMMIT  0 row(s) affected   0.011 sec
14:32:46    START TRANSACTION   0 row(s) affected   0.000 sec

14:32:46    add index `booking_id_idx`  1435436 row(s) affected Records: 1435436 @ 39.393 sec
14:33:25    add index `creative_id_idx  1435436 row(s) affected Records: 1435436  @ 68.801 sec
14:34:34    add index `position_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 142.877 sec
14:36:57    add index `action_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 162.160 sec
14:40:00    add index `profile_id_idx` 1435436 row(s) affected Records: 1435436  Duplicates: 0  Warnings: 0 @ 763.309 sec

InformationsquelleAutor darkownage | 2013-06-11

4

Diese manual-Seite auch schlägt ändern der innodb_autoinc_lock_mode.

Wenn Sie brauchen nicht die Funktion, deaktivieren Binär-logging.

Erhöhung der Größe der einige InnoDB-Puffer könnte auch helfen (die innodb_buffer_pool_size im besonderen).

Ich glaube, mit einer Transaktion ist nicht wünschenswert, in diesem Fall. Wenn eine kleine Anzahl von aufeinander folgenden änderungen angewandt werden müssen, die innerhalb der gleichen Transaktion, diese änderungen können optimiert werden konsolidiert in einem einzigen schreiben. In deinem Fall glaube ich, werden Sie nur laden Sie Ihre redo-log nutzlos.

Dies führt mich zu einem anderen Vorschlag: versuchen Sie eine kleinere Anzahl von Zeilen zu einer Zeit, wie diese:
```
INSERT INTO destination
SELECT * FROM source LIMIT 0, 10000;

INSERT INTO destination
SELECT * FROM source LIMIT 10000, 10000; -- and so on
```
Schließlich, wenn Sie haben eine Menge Speicher zur Verfügung, können Sie manuell laden Sie die ganzen Daten in eine temporäre Tabelle im Speicher, dann legen Sie in Ihr Ziel, aus dieser Tabelle im Speicher (ggf. in kleinen Mengen):
```
CREATE TEMPORARY TABLE destination_tmp LIKE source;
ALTER destination_tmp ENGIN=MEMORY;
INSERT INTO destination_tmp SELECT * FROM source;
INSERT INTO destination SELECT * FROM destination_tmp;
```
Stellen Sie sicher, um einen ausreichend großen Wert für max_heap_table_size.
- Danke, einige nützliche Tipps drin 🙂
InformationsquelleAutor RandomSeed

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.