• <button id="eiyoe"><acronym id="eiyoe"></acronym></button>
    <em id="eiyoe"></em>

  • <rp id="eiyoe"><acronym id="eiyoe"><input id="eiyoe"></input></acronym></rp>
      查看: 128|回復: 0
      上一主題 下一主題

      詳解MySQL自增主鍵的實現

      79910

      主題

      0

      好友

      積分

      離線 發信

      跳轉到指定樓層
      樓主
      發表于 2021-09-16 12:21 | 只看該作者 | 倒序瀏覽
      目錄
      • 一、自增值保存在哪兒?
      • 二、自增值修改機制
      • 三、自增值的修改時機
      • 四、自增鎖的優化
      • 五、自增主鍵用完了

      一、自增值保存在哪兒?

      不同的引擎對于自增值的保存策略不同

      1.MyISAM引擎的自增值保存在數據文件中

      2.InnoDB引擎的自增值,在MySQL5.7及之前的版本,自增值保存在內存里,并沒有持久化。每次重啟后,第一次打開表的時候,都會去找自增值的最大值max(id),然后將max(id)+步長作為這個表當前的自增值

      select max(ai_col) from table_name for update;

      在MySQL8.0版本,將自增值的變更記錄在了redo log中,重啟的時候依靠redo log恢復重啟之前的值

      二、自增值修改機制

      如果字段id被定義為AUTO_INCREMENT,在插入一行數據的時候,自增值的行為如下:

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

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

      假設,某次要插入的值是X,當前的自增值是Y

      1.如果X<Y,那么這個表的自增值不變

      2.如果X>=Y,就需要把當前自增值修改為新的自增值

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

      三、自增值的修改時機

      創建一個表t,其中id是自增主鍵字段、c是唯一索引,建表語句如下:

      CREATE TABLE `t` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `c` int(11) DEFAULT NULL,
        `d` int(11) DEFAULT NULL,
        PRIMARY KEY (`id`),
        UNIQUE KEY `c` (`c`)
      ) ENGINE=InnoDB;
      

      假設,表t里面已經有了(1,1,1)這條記錄,這時再執行一條插入數據命令:

      insert into t values(null, 1, 1); 
      

      執行流程如下:

      1.執行器調用InnoDB引擎接口寫入一行,傳入的這一行的值是(0,1,1)

      2.InnoDB發現用于沒有指定自增id的值,獲取表t當前的自增值2

      3.將傳入的行的值改成(2,1,1)

      4.將表的自增值改成3

      5.繼續執行插入數據操作,由于已經存在c=1的記錄,所以報Duplicate key error(唯一鍵沖突),語句返回

      對應的執行流程圖如下:

      在這里插入圖片描述

      在這之后,再插入新的數據行時,拿到的自增id就是3。出現了自增主鍵不連續的情況

      唯一鍵沖突和事務回滾都會導致自增主鍵id不連續的情況

      四、自增鎖的優化

      自增id鎖并不是一個事務鎖,而是每次申請完就馬上釋放,以便允許別的事務再申請

      但在MySQL5.0版本的時候,自增鎖的范圍是語句級別。也就是說,如果一個語句申請了一個表自增鎖,這個鎖會等語句執行結束以后才釋放

      MySQL5.1.22版本引入了一個新策略,新增參數innodb_autoinc_lock_mode,默認值是1

      1.這個參數設置為0,表示采用之前MySQL5.0版本的策略,即語句執行結束后才釋放鎖

      2.這個參數設置為1

      • 普通insert語句,自增鎖在申請之后就馬上釋放
      • 類似insert … select這樣的批量插入數據的語句,自增鎖還是要等語句結束后才被釋放

      3.這個參數設置為2,所有的申請自增主鍵的動作都是申請后就釋放鎖

      為了數據的一致性,默認設置為1

      在這里插入圖片描述

      如果sessionB申請了自增值以后馬上就釋放自增鎖,那么就可能出現這樣的情況:

      • sessionB先插入了兩行數據(1,1,1)、(2,2,2)
      • sessionA來申請自增id得到id=3,插入了(3,5,5)
      • 之后,sessionB繼續執行,插入兩條記錄(4,3,3)、(5,4,4)

      當binlog_format=statement的時候,兩個session是同時執行插入數據命令的,所以binlog里面對表t2的更新日志只有兩種情況:要么先記sessionA的,要么先記錄sessionB的。無論是哪一種,這個binlog拿到從庫執行,或者用來恢復臨時實例,備庫和臨時實例里面,sessionB這個語句執行出來,生成的結果里面,id都是連續的。這時,這個庫就發生了數據不一致

      解決這個問題的思路:

      1)讓原庫的批量插入數據語句,固定生成連續的id值。所以,自增鎖直到語句執行結束才釋放,就是為了達到這個目的

      2)在binlog里面把插入數據的操作都如實記錄進來,到備庫執行的時候,不再依賴于自增主鍵去生成。也就是把innodb_autoinc_lock_mode設置為2,同時binlog_format設置為row

      如果有批量插入數據(insert … select、replace … select和load data)的場景時,從并發插入數據性能的角度考慮,建議把innodb_autoinc_lock_mode設置為2,同時binlog_format設置為row,這樣做既能并發性,又不會出現數據一致性的問題

      對于批量插入數據的語句,MySQL有一個批量申請自增id的策略:

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

      2.1個用完以后,這個語句第二次申請自增id,會分配2個

      3.2個用完以后,還是這個語句,第三次申請自增id,會分配4個

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

      insert into t values(null, 1,1);
      insert into t values(null, 2,2);
      insert into t values(null, 3,3);
      insert into t values(null, 4,4);
      create table t2 like t;
      insert into t2(c,d) select c,d from t;
      insert into t2 values(null, 5,5);
      

      insert … select,實際上往表t2中插入了4行數據。但是,這四行數據是分三次申請的自增id,第一次申請到了id=1,第二次被分配了id=2和id=3,第三次被分配到id=4到id=7

      由于這條語句實際上只用上了4個id,所以id=5到id=7就被浪費掉了。之后,再執行insert into t2 values(null, 5,5),實際上插入了的數據就是(8,5,5)

      這是主鍵id出現自增id不連續的第三種原因

      五、自增主鍵用完了

      自增主鍵字段在達到定義類型上限后,再插入一行記錄,則會報主鍵沖突的錯誤

      以無符號整型(4個字節,上限就是 2 32 − 1 2^{32}-1 232−1)為例,通過下面這個語句序列驗證一下:

      CREATE TABLE t ( id INT UNSIGNED auto_increment PRIMARY KEY ) auto_increment = 4294967295;
      INSERT INTO t VALUES(NULL);
      INSERT INTO t VALUES(NULL);
      

      第一個insert語句插入數據成功后,這個表的AUTO_INCREMENT沒有改變(還是4294967295),就導致了第二個insert語句又拿到相同的自增id值,再試圖執行插入語句,報主鍵沖突錯誤

      推薦資料

      https://time.geekbang.org/column/article/80531

      到此這篇關于詳解MySQL自增主鍵的實現的文章就介紹到這了,更多相關MySQL自增主鍵內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家!

      來源:http://www.jb51.net/article/221895.htm