以下仅考虑 InnoDB 存储引擎。
自增主键有两个性质需要考虑:
单调性
每次插入一条数据,其 ID 都是比上一条插入的数据的 ID 大,就算上一条数据被删除。
连续性
插入成功时,其数据的 ID 和前一次插入成功时数据的 ID 相邻。
自增主键的单调性
为何会有单调性的问题?
这主要跟自增主键最大值的获取方式,以及存放位置有关系。
如果最大值是通过计算获取的,并且在某些情况下需要重新获取时,会因为最新的数据被删除而减小。
自增主键最大值怎么取的?存放到哪里?
MySQL 5.7 及之前的版本,自增主键最大值会在启动(重启)后从数据库中取出放到内存:
SELECT MAX(ai_col) FROM table_name FOR UPDATE;
这样获取是通过计算的,并且由于存放在内存而容易丢失。
如果删除最新一条数据(假设 ID 为 10),因故障或者其他必要重启后再插入一条数据时会使用之前的 ID (即 ID 为 10)。
问题在于如果有其他表依赖了该 ID,则其他表的数据关联到的数据就符合要求了。除非设置了外键。
比如我要向最大一个 ID 的账号充了 100 万。但是在充值之前,该账号被删除,然后服务器故障重启,重启后有人新注册了一个账号。结果我的 100 万充到了他的新账号上。注册新账号的人以为是新手福利,笑嘻嘻。
如何解决单调性的问题?
从 MySQL 8.0 开始,自增主键最大值会在每次修改后写入到 redo log,并且在每个检查点写入引擎私有的系统表。