Mysql таблица на 40-50 млн записей со множеством SELECT и INSERT

115

11 марта 2022, 03:20

Здраствуйте. Недавно на новом проекте столкнулся в первый раз с проблемой относительно больших данных в Mysql.

Если коротко то у нас есть почтовый прокси который принимает каждый запрос imap/pop3 (таких запросов примерно 20-30 в секунду) , проверяет пару вещей и передает запрос почтовому серверу перед этим записав в таблицу logs данные о запросе.

На каждый запрос есть один SELECT COUNT (*) WHERE ip='ip запроса' ignore_for_ban=0 AND createdAt > "current timestamp - 1 hour" и один INSERT INTO logs . Если count больше 10 за последний час, что бывает только в некоторых запросах, примерно раз в 15 мин то после него идет еще и UPDATE logs SET ignore_for_ban = 1 WHERE ip='ip запроса' AND createdAt > "current timestamp - 1 day". Это все работает но жрет очень много ресурсов. Подскажите как можно оптимизировать всё это дело ? какой выбрать mysql движок (пробовали innodb и aria) и какие столбцы разумней всего индексировать ? В крайнем случае посоветуйте альтернативы mysql.

Далее структура таблицы logs:

id PrimaryKey autoIncrement notNull

username VarChar notNull

status VarChar notNull

ip VarChar notNull

continent VarChar notNull

country VarChar notNull

protocol VarChar notNull

domain VarChar

error_code Integer

error_message VarChar

destination_ip VarChar

destination_port Integer

ignore_for_ban TinyInt

Answer 1

Проблема решена. Спасибо всем за советы. В итоге я перенес всю логику бана в redis как посоветовал @Mike. Снизу предложенное им решение:

"так ... не внимательно прочитал. с ip ясно "на каждый запрос" ... Тогда поставьте Redis. делаете в редисе incr по ключу ip. Ставите на ключ время жизни 1 час (после каждого обращения). Этот счетчик не такой как дает запрос в MySQL, т.к. сброситься в 0, только если за час не было ни одного запроса с ip. НО думаю что 90% записей будут сбрасываться. Если видите что счетчик превысил 10 - вот только после этого лезете в MySQL что бы получить действительно последний час – Mike вчера"