深入淺析怎麼解決MySQL自增ID用完的問題

這篇文章為大家帶來了關於mysql的相關知識，其中主要介紹了關於自增ID的相關問題，id是有上限的，既然有上限，就總有被用完的時候，id用完了，怎麼辦呢，下面一起來看一下，希望對大家有幫助。

推薦學習：mysql影片教學

#最近看到這樣的一個面試題。 MySQL的自增 ID 用完了，怎麼辦？ 以下是這個面試問題的解決方案。

如果你有用過或了解MySQL，那你一定知道自增主鍵了。每個自增id都是定義了初始值，然後依照指定步長成長（預設步長是1）。雖然，自然數是沒有上限的，但是我們在設計表結構的時候，通常都會指定字段長度，那麼，這時候id就有上限了。既然有上限，就總有被用完的時候，如果id用完了，怎麼辦呢？今天就一起來學習下。

自增id

說到自增id，相信你的第一反應一定是在設計表結構的時候自訂一個自增id字段，那麼就有一個問題啦，在插入資料時有可能唯一主鍵衝、sql交易回滾、批次插入的時候，批量申請自增值等原因導致自增id是不連續的。

表格定義的自增值達到上線後的邏輯是：再申請下一個id的時候，取得的是同一個值（最大值）。大家可以插入sql設定id是最大值，再insert一條不主動設定id的語句就可以驗證這個結論囉。這時候如果再插入就是報主鍵衝突咯～

這裡提醒一下：232-1（4294967295）不是一個特別大的數，對於一個頻繁插入刪除資料的表來說，是可能會被用完的。因此在建表的時候你需要檢視你的表格是否有可能達到這個上限，如果有可能，就應該創建成 8 個位元組的 bigint unsigned。

InnoDB系統自增row_id

如果你建立的 InnoDB 表沒有指定主鍵，那麼 InnoDB 會為你建立一個不可見的，長度為 6 個位元組的 row_id。 InnoDB 維護了一個全域的 dict_sys.row_id 值，所有無主鍵的 InnoDB 表，每插入一行數據，都將目前的 dict_sys.row_id 值作為要插入資料的 row_id，然後把 dict_sys.row_id 的值加 1。

實際上，在程式碼實作時 row_id 是一個長度為8位元組的無符號長整型 (bigint unsigned)。但是，InnoDB 在設計時，留給row_id 的只是6 個位元組的長度，這樣寫到資料表中時只​​放了最後6 個位元組，所以row_id 能寫到資料表中的值，就有兩個特徵：

row_id 寫入表中的值範圍，是從0 到248-1；

當dict_sys.row_id=2^48時，如果再有插入資料的行為要來申請row_id，拿到以後再取最後6 個位元組的話就是0。

#雖然，2^48這個數字已經很大了，但是大家要知道一個系統是可以跑很久的，那麼還是可能達到上限的，這時候再申請就會覆蓋原來的記錄了。因此，盡量不要選擇這種方式！

Xid

MySQL中redo log 和 binlog 相符的時候，它們有一個共同的欄位叫做 Xid。它在 MySQL 中是用來對應事務的。

MySQL 內部維護了一個全域變數 global_query_id，每次執行語句的時候將它賦值給 Query_id，然後給這個變數加 1。如果目前語句是這個交易執行的第一個語句，那麼 MySQL 也會同時把 Query_id 賦值給這個交易的 Xid。而 global_query_id 是一個純內存變量，重啟之後就清零了。所以在同一個資料庫實例中，不同事務的 Xid 也是有可能相同的。

Innodb trx_id

InnoDB 內部維護了一個 max_trx_id 全域變量，每次需要申請一個新的 trx_id 時，就獲得 max_trx_id 的目前值，然後並將 max_trx_id 加 1。

InnoDB 資料可見性的核心思想是：每一行資料都記錄了更新它的trx_id，當一個事務讀到一行資料的時候，判斷這個資料是否可見的方法，就是透過交易的一致性視圖與這行資料的trx_id 做比較。但是這個過程有髒讀存在，那麼這個id就不會是原子性的，有重複的可能性。

thread_id

其實，在執行緒 id 才是 MySQL 中最常見的一種自增 id。平常我們在檢查各種現場的時候，show processlist 裡面的第一列，就是 thread_id。

thread_id 的邏輯很好理解：系統保存了一個全域變數 thread_id_counter，每新建一個連接，就將 thread_id_counter 賦值給這個新連接的執行緒變數。

thread_id_counter 定義的大小是 4 個位元組，因此達到 232-1 後，它就會重設為 0，然後繼續增加。結果跟row_id一樣，就會覆蓋原有記錄了。

上面介紹了幾種MySQL本身的一些自增id，其實，實際運用中，我們也可能會選擇外部的自增主鍵，然後持久化到資料庫，以此來取代資料庫本身的自增id。下面來說說吧。

Redis自增主鍵

其實外部自增主鍵的產生方式很多，為什麼我要介紹redis呢？因為我自己在實際應用中使用發現它的許多優點。

redis本身是原子性的，因此高並發也是線程安全的。假設主鍵欄位長度20，我們以時間 自增數構成主鍵，例如：8位元日期 12自增數。那麼，根據業務性質可以決定時間取年月日或到毫秒級，那麼在毫秒之間自增數的重複機率是極小極小的，基本的業務都能適用。

總結

上面介紹了好幾種自增id，每種自增id 都有各自的應用場景，在達到上限後的表現也不同：

#1、 表的自增id 達到上限後，再申請時它的值就不會改變，進而導致繼續插入資料時報主鍵衝突的錯誤
2、 row_id 達到上限後，則會歸0 再重新遞增，如果出現相同的row_id，後寫的資料會覆寫先前的資料
3、 Xid 只需要不在同一個binlog 檔案中出現重複值即可。雖然理論上會出現重複值，但機率極小，可以忽略不計
4、 InnoDB 的max_trx_id 遞增值每次MySQL 重啟都會被保存起來，所以我們文章中提到的髒讀的例子就是一個必現的bug，好在留給我們的時間還很充裕
5、 thread_id 是我們使用中最常見的，而且也是處理得最好的一個自增id 邏輯了
6、 redis外部自增，毫秒級別，理論上會出現重複值，但是機率極小，可以忽略不計
7、 其實，每種自增id都有各自的適用場景，大家在平常使用中可以根據具體場景再選擇。但是要未雨綢繆，因為系統的運行時間和數據的存儲，這些都是要考慮在內的，綜合考慮，選擇一個在系統運行期間一定不會出現重複即刻。你學會了嗎？

推薦學習：mysql影片教學

#

以上是深入淺析怎麼解決MySQL自增ID用完的問題的詳細內容。更多資訊請關注PHP中文網其他相關文章！