##=====================================================================================##

在數(shù)據(jù)庫(kù)表設(shè)計(jì)中會(huì)糾結(jié)于”自然鍵”和”代理鍵”的選擇，自然鍵在實(shí)現(xiàn)數(shù)據(jù)“軟刪除”時(shí)實(shí)現(xiàn)比較復(fù)雜，部分自然鍵因?yàn)殒I值過(guò)長(zhǎng)或多列組合導(dǎo)致不適合作為表主鍵，而比較常見(jiàn)兩種代理鍵為自增列(auto incremnet)和全局唯一標(biāo)識(shí)列(GUID)。

使用自增列作為主鍵的優(yōu)缺點(diǎn)：

1、?? ?主鍵鍵值長(zhǎng)度短，INT列需要4個(gè)字節(jié)，BIGINT列需要8個(gè)字節(jié);

2、?? ?自增主鍵順序遞增，在INSERT操作時(shí)”順序”寫(xiě)入表;

3、?? ?由于數(shù)據(jù)集中插入到表尾部，在高并發(fā)情況下容易造成”數(shù)據(jù)頁(yè)熱點(diǎn)”，影響插入效率;

4、?? ?自增主鍵只能保證在表內(nèi)數(shù)據(jù)唯一，對(duì)于分庫(kù)分表場(chǎng)景，可能因錯(cuò)誤操作產(chǎn)生相同的“唯一值”。

使用GUID列的優(yōu)缺點(diǎn)：

1、?? ?32位GUID字符串需要更多的存儲(chǔ)空間來(lái)存放(具體存儲(chǔ)長(zhǎng)度與字符集相關(guān))，影響主鍵和其他索引的查詢性能。

2、?? ?GUID可實(shí)現(xiàn)全局唯一，能保證在多個(gè)表之間的數(shù)據(jù)唯一性

3、?? ?GUID將數(shù)據(jù)分散到全表，不會(huì)產(chǎn)生熱點(diǎn)數(shù)據(jù)頁(yè)，但會(huì)造成大量隨機(jī)IO讀寫(xiě)

在實(shí)際使用過(guò)程中，很少場(chǎng)景會(huì)使用GUID作為主鍵，大部分業(yè)務(wù)按照數(shù)據(jù)量需求使用INT或BIGINT的自增列作為主鍵，對(duì)于需要多表唯一的場(chǎng)景可以通過(guò)程序?qū)崿F(xiàn)全局唯一的自增ID。

##=====================================================================================##

MySQL在很早版本便支持自增列并在各版本中優(yōu)化自增列功能，在MySQL 5.1.22版本引入輕量級(jí)互斥自增長(zhǎng)實(shí)現(xiàn)機(jī)制，MySQL 5.5版本中引入change buffer特性，在MySQL 8.0版本引入自增列持久化。針對(duì)目前京東主要使用MySQL 5.5/5.6/5.7三個(gè)主版本，羅列部分使用自增列需要掌握的知識(shí)點(diǎn)：

知識(shí)點(diǎn)1：自增列數(shù)據(jù)類型選擇問(wèn)題

自增列除常見(jiàn)的TINYINT/SMALLINT/INT/BIGINT等整數(shù)數(shù)據(jù)類型外，還可以使用FLOAT等浮點(diǎn)數(shù)數(shù)據(jù)類型，但強(qiáng)烈建議不使用非整數(shù)數(shù)據(jù)類型作為自增列。

選取數(shù)據(jù)類型時(shí):

1、?? ?按照所需要范圍值進(jìn)行最小化選取，如果只需要0-20的范圍值，則選擇可以存放-128到127數(shù)值的TINYINT。

2、?? ?當(dāng)所需自增范圍值不確定時(shí)，建議選擇足夠使用的數(shù)據(jù)類型，先保證數(shù)據(jù)安全再考慮操作性能，相同數(shù)據(jù)量下，使用BIGINT并不會(huì)比使用INT帶來(lái)太多性能影響。京東訂單號(hào)在前期設(shè)計(jì)時(shí)使用INT數(shù)據(jù)類型，當(dāng)INT無(wú)法滿足需求時(shí)，商城花費(fèi)大量資源進(jìn)行INT到BIGINT的升級(jí)改造，同時(shí)影響諸多關(guān)聯(lián)系統(tǒng)。

##=====================================================================================##

知識(shí)點(diǎn)2：自增列跳號(hào)問(wèn)題

1、無(wú)論MySQL還是其他關(guān)系型數(shù)據(jù)庫(kù)，為提高自增列的生成效率，都將生成自增值的操作設(shè)計(jì)為非事務(wù)性操作，表現(xiàn)為當(dāng)事務(wù)回滾時(shí)，事務(wù)中生成的自增值不會(huì)被回滾。

2、當(dāng)對(duì)自增表進(jìn)行批量插入時(shí)(INSERT … SELECT …)，即使在單一會(huì)話下，MySQL仍不能保證兩次獲取到的自增ID值連續(xù)，批量插入數(shù)據(jù)量越大，產(chǎn)生的自增ID跳號(hào)范圍越大。

自增跳號(hào)對(duì)普通業(yè)務(wù)沒(méi)有太多影響，但對(duì)于像發(fā)票這類要求號(hào)碼連續(xù)的業(yè)務(wù)，不能通過(guò)自增列來(lái)實(shí)現(xiàn)。

##=====================================================================================##

知識(shí)點(diǎn)3：自增列持久化問(wèn)題

在MySQL 5.5/5.6/5.7三個(gè)版本中，MySQL并不會(huì)將自增列分配的自增值信息固化到磁盤，當(dāng)MySQL重啟后，會(huì)根據(jù)自增列上當(dāng)前最大值和參數(shù)auto_increment_offset來(lái)確定下一次的自增值，為快速獲取自增列上最大值，MySQL要求自增列必須建有索引。如果一張自增表的數(shù)據(jù)在重啟實(shí)例前被清空，實(shí)例重啟后該表數(shù)據(jù)會(huì)從”1”開(kāi)始自增(假設(shè)表的自增初始值定義為1)。

在一次亞一數(shù)據(jù)庫(kù)升級(jí)過(guò)程中，某張業(yè)務(wù)表”恰好”因?yàn)闃I(yè)務(wù)邏輯將表中所有數(shù)據(jù)刪除，重啟后該表自增值從1開(kāi)始生成，當(dāng)該表數(shù)據(jù)流轉(zhuǎn)到其他表出現(xiàn)數(shù)據(jù)沖突，發(fā)現(xiàn)問(wèn)題后，我們緊急手動(dòng)設(shè)置該表自增值，避免事故進(jìn)一步惡化，并再后期類似操作時(shí)，重點(diǎn)關(guān)注此類自增表。

建議1：如果業(yè)務(wù)會(huì)對(duì)自增表數(shù)據(jù)進(jìn)行硬刪除，在服務(wù)器重啟前應(yīng)重點(diǎn)關(guān)注該自增表使用的自增值,可以通過(guò)information_schema.tables中的auto_increment列來(lái)獲取。

PS1：在MySQL 8.0中引入自增列持久化特性，可以避免上述問(wèn)題。

##=====================================================================================##

知識(shí)點(diǎn)4：自增列初始值問(wèn)題

在運(yùn)維過(guò)程中，會(huì)遇到研發(fā)同事問(wèn)為什么新創(chuàng)建的表不是從1開(kāi)始自增，該問(wèn)題可以從以下兩個(gè)角度排查：

1、?? ?建表語(yǔ)句，在使用SHOW CREATE TABLE或MySQLDump等命令導(dǎo)出表結(jié)構(gòu)時(shí)，會(huì)包含該表當(dāng)前使用的自增值，如：

2、?? ?全局參數(shù)auto_increment_increment和auto_increment_offset，這兩全局參數(shù)可以作用實(shí)例下所有自增表，主要應(yīng)用在分庫(kù)分表的場(chǎng)景。

##=====================================================================================##

知識(shí)點(diǎn)5：修改數(shù)據(jù)列為自增數(shù)據(jù)列

當(dāng)數(shù)據(jù)類型為數(shù)值類型且表中數(shù)據(jù)唯一時(shí)，可以將該數(shù)據(jù)列轉(zhuǎn)換為自增列，修改操作會(huì)保持列中現(xiàn)有數(shù)據(jù)，不會(huì)重新生成新數(shù)據(jù)。

##=====================================================================================##

知識(shí)點(diǎn)6：修改普通表為自增表

在MySQL中允許使用ALTER TABLE方式為普通表新增一個(gè)自增列，但由于ALTER操作為DDL語(yǔ)句，在主從復(fù)制時(shí)會(huì)將該DDL語(yǔ)句傳遞給從庫(kù)執(zhí)行，MySQL并不能保證相同記錄在主從服務(wù)器上獲得相同的自增ID，會(huì)導(dǎo)致主從數(shù)據(jù)差異。

模擬測(cè)試：

主庫(kù)上創(chuàng)建表：CREATE TABLETB1001

(

C1INT);

會(huì)話1開(kāi)啟事務(wù)并執(zhí)行：

STARTTRANSACTION;INSERT INTO TB1001(C1) SELECT 1;

會(huì)話2執(zhí)行：INSERT INTO TB1001(C1) SELECT 2;

會(huì)話1提交事務(wù)。

然后將表修改為自增表：ALTER TABLE TB1001 ADD ID INT PRIMARY KEY AUTO_INCREMENT;

主庫(kù)數(shù)據(jù)為：

從庫(kù)數(shù)據(jù)為：

原因分析：

在主庫(kù)上，C1=2的數(shù)據(jù)晚于C1=1的數(shù)據(jù)被插入，但由于C1=2的數(shù)據(jù)所在事務(wù)被先提交，因此C1=2的記錄先于C1=1的記錄在從庫(kù)上執(zhí)行，因此兩條記錄在主庫(kù)和從庫(kù)上的插入順序不同，在生成自增ID時(shí)獲得到自增ID不同，最終導(dǎo)致數(shù)據(jù)差異。

建議：在將普通表修改為自增表時(shí)，如果表中存在數(shù)據(jù)，請(qǐng)勿使用ALTER TABLE的方式修改，建議新建自增臨時(shí)表，然后將數(shù)據(jù)導(dǎo)入到該表中，再兌換表名。

##=====================================================================================##

總結(jié)

以上是生活随笔為你收集整理的mysql 自增列 类型_MySQL--自增列学习的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。