MySQL InnoDB: передовая практика массовой вставки

Я пытаюсь вставить 2x 700 000 записей в таблицу InnoDB, и, на мой взгляд, это довольно медленно.

Я пробовал несколько вещей, и я не уверен, что это лучший способ добиться наиболее эффективного способа вставки.

Создайте таблицу 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;

Как видите, много индексов и внешних ключей (innoDb нужен индекс для каждого внешнего ключа), но индексы замедляют вставку, поэтому я пытаюсь добавить их после вставки с помощью оператора alter:

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;

Не уверен, что транзакция необходима.

В верхней части моего скрипта я указал следующие параметры:

SET foreign_key_checks=0;
SET unique_checks=0;

Дно:

SET unique_checks = 1;
SET foreign_key_checks = 1;

И оператор вставки 2x 700 000 (для этого всего 2 строки)

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;

Как видите, единственная разница во вставке — идентификатор действия (1 -> 2).

Так что мне было интересно, это путь или я что-то упускаю здесь?

Последний вывод из рабочей среды MySQL:

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

mysql innodb insert bulk
person darkownage    schedule 11.06.2013    source источник

Ответы (1)


arrow_upward
4
arrow_downward

Эта страница руководства также предлагает изменение innodb_autoinc_lock_mode.

Если вам не нужна эта функция, отключить ведение двоичного журнала.

Также может помочь увеличение размера некоторых буферов InnoDB ( в частности, innodb_buffer_pool_size).

Я считаю, что использование транзакции в этом случае нежелательно. Если в рамках одной транзакции необходимо применить небольшое количество последовательных изменений, эти изменения можно оптимизировать, объединив их в одну запись. В вашем случае, я полагаю, вы только бесполезно загрузите свой журнал повторов.

Это приводит меня к другому предложению: попробуйте вставлять меньшее количество строк за раз, например:

INSERT INTO destination
SELECT * FROM source LIMIT 0, 10000;

INSERT INTO destination
SELECT * FROM source LIMIT 10000, 10000; -- and so on

Наконец, если у вас много доступной памяти, вы можете вручную загрузить все данные во временную таблицу памяти, а затем вставить в место назначения из этой таблицы памяти (возможно, небольшими партиями):

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;

Убедитесь, что для max_heap_table_size< указано достаточно большое значение. /а>.

person RandomSeed    schedule 11.06.2013
comment
Спасибо, есть полезные советы :) - person darkownage; 12.06.2013