PHP.SU

Программирование на PHP, MySQL и другие веб-технологии

PHP.SU Портал

Главная

Помощь

Поиск

Поиск Яндекс Вакансии

Пользователи

Здравствуйте, Гость

( Вход · Регистрация · Правила форума )

Забыли пароль?

нахождение большого количества строк по отсутствию ID в другой таблице

Форумы портала PHP.SU » PHP » Программирование на PHP » Работа с СУБД (Модераторы: OrmaJever, Саныч)

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

Без описания

Поиск в теме | Версия для печати

MrBeard	Отправлено: 29 Ноября, 2011 - 09:59:41
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	Добрый день. есть довольно большая архивная база данных (около 200Гб) из нескольких таблиц примерно следующего типа - CODE (SQL): скопировать код в буфер обмена id_session(INT) \| id_counter(INT) \| value(BIGINT OR TEXT) на мой взгляд(сейчас) не самая удачная конструкция, но ... такая уж есть=) есть другая база данных (с актуальной информацией), в которой есть таблица, хранящая актуальные данные по сессиям. Необходимо - удалить из архивной БД все записи с id_session, которых нету в актуальной базе данных. мне знаний хватает только на то, чтобы родить следующий запрос CODE (SQL): скопировать код в буфер обмена DELETE FROM db1.table1 WHERE id_session NOT IN (SELECT id_session FROM db2.table1) а ещё я знаю, что подобный запрос у меня будет работать до китайской пасхи=) В связи с этим два вопроса - как MySQL оптимизирует подобный запрос и где об этом почитать? - есть ли конструкции, которые помогут ускорить выполнение требуемой задачи? (Добавление) можно, кстати, сначала найти id_session, которых нету в актуальной БД, поместить их в темповую БД и удалять уже, используя IN вместо NOT IN подозреваю, что это ускорит работу, но не колоссально=(

EuGen	Отправлено: 29 Ноября, 2011 - 10:42:37
Профессионал Покинул форум Сообщений всего: 9095 Дата рег-ции: Июнь 2007 Откуда: Berlin Помог: 707 раз(а)	Кросс-БД запросы будут работать крайне медленно почти всегда. Скопируйте имеющуюся таблицу с актуальными данными в архивную БД и сделайте через JOIN (приведу пример - если я правильно понял какая таблица - архивная, а какая - актуальная. Поменяете местами, если не так) CODE (SQL): скопировать код в буфер обмена 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 ----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

Самогонщик	Отправлено: 29 Ноября, 2011 - 10:50:28
Посетитель Покинул форум Сообщений всего: 495 Дата рег-ции: Окт. 2011 Помог: 8 раз(а)	А может вообще не удалять? выдернуть только актуальные записи, сложить в другую табличку. Грохнуть архив, переименовать "другую табличку" в архив. Будет актуально если удалять нужно "вообще много как". Тут и индексы в архивной табличке очень сильно помогут.

MrBeard	Отправлено: 29 Ноября, 2011 - 11:14:27
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	EuGen пишет: Кросс-БД запросы будут работать крайне медленно почти всегда. Скопируйте имеющуюся таблицу с актуальными данными в архивную БД и сделайте через JOIN (приведу пример - если я правильно понял какая таблица - архивная, а какая - актуальная. Поменяете местами, если не так) CODE (SQL): скопировать код в буфер обмена DELETE FROM table1 LEFT JOIN table2 ON table1.id_session=table2.id_session WHERE table2.id_session IS NULL - но и это не даст "колоссального" ускорения, если отсутствуют индексы. индексы по id_session присутствуют, но количество строк в архивной БД - чуть больше 2х миллиардов. Посмотрим, не встанет ли мускуль раком=)

EuGen	Отправлено: 29 Ноября, 2011 - 11:21:45
Профессионал Покинул форум Сообщений всего: 9095 Дата рег-ции: Июнь 2007 Откуда: Berlin Помог: 707 раз(а)	MrBeard пишет: чуть больше 2х миллиардов У меня больше 200млн на InnoDB не получалось - СУБД не тянула. Правда, там и BLOB-ы были и полей гораздо больше (около полутора десятка). Если такие таблицы действительно нужны, то лучше применить разбиение (sharding) по устойчивому признаку. ----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

Stierus	Отправлено: 29 Ноября, 2011 - 12:44:55
Рекордсмен по количеству сообщений за 7 дней Покинул форум Сообщений всего: 2132 Дата рег-ции: Дек. 2008 Откуда: Москваль Помог: 52 раз(а)	Выгружаем архивную базу в файл с сортировкой по сессионному id. Выгружаем айдишники сессий из актуальной базы так же в файл с такой же сортировкой (выводв никсах перенаправлять, думаю, умеем?). Пишем скрипт, держащий 2 открытых дескриптора на эти файлы и построчно сравнивающий записи (в случае несоответсвия - в зависимости от того, что больше - смещаем позицию на одном из дескрипторов). Таким образом за один проход по файлам у нас будет список необходимых для удаления (млм, наоборот, для сохранения - не важно) айдишников. Месяц назад писал скрипт, ищущий изменения за сутки на таблицах суммарно в 7 гиг - отрабатывает за 10 минут (там логика работы с составными ключами + сравнение больших строк на идентичность ... если тупо сравнение по 1 примари полю - минуты за 4, думаю, справился бы на моих 7 гигах). Если то же самое писать на си - можно ожидать еще большего ускорения (хотя для одноразового запуска, думаю, это излишне). (Добавление) у вас таблиц архивных с одинаковой структурой и из них нужно удалить все совпадения - значит для каждой таблицы придется делать отдельную выгрузку и по каждой из выгрузок запускать скрипт, ищущий дубликаты. файл1 - файл с айдишниками сессий из актуальной базы файл2 - файл, содержащий слепок из архивной таблицы ( одной). Структура файла: на 1 строке файла - 1 запись из таблицы, строка выглядит так: "айди сессии__sql-запрос на вставку строки данных" запускается скрипт - открывается оба файла в цикле читается 1 строка из файла1 и 1 строка из файла2 ... из этих строк берутся айдишники сессий. Если айдишники совпадают - значит надо удалять, отмечаем где надо и берем следующую строку из файл1 и файл2. Если айдишники не совпадают - смотрим, какой айдишник больше. Если больше айдишник из файла1 - значит мы берем следующую строку из файл2 и делаем проверку заного, если больше айдищник из файл2 - берем следующую строку из файл1 и делаем проверку . Таким образом у нас получается 1 прогон по файлам, отсутствие утечки памяти, стабильность и высокая скорость.

MrBeard	Отправлено: 29 Ноября, 2011 - 13:30:56
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	Stierus архивная БД и актуальная не идентичны по полям. может, я не совсем верно их обозвал, но смысл следующий - в актуальной в основной таблице около 20 столбцов ещё куча связанных таблиц, туда вытягиваются данные определённые из нужных сессий. в основной таблице дублирующихся сессий нет. Архивная БД сохраняет ВООБЩЕ ВСЁ, что мне шлют в сессиях, а это - произвольное количество полей. потому в таблице с одним id_session может быть 20-150 строк. В прочем, пока я это писал, до меня дошло, что можно сделать группировку по id_session и получится применить ваш скрипт другой вопрос - выгрузка данных + проход скриптом + загрузка результата в БД будет быстрее, чем CODE (SQL): скопировать код в буфер обмена CREATE TABLE tmp_sessions engine=memory SELECT table1.id_session FROM table1 LEFT JOIN table2 USING(id_session) WHERE table2.id_session IS NULL GROUP BY id_session;

Stierus	Отправлено: 29 Ноября, 2011 - 13:35:04
Рекордсмен по количеству сообщений за 7 дней Покинул форум Сообщений всего: 2132 Дата рег-ции: Дек. 2008 Откуда: Москваль Помог: 52 раз(а)	Если вам нужно очистить все совпадающие сессии (при этом сравнивая только по айди) - какая разница сколько полей? Делайте выгрузки этих айдишников, сравнивайте, все совпадения - сохраняйте в отдельный файл, например. Потом прочтете этот файл и сделаете dele from ... where session_id in (тут 100 айдишников из сгенерированного файла) И так пока весь файл не закончится. (Добавление) Цитата: другой вопрос - выгрузка данных + проход скриптом + загрузка результата в БД будет быстрее, чем Выгрузка в файл, прогон скрипта и удаление всего лишнего (в задании вы писали, что вам надо очистить старые таблицы от лишнего). Написание скрипта займет день-два (вместе с тестом на меньших данных и отладка) ... само выполнение будет быстрее чем Inner Join на таких объемах - это точно

MrBeard	Отправлено: 29 Ноября, 2011 - 13:48:59
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	EuGeN, Stierus Спасибо

MrBeard	Отправлено: 30 Ноября, 2011 - 09:58:02
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	Query OK, 411588105 rows affected (14 hours 3 min 54.24 sec) всего то...=)

Stierus	Отправлено: 30 Ноября, 2011 - 10:16:55
Рекордсмен по количеству сообщений за 7 дней Покинул форум Сообщений всего: 2132 Дата рег-ции: Дек. 2008 Откуда: Москваль Помог: 52 раз(а)	Лень взяла верх над стремлением к красоте и скорости

MrBeard	Отправлено: 30 Ноября, 2011 - 10:39:49
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	я бы назвал это элементарным математическим расчётом для одноразовой операции 10 минут написания запроса + 14 часов выполнения его без моего вмешательства выиграли у 1-2 дней написания скрипта + непрогнозируемого времени удаления этих же полумиллиарда строк=)

EuGen	Отправлено: 30 Ноября, 2011 - 10:40:41
Профессионал Покинул форум Сообщений всего: 9095 Дата рег-ции: Июнь 2007 Откуда: Berlin Помог: 707 раз(а)	А какой вариант дал 14 часов исполнения запроса? ----- Есть в мире две бесконечные вещи - это Вселенная и человеческая глупость. Но насчет первой .. я не уверен.

MrBeard	Отправлено: 30 Ноября, 2011 - 10:45:26
Гость Покинул форум Сообщений всего: 97 Дата рег-ции: Авг. 2011 Помог: 2 раз(а)	CODE (SQL): скопировать код в буфер обмена DELETE table1 FROM table1 LEFT JOIN table2 ON table1.id_session = table2.id_session WHERE table2.id_session IS NULL;

Поиск в теме | Версия для печати

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

Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)

« Работа с СУБД »

Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.