Задавайте вопросы, мы ответим
Вы не зашли.
Уважаемые! Есть две таблицы:
Отредактированно Suguby (14.02.2014 13:56:12)
Неактивен
Какая версия MySQL?
Пробовали переписать через JOIN в виде multiple-table syntax?
Неактивен
У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?
Неактивен
Suguby, рад видеть на форуме
Несколько вариантов:
1) как предложил vasya переписать через JOIN
2) до того, как чистить первую таблицу, запомнить source_id удаляемых строк
3) заменить DELETE на
Неактивен
Suguby написал:
У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?
Неактивен
Suguby написал:
У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?
multi-delete доступен давно, а вот подзапросы в IN в вашей версии ещё работают плохо. Имеет смысл перейти на MariaDB 5.3/5.5 там много плюшек. Посмотрите https://mariadb.com/kb/en/optimizer-fea … on-matrix/
Неактивен
rgbeast написал:
2) до того, как чистить первую таблицу, запомнить source_id удаляемых строк
Не получится. Судя по постановке вопроса source_id может быть несколько и не все они могут быть удалены. А из второй таблицы удаление только, если не осталось соответствия.
Неактивен
vasya написал:
Suguby написал:
У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?
DELETE hits LEFT JOIN ( SELECT source_id FROM visits UNION SELECT start_page_id FROM visits UNION SELECT end_page_id FROM visits ) t ON hits.id=t.source_id WHERE t.source_id IS NULL;
но тут же все равно три резалт сета в UNION - по 4млн каждый. думаете выйгрыш будет? а потом еще LEFT JOIN джойн с 8 млн... хотя попробовать можно. Может оптимизатор лучше сработает.
может вообще временную таблицу? хотя это тоже не выход...
я подумываю что бы
Неактивен
rgbeast написал:
Suguby, рад видеть на форуме
Несколько вариантов:
1) как предложил vasya переписать через JOIN
2) до того, как чистить первую таблицу, запомнить source_id удаляемых строк
3) заменить DELETE наUPDATE hits LEFT JOIN visits ON hits.id=visits.source_id SET hits.to_delete=1 WHERE visits.source_id IS NULL;
Потом можно удалять по несколько тысяч, а не все сразу
Привет, Григорий!
сложно получить айдишники удаляемых строк...
может
Неактивен
Suguby написал:
vasya написал:
Suguby написал:
У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?
DELETE hits LEFT JOIN ( SELECT source_id FROM visits UNION SELECT start_page_id FROM visits UNION SELECT end_page_id FROM visits ) t ON hits.id=t.source_id WHERE t.source_id IS NULL;но тут же все равно три резалт сета в UNION - по 4млн каждый. думаете выйгрыш будет? а потом еще LEFT JOIN джойн с 8 млн... хотя попробовать можно. Может оптимизатор лучше сработает.
может вообще временную таблицу? хотя это тоже не выход...
В случае delete с IN подзапросом на вашей версии, подзапрос будет выполняться для каждой строки (хотя он и является независимым).
delete через left join сделает подзапрос единожды, так что выигрыш должен быть и немалый.
Неактивен
Suguby написал:
может
UPDATE hits LEFT JOIN visits ON hits.id=visits.source_id SET hits.to_delete=1 WHERE visits.source_id IS NULL;так попробовать? все равно джойны больших таблиц...
UPDATE hits LEFT JOIN visits ON hits.id=visits.start_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.start_page_id IS NULL;
UPDATE hits LEFT JOIN visits ON hits.id=visits.end_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.end_page_id IS NULL;
DELETE FROM hits WHERE hits.to_delete = 3;
Смущают большее кол-во операций записи. Но этот вариант хорош тем, что удалять можно будет порциями, да и работать он будет по индексам. Нужно сравнивать.
Неактивен
хм
Неактивен
Это я ошибся в синтаксисе
Неактивен
vasya написал:
Это я ошибся в синтаксисе
DELETE hits from
...
сорри не заметил.
Неактивен
Suguby написал:
может
UPDATE hits LEFT JOIN visits ON hits.id=visits.source_id SET hits.to_delete=1 WHERE visits.source_id IS NULL;так попробовать? все равно джойны больших таблиц...
UPDATE hits LEFT JOIN visits ON hits.id=visits.start_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.start_page_id IS NULL;
UPDATE hits LEFT JOIN visits ON hits.id=visits.end_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.end_page_id IS NULL;
DELETE FROM hits WHERE hits.to_delete = 3;
Вадим, мне кажется подобное решение имеет шанс работать. Только не забыть перед этим:
UPDATE hits SET hits.to_delete=0 WHERE hits.to_delete>0;
Можно еще попробовать так оптимизировать последующие запросы:
UPDATE hits LEFT JOIN visits ON hits.id=visits.start_page_id SET hits.to_delete=2 WHERE hits.to_delete=1 AND visits.start_page_id IS NULL;
И индекс на to_delete.
Тут еще плюс, что разбито на несколько запросов, что уменьшает время блокировки таблицы. Если таблица не будет долго блокирована, то не важно сколько это в итоге исполняется.
Еще вариант не делать сразу UPDATE, а вставлять id во временную таблицу, а потом выбирать те, которые повторяются 3 раза. Тогда все операции, кроме последнего DELETE не будут блокировать исходные таблицы, а DELETE можно будет разбить на кусочки.
Неактивен
vasya написал:
Suguby написал:
vasya написал:
DELETE hits LEFT JOIN ( SELECT source_id FROM visits UNION SELECT start_page_id FROM visits UNION SELECT end_page_id FROM visits ) t ON hits.id=t.source_id WHERE t.source_id IS NULL;но тут же все равно три резалт сета в UNION - по 4млн каждый. думаете выйгрыш будет? а потом еще LEFT JOIN джойн с 8 млн... хотя попробовать можно. Может оптимизатор лучше сработает.
может вообще временную таблицу? хотя это тоже не выход...В случае delete с IN подзапросом на вашей версии, подзапрос будет выполняться для каждой строки (хотя он и является независимым).
delete через left join сделает подзапрос единожды, так что выигрыш должен быть и немалый.
в итоге запрос выполнялся 50 минут что в разы дольше моего варианта... (это на рабстанции у которой запись на диск про iotop 15Мб/сек - очень тормозная)
наверно из-за того что в этом подселекте получается тройная мощность множества, которое потом лефт джойнится с хитами...
следующий вариант - буду пробовать через апдейты, как rgbeast предложил.
Неактивен
rgbeast написал:
Suguby написал:
может
UPDATE hits LEFT JOIN visits ON hits.id=visits.source_id SET hits.to_delete=1 WHERE visits.source_id IS NULL;так попробовать? все равно джойны больших таблиц...
UPDATE hits LEFT JOIN visits ON hits.id=visits.start_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.start_page_id IS NULL;
UPDATE hits LEFT JOIN visits ON hits.id=visits.end_page_id SET hits.to_delete=hits.to_delete + 1 WHERE visits.end_page_id IS NULL;
DELETE FROM hits WHERE hits.to_delete = 3;Вадим, мне кажется подобное решение имеет шанс работать. Только не забыть перед этим:
UPDATE hits SET hits.to_delete=0 WHERE hits.to_delete>0;
Можно еще попробовать так оптимизировать последующие запросы:
UPDATE hits LEFT JOIN visits ON hits.id=visits.start_page_id SET hits.to_delete=2 WHERE hits.to_delete=1 AND visits.start_page_id IS NULL;
И индекс на to_delete.
Тут еще плюс, что разбито на несколько запросов, что уменьшает время блокировки таблицы. Если таблица не будет долго блокирована, то не важно сколько это в итоге исполняется.
Еще вариант не делать сразу UPDATE, а вставлять id во временную таблицу, а потом выбирать те, которые повторяются 3 раза. Тогда все операции, кроме последнего DELETE не будут блокировать исходные таблицы, а DELETE можно будет разбить на кусочки.
блокировка не важна, табличка статистическая, используется раз в сутки для агрегации статистики. и очистка - раз в сутки.
В качестве любопытства: а чем
Неактивен
Cardinality индекса 2, а его эффективность будет зависеть от того сколько строк удовлетворяет условию to_delete=1. Если небольшое количество, то его использование может быть эффективным. Тут надо поэкспериментировать, заранее ответ сказать нельзя.
Также попробовать FORCE INDEX, если оптимизатор не станет использовать.
Неактивен
Повел эксперименты:
Отредактированно Suguby (18.02.2014 11:31:23)
Неактивен
А такой вариант:
Неактивен
для чистоты эксперимента сделал еще
Неактивен
На боевом - провал Визитов 5 773 267, хитов 4 197 341, запрос
Неактивен
А временная таблица на диск не сваливалась?
Кроме того вы тестили на MariaDB 5.5? А там многое оптимизировано (внутр таблица с индексом, более быстрый джойн и т.д.)
Неактивен
vasya написал:
А временная таблица на диск не сваливалась?
Кроме того вы тестили на MariaDB 5.5? А там многое оптимизировано (внутр таблица с индексом, более быстрый джойн и т.д.)
А как проверить сваливалась ли? Да, боевой у нас 5.2.14-MariaDB. сейчас проверяю на боевом с временной таблицей - времена вроде нормальные, единицы минут на каждый запрос.
Придумал уникальный индекс и инсерт игнор
Неактивен
впрочем мой вариант не подходит - получается обьединение множеств, не пересечение
Неактивен