У меня есть большой набор данных примерно из 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
), чтобы данные оставались чистыми. Это ежедневный процесс, который будет выполняться автоматически, и неизбежно будут некоторые дубликаты. Кроме того, кажется, что перестроение индекса для такого большого набора данных, когда я снова включу индекс, займет столько же времени. - Использовать DATA OUTFILE: экспортировать и повторно импортировать очищенные данные непосредственно в
my_data
. Я где-то видел, что это рекомендуется, но, подумав об этом, индексы/PK все равно будут спорным моментом при повторной вставке. - Поменяйте местами таблицы. Замена
my_data
наtemporary_data
звучит привлекательно, но эта таблица имеет множество взаимосвязей внешних ключей для поляs_id
, поэтому я хочу быть уверенным, что этот подход стоит хлопот с отключением внешних ключей и повторной включение их. Дочерние таблицы будут содержать значительно меньше записей, чемmy_data
, поэтому в этом отношении повторное включение внешних ключей может быть незначительным. - ЗАГРУЗИТЬ ДАННЫЕ В ФАЙЛ напрямую: загружать данные непосредственно в
my_data
, используя условные операторы в части оператора SET, чтобы сделать все поляNULL
, если они не соответствуют критериям очистки, которые я первоначально применил кtemporary_data
перед загрузкой вmy_data
. Это хакерство, но оно основано на предположении, что LOAD DATA INFILE будет быстрее, чем INSERT... SELECT даже перед лицом индексации, и будет только одна строка нулей для удаления после ее запуска из-за уникального ограничения на таблицу .
Ни одна из этих идей не похожа на ужасно великую. Если у кого-то есть какие-либо советы, я весь слух.
s_id
, потому что он используется в качестве внешнего ключа примерно для семи других таблиц, которые будут намного более разреженными, чем то, что хранится вmy_data
. Я разработал его таким образом, чтобы не хранить кучу пустых полей для необязательных данных, которые появятся позже, тем самым сэкономив немного места. Я попробую новый запрос с предложениемORDER BY
и посмотрю, поможет ли это. Я также работаю над переходом на SSD. 28.10.2018ORDER BY t.t_id, t.s_name;
, если ПК останетсяs_id
? Несмотря на то, что составной уникальный индекс t_id и s_name не является PK, принудительная вставка их в одном и том же порядке каждый раз заставит его вставляться в порядке PK, верно? 19.11.2018Sleep
означает, что соединение все еще существует (возможно, в пуле), но в данный момент соединение не выполняет никакого SQL. Это нормально и безвредно. Постепенное увеличение и резкое сокращениеtmpdir
используемого пространства означает, что запрос был достаточно сложным, чтобы нуждаться во временной таблице на диске. В некоторых ситуациях этого можно избежать. Возможно снижение512
позволит избежать необходимости хранения временных файлов на диске. 20.11.2018