當(dāng)我們使用 MySQL 進(jìn)行數(shù)據(jù)存儲(chǔ)時(shí)，一般會(huì)為一張表設(shè)置一個(gè)自增主鍵，當(dāng)有數(shù)據(jù)行插入時(shí)，該主鍵字段則會(huì)根據(jù)步長(zhǎng)與偏移量增長(zhǎng)（默認(rèn)每次+1）。
創(chuàng)新互聯(lián)專注于企業(yè)成都營(yíng)銷網(wǎng)站建設(shè)、網(wǎng)站重做改版、惠水網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5技術(shù)、商城網(wǎng)站開(kāi)發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為惠水等各大城市提供網(wǎng)站開(kāi)發(fā)制作服務(wù)。
下文以 Innodb 引擎為主進(jìn)行介紹，使用自增主鍵的好處有很多，如：索引空間占比小、范圍查詢與排序都友好、避免像 UUID 這樣隨機(jī)字符串帶來(lái)的頁(yè)分裂問(wèn)題等...
當(dāng)我們對(duì)該表設(shè)置了自增主鍵之后，則會(huì)在該表上產(chǎn)生一個(gè)計(jì)數(shù)器，用于為自增列分配 ID 。
自增的值并不是保存在表結(jié)構(gòu)信息內(nèi)的，對(duì)于不同的版本它們有如下的區(qū)別：
計(jì)數(shù)器的值存儲(chǔ)在內(nèi)存中的，重啟后丟棄，下一次將讀取最大的一個(gè)自增ID往后繼續(xù)發(fā)號(hào)。
計(jì)數(shù)器的值將會(huì)持久化到磁盤(pán)。在每次發(fā)號(hào)時(shí)都將寫(xiě)入 Redolog ，并在每個(gè) Checkpoint 都進(jìn)行保存，重啟時(shí)候使用 Redolog 恢復(fù)重啟之前的值。
可以預(yù)先確定插入行數(shù)的語(yǔ)句（像簡(jiǎn)單 insert 的語(yǔ)句包含多個(gè) value 這種情況也是屬于簡(jiǎn)單插入，因?yàn)樵谶M(jìn)行插入時(shí)就已經(jīng)可以確定行數(shù)了）
預(yù)先不知道要插入的行數(shù)的語(yǔ)句（包括 INSERT ... SELECT, REPLACE ... SELECT 和 LOAD DATA 語(yǔ)句，但不包括 plain INSERT ）
如果一個(gè)事務(wù)正在向表中插入值，則會(huì)產(chǎn)生表級(jí)的共享鎖，以便當(dāng)前事務(wù)插入的行接收連續(xù)的主鍵值。
當(dāng)處于[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]時(shí)，每次訪問(wèn)計(jì)數(shù)器時(shí)都會(huì)加上一個(gè)名為 AUTO-INC 的表級(jí)鎖
傳統(tǒng)模式：鎖只持有到該語(yǔ)句執(zhí)行結(jié)束，注意是語(yǔ)句結(jié)束，不是事務(wù)結(jié)束
連續(xù)模式：批量插入時(shí)鎖持有到該語(yǔ)句執(zhí)行結(jié)束，簡(jiǎn)單插入時(shí)鎖持有到申請(qǐng)完自增ID后即釋放，不直到語(yǔ)句完成
通過(guò)調(diào)整 innodb_autoinc_lock_mode 配置項(xiàng)，可以定義 AUTO-INC 鎖的模式，不同的模式對(duì)應(yīng)的策略與鎖的粒度也將不同。
當(dāng)使用基于 Binlog 的復(fù)制場(chǎng)景時(shí)，對(duì)于 statement（SBR）同步模式下只有[ 傳統(tǒng)模式 ]與[ 連續(xù)模式 ]能保證語(yǔ)句的正確性。
基于 row（RBR）行復(fù)制的情況下任何配置模式都可以。
執(zhí)行語(yǔ)句時(shí)加 AUTO-INC 表級(jí)鎖，執(zhí)行完畢后釋放
針對(duì) Bulk Inserts 時(shí)才會(huì)采用 AUTO-INC 鎖，而針對(duì) Simple Inserts 時(shí)，則采用了一種新的輕量級(jí)的互斥鎖來(lái)分配 auto_increment 列的值。
該模式下可以保證同一條 insert 語(yǔ)句中新插入的自增 ID 都是連續(xù)的，但如果前一個(gè)事務(wù) rollback 丟棄了一部分 ID 的話也會(huì)存在后續(xù) ID 出現(xiàn)間隔的情況。
來(lái)一個(gè)分配一個(gè)，不會(huì)產(chǎn)生 AUTO-INC 表級(jí)鎖 ，僅僅會(huì)鎖住分配 ID 的過(guò)程。
由于鎖的粒度減少，多條語(yǔ)句在插入時(shí)進(jìn)行鎖競(jìng)爭(zhēng)，自增長(zhǎng)的值可能不是連續(xù)的。
且當(dāng) Binlog 模式為 statement（SBR）時(shí)自增 ID 不能保證數(shù)據(jù)的正確性
不一定，業(yè)務(wù)也不應(yīng)該過(guò)分依賴 MySQL 自增 ID 的連續(xù)性，在以下三種情況下，并不能保證自增 ID 的連續(xù)性：
假設(shè)已存在數(shù)據(jù){1,張三}，且張三所屬的字段設(shè)置了唯一主鍵
此時(shí)再次插入{null,張三}時(shí)候，主鍵沖突插入失敗，但表的計(jì)數(shù)器已由2變成了3
當(dāng)下次插入{null,李四}的時(shí)候最終入庫(kù)的會(huì)變成{3,李四}
在一個(gè)事務(wù)里進(jìn)行數(shù)據(jù)的插入，但最后并沒(méi)提交，而是執(zhí)行了 Rollback 。那么計(jì)數(shù)器已遞增的 ID 是不會(huì)返還的，而是被直接丟棄。
發(fā)生大量插入時(shí)可能會(huì)出現(xiàn)自增 ID 并不是連續(xù)的情況
當(dāng)我們?yōu)楸碓O(shè)置了自增主鍵后，自增 ID 的范圍則與主鍵的數(shù)據(jù)類型長(zhǎng)度相關(guān)。
如果沒(méi)有一張表里沒(méi)有設(shè)置任何主鍵，則會(huì)自動(dòng)生成一個(gè)隱性的6字節(jié)的 row_id 作為主鍵，它的取值范圍為 0 到 2^48-1。
row_id 是由一個(gè)全局的 dict_sys.row_id 參數(shù)進(jìn)行維護(hù)的，所有沒(méi)有主鍵的表都會(huì)用上它（并不是每一個(gè)表單獨(dú)占一份 row_id list ）
那么針對(duì)這兩種主鍵，則會(huì)有以下兩種情況發(fā)生：
當(dāng)自增 ID 到達(dá)上限后，受到主鍵數(shù)據(jù)類型的影響，計(jì)數(shù)器發(fā)放的下一個(gè) ID 也是當(dāng)前這個(gè) Max ID ，當(dāng)執(zhí)行語(yǔ)句時(shí)則會(huì)提示主鍵沖突。
建議根據(jù)業(yè)務(wù)合理規(guī)劃，在進(jìn)行表設(shè)計(jì)時(shí)就選擇適合的數(shù)據(jù)類型。
當(dāng)然也可以直接選擇 Bigint 類型，它的取值范圍是無(wú)符號(hào)情況下：0到 2^64–1（18446744073709551615）
這里并不是指 bigint 類型一定不會(huì)用完，畢竟一個(gè)有范圍的持續(xù)增長(zhǎng)的值一定會(huì)有溢出的時(shí)候，只是說(shuō)一般場(chǎng)景下它都是足夠使用的。
當(dāng) row_id 使用完后則又會(huì)從 0 開(kāi)始發(fā)放，此時(shí)新插入的數(shù)據(jù)將覆蓋回 row_id=0 的數(shù)據(jù)行。
由于它并不產(chǎn)生錯(cuò)誤，還會(huì)造成數(shù)據(jù)的覆蓋寫(xiě)。所以我們平時(shí)還是盡量給表都設(shè)置一個(gè)合理的主鍵才是。
在實(shí)際業(yè)務(wù)場(chǎng)景中，ID 常常需要返回給客戶端用來(lái)進(jìn)行相關(guān)業(yè)務(wù)操作。
假如我們有個(gè) userinfo?uid=? 的 API 接口，而用戶 ID 是自增的，這時(shí)會(huì)發(fā)生什么？
該接口通過(guò)簡(jiǎn)單的嘗試就可以暴露出真實(shí)的業(yè)務(wù)用戶總數(shù)，可以很方便的使用爬蟲(chóng)從1開(kāi)始遞增獲取數(shù)據(jù)信息。
那么有的同學(xué)說(shuō)，我既想使用自增 ID 帶來(lái)的好處，也不想承受這種比較常見(jiàn)的問(wèn)題，那該怎么辦呢？
在輸出或者獲取前對(duì)指定字段進(jìn)行可逆的轉(zhuǎn)義操作
優(yōu)點(diǎn)：實(shí)現(xiàn)起來(lái)比較簡(jiǎn)單，無(wú)論單體業(yè)務(wù)或者分布式應(yīng)用都無(wú)需考慮對(duì)數(shù)據(jù)源的解析，只需在客戶端實(shí)現(xiàn)自己的轉(zhuǎn)義與解析方法即可；
缺點(diǎn)：業(yè)務(wù)入侵較大，且需要前后端各個(gè)合作方確認(rèn)統(tǒng)一的標(biāo)準(zhǔn)；如果轉(zhuǎn)義方法有調(diào)整，變更影響面也會(huì)很大；字符串長(zhǎng)度會(huì)隨ID長(zhǎng)度而變化，使用空位填充也會(huì)特別明顯；
優(yōu)點(diǎn)：由于采用了時(shí)間戳進(jìn)行 ID 生成，該 ID 是有序的，對(duì)范圍查詢與排序都比較友好；
缺點(diǎn)：需要保證發(fā)號(hào)節(jié)點(diǎn)的高可用性；另外由于生成時(shí)依賴時(shí)間戳，需要考慮時(shí)鐘回?fù)芘c時(shí)鐘同步的問(wèn)題；
維護(hù)一份 ID 與 hash 的映射字典，它可以存在于客戶端本身，也可以依賴其他如 Redis 、ETCD 之類的組件
優(yōu)點(diǎn)：hash 長(zhǎng)度不會(huì)隨著 ID 長(zhǎng)度或值的變化而變化；可以根據(jù)已有的 hash code 來(lái)造布隆過(guò)濾器；
缺點(diǎn)：業(yè)務(wù)入侵較大，查詢時(shí)同樣需要先根據(jù) hash key 找到對(duì)應(yīng)的 ID 值；需要考慮選擇合適的 hash 算法以及解決 hash 沖突或擴(kuò)容的問(wèn)題。
create table cdat
(
localt char(20) not null,
cd char(5) not null,
snosat char(2) not null,
rnorec char(3) not null,
id INT(20) not null AUTO_INCREMENT,
primary key (id)
);
MySQL是一個(gè)開(kāi)放源碼的小型關(guān)聯(lián)式數(shù)據(jù)庫(kù)管理系統(tǒng)，開(kāi)發(fā)者為瑞典MySQL AB公司。目前MySQL被廣泛地應(yīng)用在Internet上的中小型網(wǎng)站中。由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，許多中小型網(wǎng)站為了降低網(wǎng)站總體擁有成本而選擇了MySQL作為網(wǎng)站數(shù)據(jù)庫(kù)。
系統(tǒng)特性
1．使用C和C++編寫(xiě)，并使用了多種編譯器進(jìn)行測(cè)試，保證源代碼的可移植性
2．支持AIX、FreeBSD、HP-UX、Linux、Mac OS、NovellNetware、OpenBSD、OS/2 Wrap、Solaris、Windows等多種操作系統(tǒng)
3．為多種編程語(yǔ)言提供了API。這些編程語(yǔ)言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。
4．支持多線程，充分利用CPU資源
5．優(yōu)化的SQL查詢算法，有效地提高查詢速度
6．既能夠作為一個(gè)單獨(dú)的應(yīng)用程序應(yīng)用在客戶端服務(wù)器網(wǎng)絡(luò)環(huán)境中，也能夠作為一個(gè)庫(kù)而嵌入到其他的軟件中。
7．提供多語(yǔ)言支持，常見(jiàn)的編碼如中文的GB 2312、BIG5，日文的Shift_JIS等都可以用作數(shù)據(jù)表名和數(shù)據(jù)列名。
8．提供TCP/IP、ODBC和JDBC等多種數(shù)據(jù)庫(kù)連接途徑。
9．提供用于管理、檢查、優(yōu)化數(shù)據(jù)庫(kù)操作的管理工具。
10．支持大型的數(shù)據(jù)庫(kù)?？梢蕴幚?yè)碛猩锨f(wàn)條記錄的大型數(shù)據(jù)庫(kù)。
11．支持多種存儲(chǔ)引擎。
索引功能
索引是一種特殊的文件（InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分），它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針。索引不是萬(wàn)能的，索引可以加快數(shù)據(jù)檢索操作，但會(huì)使數(shù)據(jù)修改操作變慢。每修改數(shù)據(jù)記錄，索引就必須刷新一次。為了在某種程序上彌補(bǔ)這一缺陷，許多SQL命令都有一個(gè)DELAY_KEY_WRITE項(xiàng)。這個(gè)選項(xiàng)的作用是暫時(shí)制止MySQL在該命令每插入一條新記錄和每修改一條現(xiàn)有之后立刻對(duì)索引進(jìn)行刷新，對(duì)索引的刷新將等到全部記錄插入/修改完畢之后再進(jìn)行。在需要把許多新記錄插入某個(gè)數(shù)據(jù)表的場(chǎng)合，DELAY_KEY_WRITE選項(xiàng)的作用將非常明顯。另外，索引還會(huì)在硬盤(pán)上占用相當(dāng)大的空間。因此應(yīng)該只為最經(jīng)常查詢和最經(jīng)常排序的數(shù)據(jù)列建立索引。注意，如果某個(gè)數(shù)據(jù)列包含許多重復(fù)的內(nèi)容，為它建立索引就沒(méi)有太大的實(shí)際效果。從理論上講，完全可以為數(shù)據(jù)表里的每個(gè)字段分別建一個(gè)索引，但MySQL把同一個(gè)數(shù)據(jù)表里的索引總數(shù)限制為16個(gè)。
1．InnoDB數(shù)據(jù)表的索引
與InnoDB數(shù)據(jù)表相比，在InnoDB數(shù)據(jù)表上，索引對(duì)InnoDB數(shù)據(jù)表的重要性要大得多。在InnoDB數(shù)據(jù)表上，索引不僅會(huì)在搜索數(shù)據(jù)記錄時(shí)發(fā)揮作用，還是數(shù)據(jù)行級(jí)鎖定機(jī)制的苊、基礎(chǔ)。“數(shù)據(jù)行級(jí)鎖定”的意思是指在事務(wù)操作的執(zhí)行過(guò)程中鎖定正在被處理的個(gè)別記錄，不讓其他用戶進(jìn)行訪問(wèn)。這種鎖定將影響到（但不限于）SELECT、LOCKINSHAREMODE、SELECT、FORUPDATE命令以及INSERT、UPDATE和DELETE命令。出于效率方面的考慮，InnoDB數(shù)據(jù)表的數(shù)據(jù)行級(jí)鎖定實(shí)際發(fā)生在它們的索引上，而不是數(shù)據(jù)表自身上。顯然，數(shù)據(jù)行級(jí)鎖定機(jī)制只有在有關(guān)的數(shù)據(jù)表有一個(gè)合適的索引可供鎖定的時(shí)候才能發(fā)揮效力。
2．限制
如果WHERE子句的查詢條件里有不等號(hào)（WHEREcoloum!=），MySQL將無(wú)法使用索引。類似地，如果WHERE子句的查詢條件里使用了函數(shù)（WHEREDAY（column）=），MySQL也將無(wú)法使用索引。在JOIN操作中（需要從多個(gè)數(shù)據(jù)表提取數(shù)據(jù)時(shí)），MySQL只有在主鍵和外鍵的數(shù)據(jù)類型相同時(shí)才能使用索引。
如果WHERE子句的查詢條件里使用比較操作符LIKE和REGEXP，MySQL只有在搜索模板的第一個(gè)字符不是通配符的情況下才能使用索引。比如說(shuō)，如果查詢條件是LIKE'abc%‘，MySQL將使用索引；如果查詢條件是LIKE'%abc’，MySQL將不使用索引。
在ORDERBY操作中，MySQL只有在排序條件不是一個(gè)查詢條件表達(dá)式的情況下才使用索引。（雖然如此，在涉及多個(gè)數(shù)據(jù)表查詢里，即使有索引可用，那些索引在加快ORDERBY方面也沒(méi)什么作用）。如果某個(gè)數(shù)據(jù)列里包含許多重復(fù)的值，就算為它建立了索引也不會(huì)有很好的效果。比如說(shuō)，如果某個(gè)數(shù)據(jù)列里包含的凈是些諸如“0/1”或“Y/N”等值，就沒(méi)有必要為它創(chuàng)建一個(gè)索引。
以插入語(yǔ)句的方法來(lái)解決此問(wèn)題。
如下參考：
1.在添加字段之前，第一個(gè)zd應(yīng)該首先檢查tb1表的當(dāng)前結(jié)構(gòu)。
2.將字段列instance添加到表中，如下圖。
3.再次查看表結(jié)構(gòu)，比較添加字段之前和之后的情況。
4.最后，插入新的數(shù)據(jù)行，看看它是什么樣子，最后就加入了自動(dòng)增長(zhǎng)字段如下圖。
注意事項(xiàng)：
MySQL使用的SQL語(yǔ)言是訪問(wèn)數(shù)據(jù)庫(kù)最常用的標(biāo)準(zhǔn)化語(yǔ)言。MySQL軟件采用雙重許可政策，分為社區(qū)版和商業(yè)版，由于其體積小，速度快，整體擁有成本低，尤其是開(kāi)源這一特點(diǎn)，一般中小型網(wǎng)站開(kāi)發(fā)都選擇MySQL作為網(wǎng)站數(shù)據(jù)庫(kù)。
設(shè)置自增列
MYSQL的自增列一定要是有索引的列，設(shè)置種子值要在表的后面設(shè)置
--mysql
-- 設(shè)置自增ID從N開(kāi)始
CREATE TABLE empautoinc(
ID INT PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 100 ; --（設(shè)置自增ID從100開(kāi)始）
insert into empautoinc(id) values(null);
Query OK, 1 row affected (0.00 sec)
mysql select * from empautoinc;
+-----+
| ID |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)
show table status like 'empautoinc'G;
*************************** 1. row ***************************
Name: empautoinc
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 101
Create_time: 2016-10-27 01:50:32
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
設(shè)置自增列的步長(zhǎng)，可以分為全局級(jí)別和會(huì)話級(jí)別
如果是會(huì)話級(jí)別，那么當(dāng)用戶新建一個(gè)會(huì)話的時(shí)候，那么步長(zhǎng)又回到了全局級(jí)別，所以mysql的步長(zhǎng)跟sqlserver的步長(zhǎng)有很大的不同
mysql不能設(shè)置為 表級(jí)別 的步長(zhǎng)
私信666領(lǐng)取資料
重慶分公司
重慶分公司
