故事背景

今天運(yùn)維那邊反饋有一個(gè)設(shè)備在后臺(tái)查不到，我第一時(shí)間懷疑可能是數(shù)據(jù)出了問(wèn)題，導(dǎo)致服務(wù)報(bào)錯(cuò)了沒(méi)有入庫(kù)。

我拿著日志去本地請(qǐng)求接口，發(fā)現(xiàn)程序是沒(méi)有報(bào)錯(cuò)的，我們的邏輯是先把唯一id放到redis里面，如果redis沒(méi)有值就insert，有就update，做了一層緩存，估計(jì)是這樣的話批量插入和更新數(shù)據(jù)庫(kù)會(huì)快一點(diǎn)。

然后我看redis是有值的，以為是redis和數(shù)據(jù)庫(kù)數(shù)據(jù)不一致問(wèn)題，我就把redis的key刪了，重新再跑一下，結(jié)果打印了insert語(yǔ)句，但是沒(méi)有插入到數(shù)據(jù)，看來(lái)事情并沒(méi)有那么簡(jiǎn)單- -

問(wèn)題分析

因?yàn)閿?shù)據(jù)表很大，有5E+數(shù)據(jù)，我第一反應(yīng)是mysql表數(shù)據(jù)量可能爆了，但是查了下好像沒(méi)有太大限制

再認(rèn)真看了下表的自增id，這個(gè)數(shù)字讓人有點(diǎn)熟悉的：2147483647 這個(gè)不就是int的最大值嗎。意思是因?yàn)樽栽鰅d超過(guò)了int，所以插入失敗了，id設(shè)的就是int類型，還有個(gè)小彩蛋，目前數(shù)據(jù)庫(kù)設(shè)的int長(zhǎng)度是50，但是根本沒(méi)什么鳥(niǎo)用。

知道了問(wèn)題在哪，但是這個(gè)問(wèn)題處理起來(lái)很麻煩，因?yàn)閿?shù)據(jù)量太大了，先請(qǐng)教一下deepseek吧。

方案處理

deepseek給我提供了三個(gè)方案：

第一個(gè)是最簡(jiǎn)單粗暴的改BIGINT，不用遷移數(shù)據(jù)，但是會(huì)全程鎖表。

第二個(gè)分布式ID需要重新設(shè)計(jì)表，需要把數(shù)據(jù)遷移到新表，而且還要redis等支撐。

第三個(gè)分庫(kù)分表就更麻煩了，分庫(kù)分表需要引入框架，不按照分片查詢還需要引入ES，引入了ES還需要引入同步mysql和ES的中間件logstash等。

但是改bigint估計(jì)鎖表太久，我先看看有沒(méi)有其他辦法先緊急處理下數(shù)據(jù)。但是按理說(shuō)int最大值是21E+，數(shù)據(jù)表數(shù)據(jù)才5E+，按理說(shuō)是用不完的。結(jié)果我看到自增的id值居然是不連續(xù)的

按理說(shuō)自增id應(yīng)該是一個(gè)接著一個(gè)，不會(huì)有空隙的，后面查了一下由于數(shù)據(jù)庫(kù)自增id有個(gè)高性能策略，設(shè)置了id就不一定連續(xù)。

后面又查了下有沒(méi)有一鍵把數(shù)據(jù)表id重排的方法，結(jié)果也是沒(méi)有的。最后我是寫了一個(gè)存儲(chǔ)過(guò)程先把最后100萬(wàn)的id清理出來(lái)，可以先頂個(gè)幾天，后面再想辦法處理。

BEGIN
  DECLARE start_id INTDEFAULT1;
DECLARE end_id INTDEFAULT100000;
DECLARE current_batch INTDEFAULT0;
  WHILE start_id <= end_id DO
    -- 更新臨時(shí)表中的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

最后重新設(shè)置自增值，如果自增值已經(jīng)存在，則會(huì)跳到max(id)+1

-- 重置自增值
ALTER TABLE your_table AUTO_INCREMENT =max(id)+1;

清理了大概500萬(wàn)的id段出來(lái)，然后我懷疑id間隔這么大是因?yàn)椴l(fā)太高導(dǎo)致的。一開(kāi)始程序是單線程，消費(fèi)到500條就批量入庫(kù)，但是后面發(fā)現(xiàn)單線程消費(fèi)比較慢，數(shù)據(jù)量太多消費(fèi)有點(diǎn)延遲。后面改成java批量消費(fèi)，配置了30個(gè)消費(fèi)者。接著我嘗試了一下減少消費(fèi)者數(shù)量，設(shè)置成15個(gè)，id的間隔真的變小了。

設(shè)置BIGINT

節(jié)后回來(lái)發(fā)現(xiàn)id還剩200萬(wàn)，討論到最后還是把id的數(shù)據(jù)類型從int改成bigint

ALTER TABLE xxx MODIFY id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT

UNSIGNED 無(wú)符號(hào)位，不算負(fù)數(shù)，可以增加一倍數(shù)據(jù)，NOT NULL 非空 AUTO_INCREMENT自增

在測(cè)試環(huán)境有一億數(shù)據(jù)，修改id的類型大概用了一個(gè)小時(shí)，現(xiàn)網(wǎng)我估計(jì)也是用6-7個(gè)小時(shí)也差不多了。結(jié)果改了一晚上都還沒(méi)改好，然后我找了一個(gè)可以查詢sql進(jìn)度的語(yǔ)句......

SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED, ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 2) AS "Progress (%)" FROM performance_schema.events_stages_current;

不查不知道，一查嚇一跳，跑了十幾個(gè)小時(shí)居然還不到50%，而且還越跑越慢。對(duì)比了一下測(cè)試環(huán)境和現(xiàn)網(wǎng)環(huán)境的buffer_pool等數(shù)據(jù)也是設(shè)置正常。

估計(jì)是索引樹(shù)變大插入的數(shù)據(jù)要花多不少時(shí)間，還有一個(gè)就是現(xiàn)網(wǎng)數(shù)據(jù)庫(kù)還有其他線程會(huì)搶占CPU導(dǎo)致速度緩慢。

統(tǒng)計(jì)了一下后面的數(shù)據(jù)大概是1個(gè)小時(shí)完成1.5%左右

最后我是周一晚上執(zhí)行的，周四早上上班的時(shí)候才跑完，用了2天多一點(diǎn)的時(shí)間~

總結(jié)

剛剛才在掘金刷到一篇文章《字節(jié)面試：MySQL自增ID用完會(huì)怎樣？》，評(píng)論區(qū)都說(shuō)有沒(méi)有用完的，結(jié)果我真用完了，就感覺(jué)有點(diǎn)不可思議?？偨Y(jié)一下有幾個(gè)原因吧：

1、數(shù)據(jù)量確實(shí)很大，有5E多數(shù)據(jù)，然后并發(fā)也很高。其實(shí)當(dāng)初他們?cè)O(shè)計(jì)的時(shí)候也預(yù)料過(guò)這個(gè)問(wèn)題，所以設(shè)了個(gè)int長(zhǎng)度50，但是這個(gè)長(zhǎng)度沒(méi)起作用- -所以設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候一定要做好，不然幾億數(shù)據(jù)改個(gè)字段類型要2天

2、數(shù)據(jù)庫(kù)的自增id策略選了高性能策略，導(dǎo)致并發(fā)高的時(shí)候id間隔很大。30個(gè)消費(fèi)者異步處理，10條數(shù)據(jù)大概用了100個(gè)id的間隔，消耗太快了。所以這里存在一個(gè)時(shí)間和空間的取舍，使用多線程還是挺危險(xiǎn)的操作，要謹(jǐn)慎一點(diǎn)。

還有一個(gè)小插曲，因?yàn)橄到y(tǒng)兩天沒(méi)消費(fèi)數(shù)據(jù)，kafka的數(shù)據(jù)堆積了很多，然后我把消費(fèi)者數(shù)量從30個(gè)改成50個(gè)，跑了兩天，kafka還是有1天的延遲，看來(lái)麻木添加消費(fèi)者數(shù)量已經(jīng)沒(méi)啥提升的作用了，想起八股文說(shuō)多線程弄太多反而增加上下文切換的時(shí)間浪費(fèi)，跟這個(gè)同理。

最后我弄成sql批量消費(fèi)，消費(fèi)速度馬上提上去了。程序的消費(fèi)策略：

單線程批量500個(gè)開(kāi)始消費(fèi) ——> 30個(gè)線程單個(gè)消費(fèi) ——> 30個(gè)線程批量50個(gè)開(kāi)始消費(fèi)

所以說(shuō)多線程異步+批量操作的策略還是很重要的！不過(guò)多線程一定要注意異步問(wèn)題~

到此這篇關(guān)于MySQL 自增 ID 超過(guò) int 最大值的問(wèn)題解決的文章就介紹到這了,更多相關(guān)MySQL 自增 ID 超過(guò) int 最大值內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論