一文讓你徹底弄懂MySQL自增列

MYSQL的自增列在實際生產中應用的非常廣泛，相信各位所在的公司or團隊，MYSQL開發規范中一定會有要求盡量使用自增列去充當表的主鍵，為什么DBA會有這樣的要求，各位在使用MYSQL自增列時遇到過哪些問題？這些問題是由什么原因造成的呢？本文由淺入深，帶領大家徹底弄懂MYSQL的自增機制。

網站建設哪家好，找創新互聯！專注于網頁設計、網站建設、微信開發、微信小程序開發、集團企業網站建設等服務項目。為回饋新老客戶創新互聯還提供了城關免費建站歡迎大家使用！

1.? 通過auto_increment關鍵字來指定自增的列，并指定自增列的初始值為1。

[root@localhost][test1]Create table t(id int auto_increment ,namevarchar(10),primary key(id))auto_increment=1;

QueryOK, 0 rows affected (0.63 sec)

2.? 自增列上必須有索引，將t表的主鍵索引刪除掉，會報錯

[root@localhost][test1]alter table t drop primary key;

ERROR1075 (42000): Incorrect table definition; there can be only one auto column andit must be defined as a key

3.? 設定auto_increment_increment參數，可以調整自增步長，該參數有session級跟global級，在分庫分表以及雙主or多主的模式下比較有用。

4.? 一個表上只能有一個自增列

5.? Mysql5.7及以下版本，innodb表的自增值保存在內存中，重啟后表的自增值會設為max(id)+1，而myisam引擎的自增值是保存在文件中，重啟不會丟失。Mysql8.0開始，innodb的自增id能持久化了，重啟mysql，自增ID不會丟。

首先：表中自增列的上限是根據自增列的字段類型來定的。

若設定了自增id充當主鍵，當達到了自增id的上限值時，會發生什么樣的事情呢？還是以上面創建的 t表為例， 先回顧它的表結構：

CREATETABLE `t` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10) COLLATE utf8mb4_binDEFAULT NULL,

PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

無符號的int類型，上限是2147483647。這里我們將表的自增值設為2147483647，再插入兩行數據：

[root@localhost][test1]alter table t auto_increment=2147483647;

QueryOK, 0 rows affected (0.01 sec)

Records:0? Duplicates: 0? Warnings: 0

[root@localhost][test1]insert into t(name) values ('test');??????????

QueryOK, 1 row affected (0.01 sec)

[root@localhost][test1]insert into t(name) values ('test');

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

可以看到，第一個插入沒問題，因為自增列的值為2147483647，這是達到了上限，還沒有超過，第二行數據插入時，則報出主鍵重復，在達到上限后，無法再分配新的更大的自增值，也沒有從1開始從頭分配，在這里表的auto_increment值會一直是2147483647。

對于寫入量大，且經常刪除數據的表，自增id設為int類型還是偏小的，所以我們為了避免出現自增id漲滿的情況，這邊統一建議自增id的類型設為unsigned bingint，這樣基本可以保障表的自增id是永遠夠用的。

這里內容比較多，innodb是索引組織表，所以涉及到索引的知識，但這不是本文的重點，我們快速回顧索引知識：

1.? Innodb索引分為主鍵跟輔助索引，主鍵即全表，輔助索引葉子節點保存主鍵的值，而主鍵的葉子節點保存數據行，中間節點存著葉子節點的路由值。

2.? Innodb存儲數據（索引）的單位是頁，這里默認是16K，這也意味著，數據本身越小，一個頁中能存數據的量越多，而檢索效率不僅僅由索引的層數來決定，更是由一次能夠緩存的數據量來定，也就是說數據本身越小，則一次IO能夠提取到緩沖區的數據越多（OS每次IO的量是固定的4K），查詢的效率越好。

其實能夠理解索引的結構及索引寫入插入、更新的原理，則自然就明白為何建議使用自增id。這里我直接列出使用自增id 當主鍵的好處吧：

1.? 順序寫入，避免了葉的分裂，數據寫入效率好

2.? 縮小了表的體積，特別是相比于UUID當主鍵，甚至組合字段當主鍵時，效果更明顯

3.? 查詢效率好，原因就是我上面說到索引知識的第二點。

4.? 某些情況下，我們可以利用自增id來統計大表的大致行數。

5.? 在數據歸檔or垃圾數據清理時，也可方便的利用這個id去操作，效率高。

容易出現不連續的id

有的同志會發現，自己的表中id值存在空洞，如類似于1、2、3、8、9、10這樣，有的適合有想依賴于自增id的連續性來實現業務邏輯，所以會想方設法去修改id讓其變的連續，其實，這是沒有必要的，這一塊的業務邏輯交由MySQL實現是很不理智的，表的記錄小還好，要是表的數據量很大，修改起來就糟糕了。那么，為什么自增id會容易出現空洞呢？

自增id的修改機制如下：

在MySQL里面，如果字段id被定義為AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：

1. 如果插入數據時id字段指定為0、null 或未指定值，那么就把這個表當前的

AUTO_INCREMENT值填到自增字段；

2. 如果插入數據時id字段指定了具體的值，就直接使用語句里指定的值。

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設，某次要插入的值是X，當前的自增值是Y。

1. 如果XY，那么這個表的自增值不變；

2. 如果X≥Y，就需要把當前自增值修改為 新的自增值 。

新的自增值生成算法是：從auto_increment_offset開始，以auto_increment_increment為步長，持續疊加，直到找到第一個大于X的值，作為新的自增值。

Insert、update、delete操作會讓id不連續。

Delete、update：刪除中間數據，會造成空動，而修改自增id值，也會造成空洞（這個很少）。

Insert：插入報錯（唯一鍵沖突與事務回滾），會造成空洞，因為這時候自增id已經分配出去了，新的自增值已經生成，如下面例子：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] begin;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] insert intot(name) values('aaa');

Query OK, 1 row affected (0.00 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

+----+------+

5 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] rollback;

Query OK, 0 rows affected (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 6 |

+----------------+

1 row in set (0.01 sec)

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

可以看到，雖然事務回滾了，但自增id已經回不到從前啦，唯一鍵沖突也是這樣的，這里就不做測試了。

在批量插入時（insert select等），也存在空洞的問題。看下面實驗：

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

+----+------+

4 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|????????????? 5 |

+----------------+

1 row in set (0.00 sec)

[root@localhost][test1] insert intot(name) select name from t;??????????????????????

Query OK, 4 rows affected (0.04 sec)

Records: 4?Duplicates: 0? Warnings: 0

[root@localhost][test1] select * fromt;

+----+------+

| id | name |

+----+------+

|? 1| aaa? |

|? 2| aaa? |

|? 3| aaa? |

|? 4| aaa? |

|? 5| aaa? |

|? 6| aaa? |

|? 7| aaa? |

|? 8| aaa? |

+----+------+

8 rows in set (0.00 sec)

[root@localhost][test1] selectAuto_increment from information_schema.tables where table_name='t';

+----------------+

| Auto_increment |

+----------------+

|???????????? 12 |

+----------------+

1 row in set (0.00 sec)

可以看到，批量插入，導致下一個id值不為9了，再插入數據，即產生了空洞，這里是由mysql申請自增值的機制所造成的，MySQL在批量插入時，若一個值申請一個id，效率太慢，影響了批量插入的速度，故mysql采用下面的策略批量申請id。

1.? 語句執行過程中，第一次申請自增id，會分配1個；

2.? 1個用完以后，這個語句第二次申請自增id，會分配2個；

3.? 2個用完以后，還是這個語句，第三次申請自增id，會分配4個；

4.? 依此類推，同一個語句去申請自增id，每次申請到的自增id個數都是上一次的兩倍。

在對自增列進行操作時，存在著自增鎖，mysql的innodb_autoinc_lock_mode參數控制著自增鎖的上鎖機制。該參數有0、1、2三種模式：

0：語句執行結束后釋放自增鎖，MySQL5.0時采用這種模式，并發度較低。

1：mysql的默認設置。普通的insert語句申請后立馬釋放，insert select、replace insert、load data等批量插入語句要等語句執行結束后才釋放，并發讀得到提升

2：所有的語句都是申請后立馬釋放，并發度大大提升！但是在binlog為statement格式時，主從數據會發生不一致。這一塊網上有很多介紹，我不做介紹了。

在徹底了解了MYSQL的自增機制以后，在實際生產中就能靈活避坑，這里建議不要用自增id值去當表的行數，當需要對大表準確統計行數時，可以去count(*)從庫，如果業務很依賴大表的準確行數，直接弄個中間表來統計，或者考慮要不要用mysql的innodb來存儲數據，這個是需要自己去權衡。另外對于要求很高的寫入性能，但寫入量又比較大的業務，自增id的使用依然存在熱點寫入的問題，存在性能瓶頸，這時候可通過分庫分表來解決。

MySQL 數據庫如何添加列

傳統情況

我們先回顧一下，在沒有 "立刻加列" 功能時，加列操作是怎么完成的。我們也借此來熟悉一下本期的圖例：

當進行 加列操作 時，所有的數據行 都必須要 增加一段數據（圖中的 列 4 數據）

如上一期圖解所講，當改變數據行的長度，就需要 重建表空間（圖中灰藍的部分為發生變更的部分）

數據字典中的列定義也會被更新

以上操作的問題在于 每次加列 操作都需要重建表空間，這就需要大量 IO以及大量的時間

立刻加列

"立刻加列" 的過程如下圖：

請點擊輸入圖片描述

請點擊輸入圖片描述

"立刻加列" 時，只會變更數據字典中的內容，包括：

在列定義中增加 新列的定義

增加 新列的默認值

"立刻加列"?后，當要讀取表中的數據時：

由于 "立刻加列" 沒有 變更行數據，讀取的行數據只有 3 列

MySQL 會將 新增的第 4 列的默認值，追加到 讀取的數據后

以上過程描述了 如何讀取?在 "立刻加列" 之前寫入的數據，其實質是：在讀取數據的過程中，"偽造"?了一個新列出來

那么如何讀取?在 "立刻加列" 之后?寫入的數據呢 ? 過程如下圖：

當讀取 行 4 時：

請點擊輸入圖片描述

請點擊輸入圖片描述

通過判斷?數據行的頭信息中的instant?標志位，可以知道該行的格式是 "新格式"：該行頭信息后有一個新字段?"列數"

通過讀取?數據行的?"列數"?字段，可以知道 該行數據中多少列有 "真實" 的數據，從而按列數讀取數據

通過上圖可以看到：讀取?在"立刻加列"?前/后寫入的數據是不同的流程

通過以上的討論，我們可以總結?"立刻加列"?之所以高效的原因是：

在執行?"立刻加列"?時，不變更數據行的結構

讀取 "舊" 數據時，"偽造"?新增的列，使結果正確

寫入 "新" 數據時，使用了新的數據格式（增加了instant標志位 和?"列數"?字段)，以區分新舊數據

讀取 "新" 數據時，可以如實讀取數據

那么?我們是否能一直 "偽造"?下去???"偽造"?何時會被拆穿 ?

考慮以下場景：

用 "立刻加列" 增加列 A

寫入數據行 1

用 "立刻加列" 增加列?B

寫入數據行?2

刪除列?B

我們推測一下 "刪除列 B" 的最小代價：需要修改 數據行中的instant標志位或?"列數"?字段，這至少會影響到?"立刻加列"?之后寫入的數據行，成本類似于重建數據

從以上推測可知：當出現 與?"立刻加列"?操作不兼容 的 DDL 操作時，數據表需要進行重建，如下圖所示：

請點擊輸入圖片描述

請點擊輸入圖片描述

擴展思考題：是否能設計其他的數據格式，取代instant標志位和?"列數"?字段，使得 加列/刪列 操作都能 "立刻完成" ?（提示：考慮 加列?- 刪列?- 再加列 的情況）

使用限制

在了解原理之后，我們來看看?"立刻加列"?的使用限制，就很容易能理解其中的前兩項：

"立刻加列"?的加列位置只能在表的最后，而不能加在其他列之間

在元數據中，只記錄了 數據行 應有多少列，而沒有記錄 這些列 應出現的位置。所以無法實現指定列的位置

"立刻加列"?不能添加主鍵列

加列 不能涉及聚簇索引的變更，否則就變成了 "重建" 操作，不是 "立刻" 完成了

"立刻加列"不支持壓縮的表格式

按照 WL 的說法："COMPRESSED is no need to supported"（沒必要支持不怎么用的格式）

總結回顧

我們總結一下上面的討論：

"立刻加列" 之所以高效的原因是：

在執行 "立刻加列" 時，不變更數據行的結構

讀取 "舊" 數據時，"偽造"?新增的列，使結果正確

寫入 "新" 數據時，使用了新的數據格式?(增加了?instant 標志位?和 "列數" 字段)，以區分新舊數據

讀取 "新" 數據時，可以如實讀取數據

"立刻加列"?的 "偽造" 手法，不能一直維持下去。當發生?與 "立刻加列" 操作不兼容?的 DDL?時，表數據就會發生重建

回到之前遺留的兩個問題：

"立刻加列" 是如何工作的 ?

我們已經解答了這個問題

所謂 "立刻加列" 是否完全不影響業務，是否是真正的 "立刻" 完成 ?

可以看到：就算是 "立刻加列"，也需要變更 數據字典，那么 該上的鎖還是逃不掉的。也就是說 這里的 "立刻" 指的是 "不變更數據行的結構"，而并非指 "零成本地完成任務"

navicat mysql怎么在表中添加列？

你是想改表結構還是插入數據？\x0d\x0a表結構：右鍵點中表，選設計表，移動到最底部一條，“創建欄位”將在這之后增加一列；“插入欄位”將在這列之前增加一列。\x0d\x0a插入數據：雙擊打開表，下面有個“+”的符號，點一下，增加一條記錄，然后再點一下下面的對鉤保存數據。\x0d\x0anavicat 很簡單的。慢慢熟悉吧，記得去把它漢化哦。

當前標題：MySQL怎么弄列 岳綺羅和顧玄武是什么關系
分享鏈接：http://vcdvsql.cn/article18/ddopsgp.html

成都網站建設公司_創新互聯，為您提供自適應網站、網站建設、虛擬主機、網站營銷、App設計、網站收錄

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯