Warning: Cannot use a scalar value as an array in /home/admin/public_html/forum/include/fm.class.php on line 757

Warning: Invalid argument supplied for foreach() in /home/admin/public_html/forum/include/fm.class.php on line 770
Форумы портала PHP.SU :: Версия для печати :: нахождение большого количества строк по отсутствию ID в другой таблице
Форумы портала PHP.SU » » Работа с СУБД » нахождение большого количества строк по отсутствию ID в другой таблице

Страниц (1): [1]
 

1. MrBeard - 29 Ноября, 2011 - 09:59:41 - перейти к сообщению
Добрый день.
есть довольно большая архивная база данных (около 200Гб) из нескольких таблиц примерно следующего типа -
CODE (SQL):
скопировать код в буфер обмена
  1. id_session(INT) | id_counter(INT) | value(BIGINT OR TEXT)

на мой взгляд(сейчас) не самая удачная конструкция, но ... такая уж есть=)
есть другая база данных (с актуальной информацией), в которой есть таблица, хранящая актуальные данные по сессиям.
Необходимо - удалить из архивной БД все записи с id_session, которых нету в актуальной базе данных.
мне знаний хватает только на то, чтобы родить следующий запрос
CODE (SQL):
скопировать код в буфер обмена
  1. DELETE FROM db1.table1
  2. WHERE id_session NOT IN (SELECT id_session FROM db2.table1)

а ещё я знаю, что подобный запрос у меня будет работать до китайской пасхи=)
В связи с этим два вопроса
- как MySQL оптимизирует подобный запрос и где об этом почитать?
- есть ли конструкции, которые помогут ускорить выполнение требуемой задачи?
(Добавление)
можно, кстати, сначала найти id_session, которых нету в актуальной БД, поместить их в темповую БД и удалять уже, используя IN вместо NOT IN
подозреваю, что это ускорит работу, но не колоссально=(
2. EuGen - 29 Ноября, 2011 - 10:42:37 - перейти к сообщению
Кросс-БД запросы будут работать крайне медленно почти всегда.
Скопируйте имеющуюся таблицу с актуальными данными в архивную БД и сделайте через JOIN (приведу пример - если я правильно понял какая таблица - архивная, а какая - актуальная. Поменяете местами, если не так)
CODE (SQL):
скопировать код в буфер обмена
  1. DELETE FROM table1 LEFT JOIN table2 ON table1.id_session=table2.id_session WHERE table2.id_session IS NULL

- но и это не даст "колоссального" ускорения, если отсутствуют индексы.

Про оптимизацию лучше всего читать на официальном сайте: http://dev[dot]mysql[dot]com/doc/refman/[dot][dot][dot]ptimization[dot]html
3. Самогонщик - 29 Ноября, 2011 - 10:50:28 - перейти к сообщению
А может вообще не удалять? выдернуть только актуальные записи, сложить в другую табличку. Грохнуть архив, переименовать "другую табличку" в архив. Будет актуально если удалять нужно "вообще много как". Тут и индексы в архивной табличке очень сильно помогут.
4. MrBeard - 29 Ноября, 2011 - 11:14:27 - перейти к сообщению
EuGen пишет:
Кросс-БД запросы будут работать крайне медленно почти всегда.
Скопируйте имеющуюся таблицу с актуальными данными в архивную БД и сделайте через JOIN (приведу пример - если я правильно понял какая таблица - архивная, а какая - актуальная. Поменяете местами, если не так)
CODE (SQL):
скопировать код в буфер обмена
  1. DELETE FROM table1 LEFT JOIN table2 ON table1.id_session=table2.id_session WHERE table2.id_session IS NULL

- но и это не даст "колоссального" ускорения, если отсутствуют индексы.


индексы по id_session присутствуют, но количество строк в архивной БД - чуть больше 2х миллиардов. Посмотрим, не встанет ли мускуль раком=)
5. EuGen - 29 Ноября, 2011 - 11:21:45 - перейти к сообщению
MrBeard пишет:
чуть больше 2х миллиардов

У меня больше 200млн на InnoDB не получалось - СУБД не тянула. Правда, там и BLOB-ы были и полей гораздо больше (около полутора десятка).

Если такие таблицы действительно нужны, то лучше применить разбиение (sharding) по устойчивому признаку.
6. Stierus - 29 Ноября, 2011 - 12:44:55 - перейти к сообщению
Выгружаем архивную базу в файл с сортировкой по сессионному id. Выгружаем айдишники сессий из актуальной базы так же в файл с такой же сортировкой (выводв никсах перенаправлять, думаю, умеем?). Пишем скрипт, держащий 2 открытых дескриптора на эти файлы и построчно сравнивающий записи (в случае несоответсвия - в зависимости от того, что больше - смещаем позицию на одном из дескрипторов). Таким образом за один проход по файлам у нас будет список необходимых для удаления (млм, наоборот, для сохранения - не важно) айдишников.

Месяц назад писал скрипт, ищущий изменения за сутки на таблицах суммарно в 7 гиг - отрабатывает за 10 минут (там логика работы с составными ключами + сравнение больших строк на идентичность ... если тупо сравнение по 1 примари полю - минуты за 4, думаю, справился бы на моих 7 гигах). Если то же самое писать на си - можно ожидать еще большего ускорения (хотя для одноразового запуска, думаю, это излишне).
(Добавление)
у вас таблиц архивных с одинаковой структурой и из них нужно удалить все совпадения - значит для каждой таблицы придется делать отдельную выгрузку и по каждой из выгрузок запускать скрипт, ищущий дубликаты.
файл1 - файл с айдишниками сессий из актуальной базы
файл2 - файл, содержащий слепок из архивной таблицы ( одной). Структура файла: на 1 строке файла - 1 запись из таблицы, строка выглядит так: "айди сессии__sql-запрос на вставку строки данных"
запускается скрипт - открывается оба файла
в цикле читается 1 строка из файла1 и 1 строка из файла2 ... из этих строк берутся айдишники сессий. Если айдишники совпадают - значит надо удалять, отмечаем где надо и берем следующую строку из файл1 и файл2. Если айдишники не совпадают - смотрим, какой айдишник больше. Если больше айдишник из файла1 - значит мы берем следующую строку из файл2 и делаем проверку заного, если больше айдищник из файл2 - берем следующую строку из файл1 и делаем проверку . Таким образом у нас получается 1 прогон по файлам, отсутствие утечки памяти, стабильность и высокая скорость.
7. MrBeard - 29 Ноября, 2011 - 13:30:56 - перейти к сообщению
Stierus
архивная БД и актуальная не идентичны по полям. может, я не совсем верно их обозвал, но смысл следующий - в актуальной в основной таблице около 20 столбцов ещё куча связанных таблиц, туда вытягиваются данные определённые из нужных сессий. в основной таблице дублирующихся сессий нет. Архивная БД сохраняет ВООБЩЕ ВСЁ, что мне шлют в сессиях, а это - произвольное количество полей. потому в таблице с одним id_session может быть 20-150 строк.
В прочем, пока я это писал, до меня дошло, что можно сделать группировку по id_session и получится применить ваш скрипт
другой вопрос - выгрузка данных + проход скриптом + загрузка результата в БД будет быстрее, чем
CODE (SQL):
скопировать код в буфер обмена
  1. CREATE TABLE tmp_sessions engine=memory
  2.     SELECT table1.id_session
  3.     FROM table1
  4.     LEFT JOIN table2 USING(id_session)
  5.     WHERE table2.id_session IS NULL
  6.     GROUP BY id_session;
8. Stierus - 29 Ноября, 2011 - 13:35:04 - перейти к сообщению
Если вам нужно очистить все совпадающие сессии (при этом сравнивая только по айди) - какая разница сколько полей? Делайте выгрузки этих айдишников, сравнивайте, все совпадения - сохраняйте в отдельный файл, например. Потом прочтете этот файл и сделаете dele from ... where session_id in (тут 100 айдишников из сгенерированного файла) И так пока весь файл не закончится.
(Добавление)
Цитата:
другой вопрос - выгрузка данных + проход скриптом + загрузка результата в БД будет быстрее, чем
Выгрузка в файл, прогон скрипта и удаление всего лишнего (в задании вы писали, что вам надо очистить старые таблицы от лишнего).

Написание скрипта займет день-два (вместе с тестом на меньших данных и отладка) ... само выполнение будет быстрее чем Inner Join на таких объемах - это точно Улыбка
9. MrBeard - 29 Ноября, 2011 - 13:48:59 - перейти к сообщению
EuGeN, Stierus
Спасибо
10. MrBeard - 30 Ноября, 2011 - 09:58:02 - перейти к сообщению
Query OK, 411588105 rows affected (14 hours 3 min 54.24 sec)

всего то...=)
11. Stierus - 30 Ноября, 2011 - 10:16:55 - перейти к сообщению
Лень взяла верх над стремлением к красоте и скорости Улыбка
12. MrBeard - 30 Ноября, 2011 - 10:39:49 - перейти к сообщению
я бы назвал это элементарным математическим расчётом

для одноразовой операции 10 минут написания запроса + 14 часов выполнения его без моего вмешательства выиграли у 1-2 дней написания скрипта + непрогнозируемого времени удаления этих же полумиллиарда строк=)
13. EuGen - 30 Ноября, 2011 - 10:40:41 - перейти к сообщению
А какой вариант дал 14 часов исполнения запроса?
14. MrBeard - 30 Ноября, 2011 - 10:45:26 - перейти к сообщению
CODE (SQL):
скопировать код в буфер обмена
  1. DELETE table1  FROM table1
  2. LEFT JOIN table2 ON table1.id_session = table2.id_session
  3. WHERE table2.id_session IS NULL;

 

Powered by ExBB FM 1.0 RC1