MySQL 自增 ID 超 int 上限的實戰解法
故事背景
今天運維那邊反饋有一個設備在后臺查不到，我第一時間懷疑可能是數據出了問題，導致服務報錯了沒有入庫。
我拿著日志去本地請求接口，發現程序是沒有報錯的，我們的邏輯是先把唯一id放到redis里面，如果redis沒有值就insert，有就update，做了一層緩存，估計是這樣的話批量插入和更新數據庫會快一點。
然后我看redis是有值的，以為是redis和數據庫數據不一致問題，我就把redis的key刪了，重新再跑一下，結果打印了insert語句，但是沒有插入到數據，看來事情并沒有那么簡單- -
問題分析
因為數據表很大，有5E+數據，我第一反應是mysql表數據量可能爆了，但是查了下好像沒有太大限制
圖片
再認真看了下表的自增id，這個數字讓人有點熟悉的：2147483647 這個不就是int的最大值嗎。意思是因為自增id超過了int，所以插入失敗了，id設的就是int類型，還有個小彩蛋，目前數據庫設的int長度是50，但是根本沒什么鳥用。
圖片
知道了問題在哪，但是這個問題處理起來很麻煩，因為數據量太大了，先請教一下deepseek吧。
方案處理
圖片
deepseek給我提供了三個方案：
第一個是最簡單粗暴的改BIGINT，不用遷移數據，但是會全程鎖表。
第二個分布式ID需要重新設計表，需要把數據遷移到新表，而且還要redis等支撐。
第三個分庫分表就更麻煩了，分庫分表需要引入框架，不按照分片查詢還需要引入ES，引入了ES還需要引入同步mysql和ES的中間件logstash等。
但是改bigint估計鎖表太久，我先看看有沒有其他辦法先緊急處理下數據。但是按理說int最大值是21E+，數據表數據才5E+，按理說是用不完的。結果我看到自增的id值居然是不連續的
圖片
按理說自增id應該是一個接著一個，不會有空隙的，后面查了一下由于數據庫自增id有個高性能策略，設置了id就不一定連續。
圖片
后面又查了下有沒有一鍵把數據表id重排的方法，結果也是沒有的。最后我是寫了一個存儲過程先把最后100萬的id清理出來，可以先頂個幾天，后面再想辦法處理。
BEGIN
DECLARE start_id INTDEFAULT1;
DECLARE end_id INTDEFAULT100000;
DECLARE current_batch INTDEFAULT0;
WHILE start_id <= end_id DO
-- 更新臨時表中的ID
UPDATEtable
SET id = start_id +1
WHERE id = (select original_id from (
SELECT id AS original_id
FROMtable
ORDERBY id DESC
LIMIT 1) as test);
SET start_id = start_id +1;
END WHILE;
END最后重新設置自增值，如果自增值已經存在，則會跳到max(id)+1
-- 重置自增值
ALTER TABLE your_table AUTO_INCREMENT = max(id)+1;清理了大概500萬的id段出來，然后我懷疑id間隔這么大是因為并發太高導致的。一開始程序是單線程，消費到500條就批量入庫，但是后面發現單線程消費比較慢，數據量太多消費有點延遲。后面改成java批量消費，配置了30個消費者。接著我嘗試了一下減少消費者數量，設置成15個，id的間隔真的變小了。
設置 BIGINT
節后回來發現id還剩200萬，討論到最后還是把id的數據類型從int改成bigint
ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENTUNSIGNED 無符號位，不算負數，可以增加一倍數據，NOT NULL 非空 AUTO_INCREMENT自增
在測試環境有一億數據，修改id的類型大概用了一個小時，現網我估計也是用6-7個小時也差不多了。結果改了一晚上都還沒改好，然后我找了一個可以查詢sql進度的語句......
SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED, ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS "Progress (%)" FROM performance_schema.events_stages_current;不查不知道，一查嚇一跳，跑了十幾個小時居然還不到50%，而且還越跑越慢。對比了一下測試環境和現網環境的buffer_pool等數據也是設置正常。
估計是索引樹變大插入的數據要花多不少時間，還有一個就是現網數據庫還有其他線程會搶占CPU導致速度緩慢。
統計了一下后面的數據大概是1個小時完成1.5%左右
圖片
最后我是周一晚上執行的，周四早上上班的時候才跑完，用了2天多一點的時間~
總結
剛剛才在掘金刷到一篇文章《字節面試：MySQL自增ID用完會怎樣？》，評論區都說有沒有用完的，結果我真用完了，就感覺有點不可思議?？偨Y一下有幾個原因吧：
1、數據量確實很大，有5E多數據，然后并發也很高。其實當初他們設計的時候也預料過這個問題，所以設了個int長度50，但是這個長度沒起作用- -所以設計數據庫的時候一定要做好，不然幾億數據改個字段類型要2天
2、數據庫的自增id策略選了高性能策略，導致并發高的時候id間隔很大。30個消費者異步處理，10條數據大概用了100個id的間隔，消耗太快了。所以這里存在一個時間和空間的取舍，使用多線程還是挺危險的操作，要謹慎一點。
還有一個小插曲，因為系統兩天沒消費數據，kafka的數據堆積了很多，然后我把消費者數量從30個改成50個，跑了兩天，kafka還是有1天的延遲，看來麻木添加消費者數量已經沒啥提升的作用了，想起八股文說多線程弄太多反而增加上下文切換的時間浪費，跟這個同理。
最后我弄成sql批量消費，消費速度馬上提上去了。程序的消費策略：
單線程批量500個開始消費 ——> 30個線程單個消費 ——> 30個線程批量50個開始消費
所以說多線程異步+批量操作的策略還是很重要的！不過多線程一定要注意異步問題~
參考資料：https://juejin.cn/post/7494167764917305379
