Удалить и повторно использовать первые идентификаторы автоматического увеличения из таблицы InnoDB

0

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

У меня есть следующие таблицы MySQL (упрощенные для понимания):

users (InnoDB - utf8_general_ci):
- usr_Id: int(11) unsigned Auto_Increment
- usr_Username: varchar(50)
- usr_Password: varchar(50)

messages (InnoDB - utf8_general_ci):
- msg_Id: int(11) unsigned Auto_Increment
- msg_UserId: int(11) unsigned
- msg_Date: datetime
- msg_Subject: varchar(50)
- msg_Text: varchar(1024)

И есть простой веб-интерфейс (PHP), где пользователи регистрируются, чтобы проверить свои сообщения:

SELECT msg_Id, msg_Subject FROM messages WHERE msg_UserId = <Logged User Id> ORDER BY msg_Date DESC;

Когда пользователь нажимает на тему сообщения в списке, он показывает всплывающее окно с сообщением:

SELECT msg_Text FROM messages WHERE msg_Id = <Id From Clicked Message>;

Каждый день в 4 часа cronjob автоматически удаляет все сообщения старше 3 месяцев:

DELETE FROM messages WHERE msg_Date < DATE_SUB(NOW(), INTERVAL 3 MONTH);

Итак, вот моя проблема: эта система находится в тестировании около 5 месяцев, с менее чем 100 пользователями, а msg_Id уже 91451! Когда в реальном использовании, я ожидаю как минимум 2000-5000 пользователей!

После того, как старые сообщения автоматически удаляются, и я не использую msg_Id для связывания разных таблиц, мне интересно, могу ли я "повторно использовать" первые идентификаторы через некоторое время, возможно, заблокировав сервер на несколько минут, чтобы выполнить процесс "сброса" идентификаторов? Или, может быть, есть более простой способ? Пожалуйста, какие у меня варианты?

Спасибо!

  • 4
    Там действительно нет причин для. Просто позвольте этому числу продолжать увеличиваться. Нет практически никаких шансов, что он когда-нибудь достигнет пределов этой колонки.
  • 0
    Пока есть записи с более высокими значениями автоматического приращения, сервер не сможет повторно использовать более низкие значения. Плюс Дэйв прав, просто проверьте максимальное число для bigint ...
Показать ещё 3 комментария
Теги:
mariadb
auto-increment

1 ответ

1

Даже не думайте о повторном использовании значений AUTO_INCREMENT; это не стоит хлопот. Даже не думайте о том, чтобы изобретать это колесо. В него встроено много хороших функций.

Сделайте некоторые математические. Если вы считаете, что можете превысить 4 миллиарда строк, измените msg_id с INT UNSIGNED на BIGINT UNSIGNED. 1 миллион BIGINTs займет 4 BIGINTs больше, чем INT. (Или больше, если есть вторичные индексы.)

(Необязательно) Не загромождайте SQL путем префикса имен столбцов с именем таблицы.)

Подумайте о том, как использовать PARTITION BY RANGE(TO_DAYS(msg_Date)) для эффективного PARTITION BY RANGE(TO_DAYS(msg_Date)) старых данных.

Делай математику! 91451 * 5000/100/5 = только 1 м строк/месяц. Потребуется 3 столетия, чтобы добраться до 4 миллиардов даже без повторного использования идентификаторов.

91K строк - это "маленькая" таблица. Стол с миллиардом строк "большой", но выполнимый.

Необходимые индексы:

С разделением (см. Это):

PRIMARY KEY(UserId, Date, msg_id),
INDEX(msg_id)

Без разбиения:

PRIMARY KEY(Date, msg_id),
INDEX(msg_id),
INDEX(UserId, Date)

Они будут эффективно поддерживать SELECTs вы предоставили.

Внимание: в какой-то день появится пользователь с тысячами сообщений. Ваш первый SELECT может быть медленнее, чем требуется, или может вызвать проблемы в пользовательском интерфейсе. Мой выбор индексов поможет некоторым.

Ещё вопросы

Сообщество Overcoder
Наверх
Меню