云和恩墨技術(shù)通訊：關(guān)于MySQL序列的那點(diǎn)事-陳龍

1、云和恩墨ORACLE月度通訊2020-07陳龍經(jīng)驗(yàn)：關(guān)于MYSQL序列的那點(diǎn)事在MySQL的日常運(yùn)維實(shí)踐中，了解并掌握序列的相關(guān)知識(shí)是我們成為一名成熟MySQL DBA的必修課，本文將為讀者介紹MySQL序列的那點(diǎn)事，在此分享給大家。問(wèn)題描述為了達(dá)到標(biāo)識(shí)的目的，許多應(yīng)用程序需要生成唯一編號(hào)，比如：商品編號(hào)、交易流水號(hào)等。MySQL數(shù)據(jù)庫(kù)同樣能夠支持這樣的需求場(chǎng)景，AUTO_INCREMENT就是為MySQL實(shí)現(xiàn)序列的方式，它會(huì)自動(dòng)生成序列編號(hào)。但是它的使用是有要求的，比如：1.每個(gè)表只能有一個(gè)列具備AUTO_INCREMENT屬性，并且為整數(shù)型;2.AUTO_INCREMENT列不能包含NUL

2、L值（MySQL會(huì)自動(dòng)設(shè)置為NOT NULL）;3.AUTO_INCREMENT列上必選要有索引，常見(jiàn)為primary key和unique index;備注：由于存儲(chǔ)引擎的不同對(duì)于序列的定義和使用存在差異，本文以innodb引擎作為講解，具體差異區(qū)別。在MySQL數(shù)據(jù)庫(kù)中，如果手動(dòng)刪除某一行后在重新插入，那原來(lái)的序列還會(huì)被復(fù)用么？真會(huì)如你想的哪樣嗎？本文將用實(shí)驗(yàn)結(jié)果來(lái)告訴你:問(wèn)題分析刪除表中偶數(shù)序列行, 后插入數(shù)據(jù)時(shí),發(fā)現(xiàn)序列（id）發(fā)生了斷層：在插入新數(shù)據(jù)后，原來(lái)被刪除的序列已經(jīng)不再重復(fù)使用了，而下一個(gè)序列為未使用的最小整數(shù)。刪除當(dāng)前行對(duì)于下一次序列的分配，沒(méi)有影響。對(duì)于每次數(shù)據(jù)進(jìn)行插入，

3、都會(huì)從AUTO_INCREMENT列中獲取最大值，在進(jìn)行偏移量增加。如默認(rèn)的偏移量為1.說(shuō)明：對(duì)于最大值獲取，不是簡(jiǎn)單使用max函數(shù)，這樣并不準(zhǔn)確，因?yàn)樵诓⑿惺聞?wù)中，可能會(huì)有其他會(huì)話進(jìn)行插入更改，因此當(dāng)前會(huì)話max值并不是準(zhǔn)確的，同時(shí)還會(huì)存在自增長(zhǎng)字段的值之間發(fā)生沖突，所以MySQL會(huì)調(diào)用LAST_INSERT_ID()，返回最新AUTO_INCREMENT最大值。如圖所示:那對(duì)于更復(fù)雜的高并發(fā)的場(chǎng)景，AUTO_INCREMENT 計(jì)數(shù)會(huì)怎么分配呢？問(wèn)題結(jié)論從上面的結(jié)果可以看出，當(dāng)會(huì)話A持有該序列后，會(huì)對(duì)該序列占有語(yǔ)句鎖，會(huì)話B重新申請(qǐng)下一個(gè)序列，因此出現(xiàn)了序列不連續(xù)情況，這樣的目的其實(shí)也是為了避免線程沖突，性能優(yōu)先。在高效使用AUTO_INCREMENT列時(shí)，需要注意：1.自增序列的目的是得到一系列的正整數(shù)序列,因此不支持非正數(shù)使用。2.可以將AUTO_INCREMENT列定義為UNSIGED類(lèi)型,創(chuàng)建主鍵UNSIGNED和AUTO_INCREMENT連用表示從0開(kāi)始自增 (由0開(kāi)始自增，所以第一個(gè)自增的id為 1 )但可以增加的范圍為,不加