从innodb表中删除并重用第一个自动增量id

wvt8vs2t  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(294)

英语不是我的第一语言,所以我为任何语法错误提前道歉。
我有以下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)

还有一个简单的web界面(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\ u id已经是91451了!在实际使用中,我预计至少有2000-5000个用户!
一旦较旧的消息被自动删除,并且我不使用msg\u id链接不同的表,我想知道我是否可以在一段时间后“重用”第一个id,或者阻塞服务器几分钟来执行“重置”id的过程?或者有更简单的方法?请问,我有什么选择?
谢谢您!

qojgxg4l

qojgxg4l1#

别想再利用了 AUTO_INCREMENT 价值观;不值得这么麻烦。别想重新发明这个轮子。它有许多好的特性。
算算一下。如果您认为您可能会超过40亿行,请更改 msg_idINT UNSIGNEDBIGINT UNSIGNED . 100万 BIGINTs 需要4mb以上 INT . (或者更多,如果有二级索引。)
(可选)不要用表名作为列名的前缀来混乱sql。)
考虑使用 PARTITION BY RANGE(TO_DAYS(msg_Date)) 帮助高效地删除旧数据。
算算吧!91451*5000/100/5=1m行/月。即使不重用ID,也需要3个世纪才能达到40亿。
91k行是一个“小”表。一个有十亿行的表是“大”的,但是可行的。
所需索引:
使用分区(请参见此项):

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

无分区:

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

这些将有效地支持 SELECTs 你提供的。
注意:总有一天会有一个用户有成千上万的消息。你的第一个 SELECT 可能比期望的慢,或者可能在ui中造成问题。我对索引的选择会有所帮助。

相关问题