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

引言

在面試中，大家應該經曆過如下場景

面試官:"用過mysql吧，你們是用自增主鍵還是UUID？"

你:"用的是自增主鍵"

面試官:"為什麼是自增主鍵？"

你:"因為采用自增主鍵，資料在實體結構上是順序存儲，性能最好，blabla..."

面試官:"那自增主鍵達到最大值了，用完了怎麼辦？"

你:"what，沒複習啊！！"

(然後，你就可以回去等通知了！)

這個問題是一個粉絲給我提的，我覺得挺有​

​意(KENG)思(B)!​

​

于是，今天我們就來談一談，這個自增主鍵用完了該怎麼辦！

正文

簡單版

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

類型	最小值	最大值	存儲大小
Int(有符号)	-2147483648	2147483648	4 bytes
Int(無符号)	4294967295

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

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

那解決方法也是很簡單的，将Int類型改為BigInt類型，BigInt的範圍如下

BigInt(有符号)	-9223372036854775808	9223372036854775808	8 bytes
BigInt(無符号)	18446744073709551615

就算你每秒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​

​這樣的語句線上修改表資料結構，會導緻這張表無法進行更新類操作(​

​DELETE​

​、​

​UPDATE​

​DELETE​

​)。

是以，直接​

​ALTER​

​是不行滴！

那我們隻能用方式二或者方式三

方式二:借助第三方工具

業内有一些第三方工具可以支援線上修改表結構，使用這些第三發工具，能夠讓你在執行​

​ALTER​

​操作的時候，表不會阻塞！比較出名的有兩個

• 1、​

  ​pt-online-schema-change​

  ​，簡稱​

  ​pt-osc​

• 2、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(11) NOT 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類型，一般達不到最大值，我們就分庫分表了，是以不曾遇見過！"