Форум пользователей MySQL

Suguby · 14.02.2014 13:54:42

Уважаемые! Есть две таблицы:

CREATE TABLE `visits` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

   ...

  `source_id` bigint(20) DEFAULT NULL,

  `start_page_id` bigint(20) DEFAULT NULL,

  `end_page_id` bigint(20) DEFAULT NULL,

   ...

  PRIMARY KEY (`id`),

   ...

  KEY `visits_a34b03a6` (`source_id`),

  KEY `visits_67129887` (`start_page_id`),

  KEY `visits_10b15c48` (`end_page_id`),

   ...

  CONSTRAINT `start_page_id_refs_id_1a830e2779f49e3b` FOREIGN KEY (`start_page_id`) REFERENCES `hits` (`id`),

  CONSTRAINT `source_id_refs_id_1a830e2779f49e3b` FOREIGN KEY (`source_id`) REFERENCES `hits` (`id`),

  CONSTRAINT `end_page_id_refs_id_1a830e2779f49e3b` FOREIGN KEY (`end_page_id`) REFERENCES `hits` (`id`),

   ...

) ENGINE=InnoDB AUTO_INCREMENT=8881673 DEFAULT CHARSET=utf8;

CREATE TABLE `hits` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `url_id` bigint(20) NOT NULL,

  `query` varchar(3072) NOT NULL,

  `hash` varchar(64) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `hash` (`hash`),

  KEY `hits_c379dc61` (`url_id`),

  CONSTRAINT `url_id_refs_id_12af3b123142f3d3` FOREIGN KEY (`url_id`) REFERENCES `urls` (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3929842 DEFAULT CHARSET=utf8;

то есть первая своими тремя полями ссылается на вторую.
Первую табличку переодически чистят, удаляя данные за месяц. Необходимо очистить и вторую - удалить те записи, на которые не осталось ссылок из первой таблицы.

Я сделал это так:

DELETE FROM hits WHERE id NOT IN ( SELECT source_id FROM visits UNION SELECT start_page_id FROM visits UNION SELECT end_page_id FROM visits )

 

Проблема в том, что первая табличка - 9млн записей, а вторая - 4 млн

и запрос выполняется десятки минут

Как оптимизировать?

Отредактированно Suguby (14.02.2014 13:56:12)

vasya · 14.02.2014 14:44:41

Какая версия MySQL?
Пробовали переписать через JOIN в виде multiple-table syntax?

Suguby · 14.02.2014 15:19:40

У нас Server version: 5.2.14-MariaDB - эта фича уже есть? и что-то не пойму какой может быть запрос для удаления... с LEFT OUTER JOIN?

rgbeast · 14.02.2014 15:24:09

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;

Потом можно удалять по несколько тысяч, а не все сразу

vasya · 14.02.2014 15:29:48

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;

vasya · 14.02.2014 15:35:27

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/

vasya · 14.02.2014 15:44:40

rgbeast написал:
2) до того, как чистить первую таблицу, запомнить source_id удаляемых строк

Не получится. Судя по постановке вопроса source_id может быть несколько и не все они могут быть удалены. А из второй таблицы удаление только, если не осталось соответствия.

Suguby · 14.02.2014 17:46:16

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 млн... хотя попробовать можно. Может оптимизатор лучше сработает.
может вообще временную таблицу? хотя это тоже не выход...

я подумываю что бы

SELECT id FROM hits LEFT JOIN visits ON hits.id=visits.source_id WHERE visits.source_id IS NULL;

SELECT id FROM hits LEFT JOIN visits ON hits.id=visits.start_page_id WHERE visits.start_page_id IS NULL;

SELECT id FROM hits LEFT JOIN visits ON hits.id=visits.end_page_id WHERE visits.end_page_id IS NULL;

на клиента вытащить и там пересечения множеств сделать, а потом пачками удалять.

да, 2) не подходит (хотя я именно так сначала и сделал

переход на новую версию в продакшене сами знаете во что выливается... но будем посмотреть.

Suguby · 14.02.2014 17:50:51

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;

Потом можно удалять по несколько тысяч, а не все сразу

Привет, Григорий!

сложно получить айдишники удаляемых строк...

может

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 · 14.02.2014 17:54:56

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 сделает подзапрос единожды, так что выигрыш должен быть и немалый.

vasya · 14.02.2014 18:13:20

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;
так попробовать? все равно джойны больших таблиц...

Смущают большее кол-во операций записи. Но этот вариант хорош тем, что удалять можно будет порциями, да и работать он будет по индексам. Нужно сравнивать.

Suguby · 14.02.2014 19:23:37

хм

DELETE

  hits 

  LEFT JOIN 

    (SELECT 

      source_id 

    FROM

      visits 

    UNION

    SELECT 

      start_page_id 

    FROM

      visits 

    UNION

    SELECT 

      end_page_id 

    FROM

      visits) AS t 

    ON hits.id = t.source_id 

WHERE t.source_id IS NULL;

на Server version: 5.5.35-MariaDB-1~precise (рабстанция) пишет синтаксис егог возле LEFT JOIN, хотя DELETE -> SELECT * FROM работает... может лефт джойнить в удалении нельзя?

vasya · 14.02.2014 19:33:34

Это я ошибся в синтаксисе

DELETE hits from

  hits

  LEFT JOIN

    (SELECT

      source_id

    FROM

      visits

    UNION

    SELECT

      start_page_id

    FROM

      visits

    UNION

    SELECT

      end_page_id

    FROM

      visits) AS t

    ON hits.id = t.source_id

WHERE t.source_id IS NULL;

Suguby · 14.02.2014 19:49:02

vasya написал:
Это я ошибся в синтаксисе
DELETE hits from
...

сорри не заметил.

rgbeast · 15.02.2014 00:07:48

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 можно будет разбить на кусочки.

Suguby · 15.02.2014 11:12:11

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 предложил.

Suguby · 15.02.2014 11:16:33

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 можно будет разбить на кусочки.

блокировка не важна, табличка статистическая, используется раз в сутки для агрегации статистики. и очистка - раз в сутки.

В качестве любопытства: а чем

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 селективность будет 2 (это же бинарный признак - да/нет) оптимизатор его разве будет использовать?

rgbeast · 15.02.2014 15:36:44

Cardinality индекса 2, а его эффективность будет зависеть от того сколько строк удовлетворяет условию to_delete=1. Если небольшое количество, то его использование может быть эффективным. Тут надо поэкспериментировать, заранее ответ сказать нельзя.

Также попробовать FORCE INDEX, если оптимизатор не станет использовать.

Suguby · 18.02.2014 11:30:55

Повел эксперименты:

DELETE FROM hits WHERE id NOT IN ( SELECT source_id FROM visits UNION SELECT start_page_id FROM visits UNION SELECT end_page_id FROM visits )

[INFO]: From Hit deleted 0 rows (52 mins 36 secs)

UPDATE hits SET hits.to_delete=0 WHERE hits.to_delete>0;

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=2 WHERE hits.to_delete=1 AND visits.start_page_id IS NULL;

UPDATE hits LEFT JOIN visits ON hits.id=visits.end_page_id

                SET hits.to_delete=3 WHERE hits.to_delete=2 AND visits.end_page_id IS NULL;

DELETE FROM hits WHERE hits.to_delete = 3;

[INFO]: From Hit deleted 125381 rows (1 hours 25 mins 59 secs)

С апдейтом медленнее получилось

Отредактированно Suguby (18.02.2014 11:31:23)

vasya · 18.02.2014 12:42:57

А такой вариант:

CREATE TEMPORARY TABLE temp (id bigint(20) DEFAULT NULL);

INSERT INTO temp SELECT hits.source_id FROM hits LEFT JOIN visits ON hits.id=visits.source_id WHERE visits.source_id IS NULL;

INSERT INTO temp SELECT hits.start_page_id FROM hits LEFT JOIN visits ON hits.id=visits.start_page_id WHERE visits.start_page_id IS NULL;

INSERT INTO temp SELECT hits.end_page_id FROM hits LEFT JOIN visits ON hits.id=visits.end_page_id WHERE visits.end_page_id IS NULL;

SELECT id FROM temp GROUP BY id HAVING count(*)=3;

и по этим id удалять?

Этот вариант перспективен тем, что все изменения происходят со временной таблицей в памяти (нужно лишь следить, чтобы она не ушла на диск, но тут при необходимости и в сессии, емнип, можно изменить предельный размер для таблиц в памяти), а это в разы быстрее.

Suguby · 18.02.2014 12:50:31

для чистоты эксперимента сделал еще

DELETE hits FROM 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;

[INFO]: From Hit deleted 63278 rows (25 mins 28 secs)

Так что я был неправ - удалнние с мульти джойном быстрее всего

Спасибо Василий, остановлюсь на этом варианте - время приемлимо.

Suguby · 19.02.2014 11:31:50

На боевом - провал Визитов 5 773 267, хитов 4 197 341, запрос

DELETE hits FROM hits LEFT JOIN...

висел всю ночь - так и не выполнился

надо теперь попробовать

CREATE TEMPORARY TABLE temp (id bigint(20) DEFAULT NULL);

vasya · 19.02.2014 12:20:20

А временная таблица на диск не сваливалась?
Кроме того вы тестили на MariaDB 5.5? А там многое оптимизировано (внутр таблица с индексом, более быстрый джойн и т.д.)

Suguby · 19.02.2014 12:42:21

vasya написал:
А временная таблица на диск не сваливалась?
Кроме того вы тестили на MariaDB 5.5? А там многое оптимизировано (внутр таблица с индексом, более быстрый джойн и т.д.)

А как проверить сваливалась ли? Да, боевой у нас 5.2.14-MariaDB. сейчас проверяю на боевом с временной таблицей - времена вроде нормальные, единицы минут на каждый запрос.
Придумал уникальный индекс и инсерт игнор

CREATE TEMPORARY TABLE temp2 (id BIGINT(20) DEFAULT NULL, UNIQUE KEY `id` (`id`));

INSERT INTO temp2 SELECT hits.id FROM hits LEFT JOIN visits ON hits.id=visits.source_id WHERE visits.source_id IS NULL;

INSERT IGNORE INTO temp2 SELECT hits.id FROM hits LEFT JOIN visits ON hits.id=visits.start_page_id WHERE visits.start_page_id IS NULL;

INSERT IGNORE INTO temp2 SELECT hits.id FROM hits LEFT JOIN visits ON hits.id=visits.end_page_id WHERE visits.end_page_id IS NULL;

DELETE hits FROM hits JOIN temp2 on id;

DROP TEMPORARY TABLE temp2;

может в целом будет быстрее?

Suguby · 19.02.2014 13:01:22

впрочем мой вариант не подходит - получается обьединение множеств, не пересечение

Форум пользователей MySQL

#1 14.02.2014 13:54:42

Частичная очистка больших таблиц

#2 14.02.2014 14:44:41

Re: Частичная очистка больших таблиц

#3 14.02.2014 15:19:40

Re: Частичная очистка больших таблиц

#4 14.02.2014 15:24:09

Re: Частичная очистка больших таблиц

#5 14.02.2014 15:29:48

Re: Частичная очистка больших таблиц

Suguby написал:

#6 14.02.2014 15:35:27

Re: Частичная очистка больших таблиц

Suguby написал:

#7 14.02.2014 15:44:40

Re: Частичная очистка больших таблиц

rgbeast написал:

#8 14.02.2014 17:46:16

Re: Частичная очистка больших таблиц

vasya написал:

Suguby написал:

#9 14.02.2014 17:50:51

Re: Частичная очистка больших таблиц

rgbeast написал:

#10 14.02.2014 17:54:56

Re: Частичная очистка больших таблиц

Suguby написал:

vasya написал:

Suguby написал:

#11 14.02.2014 18:13:20

Re: Частичная очистка больших таблиц

Suguby написал:

#12 14.02.2014 19:23:37

Re: Частичная очистка больших таблиц

#13 14.02.2014 19:33:34

Re: Частичная очистка больших таблиц

#14 14.02.2014 19:49:02

Re: Частичная очистка больших таблиц

vasya написал:

#15 15.02.2014 00:07:48

Re: Частичная очистка больших таблиц

Suguby написал:

#16 15.02.2014 11:12:11

Re: Частичная очистка больших таблиц

vasya написал:

Suguby написал:

vasya написал:

#17 15.02.2014 11:16:33

Re: Частичная очистка больших таблиц

rgbeast написал:

Suguby написал:

#18 15.02.2014 15:36:44

Re: Частичная очистка больших таблиц

#19 18.02.2014 11:30:55

Re: Частичная очистка больших таблиц

#20 18.02.2014 12:42:57

Re: Частичная очистка больших таблиц

#21 18.02.2014 12:50:31

Re: Частичная очистка больших таблиц

#22 19.02.2014 11:31:50

Re: Частичная очистка больших таблиц

#23 19.02.2014 12:20:20

Re: Частичная очистка больших таблиц

#24 19.02.2014 12:42:21

Re: Частичная очистка больших таблиц

vasya написал:

#25 19.02.2014 13:01:22

Re: Частичная очистка больших таблиц

Board footer