Английский не мой первый язык, поэтому я заранее извиняюсь за любую грамматическую ошибку.
У меня есть следующие таблицы 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 для связывания разных таблиц, мне интересно, могу ли я "повторно использовать" первые идентификаторы через некоторое время, возможно, заблокировав сервер на несколько минут, чтобы выполнить процесс "сброса" идентификаторов? Или, может быть, есть более простой способ? Пожалуйста, какие у меня варианты?
Спасибо!
Даже не думайте о повторном использовании значений 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
может быть медленнее, чем требуется, или может вызвать проблемы в пользовательском интерфейсе. Мой выбор индексов поможет некоторым.