Как создать Mysql-запрос на удаление записей с нетривиальным условием?

Question

[artemev]

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

id | user_id | game_id | score
таблица имеет составной индекс по полям user_id и game_id

Необходимо:
ограничить бешеный рост этой таблицы. Очевидно, что этого можно добиться, ограничив количество записей набранных очков. То есть, чтобы конкретный пользователь для конкретной игры мог сохранить не более, например, 30 последних результатов + самый первый (чтобы можно было оценить прогресс).

Вопрос:
как составить запрос на удаление всех записей за исключением последних 30 и самой первой (для конкретного пользователя и конкретной игры)? Возможно ли такое вообще? Или придется пересмотреть архитектуру приложения...

Answer 1

[svd71]

Select * from users  u0 where
Not exists (select * from users u1 where
U1.user_id=u0.user_id order by  id asc limit 1)
Or
Not exists ( select * from users u2 where
U2.user_id=u0.user_id order by id desc 30)

Сначала убеждаешься, что запрос вываливает нужные данные. Потом переписываешь его на delete from.

Comments

[artemev]

@svd71 спасибо большое! Но запрос с двумя подзапросами... если учесть, что в таблице будет минимум 30 млн. записей, то насколько это будет быстро работать?

[svd71]

поверьте, очень быстро.

[svd71]

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

[artemev]

@svd71 нет, как раз выполняться он будет довольно часто. При каждом сохранении нового результата. Т.е. при сохранении результата:

1. нашим запросом удаляем "лишние" записи (поскольку мы будем делать это при каждом сохранении, то "лишней" будет всегда одна запись)
2. добавляем новую запись

В итоге получаем, что каждый пользователь для конкретной игры может сохранить 30 последних результатов + самый первый

Запрос, кстати сказать, для моих условий не совсем верный (т.к. в условии удаления учитываются пользователи, но не учитываются игры), но суть мне понятна. Спасибо!

Если есть мысли как эту задачу выполнить более правильно, то буду очень рад, если Вы ими поделитесь.

[svd71]

тогда стоит ее поручить каждому пользователю в отдельности. В запрос добавить проверку совпадения user_id с указанным пользователем. При логине этого юзера использовать этот скрипт для подчиски мусора.

Answer 2

[Владлен Грачев]

При каждом добавлении новой записи такой запрос делать не нужно.

Варианта вижу два:
1. Запускать по расписанию скрипт, удаляющий все лишние записи раз в сутки, например (суть скрипта уже описал @svd71).
2. При добавлении нового результата удалять второй (сортируем, задаем offset). Остается вопрос обработки первых 30 игр. Его можно решить, например, создавая после первой игры еще 29 нулевых значений (на мой взгляд, такое решение удобнее, чем каждый раз проверять, появилась, существует ли уже 30 записей).

Comments

[artemev]

@gwer
1. Тогда будет не удобно делать выборку, т.к. придется фильтровать, показывая только 30 последних значений и самое первое. А если делать как я объяснял, то просто делаешь селект с условием по user_id и game_id (по ним есть индекс). Результатов будет всегда <= 30 + самый первый результат.

2. Вы забыли о том, что необходимо хранить (не удалять) самую первую запись (чтобы можно было видеть свой прогресс).

[artemev]

@gwer "на мой взгляд, такое решение удобнее, чем каждый раз проверять, появилась, существует ли уже 30 записей" - тогда придется проверять первая ли это игра

[Владлен Грачев]

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

Я же сказал, удалять вторую запись по порядку. Или предпоследнюю, не знаю, как вам яснее будет.

Тогда будет не удобно делать выборку, т.к. придется фильтровать, показывая только 30 последних значений и самое первое.

Поэтому и предложил два варианта. В каких-то случаях это некритично. В одном случае, неважно, что число записей колеблется от 30 до 40, например, если основная цель — сократить массивность БД и сохранить первую и последние записи. В другом — можно отсеивать лишние, но еще существующие, после выборки. От задачи и пожеланий зависит.

Тогда придется проверять первая ли это игра

Флаги для игр для каждого пользователя (заведомо меньшая таблица). Обработка первой игры отдельным скриптом. Реализация зависит от фантазии и требований. Можно придумать множество вариантов вплоть до полного переделывания архитектуры. Но панацею вряд ли найдете.

Answer 3

[demimurych]

А что означает бешеный рост?
Если мне не изменяет память, то практика удаления записей из таблиц считается порочной с точки зрения производительности, и рекомендуется не удалять записи, а обозначать их как удаленные.

Comments

[artemev]

@demimurych честно сказать, даже и не знаю... Если рассуждать чисто логически, то чем меньше записей, тем быстрее все это дело будет работать. Возможно в данном случае это не так? Не знаю. Я в MySQL не большой специалист. Если кто просветит по этому поводу, буду только рад!

[demimurych]

Есть такие вещи как,
индексы, дефрагментация таблиц, и ограниченность ресурсов.

Чтобы дать развернутый ответ нужно понимать, какой тип таблиц вы используете. Какой обьем данных вы пишите/удаляете, какие ресурсы вам доступны для обслуживания базы.

Если говорить очень поверхностно, то любое удаление на самом деле не приводит к удалению данных из служебных файлов базы. Частое удаление приводит к дефрагментации таблиц что в свою очередь начинает влиять на производительность. Бороться с фрагментацией можно при помощи optimize table НО чтобы с ней бороться нужно четко понимать есть ли она вообще. т.е. исходить из конкретных цифр.