我的一张日志表用 int 做自增主键、跑了两三年相安无事,某天起所有 INSERT 突然全部失败、报主键重复,我反复确认插入的数据没有重复主键百思不得其解,最后才惊觉这张表的自增值已经悄悄爬到了 int 的上限二十一亿、再也加不上去了

这是一次让我把"自增主键的类型"这件事,从"用 int 够用了吧",重新理解成"它是一个只增不减、迟早会撑满有限容器的单调计数器"的事故。我的一张日志表用 int 做自增主键,跑了两三年相安无事。某天起,所有 INSERT 突然全部失败、报主键重复。我反复确认插入的数据没有重复主键、百思不得其解,最后才惊觉:这张表的自增值,已经悄悄爬到了 int 的上限二十一亿,再也加不上去了。这篇就把这次"自增主键耗尽、写入全挂"的事故,从头到尾复盘一遍。

故障现场:没有重复主键,INSERT 却全报主键重复

我有一张写入很频繁的表(日志/流水/消息记录),主键是 id INT AUTO_INCREMENT。建表时我想"int 能存到二十多亿,够用了"。这表跑了两三年,一直稳稳的。可某天监控告警:这张表的所有写入开始批量失败,日志里全是 Duplicate entry '2147483647' for key 'PRIMARY'。

我一看"主键重复"就懵了:我插的是新数据、根本没指定主键、让它自增的啊,哪来的重复? 我反复检查插入语句、检查有没有人手动塞了重复 id,都没有。我又怀疑是不是主键索引坏了。直到我注意到那个反复出现的数字 2147483647——这个数字我太眼熟了,正是 有符号 int 的最大值(2^31 - 1,约 21 亿)。这时我才彻底明白根因——自增主键是一个只增不减的单调计数器:每插入一行,AUTO_INCREMENT 的下一个值就 +1;两三年下来,即便表里因为定期清理老数据、实际行数并不多,但这个自增计数器一直在往上爬、从不因为删行而回退。它爬到了 int 能表示的最大值 2147483647 之后,数据库想再给新行分配下一个自增值,却无法超过这个上限了——于是它要么继续尝试用 2147483647(和已有的那行冲突)、要么直接拒绝,表现出来就是 Duplicate entry '2147483647'、写入全部失败。我以为"21 亿很大、够用",却忘了自增计数器是累计的写入次数、不是当前的行数;一张高频写入的表,几年累计的插入次数,足以把这个看似很大的上限撑爆。

-- 建表时: 图省事用 int 做自增主键
CREATE TABLE app_log (
    id INT AUTO_INCREMENT PRIMARY KEY,   -- ★ int 上限约 21 亿
    msg VARCHAR(255),
    created_at DATETIME
);

-- 跑了两三年, 即便定期 DELETE 清理老数据、表里行数不多,
-- 但 AUTO_INCREMENT 计数器只增不减、累计写入次数一直往上爬:
SHOW TABLE STATUS LIKE 'app_log';
--   Auto_increment: 2147483647   ← 已到 int 最大值!

-- 再 INSERT 新行:
INSERT INTO app_log (msg, created_at) VALUES ('x', NOW());
-- ✗ ERROR 1062: Duplicate entry '2147483647' for key 'PRIMARY'
--   自增值无法超过 int 上限, 卡在 2147483647, 新行分配不到下一个值 → 全部失败

-- 关键: 自增值 = 累计写入次数(只增不减、删行不回退), 不是当前行数!
--   高频写入的表, 几年累计插入次数足以撑爆 21 亿这个"看着很大"的上限。

问题被钉死在这个认知错位上:我以为"int 能到 21 亿,这表绝不可能有 21 亿行数据,够用了",但自增主键的值不等于当前行数——它是一个累计的、只增不减的计数器,记录的是"这张表历史上一共分配过多少个自增值"(约等于累计的插入次数);删除行不会让它回退。所以一张高频写入、又定期删旧数据的表,可能行数一直不多,但自增值已经悄悄爬到了天上。我用"当前行数不会那么多"去判断"自增值会不会到上限",这两件事根本不是一回事——前者可能因为清理一直很小,后者却随每一次插入无情地、不可逆地增长。当这个单调增长的计数器,装进了我给它的那个"看着够大、其实有限"的 int 容器、并最终撑满它时,整张表的写入就一起瘫痪了。我以为我给了它一个绰绰有余的空间,却没算清它是只进不退、迟早会把任何有限空间填满的。

第一件事:想明白自增值是只增不减的计数器,而非行数

把这次事故彻底想清楚,关键是理解AUTO_INCREMENT 是一个单调递增、只增不减的计数器:每次插入新行,它分配一个比上次更大的值,然后自增指针前移;这个指针不会因为删除行而回退(删了 id=100 的行,下一个新行仍然是 101 而不是复用 100),也不会因为事务回滚等而保证连续(中间会有空洞)。因此它的当前值,反映的是"历史上累计分配过多少个 id"(约等于累计插入次数),而不是"表里现在有多少行"。一旦这个累计值达到主键整数类型的上限,就再也分配不出新值,写入全部失败。

这就引出了选主键类型的关键:要按"这张表预计累计会插入多少次"(而不是"同时存多少行")来选自增主键的整数类型;并且要给它留足够大的余量,因为这个增长是不可逆的——你无法靠删数据让它退回去。各类型上限差距巨大:TINYINT 约 127、SMALLINT 约 3.2 万、INT 约 21 亿(有符号)、BIGINT 约 922 京(9.2×10^18)。对任何"会持续高频写入、长期运行"的表,主键几乎都应该用 BIGINT——它大到实际上永远用不完,一劳永逸地避免耗尽。用 int 不是不行,而是只适合那些"累计插入次数确定不会接近 21 亿"的表;对增长不可控的表用 int,就是给一个停不下来的计数器,配了一个迟早会被填满的小容器。关键认知是:给一个"只增不减、会持续累积"的量分配容器时,要按它"累计能涨到多大"(而非"当前是多少")来定容量,并留足余量;任何有限的容器,迟早会被一个不可逆的单调增长填满。

-- 正解1: 高频写入/长期运行的表, 自增主键用 BIGINT(几乎用不完)
CREATE TABLE app_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,   -- ★ 上限约 9.2×10^18, 实际用不完
    msg VARCHAR(255),
    created_at DATETIME
);

-- 各整数类型的自增上限(有符号), 按"累计插入次数"来选:
--   TINYINT  ~127            SMALLINT ~3.2万
--   INT      ~21亿(2^31-1)  BIGINT   ~922京(2^63-1)
-- 实在确定量极小, 可用无符号(UNSIGNED)翻倍上限, 但治标不治本

-- 正解2: 监控自增值距离类型上限的比例, 接近就提前告警/扩容
SELECT TABLE_NAME, AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND AUTO_INCREMENT IS NOT NULL
ORDER BY AUTO_INCREMENT DESC;   -- 定期看哪张表的自增值快到上限了

-- 正解3: 已经用了 int 又快满, 改大类型(需停机/在线 DDL, 提前规划别等满了)
ALTER TABLE app_log MODIFY id BIGINT AUTO_INCREMENT;   -- 大表代价高, 务必趁早

-- 误区: 想靠"删旧数据/TRUNCATE"让自增值回退 —— DELETE 不回退;
--   TRUNCATE 会重置自增但会清空全表数据, 不是缩容的办法

想通这一层,我才明白自己错在哪:我用"表里同时存多少行"去估"自增主键够不够用",却没意识到自增值是"累计插入了多少次"——一个只增不减、删行也不回退的单调计数器。我那张表行数因为定期清理一直不多,这给了我"数据量不大、int 够用"的错觉;可它的自增值,随着每一次写入,不动声色地、不可逆地爬向了 21 亿的悬崖。根治之道,是从一开始就按累计写入量、用 BIGINT 给单调增长的主键留足空间,并监控自增值接近上限的情况、提前扩容。不是看它现在多大,而是看它停不下来的增长迟早会涨到多大,并据此给足空间。

第二件事:正解——按累计写入量选 BIGINT,监控自增值,趁早扩容

找到根因,正解就清晰了:自增主键的类型要按"累计会插入多少次"(而非当前行数)来选,对任何高频写入、长期运行的表一律用 BIGINT(上限约 9.2×10^18,实际用不完);同时监控各表自增值距离类型上限的比例、接近就提前告警;万一已经用了 int 又快满,务必趁早用在线 DDL 改成 BIGINT(等真满了再改,代价和风险都大得多)。

-- 错误: 高频写入表用 int 自增主键, 几年后撑爆
CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, payload TEXT);

-- 正解1: 一律用 BIGINT, 一劳永逸(上限约 9.2e18, 实际用不完)
CREATE TABLE events (id BIGINT AUTO_INCREMENT PRIMARY KEY, payload TEXT);

-- 正解2: 监控 —— 查各表自增值, 算它已用了类型上限的百分之多少, 超阈值告警
SELECT t.TABLE_NAME, t.AUTO_INCREMENT
FROM information_schema.TABLES t
WHERE t.TABLE_SCHEMA = 'mydb' AND t.AUTO_INCREMENT IS NOT NULL
ORDER BY t.AUTO_INCREMENT DESC;     -- 接近 21 亿(int)的赶紧扩容
-- int 上限 2147483647; 自增值过半就该警觉、过 80% 就该排期扩容

-- 正解3: 趁早扩容(别等满), 在线 DDL 改类型
ALTER TABLE events MODIFY id BIGINT AUTO_INCREMENT, ALGORITHM=INPLACE, LOCK=NONE;
-- 大表用 gh-ost / pt-online-schema-change 等在线工具更稳

-- 提醒: DELETE 不会让自增值回退; 别指望删数据来缩自增计数器
--   TRUNCATE 会重置自增但会清空全表, 不是缩容手段

这套做法的精髓,是承认自增主键是个"不可逆的单调增长量",从一开始就按"它最终会涨到多大"给它足够大的容器(BIGINT),并对这个会悄悄逼近上限的量做主动监控、在撑满前从容扩容。用 BIGINT 一步到位、永不耗尽是治本;监控自增值占比是"万一漏了也能提前发现"的防线;趁早扩容则是"已经踩了 int 的坑也还来得及"的补救——关键是别等到写入全挂了才动手。不是事后救火,而是为不可逆的增长提前备足空间、并盯着它别悄悄逼近上限。

【选自增主键类型, 我现在认死的几条】

1. 自增值是"累计插入次数"的单调计数器, 只增不减, 删行不回退

2. 它 != 当前行数; 定期清理数据也不会让自增值降下来

3. 按"累计会插入多少次"选类型, 不是按"同时存多少行"

4. 高频写入/长期运行的表, 自增主键一律用 BIGINT(实际用不完)

5. int 上限约 21 亿, 高频表几年就可能撑爆, 别想当然觉得够

6. 监控各表自增值 / 类型上限的占比, 接近就提前告警扩容

7. 已用 int 又快满, 趁早在线 DDL 改 BIGINT; 别等写入全挂才动手

第三件事:其他"给单调增长的量配了有限容器"的同类坑

顺着"一个只增不减/会持续累积的量,被装进了一个迟早会被填满的有限容器"这条线,我把同类的坑都排查了一遍:

第一个,时间戳用 32 位存(2038 年问题)。Unix 时间戳用有符号 32 位整数存,2038 年会溢出回绕成负数;时间是单调增长的,要用 64 位存。

第二个,计数器/序列号字段类型太小。点赞数、访问量等累计计数用了 int 甚至 smallint,爆款内容一冲就溢出;累计量要用够大的类型。

第三个,日志/分区表不清理,磁盘只增不减直到写满。日志、binlog、临时文件持续写入又不轮转清理,磁盘是有限容器,迟早写爆。

第四个,消息队列/积压只进不出。生产快于消费,积压持续增长,队列容量有限,迟早撑爆或 OOM。

第四件事:整数类型与自增上限——一张对照表

我把常见整数类型的自增上限摆在一起对比,核心看"能撑多少次累计插入":

看清这张表,选型就有谱了:按"这张表累计会插入多少次"选类型并留足余量;高频写入、长期运行的表一律 BIGINT(922 京实际用不完);只有确定累计插入远小于 21 亿的表才考虑 int;TINYINT/SMALLINT 几乎不该做自增主键。我这次踩坑,正是用 int 给一张高频写入的日志表做主键——累计插入次数几年就摸到了 21 亿的天花板。BIGINT 多占几个字节,换来的是永不耗尽,这笔账非常划算。

第五件事:我曾经对自增主键想当然的几个误区

这次事故也把我对自增主键的一堆"想当然"照了个底朝天:

这些误区的根子是同一个:我用"当前的规模(行数)"去判断一个"累计的、只增不减的量(自增值)"够不够用,而这两者随着不断清理旧数据,会越拉越远——行数可能一直小,累计值却一路涨到上限。我没把自增主键当成一个"不可逆的单调计数器"来对待,而是当成了"和数据量挂钩、删了就少"的东西。把"累计量"当成"当前量"、用当前的小去推断未来的够,是这类容量耗尽的共同根源。

第六件事:建表选主键、排查"INSERT 全报主键重复"时,我现在的自检习惯

现在每当我建表选自增主键类型、或排查"INSERT 突然全部报主键重复",我都会先按这张图问自己:

这张图的精髓,是"INSERT 全报主键重复先看那个值是不是类型上限(如 21 亿);自增主键按累计插入次数选类型、高频长期表一律 BIGINT"。设计就高频长期表自增主键用 BIGINT、按累计插入次数留足余量、监控自增值占比、排查就看报错的重复值是不是某类型的上限值。这套习惯,让我从"int 够用了吧"变成了"先想它累计会涨到多大"——核心始终是:AUTO_INCREMENT 是一个单调递增、只增不减的计数器:每次插入新行它分配一个比上次更大的值然后自增指针前移,这个指针不会因为删除行而回退(删了 id=100 下一个新行仍是 101 而不是复用 100)也不会因为事务回滚等保证连续(中间会有空洞);因此它的当前值反映的是历史上累计分配过多少个 id(约等于累计插入次数)而不是表里现在有多少行,一旦这个累计值达到主键整数类型的上限就再也分配不出新值、写入全部失败(表现为 Duplicate entry '上限值');所以选自增主键类型要按这张表预计累计会插入多少次而不是同时存多少行来选、并留足够大的余量因为这个增长是不可逆的你无法靠删数据让它退回去;各类型上限差距巨大 TINYINT 约 127、SMALLINT 约 3.2 万、INT 约 21 亿(有符号)、BIGINT 约 922 京;对任何会持续高频写入长期运行的表主键几乎都应该用 BIGINT——它大到实际上永远用不完一劳永逸地避免耗尽;同时要监控各表自增值距离类型上限的比例接近就提前告警扩容、已经用了 int 又快满就趁早用在线 DDL 改 BIGINT 别等真满了再改;一句话给一个只增不减会持续累积的量分配容器时要按它累计能涨到多大而非当前是多少来定容量并留足余量、任何有限的容器迟早会被一个不可逆的单调增长填满。

我立下的几条规矩

这场"自增主键耗尽、写入全挂"的事故,换来了我建表时,刻进骨子里的几条铁律:

1. 自增值是"累计插入次数"的单调计数器,只增不减,删行不回退。
2. 它 != 当前行数;定期清理数据也不会让自增值降下来。
3. 按"累计会插入多少次"选自增主键类型,不是按"同时存多少行"。
4. 高频写入/长期运行的表,自增主键一律用 BIGINT(实际用不完)。
5. int 上限约 21 亿,高频表几年就可能撑爆,别想当然觉得够。
6. 监控各表自增值 / 类型上限的占比,接近就提前告警扩容。
7. 已用 int 又快满,趁早在线 DDL 改 BIGINT;别等写入全挂才动手。

附:我现在建表与自增值监控的"BIGINT 主键 + 容量巡检"骨架

这是我现在建表选主键、防自增耗尽固定套的骨架——把这次踩坑的教训(高频长期表用 BIGINT、按累计量留余量、监控接近上限)固化成一套规范,让"自增主键耗尽写入全挂"那种坑再不会埋进系统:

-- 建表规范: 凡是会持续写入/长期运行的表, 自增主键一律 BIGINT
CREATE TABLE app_log (
    id          BIGINT      AUTO_INCREMENT PRIMARY KEY,  -- ★ 永不耗尽
    msg         VARCHAR(255) NOT NULL,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created (created_at)
);

-- 容量巡检: 定期扫描全库, 找出自增值已用类型上限较高比例的表
SELECT
    t.TABLE_NAME,
    t.AUTO_INCREMENT                          AS cur,
    c.COLUMN_TYPE                             AS pk_type,
    -- int 有符号上限 2147483647; 这里以 int 为例算占比
    ROUND(t.AUTO_INCREMENT / 2147483647 * 100, 2) AS int_used_pct
FROM information_schema.TABLES  t
JOIN information_schema.COLUMNS c
  ON  c.TABLE_SCHEMA = t.TABLE_SCHEMA
  AND c.TABLE_NAME   = t.TABLE_NAME
  AND c.EXTRA = 'auto_increment'
WHERE t.TABLE_SCHEMA = DATABASE()
  AND c.DATA_TYPE = 'int'                     -- 重点盯还在用 int 的表
  AND t.AUTO_INCREMENT > 2147483647 * 0.7     -- 用过 70% 就报出来
ORDER BY int_used_pct DESC;

# 把巡检接入告警: 每天跑一次, 自增值用量超阈值就通知, 提前排期扩容
INT_MAX = 2147483647
def check_autoincrement(conn, warn=0.7, crit=0.85):
    rows = conn.query("""
        SELECT t.TABLE_NAME, t.AUTO_INCREMENT
        FROM information_schema.TABLES t
        JOIN information_schema.COLUMNS c
          ON c.TABLE_SCHEMA=t.TABLE_SCHEMA AND c.TABLE_NAME=t.TABLE_NAME
         AND c.EXTRA='auto_increment' AND c.DATA_TYPE='int'
        WHERE t.TABLE_SCHEMA=DATABASE() AND t.AUTO_INCREMENT IS NOT NULL
    """)
    for name, cur in rows:
        pct = cur / INT_MAX
        if pct >= crit:
            alert(f"[严重] {name} 自增值已用 {pct:.0%}, 即将耗尽, 立即扩容 BIGINT!")
        elif pct >= warn:
            alert(f"[预警] {name} 自增值已用 {pct:.0%}, 排期改 BIGINT")
    # 关键: 在撑满之前就发现, 用在线 DDL 从容扩容, 别等写入全挂

这套骨架把我这次的教训钉死在了规范里:建表时高频/长期表自增主键一律 BIGINT(从源头永不耗尽);对历史遗留还在用 int 的表,用容量巡检 + 每日告警盯住自增值占类型上限的比例、用过 70% 预警、85% 严重、在撑满之前就用在线 DDL 从容扩成 BIGINT。这样,自增主键这个"只增不减的单调计数器"永远有足够的空间可涨,即便有漏网的 int 老表也能在耗尽前被揪出来扩容,而不再是当初那个"行数不多写入却全挂、报主键重复才发现自增到顶"的局面。把"为不可逆的单调增长按其终值备足容器、并监控其逼近上限"这个道理,沉淀成建表与巡检的固定规范,这是我对这次"自增主键耗尽"最实在的交代——毕竟,一个只会往上爬、永不回头的数,你给它的台阶,得高到它这辈子都爬不到头。

写在最后

回头看,这场由"int 自增主键"引发的"写入全挂"事故,真正教给我的,远不止"主键改用 BIGINT"这一个技巧。它让我对"有一类量,是'只进不退、不断累积'的——它每发生一次就往上加一点、且永不回头;而我们用来盛放它的'容器',却往往是有上限的;我们在选容器时,极容易犯一个错:用这个量'当下的大小'去判断容器'够不够',却忘了它会不可逆地、持续地涨下去,任何有限的容器,在足够长的时间里,都一定会被这种增长填满",有了一次刻骨的体会。我栽跟头,是因为我用一个"累计的、只增不减的量(自增值)"当下看起来不大,去判断了它"会不会耗尽容器(int 上限)"——我看着那张表行数一直不多(因为我定期清理旧数据),就笃定"数据量不大、int 二十多亿绰绰有余";我把"表里现在有多少行"(一个会随清理而下降的当前量)和"自增值涨到了多少"(一个只随写入上涨、永不回退的累计量)混为了一谈;于是那个累计计数器在我"数据量不大"的错觉里,不动声色地、每写一次就逼近一步地,爬到了 21 亿的悬崖边,直到某天再也迈不出下一步、整张表的写入轰然瘫痪。这让我领悟到一个关于"累计量、容器与不可逆增长"的深刻认知:对一个"只增不减、持续累积"的量(累计计数、单调序号、累计用量),判断"给它的容器够不够",绝不能看它"当下的值",而要看它"在系统的整个生命周期里累计会涨到多大"——因为它的增长是不可逆的,你既不能让它回退,也不能靠"清理当前数据"来缓解(清理减少的是当前量,减不了累计量);有限的容器面对不可逆的单调增长,结局是确定的:不是"会不会满",而是"什么时候满";所以正确的做法,是从一开始就按"累计能涨到多大"给它一个大到在可见的将来都填不满的容器(用 BIGINT 而非 int、用 64 位而非 32 位),并对它逼近上限的过程保持监控、在撑满前就从容应对;最危险的,是被"当前还很空"的表象麻痺,用现状去推断一个注定要不断上涨的量的未来。这给了我一种看待"一切'累计/单调增长之量'之事"时的清醒:每当我为一个会持续累积、只增不减的量(序号、计数、用量、时间)选择存放它的容器或类型时,要追问"这个量在系统的整个生命周期里,累计会涨到多大?我给的容器,是按它'当下的值'选的,还是按它'最终会涨到的值'选的?它逼近上限时,我有没有监控、来不来得及扩容"——按累计的、不可逆增长的终值给足容量并留余量,而不是用当前的小去推断未来的够;"为不可逆的单调增长按其终值备足容器、并监控其逼近上限",是选对自增主键类型、也是设计一切承载累计量之系统的关键。认清自增值是累计插入次数的单调计数器、只增不减删行不回退、要按累计量用 BIGINT 留足空间——这,是我用一次"int 主键耗尽、写入全挂"的事故,换来的、关于数据库、也关于如何对待不可逆累计增长的、最朴素也最深刻的领悟。如果这篇复盘,能让你下次建一张会持续写入的表、在主键类型上随手想写 int 时,先停一秒想想"这表几年累计会插入多少次?要不要直接 BIGINT?",那我对着那张"行数不多、写入却全挂"的表排查的大半天,就值了。

—— 别看了 · 2026