датаваровед

[anspa]

В прикладной базе данных (mysql 5.0, myisam) есть некоторый класс больших таблиц, всего их к примеру 30 штук. Новые данные в эти таблицы загружаются почти каждый день. Записи сами по себе короткие, скажем до 50 байт. Количество записей в каждой из таблиц остается примерно одинаковым - от 1 миллиона до 20 миллионов (на самом деле число записей постепенно растет, скажем на 5-10% с каждой загрузкой новых данных, т.е. не очень существенно, а может и вообще не расти). Ежедневно новые данные поступают примерно в том же объеме что и количество старых записей, т.е. если в таблице А был 1 миллион то загружаем еще 1 миллион каждый день. Но затем нужно удалить все дубликаты записей по некоему составному ключу, т.е. 90% старых записей "перетерлись" новыми, в таблице должны остаться 10% старых записей плюс новые записи.

Итак, операции по обработке данных а) загрузка порции новых данных б) удаление дубликатов. Вопрос - как оптимально и за приемлемое время, и в то же время не сильно нагружая базу данных избавиться от дубликатов после загрузки новых данных во всех таблицах, параллельно. Если на загрузку 1 гигабайта информации в таблицу на обычном PC-сервере уходит примерно 20-30 минут (с некоторой пред-обработкой и перестройкой индексов), то чтобы отделить и пометить мух (дубликаты) от котлет в этом гигабайте - вполне может уйти много часов, если учесть что база данных еще и работает по другим запросам, а таблицы желательно обрабатывать по нескольку штук сразу.

Разумным было бы решение грузить новые данные в другую таблицу, потом искать пересечение со старой таблицей и добавлять лишь уникальные недубликатные записи из старой таблицы в новую. Но.. пока, в общем, сделано так: структура таблицы дополнена двумя столбцами file_id int(11) и fixed char(1). File_id представляет собой номер обрабатываемого файла при загрузке. Т.е. каждая новая порция загружаемых данных будет иметь уникальный file_id для всех записей. Fixed при загрузке устанавливается в NULL по умолчанию, а процесс фильтрации дубликатов устанавливает fixed = 1 для дублированных записей, которые перетерлись новыми данными.
Итак, встречайте table A:
( file_id int(11),
id1 char(20),
id2 int(8),
col3 char(20),
col4 char(10),
fixed char(1)
);
Как ищем дубликаты:
select min(file_id) min_file_id, max(file_id) file_id, id1, id2
from A
where fixed is null
group by id1, id2
having count(*) > 1 and min_file_id < file_id
Такой запрос по 3 миллионам записей с 1.8 миллионов дубликатов выполняется где-то за 5-10 минут. Затем в цикле по всем найденным комбинациям file_id, id1, id2 исполняем update, чтоб пометить дубликаты с конкретными значениями ключей:
update A set fixed=1 where file_id < ? and id1 = ? and id2 = ?
На этот запрос и тратится основное время процедуры.
Столбцы file_id, id1 и id2 проиндексированы.

Потом, на следующий день, перед загрузкой порции новых данных выполнится delete:
delete from A where fixed = 1;

Почему так сделано: потому что иногда желательно посмотреть сразу после загрузки новых данных что было в старых а что есть в новых. Иногда делается что-то вроде просмотра какие значения с одним и тем же ключем изменились и т.п. Одним словом, старые данные желательно подержать до следующей загрузки.

Производительность такого способа - где-то 5000 записей в минуту. Т.е. часа за 3-4 можно обработать одну таблицу с миллионом записей. 10 миллионов - за 30 часов, 20 миллионов и того больше, там вообще индексы тормозить начинают и закон больших чисел для игрушечных баз данных вступает в дело..

Вопрос конечно же - можно ли сделать этот процесс оптимальнее и как (не забываем что хотелось бы обрабатывать таблиц по 5 одновременно и не слишком перегружать сервер базы данных).

Посмотрел вариант с созданием временной таблицы.. Гнусно как-то.