MySQL INSERT ... SELECT большой набор данных из 420 миллионов записей

У меня есть большой набор данных из примерно 420 миллионов записей, и я смог загрузить их во временную таблицу своевременно, примерно за 15 минут, с помощью оператора LOAD DATA INFILE. Мне нужна эта временная таблица для размещения данных, потому что я немного очищаю ее перед загрузкой в ​​конечный пункт назначения.

Временная таблица определяется как:

CREATE TABLE `temporary_data` (
  `t_id` smallint(10) unsigned NOT NULL,
  `s_name` varchar(512) NOT NULL,
  `record_type` varchar(512) NOT NULL,
  `record_value` varchar(512) NOT NULL
) ENGINE=MyISAM;

Целевая таблица, в которую необходимо загрузить эти данные, называется my_data и определяется как:

CREATE TABLE `my_data` (
  `s_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `t_id` smallint(10) unsigned NOT NULL,
  `s_name` varchar(63) NOT NULL,
  PRIMARY KEY (`s_id`),
  UNIQUE KEY `IDX_MY_DATA_S_NAME_T_ID` (`t_id`,`s_name`) USING BTREE,
  KEY `IDX_MY_DATA_S_NAME` (`s_name`) USING BTREE,
  CONSTRAINT `FK_MY_DATA_MY_PARENT` FOREIGN KEY (`t_id`) REFERENCES `my_parent` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Проблема в том, что запрос на загрузку данных из временной таблицы в my_data выполняется очень медленно, как я подозревал, потому что my_data содержит два индекса и первичный ключ. До сих пор он работал более 6 часов с этим запросом:

INSERT IGNORE INTO my_data (t_id, s_name)
SELECT t_id, s_name
FROM temporary_data;

Мне нужно определить способ ускорить этот запрос, чтобы он выполнялся своевременно (идеальным вариантом было бы менее 30 минут).

Некоторые подходы, которые я рассмотрел:

  1. Отключить индексы: Я мог бы обойтись без отключения / удаления индекса IDX_MY_DATA_S_NAME, но я полагаюсь на уникальный индекс (IDX_MY_DATA_S_NAME_T_ID), чтобы сохранить данные в чистоте. Это ежедневный процесс, который будет выполняться автоматически, и неизбежно будут дублироваться. Кроме того, похоже, что перестроение индекса для такого большого набора данных займет столько же времени, когда я снова включу индекс.
  2. Используйте DATA OUTFILE: Экспорт и повторный импорт очищенных данных непосредственно в my_data. Я где-то видел, что это рекомендуется, но, подумав об этом, индексы / PK все равно будут предметом разногласий при повторной вставке.
  3. Поменяйте местами таблицы: Замена my_data на temporary_data звучит привлекательно, но в этой таблице много взаимосвязей внешних ключей для поля s_id, поэтому я хотел бы получить некоторую уверенность в том, что этот подход стоит хлопот по отключению внешних ключей и их повторному включению. Дочерние таблицы будут содержать значительно меньше записей, чем my_data, поэтому повторное включение внешних ключей может быть незначительным в этом отношении.
  4. ЗАГРУЗИТЬ ДАННЫЕ В ФАЙЛ напрямую: Загрузите данные непосредственно в my_data, используя условные обозначения в части SET оператора, чтобы сделать все поля NULL, когда они не соответствуют критериям очистки, которые я изначально применял к temporary_data перед загрузкой их в my_data. Это хакерский метод, но он основан на предположении, что LOAD DATA INFILE будет быстрее, чем INSERT ... SELECT даже при индексировании, и после его запуска будет только одна строка нулей, которую нужно будет удалить из-за уникального ограничения в таблице. .

Ни одна из этих идей не кажется ужасно отличной. Если у кого-то есть какие-то советы, я все уши.

Что показывает show status like '%inno%wait%';?

alvits 26.10.2018 03:08

Innodb_buffer_pool_wait_free 0 Innodb_log_waits 0 Innodb_row_lock_current_waits 0 Innodb_row_lock_waits 0

Adam 26.10.2018 03:54

Я использую отдельный файл для режима таблицы и вижу, что файл .ibd продолжает расти. show processlist показывает, что он выполняется также в состоянии sending data.

Adam 26.10.2018 03:55

Просмотрите советы и приемы здесь.

Rick James 27.10.2018 04:32

Спасибо за ссылку. Я обязательно это проверю.

Adam 28.10.2018 04:21
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
2
5
963
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Избавьтесь от s_id, он, наверное, бесполезен. Затем продвигайте UNIQUE(t_id, s_name) to be thePRIMARY KEY`. Это сокращает количество тестов, выполняемых для каждой вставленной строки.

Рассмотрите возможность отключения FOREIGN KEYs; в конце концов, им необходимо выполнить проверку, которая может оказаться избыточной.

INSERT IGNORE INTO my_data (t_id, s_name)
    SELECT t_id, s_name
    FROM temporary_data
    ORDER BY t_id, s_name;  -- Add this

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

Вы увеличиваете стол? Или заменить? При замене есть подход намного лучше.

Более...

Вы заметили, что INSERT IGNORE тратит значение AUTO_INCREMENT для каждой строки, которая не вставляется? Попробуем другой подход ...

INSERT INTO my_data (t_id, s_name)
    SELECT t.t_id, t.s_name
        FROM temporary_data AS t
        LEFT JOIN my_data AS m  USING(t_id, s_name)
        WHERE m.s_id IS NULL
        ORDER BY t.t_id, t.s_name;

ORDER BY избегает прыжков во время INSERT.
LEFT JOIN ограничивает активность "новыми" строками. Никакие значения AUTO_INCREMENT не будут записаны.

Сколько строк будет вставляться каждый раз? Если это миллионы, то лучше разбить на куски. См. Мой обсуждение о фрагментировании. Это мая будет быстрее, чем построение огромного следа отмены, чтобы в конечном итоге бросить.

Дальнейшее обсуждение - Дано

my_data:  PRIMARY KEY(s_id)  -- and s_id is AUTO_INCREMENT
my_data:  INDEX(t_id, s_name)
INSERT...SELECT...ORDER BY (t_id, s_name)  -- same as index

Они эффективны:

  • Поскольку ORDER BY и вторичный индекс одинаковы, добавления в индекс будут выполняться эффективно.
  • Между тем, новые значения AUTO_INCREMENT будут генерироваться последовательно на «конце» таблицы.

Единственное, что лучше было бы, если бы (t_id, s_name) был Уникальным. Затем мы могли бы полностью избавиться от s_id и заменить два индекса на этот:

PRIMARY KEY(t_id, s_name)

Это было бы проблемой, если бы другие таблицы ссылались на s_id. Обходной путь возможный - сохранить s_id и иметь

PRIMARY KEY(t_id, s_name)
INDEX(s_id)   -- sufficient for AUTO_INCREMENT

Я недостаточно знаю общую картину и другие вопросы, чтобы судить, в каком направлении двигаться. Итак, мое первоначальное предложение (до «Дальнейшего обсуждения») было «консервативным».

К сожалению, я увеличиваю. Я создаю ежедневную работу ETL, чтобы загрузить 420 миллионов записей. Цель состоит в том, чтобы добавлять новые и никогда не удалять их. Это закончилось примерно через 12 часов. Я не могу избавиться от s_id, потому что он используется в качестве внешнего ключа примерно для семи других таблиц, которые будут гораздо более разреженными, чем то, что хранится в my_data. Я разработал это таким образом, чтобы не хранить кучу пустых полей для дополнительных данных, которые появятся позже, тем самым сэкономив место. Я попробую новый запрос с предложением ORDER BY и посмотрю, поможет ли это. Я тоже работаю над переходом на SSD.

Adam 28.10.2018 04:23

@Adam - Я добавил еще в свой ответ. @ Adam

Rick James 28.10.2018 06:27

Вы по-прежнему рекомендовали бы сохранить пункт ORDER BY t.t_id, t.s_name;, если PK останется s_id? Даже если составной уникальный индекс t_id и s_name не является PK, принудительная их вставка в одном и том же порядке каждый раз "заставит" их вставлять в порядке PK, верно?

Adam 19.11.2018 02:35

@Adam - См. Мое "дальнейшее обсуждение".

Rick James 19.11.2018 03:22

Спасибо за вашу помощь в этом. Интересно, что когда я запускаю запрос сейчас с ORDER BY, я вижу его в списке процессов, создавая индекс сортировки из-за предложения ORDER BY, а запуск df -h / mnt / * в моем поле Digital Ocean показывает размер тома У меня есть MySQL tmpdir, который постоянно увеличивается. Затем примерно через 20-30 минут список процессов показывает, что команда теперь находится в "спящем" состоянии, а пространство tmpdir освобождено. Результаты не вставляются, и ошибки нет. Он просто говорит, что процесс спит.

Adam 20.11.2018 17:28

@Adam - Sleep означает, что соединение все еще существует (возможно, "объединенное"), но соединение в данный момент не выполняет никакого SQL. Это нормально и безвредно. Постепенное увеличение и резкое сокращение используемого пространства tmpdir означает, что запрос был достаточно сложным, чтобы потребовалась временная таблица на диске. В ситуациях некоторые этого можно избежать. Возможно понижение 512 позволит избежать потребности в температуре на диске.

Rick James 20.11.2018 17:35

Другие вопросы по теме