mysql8.0版本修複自增列值重複利用情況（修複bug#199）

標籤：改變   select   arc   寫法   常見   table   計數   帶來   自增   

注意：mysql8.0之前的版本，InnoDB儲存引擎的表自增id可能出現重複利用的情況。

    這在很多情境下可能導致問題，包括但不限於：主備切換、曆史資料移轉等情境。在bug#199下面一大堆的回複裡，可以看到大量的同行抱怨。如，假設t1有個曆史表t1_history用來存t1錶的歷史資料，那麼mysqld重啟前，ti_history表中可能已經有了（2,2）這條資料，而重啟後我們又插入了（2，2），當新插入的(2,2)遷移到曆史表時，會違反主鍵約束。這類問題是否在資料移轉中會出現呢，我們也需要注意一下。比如我們使用mysqldump匯出資料，然後匯入到另外一個環境。mysqldump匯出資料裡面是指定了自增長值的方式，而非空。

建表時可以指定AUTO_INCREMENT值，不指定時預設為1，這個值表示當前自增列的起始值大小，如果新插入的資料沒有指定自增列的值，那麼自增列的值即為這個起始值。對於InnoDB表，這個值沒有持久到檔案中。而是存在記憶體中（dict_table_struct.autoinc）。那麼又問，既然這個值沒有持久下來，為什麼我們每次插入新的值後，show create table t1看到AUTO_INCREMENT值是跟隨變化的。其實show create table t1是直接從dict_table_struct.autoinc取得的（ha_innobase::update_create_info）。

   知道了AUTO_INCREMENT是即時儲存記憶體中的。那麼，mysqld重啟後，從哪裡得到AUTO_INCREMENT呢？記憶體值肯定是丟失了，實際上MySQL採用執行類似select max(id)+1 from t1;方法來得到AUTO_INCREMENT，而這種方法就是造成自增id重複的原因。

InnoDB AUTO_INCREMENT鎖定模式：

   innodb_autoinc_lock_mode 配置參數 有三種可能的設定 。對於“ 傳統 ”，“ 連續 ”或 “ 交錯 ”鎖定模式，設定分別為0,1或2 。從MySQL 8.0開始，交錯鎖定模式（innodb_autoinc_lock_mode=2）是預設設定。在MySQL 8.0之前，連續鎖定模式是預設值（innodb_autoinc_lock_mode=1）。

《在MySQL8.0的解決思路：》

   將自增主鍵的計數器持久化到redo log中。每次計數器發生改變，都會將其寫入到redo log中。如果資料庫發生重啟，InnoDB會根據redo log中的計數器資訊來初始化其記憶體值。為了盡量減小對系統效能的影響，計數器寫入到redo log中，並不會馬上重新整理。

--因自增主鍵沒有持久化而出現問題的常見情境：

1. 業務將自增主鍵作為業務主鍵，同時，業務上又要求主鍵不能重複。

2. 資料會被歸檔。在歸檔的過程中有可能會產生主鍵衝突。

 

所以，強烈建議不要使用自增主鍵作為業務主鍵。刨除這兩個情境，其實，自增主鍵沒有持久化的問題並不是很大，遠沒有想象中的”臭名昭著“。

 

--：最後，給出一個歸檔情境下的解決方案，

 

建立一個預存程序，根據table2（歸檔表）自增主鍵的最大值來初始化table1（線上表）。這個預存程序可放到init_file參數指定的檔案中，該檔案中的SQL會在資料庫啟動時執行。

DELIMITER ;;

CREATE PROCEDURE `auto_increment_fromtable2`(IN table1 VARCHAR(255), IN table2 VARCHAR(255))

BEGIN

set @qry = concat('SELECT @max1 := (`id` + 1) FROM `',table1,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

    set @qry = concat('SELECT @max2 := (`id` + 1) FROM `',table2,'` ORDER BY `id` DESC LIMIT 1;');

    prepare stmt from @qry;

execute stmt;

deallocate prepare stmt;

IF @max1 < @max2 THEN

    set @qry = concat('alter table `',table1,'` auto_increment=',@max2);prepare stmt from @qry;execute stmt;deallocate prepare stmt;

SELECT 'updated' as `status`;

else

SELECT 'no update needed' as `status`;

END IF;

END ;;

DELIMITER ;

###################################################################

下面複現MySQL8.0之前，自增主鍵複用的情況：

----建立測試表emp：

MySQL [test]> create table emp (id int auto_increment,name varchar(10),primary key (id));MySQL [test]> insert into emp values (1,'zhang');MySQL [test]> insert into emp values (null,'liu');MySQL [test]> insert into emp values (null,'huang');MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang ||  2 | liu   ||  3 | huang |+----+-------+3 rows in set (0.00 sec)

---從下面的資訊可以看出，emp表id自增的下一個數字是 4

MySQL [test]> show create table emp\G*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.08 sec)

---刪除id=2和3的資料，然後再插入一條資料

MySQL [test]> delete from emp where id=2 or id=3;MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang |+----+-------+1 row in set (0.01 sec)MySQL [test]> insert into emp values (null,'hhhh');MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang ||  4 | hhhh  |+----+-------+2 rows in set (0.00 sec)

（從上面的結果可以看出，如果不重啟資料庫的情況下，雖然把前面的資料刪除了，但是在此插入資料 它的自增id還是和沒刪除資料之前的順序遞增。）

---刪除id為4的資料，只保留第一行資料，然後重啟MySQL如下：

MySQL [test]> delete from emp where id=4;MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang |+----+-------+1 row in set (0.00 sec)

----重啟MySQL後，再次插入一條資料：

MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang |+----+-------+1 row in set (0.00 sec)MySQL [test]> show create table emp\G    ---（重啟完MySQL在此查看該表的自增id，這時候就是2了）*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(10) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf81 row in set (0.00 sec)

---此時再插入一條資料，會發現自增id重複利用了：

MySQL [test]> insert into emp values (null,'feng');MySQL [test]> select * from emp;+----+-------+| id | name  |+----+-------+|  1 | zhang ||  2 | feng  |+----+-------+2 rows in set (0.00 sec)

從上面的測試，我們看到在插入新的資料之前AUTO_INCREMENT為2，然後插入了（null,'feng'）， 上面的測試反映了mysqld重啟後，InnoDB儲存引擎的表自增id可能出現重複利用的情況。如果存在從庫可能會導致資料庫不一致的情況！！！！！！！

注意：

  另外，當MySQL開啟一個事務後，有類INSERT操作，自增值就會增加；但是當交易回復後，自增值並不會減小。也就是說自增值會有空洞。

￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥

二、不同的insert插入方式，會對有自增id有不同影響，如下：

1、第一種：帶有 null 值的寫法

mysql> create table emp(id int auto_increment, a int, primary key (id)) engine=innodb;mysql> insert into emp values (1,2),(2,2),(3,2);MySQL [test]> show create table emp\G     ---這時候查看該表的自增值是4*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `a` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf81 row in set (0.00 sec)     MySQL [test]>  insert into emp values (4,2),(null,2),(null,2);   ---使用null的方式插入值MySQL [test]> show create table emp\G   ----這時候會發現自增值變成了8，但是查看該表的資料id列最大是6，*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `a` int(11) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf81 row in set (0.00 sec)MySQL [test]> select * from emp;+----+------+| id | a    |+----+------+|  1 |    2 ||  2 |    2 ||  3 |    2 ||  4 |    2 ||  5 |    2 ||  6 |    2 |+----+------+6 rows in set (0.01 sec)MySQL [test]> insert into emp values (null,3);   ---此時，向該表插入一條資料，id的值就變成了8MySQL [test]>  select * from emp;+----+------+| id | a    |+----+------+|  1 |    2 ||  2 |    2 ||  3 |    2 ||  4 |    2 ||  5 |    2 ||  6 |    2 ||  8 |    3 |+----+------+7 rows in set (0.00 sec)從上面的測試可以看出，採用null寫法的時候，自增長值會多增加一個值。2、第二種：使用insert into ...select 方式插入資料，如下：mysql> create table t1(id int auto_increment primary key,name varchar(255));mysql> create table t2(name varchar(255))engine=innodb;mysql> insert into t2 values('aa'),('bb');mysql> insert into t1(name) select *from t2;   ---將t2表的資料插入到t1mysql> select * from t1;+----+------+| id | name |+----+------+|  1 | aa   ||  2 | bb   |+----+------+2 rows in set (0.00 sec)mysql> show create table t1;   ---然後查看下t1表的自增值，發現現在是4了，而資料只有2條| Table | Create Table                                                                                           | t1    | CREATE TABLE `t1` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 |1 row in set (0.00 sec)

這也說明了，採用insert .. select方式，自增長也會多增加一個值。

注意：

   我們可以看到第一種帶NULL值的寫法，自增長值會多增加一個值；第二種insert .. select，自增長也會多增加一個值。這個會帶來什麼問題呢？你會發現從庫自增長值卻是正常的（當複製格式為ROW時），這個時候其實也就是主從資料不一致了，但影響不大，除非出現記錄ID大於自增長ID，那樣插入資料重複會報錯。

究其原因，和insert語句的定位也有關係，目前有這幾類insert語句。

1、simple insert，如insert into t(name) values(‘test’)

2、bulk insert，如load data | insert into … select …. from ….

3、mixed insert，如insert into t(id,name) values(1,’a’),(null,’b’),(5,’c’);

這個和參數innodb_autoinc_lock_mode有很大的關係，預設參數值為1。innodb_autoinc_lock_mode這個參數控制著在向有auto_increment列的表插入資料時，相關鎖的行為，有三個取值：

0：這個表示tradition（傳統）

   它提供了一個向後相容的能力，在這一模式下，所有的insert語句(“insert like”) 都要在語句開始的時候得到一個表級的auto_inc鎖，在語句結束的時候才釋放這把鎖。注意呀，這裡說的是語句級而不是事務級的，一個事務可能包涵有一個或多個語句。

   它能保證值分配的可預見性，與連續性，可重複性，這個也就保證了insert語句在複製到slave的時候還能產生和master那邊一樣的值（它保證了基於語句複製的安全）。由於在這種模式下auto_inc鎖一直要保持到語句的結束，所以這個就影響到了並發的插入。

1：這個表示consecutive（連續）

   這一模式下對simple insert做了最佳化，由於simple insert一次性插入值的個數可以立馬得到確定，所以MySQL可以一次產生幾個連續的值，用於這個insert語句；總的來說這個對複製也是安全的（它保證了基於語句複製的安全）。由於現在MySQL已經推薦把二進位的格式設定成ROW格式，所以沒有複製安全問題了。

這一模式也是MySQL的預設模式，這個模式的好處是auto_inc鎖不要一直保持到語句的結束，只要語句得到了相應的值後就可以提前釋放鎖。

2：這個表示interleaved（交錯）

   由於這個模式下已經沒有了auto_inc鎖，所以這個模式下的效能是最好的；但是它也有一個問題，就是對於同一個語句來說它所得到的auto_incremant值可能不是連續的。

注意：在MySQL8.0版本中已經將innodb_autoinc_lock_mode該參數的預設值改為2！！！！！

mysql8.0版本修複自增列值重複利用情況（修複bug#199）