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

DELETE FROM list_ip WHERE update <= 1635492942;

?

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

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

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

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

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

|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| <- свежий

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

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

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

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

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

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

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

Ну и в студию

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

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

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

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

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

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

этапять!

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

👍

:)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

where ip NOT IN

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

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

Это смешно.

UPD:

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

WHERE ip   NOT IN   (SELECT ... )

update ip, (select ... from ip where ...) as m set ip.mark = 1 where ip = m.ip

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

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

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

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

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

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

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

Вот поэтому и создали Redis

https://www.postgresql.org/docs/9/hstore.html

Спасибо.

Например в mysql (старом 5.7 точно) … будет работать как улитка

Уже поправили

Хочется верить.