.jpg)
本篇文章給大家?guī)?lái)了關(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于自增id的相關(guān)問(wèn)題，id是有上限的，既然有上限，就總有被用完的時(shí)候，如果id用完了，怎么辦呢，下面一起來(lái)看一下，希望對(duì)大家有幫助。
推薦學(xué)習(xí)：mysql
最近看到這樣的一個(gè)面試題。mysql的自增 ID 用完了，怎么辦？以下是這個(gè)面試題的解決方案。
如果你用過(guò)或了解過(guò)MySQL，那你一定知道自增主鍵了。每個(gè)自增id都是定義了初始值，然后按照指定步長(zhǎng)增長(zhǎng)（默認(rèn)步長(zhǎng)是1）。雖然，自然數(shù)是沒有上限的，但是我們?cè)谠O(shè)計(jì)表結(jié)構(gòu)的時(shí)候，通常都會(huì)指定字段長(zhǎng)度，那么，這時(shí)候id就有上限了。既然有上限，就總有被用完的時(shí)候，如果id用完了，怎么辦呢？今天就一起來(lái)學(xué)習(xí)下吧。
自增id
說(shuō)到自增id，相信你的第一反應(yīng)一定是在設(shè)計(jì)表結(jié)構(gòu)的時(shí)候自定義一個(gè)自增id字段，那么就有一個(gè)問(wèn)題啦，在插入數(shù)據(jù)時(shí)有可能唯一主鍵沖、sql事務(wù)回滾、批量插入的時(shí)候，批量申請(qǐng)自增值等原因?qū)е伦栽鰅d是不連續(xù)的。
表定義的自增值達(dá)到上線后的邏輯是：再申請(qǐng)下一個(gè)id的時(shí)候，獲取的是同一個(gè)值（最大值）。大家可以插入sql設(shè)置id是最大值，再insert一條不主動(dòng)設(shè)置id的語(yǔ)句就可以驗(yàn)證這一結(jié)論啦。這個(gè)時(shí)候如果再插入就是報(bào)主鍵沖突咯～
這里提醒一下：232-1（4294967295）不是一個(gè)特別大的數(shù)，對(duì)于一個(gè)頻繁插入刪除數(shù)據(jù)的表來(lái)說(shuō)，是可能會(huì)被用完的。因此在建表的時(shí)候你需要考察你的表是否有可能達(dá)到這個(gè)上限，如果有可能，就應(yīng)該創(chuàng)建成 8 個(gè)字節(jié)的 bigint unsigned。
InnoDB系統(tǒng)自增row_id
如果你創(chuàng)建的 InnoDB 表沒有指定主鍵，那么 InnoDB 會(huì)給你創(chuàng)建一個(gè)不可見的，長(zhǎng)度為 6 個(gè)字節(jié)的 row_id。InnoDB 維護(hù)了一個(gè)全局的 dict_sys.row_id 值，所有無(wú)主鍵的 InnoDB 表，每插入一行數(shù)據(jù)，都將當(dāng)前的 dict_sys.row_id 值作為要插入數(shù)據(jù)的 row_id，然后把 dict_sys.row_id 的值加 1。
實(shí)際上，在代碼實(shí)現(xiàn)時(shí) row_id 是一個(gè)長(zhǎng)度為8字節(jié)的無(wú)符號(hào)長(zhǎng)整型 (bigint unsigned)。但是，InnoDB 在設(shè)計(jì)時(shí)，給 row_id 留的只是 6 個(gè)字節(jié)的長(zhǎng)度，這樣寫到數(shù)據(jù)表中時(shí)只放了最后 6 個(gè)字節(jié)，所以 row_id 能寫到數(shù)據(jù)表中的值，就有兩個(gè)特征：
row_id 寫入表中的值范圍，是從 0 到 248-1；
當(dāng) dict_sys.row_id=2^48時(shí)，如果再有插入數(shù)據(jù)的行為要來(lái)申請(qǐng) row_id，拿到以后再取最后 6 個(gè)字節(jié)的話就是 0。
雖然，2^48這個(gè)數(shù)字已經(jīng)很大了，但是大家要知道 一個(gè)系統(tǒng)是可以跑很久的，那么還是可能達(dá)到上限的，這時(shí)候再申請(qǐng)就會(huì)覆蓋原來(lái)的記錄了。因此，盡量不要選擇這種方式！
Xid
MySQL中redo log 和 binlog 相配合的時(shí)候，它們有一個(gè)共同的字段叫作 Xid。它在 MySQL 中是用來(lái)對(duì)應(yīng)事務(wù)的。
MySQL 內(nèi)部維護(hù)了一個(gè)全局變量 global_query_id，每次執(zhí)行語(yǔ)句的時(shí)候?qū)⑺x值給 Query_id，然后給這個(gè)變量加 1。如果當(dāng)前語(yǔ)句是這個(gè)事務(wù)執(zhí)行的第一條語(yǔ)句，那么 MySQL 還會(huì)同時(shí)把 Query_id 賦值給這個(gè)事務(wù)的 Xid。而 global_query_id 是一個(gè)純內(nèi)存變量，重啟之后就清零了。所以在同一個(gè)數(shù)據(jù)庫(kù)實(shí)例中，不同事務(wù)的 Xid 也是有可能相同的。
Innodb trx_id
InnoDB 內(nèi)部維護(hù)了一個(gè) max_trx_id 全局變量，每次需要申請(qǐng)一個(gè)新的 trx_id 時(shí)，就獲得 max_trx_id 的當(dāng)前值，然后并將 max_trx_id 加 1。
InnoDB 數(shù)據(jù)可見性的核心思想是：每一行數(shù)據(jù)都記錄了更新它的 trx_id，當(dāng)一個(gè)事務(wù)讀到一行數(shù)據(jù)的時(shí)候，判斷這個(gè)數(shù)據(jù)是否可見的方法，就是通過(guò)事務(wù)的一致性視圖與這行數(shù)據(jù)的 trx_id 做對(duì)比。但是這個(gè)過(guò)程有臟讀存在，那么這個(gè)id就不會(huì)是原子性的，存在重復(fù)的可能性。
thread_id
其實(shí)，線程 id 才是 MySQL 中最常見的一種自增 id。平時(shí)我們?cè)诓楦鞣N現(xiàn)場(chǎng)的時(shí)候，show processlist 里面的第一列，就是 thread_id。
thread_id 的邏輯很好理解：系統(tǒng)保存了一個(gè)全局變量 thread_id_counter，每新建一個(gè)連接，就將 thread_id_counter 賦值給這個(gè)新連接的線程變量。
thread_id_counter 定義的大小是 4 個(gè)字節(jié)，因此達(dá)到 232-1 后，它就會(huì)重置為 0，然后繼續(xù)增加。結(jié)果跟row_id一樣，就會(huì)覆蓋原有記錄了。
上面介紹了幾種MySQL自身的一些自增id，其實(shí)，實(shí)際運(yùn)用中，我們也可能會(huì)選擇外部的自增主鍵，然后持久化到數(shù)據(jù)庫(kù)，以此來(lái)代替數(shù)據(jù)庫(kù)自身的自增id。下面來(lái)說(shuō)說(shuō)吧。
Redis自增主鍵
其實(shí)外部自增主鍵的生成方式有很多，為什么我要介紹redis呢？因?yàn)槲易约涸趯?shí)際應(yīng)用中使用發(fā)現(xiàn)它的很多優(yōu)點(diǎn)。
redis自身是原子性的，因此高并發(fā)也是線程安全的。假設(shè)主鍵字段長(zhǎng)度20，我們以時(shí)間+自增數(shù)來(lái)構(gòu)成主鍵，例如：8位日期+12自增數(shù)。那么，根據(jù)業(yè)務(wù)性質(zhì)可以決定時(shí)間取年月日或者到毫秒級(jí)，那么在毫秒之間自增數(shù)的重復(fù)概率是極小極小的，基本的業(yè)務(wù)都能適用。
總結(jié)
上面介紹了好幾種自增id，每種自增 id 有各自的應(yīng)用場(chǎng)景，在達(dá)到上限后的表現(xiàn)也不同：
1、?表的自增 id 達(dá)到上限后，再申請(qǐng)時(shí)它的值就不會(huì)改變，進(jìn)而導(dǎo)致繼續(xù)插入數(shù)據(jù)時(shí)報(bào)主鍵沖突的錯(cuò)誤
2、?row_id 達(dá)到上限后，則會(huì)歸 0 再重新遞增，如果出現(xiàn)相同的 row_id，后寫的數(shù)據(jù)會(huì)覆蓋之前的數(shù)據(jù)
3、?Xid 只需要不在同一個(gè) binlog 文件中出現(xiàn)重復(fù)值即可。雖然理論上會(huì)出現(xiàn)重復(fù)值，但是概率極小，可以忽略不計(jì)
4、?InnoDB 的 max_trx_id 遞增值每次 MySQL 重啟都會(huì)被保存起來(lái)，所以我們文章中提到的臟讀的例子就是一個(gè)必現(xiàn)的 bug，好在留給我們的時(shí)間還很充裕
5、?thread_id 是我們使用中最常見的，而且也是處理得最好的一個(gè)自增 id 邏輯了
6、?redis外部自增，毫秒級(jí)別，理論上會(huì)出現(xiàn)重復(fù)值，但是概率極小，可以忽略不計(jì)
7、?其實(shí)，每種自增id都有各自的適用場(chǎng)景，大家在平時(shí)使用中可以根據(jù)具體場(chǎng)景再選擇。但是要未雨綢繆，因?yàn)橄到y(tǒng)的運(yùn)行時(shí)間和數(shù)據(jù)的存儲(chǔ)，這些都是要考慮在內(nèi)的，綜合考慮，選擇一個(gè)在系統(tǒng)運(yùn)行期間一定不會(huì)出現(xiàn)重復(fù)即刻。你學(xué)會(huì)了嗎？
推薦學(xué)習(xí)：mysql
.png)
