歡迎光臨
每天分享高質量文章

雜談自增主鍵用完了怎麼辦

來自:孤獨煙(微信號:zrj_guduyan)

引言

在面試中,大家應該經歷過如下場景

面試官:”用過mysql吧,你們是用自增主鍵還是UUID?”   

你:”用的是自增主鍵”    

面試官:”為什麼是自增主鍵?”    

你:”因為採用自增主鍵,資料在物理結構上是順序儲存,性能最好,blabla…”    

面試官:”那自增主鍵達到最大值了,用完了怎麼辦?”    

你:”what,沒複習啊!!”    (然後,你就可以回去等通知了!)

這個問題是一個粉絲給我提的,我覺得挺有意(KENG)思(B)!
於是,今天我們就來談一談,這個自增主鍵用完了該怎麼辦!

正文

簡單版

我們先明白一點,在mysql中,Int整型的範圍如下

我們以無符號整型為例,儲存範圍為0~4294967295,約43億!我們先說一下,一旦自增id達到最大值,此時資料繼續插入是會報一個主鍵衝突異常如下所示

//Duplicate entry '4294967295' for key 'PRIMARY'

那解決方法也是很簡單的,將Int型別改為BigInt型別,BigInt的範圍如下

就算你每秒10000條資料,跑100年,單表的資料也才
10000*24*3600*365*100=31536000000000
這數字距離BigInt的上限還差的遠,因此你將自增ID設為BigInt型別,你是不用考慮自增ID達到最大值這個問題!
然而,如果你在面試中的回答如果是

你:”簡單啊,把自增主鍵的型別改為BigInt型別就好了!”

接下來,面試官可以問你一個更坑的問題!

面試官:”你在線上怎麼修改列的資料型別的?”   

你:”what!我還是回等通知吧!”

怎麼改

目前業內在線修改表結構的方案,據我瞭解,一般有如下三種
方式一:使用mysql5.6+提供的在線修改功能
所謂的mysql自己提供的功能也就是mysql自己原生的陳述句,例如我們要修改原欄位名稱及型別。

 mysql> ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;

那麼,在mysql5.5這個版本之前,這是通過臨時表拷貝的方式實現的。執行ALTER陳述句後,會新建一個帶有新結構的臨時表,將原表資料全部拷貝到臨時表,然後Rename,完成創建操作。這個方式過程中,原表是可讀的,不可寫。
在5.6+開始,mysql支持在線修改資料庫表,在修改表的過程中,對絕大部分操作,原表可讀,也可以寫。
那麼,對於修改列的資料型別這種操作,原表還能寫麽?來來來,煙哥特意去官網找了mysql8.0版本的一張圖

如圖所示,對於修改資料型別這種操作,是不支持併發的DML操作!也就是說,如果你直接使用ALTER這樣的陳述句在線修改表資料結構,會導致這張表無法進行更新類操作(DELETEUPDATEDELETE)。
因此,直接ALTER是不行滴!

那我們只能用方式二或者方式三
方式二:借助第三方工具
業內有一些第三方工具可以支持在線修改表結構,使用這些第三發工具,能夠讓你在執行ALTER操作的時候,表不會阻塞!比較出名的有兩個

  • pt-online-schema-change,簡稱pt-osc
  • GitHub正式宣佈以開源的方式發佈的工具,名為gh-ost

pt-osc為例,它的原理如下

  • 1、創建一個新的表,表結構為修改後的資料表,用於從源資料表向新表中匯入資料。
  • 2、創建觸發器,用於記錄從拷貝資料開始之後,對源資料表繼續進行資料修改的操作記錄下來,用於資料拷貝結束後,執行這些操作,保證資料不會丟失。
  • 3、拷貝資料,從源資料表中拷貝資料到新表中。
  • 4、rename源資料表為old表,把新表rename為源表名,並將old表刪除。
  • 5、刪除觸發器。

然而這兩個有意(KENG)思(B)的工具,居然。。。居然。。。唉!如果你的表裡有觸發器和外鍵,這兩個工具是不行滴!
如果真碰上了資料庫里有觸發器和外鍵,只能硬杠了,請看方式三
方式三:改從庫表結構,然後主從切換
此法極其麻煩,需要專業水平的選手進行操作。因為我們的mysql架構一般是讀寫分離架構,從機是用來讀的。我們直接在從庫上進行表結構修改,不會阻塞從庫的讀操作。改完之後,進行主從切換即可。唯一需要註意的是,主從切換過程中可能會有資料丟失的情況!

高深版

其實答完上面的問題後,這篇文章差不多完了。但是,還記得我在開頭說的麽。這是一個很有意(KENG)思(B)的問題,為什麼呢?
假設啊,你的表裡的自增欄位為有符號的Int型別的,也就是說,你的欄位範圍為-2147483648到2147483648。
一切又那麼剛好,你的自增ID是從0開始的,也就是說,現在你的可以用的範圍為0~2147483648。
我們明確一點,表中真實的資料ID,肯定會出現一些意外,ID不一定是連續的。例如,有如下情形的出現

CREATE TABLE `t` (
  `id` int(11NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB;

執行下列SQL

insert into t values(null);
// 插入的行是 (1)
begin;
insert into t values(null);
rolllack;
insert into t values(null);
// 插入的行是 (3)

因此,表中的真實id必然會出現斷續的情況。
好,那這會你的自增主鍵id的資料範圍為0~2147483648,也就是單表21億條資料!考慮id會出現斷續,真實資料頂多18億條吧。
老哥,都單表18億條了,還不分庫分表?你一旦分庫分表了,就不能依賴於每個表的自增ID來全域性唯一標識這些資料了。此時,我們就需要提供一 個全域性唯一的ID號生成策略來支持分庫分表的環境。

因此在實際中,你根本等不到自增主鍵用完到情形!因此,你需要關註的文章應該是《分庫分表後如何部署上線》
所以,專業版回答如下

面試官:”那自增主鍵達到最大值了,用完了怎麼辦?”   

你:”這問題沒遇到過,因為自增主鍵我們用int型別,一般達不到最大值,我們就分庫分表了,所以不曾遇見過!”

    赞(0)

    分享創造快樂