LINUX.ORG.RU

Можно ли ускорить удаление в MYSQL?

 , ,


0

1
DELETE FROM list_ip WHERE ip   NOT IN   (SELECT ip FROM list_ip WHERE update > 1635492942 /* <- 2021-10-29 */ GROUP BY ip);

Суть: если с IP не было заходов последние 3 месяца - удалить устаревшие адреса из таблицы list_ip

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

UPD:

|ip_________|update____|
|192.168.1.5|2019-01-01| 
|192.168.1.5|2022-01-01| <- свежий
|192.168.1.6|2015-01-01| 
|192.168.1.6|2017-01-01|
|192.168.1.6|2018-01-01|
|192.168.1.7|2022-01-01| <- свежий

Нужно удалить из таблицы все 192.168.1.6 - адрес данный давно не заходил



Последнее исправление: morkovkin (всего исправлений: 3)

Ответ на: комментарий от vvn_black

Спасибо, но этот запрос удалит «историю» и сведения об IP, которые посещали в настоящее время (в течении 3 месяцев).

Я хочу почистить таблицу от пользовательских IP, которые давно не заходили

morkovkin
() автор топика
Последнее исправление: morkovkin (всего исправлений: 4)
Ответ на: комментарий от morkovkin

Если у тебя всё лежит одной пачкой то добавь колонку с датой последнего посещения и обновляй её всем записям по текущему ип. Потом по ней же и выноси старьё без лишних приседаний с запросом.

Ну и загляни в план запроса, вдруг индексов не хватает.

ya-betmen ★★★★★
()
Последнее исправление: ya-betmen (всего исправлений: 2)
Ответ на: комментарий от ya-betmen

Да в том и дело, что колонку с датой последнего посещения добавил

|ip_________|update____|
|192.168.1.5|2019-01-01| 
|192.168.1.5|2022-01-01| <- свежий
|192.168.1.6|2015-01-01| 
|192.168.1.6|2017-01-01|
|192.168.1.6|2018-01-01|
|192.168.1.7|2022-01-01| <- свежий
Нужно удалить из таблицы 192.168.1.6 - адрес данный давно не заходил

morkovkin
() автор топика
Последнее исправление: morkovkin (всего исправлений: 1)

Если ещё нет ключа по update то сделай его (это ускорит вложенный запрос). Если ip ещё не int то сделай его int - это ускорит все операции по сравнению со строкой (а в строку конвертируй уже в приложении).

Но вообще тут просится ещё одна таблица с соответствием ip и последнего захода с него (`ip` int, `lastvisit` int, primary key (`ip`), key `lastvisit` (`lastvisit`)) и вложенный запрос делать из неё. И тогда появляется возможность вместо «NOT IN» сделать «IN» и при довольно регулярном запуске там будет не сильно большой список.

firkax ★★★★★
()
Последнее исправление: firkax (всего исправлений: 1)
Ответ на: комментарий от ya-betmen

Если у тебя всё лежит одной пачкой то добавь колонку с датой последнего посещения и обновляй её всем записям по текущему ип.

Это конечно первое что приходит в голову для ускорения запроса из темы, но вторая же мысль - какой ужас после этого начнётся при регистрации посещений (постоянные апдейты на всю историю). Не надо так делать.

firkax ★★★★★
()
Ответ на: комментарий от firkax

Ключ установлен по update, и как справедливо отметил firkax, действительно есть проблема. Но сейчас речь не о ней.

morkovkin
() автор топика

Я хз, как в этом вашем мускуле, но в SQLite я как-то выкрутился, добавив LIMIT к DELETE, и запуская его по таймеру. Удаление размазывается на более долгое время, но каждая отдельная операция пролетает быстрее. LIMIT надо, конечно, подбирать, чтобы с одной стороны, тормоза не чувствовались, а с другой — чтобы общее время не увеличивалось до бесконечности.

alegz ★★★★
()

Должны быть индексы по update и ip в таблице ip и по ip в list_ip. Можна еще попробовать переписать подзапрос на join, может будет быстрее.

goingUp ★★★★★
()
Последнее исправление: goingUp (всего исправлений: 1)

Ну и в студию

explain select * FROM list_ip WHERE ip   NOT IN   (SELECT ip FROM list_ip WHERE update > 1635492942 /* <- 2021-10-29 */ GROUP BY ip);
goingUp ★★★★★
()
Ответ на: комментарий от goingUp
+------+--------------+----------------+-------+--------------------+--------------+---------+------+--------+--------------------------+
| id   | select_type  | table   | type  | possible_keys             | key          | key_len | ref  | rows   | Extra                    |
+------+--------------+----------------+-------+--------------------+--------------+---------+------+--------+--------------------------+
|    1 | PRIMARY      | list_ip | ALL   | NULL                      | NULL         | NULL    | NULL | 904570 | Using where              |
|    2 | MATERIALIZED | list_ip | range | unique_index,update_index | unique_index | 4       | NULL |  17970 | Using where; Using index |
+------+--------------+---------+-------+---------------------------+--------------+---------+------+--------+--------------------------+
2 rows in set (0.001 sec)
morkovkin
() автор топика

NOT IN исключает работу индексов и по этому никогда NОТ не используют там где нужна скорость, если выборку переписать наоборот, через IN, то должно быть ускорение автоматом.

anonymous
()

ААААААААААААААААА!!!!11

Q: Как вынести мусор на помойку?
A: Чтобы вынести мусор, надо выбрать в доме все предметы, которые не являются мусорным ведром, и вынести не их!

thesis ★★★★★
()
Последнее исправление: thesis (всего исправлений: 1)
Ответ на: комментарий от morkovkin

Первая строчка type ALL это плохо. Нужно создать индекс по ip.

goingUp ★★★★★
()
Ответ на: комментарий от thesis

Чтобы вынести мусор, надо выбрать в доме все предметы, которые не являются мусорным ведром, и вынести не их!

этапять!

это придумал истеный погромист!

Chord ★★★
()
Последнее исправление: Chord (всего исправлений: 1)

А так пробовал? Не то чтобы кардинально быстрее будет, но список в IN будет гораздо меньше с учетом того что хвост лога регулярно подчищается. Попробовать стоит

DELETE FROM list_ip WHERE ip IN (SELECT ip FROM list_ip GROUP BY ip HAVING MAX(update) < 161…)

А так, я б поставил уникальный ключ на IP и делал не insert, а upsert с обновлением значения в поле update. Тогда удаление станет тривиальным

cobold ★★★★★
()
Ответ на: комментарий от cobold
DELETE FROM list_ip WHERE ip IN (SELECT ip FROM list_ip GROUP BY ip HAVING MAX(update) < 161…)

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

А так, я б поставил уникальный ключ на IP и делал не insert, а upsert с обновлением значения в поле update. Тогда удаление станет тривиальным

Это невозможно по логике, которую не раскрывал дабы не забивать лишней информацией топик

morkovkin
() автор топика
Последнее исправление: morkovkin (всего исправлений: 2)

Это же mysql. select into что нужно и дроп оригинальной таблицы - самый быстрый способ!

Shadow ★★★★★
()
Ответ на: комментарий от firkax

постоянные апдейты на всю историю

Если будут проблемы то всегда можно нормализовать историю. Но вообще ип+дата это меньше 20 байт, т.е. 100000 записей это меньше 2 метров и живет целиком в оперативке.

Хотя я не исключаю, что у ТС проблемы с индексами.

ya-betmen ★★★★★
()
Ответ на: комментарий от cobold

А если через триггер на вставку сделать матвью?

Рабочую базу править не вариант. Так всегда бывает, когда несколько людей работает над одним проектом. Сейчас править что либо === навредить ,кому либо.

Таблиц больше тысячи, как они между собой связаны не понимает даже разработчик CRM.

morkovkin
() автор топика
Ответ на: комментарий от ya-betmen

Но вообще ип+дата это меньше 20 байт, т.е. 100000 записей это меньше 2 метров и живет целиком в оперативке.

Если бы было в оперативке и занимало 2МБ то оно бы не лагало. Да и незачем порочные практики вводить, даже если сейчас по причине маленькой таблицы они бы не были заметны.

firkax ★★★★★
()
Ответ на: комментарий от morkovkin

Рабочую базу править не вариант.

Ну я же выше тебе предлагал как сделать (и ты даже отвечал).

Можно ли ускорить удаление в MYSQL? (комментарий)

Править базу не нужно, нужно добавить ещё одну таблицу (она своим существованием никого не заденет) и делать тот самый апдейт в неё одновременно с инсертом в list_ip

firkax ★★★★★
()
Ответ на: комментарий от firkax

оно бы не лагало

Мы до сих пор не видели план запроса.

порочные практики

Нормальная практика если ТС не хочет переделывать бд.

ya-betmen ★★★★★
()

а зачем ЭТО держать в MySQL ? да вообще в sql

потому что вот это точно стезя key-value, кешей, redis и подобных.

MKuznetsov ★★★★★
()

where ip NOT IN

Убери. Ты что делаешь? Совсем что-ли?

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)

В базе сотни тысяч записей

Это смешно.

UPD:

Говнобаза. Сделал ненормализированную херню и мучаешься.

WHERE ip   NOT IN   (SELECT ... )
Кмк никогда нормально не работал в mysql/mariadb. Зато там можно довольно шустро сделать апдейт с подзапросом:
update ip, (select ... from ip where ...) as m set ip.mark = 1 where ip = m.ip
А потом сделать delete. Или сделать detete по таблице и подзапросу.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 1)
Ответ на: комментарий от crutch_master

Нормально отношусь к критике. Услышал много полезной информации. Благодарю за советы, все мотаю на ус.

morkovkin
() автор топика
Ответ на: комментарий от crutch_master

Добра, подскажи, пожалуйста, книгу (или сайт) чтобы учить SQL и реляционную модель данных. Ну, чтобы NOT IN не было желания использовать.

Заранее благодарю.

anonymous
()
Ответ на: комментарий от anonymous

Добра, подскажи, пожалуйста, книгу (или сайт) чтобы учить SQL и реляционную модель данных. Ну, чтобы NOT IN не было желания использовать.

Офф. документация, т.к. тут именно тут дело не в sql, надо знать особенности рсубд на которой работаешь. Например в mysql (старом 5.7 точно) select * from x where id in (select id from y where z) будет работать как улитка, а в оракле - это нормальный вариант (по крайней мере версии 12 и старше, если планировщик ничего не начудит).
Такие дела.
Ну и тесты. Если сомневаешься, сделай таблицу на миллион записей, да над ней ставь эксперименты. На inmemory таблицах работать должно максимум в 5 раз медленнее, чем на ассоциативных массивах какого-нибудь динамического язычка типа питона, жс или пхп
Про реляционную модель данных - любой учебник/методичка по бд для шараги. Тут главное понять, что такое аномалии вставки/обновления, а там с нормализациями уже всё легко на голову налезет и будет понятно, что в бд говнякать можно, а что совсем никак нельзя.

crutch_master ★★★★★
()
Ответ на: комментарий от morkovkin

Нормально отношусь к критике.

Так погоди, говнобазу же не ты сделал. Тебе костыли в неё надо вставлять.
И насчёт 100к записей - это не критика, а оценка объёма. На 100к запросы должны выполняться максимум секунды. Если там минуты - то это уже полная жопа.

crutch_master ★★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.