У меня есть большой набор данных из примерно 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 минут).
Некоторые подходы, которые я рассмотрел:
IDX_MY_DATA_S_NAME
, но я полагаюсь на уникальный индекс (IDX_MY_DATA_S_NAME_T_ID
), чтобы сохранить данные в чистоте. Это ежедневный процесс, который будет выполняться автоматически, и неизбежно будут дублироваться. Кроме того, похоже, что перестроение индекса для такого большого набора данных займет столько же времени, когда я снова включу индекс.my_data
. Я где-то видел, что это рекомендуется, но, подумав об этом, индексы / PK все равно будут предметом разногласий при повторной вставке.my_data
на temporary_data
звучит привлекательно, но в этой таблице много взаимосвязей внешних ключей для поля s_id
, поэтому я хотел бы получить некоторую уверенность в том, что этот подход стоит хлопот по отключению внешних ключей и их повторному включению. Дочерние таблицы будут содержать значительно меньше записей, чем my_data
, поэтому повторное включение внешних ключей может быть незначительным в этом отношении.my_data
, используя условные обозначения в части SET оператора, чтобы сделать все поля NULL
, когда они не соответствуют критериям очистки, которые я изначально применял к temporary_data
перед загрузкой их в my_data
. Это хакерский метод, но он основан на предположении, что LOAD DATA INFILE будет быстрее, чем INSERT ... SELECT даже при индексировании, и после его запуска будет только одна строка нулей, которую нужно будет удалить из-за уникального ограничения в таблице. .Ни одна из этих идей не кажется ужасно отличной. Если у кого-то есть какие-то советы, я все уши.
Innodb_buffer_pool_wait_free 0 Innodb_log_waits 0 Innodb_row_lock_current_waits 0 Innodb_row_lock_waits 0
Я использую отдельный файл для режима таблицы и вижу, что файл .ibd продолжает расти. show processlist
показывает, что он выполняется также в состоянии sending data
.
Спасибо за ссылку. Я обязательно это проверю.
Избавьтесь от s_id
, он, наверное, бесполезен. Затем продвигайте UNIQUE(t_id, s_name) to be the
PRIMARY 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 - Я добавил еще в свой ответ. @ Adam
Вы по-прежнему рекомендовали бы сохранить пункт ORDER BY t.t_id, t.s_name;
, если PK останется s_id
? Даже если составной уникальный индекс t_id и s_name не является PK, принудительная их вставка в одном и том же порядке каждый раз "заставит" их вставлять в порядке PK, верно?
@Adam - См. Мое "дальнейшее обсуждение".
Спасибо за вашу помощь в этом. Интересно, что когда я запускаю запрос сейчас с ORDER BY, я вижу его в списке процессов, создавая индекс сортировки из-за предложения ORDER BY, а запуск df -h / mnt / * в моем поле Digital Ocean показывает размер тома У меня есть MySQL tmpdir, который постоянно увеличивается. Затем примерно через 20-30 минут список процессов показывает, что команда теперь находится в "спящем" состоянии, а пространство tmpdir освобождено. Результаты не вставляются, и ошибки нет. Он просто говорит, что процесс спит.
@Adam - Sleep
означает, что соединение все еще существует (возможно, "объединенное"), но соединение в данный момент не выполняет никакого SQL. Это нормально и безвредно. Постепенное увеличение и резкое сокращение используемого пространства tmpdir
означает, что запрос был достаточно сложным, чтобы потребовалась временная таблица на диске. В ситуациях некоторые этого можно избежать. Возможно понижение 512
позволит избежать потребности в температуре на диске.
Что показывает
show status like '%inno%wait%';
?