MySQL資料庫引擎、事務隔離級別、鎖

　MySQL資料庫引擎、事務隔離級別、鎖

• 資料庫引擎InnoDB和MyISAM有什麼區別

  • 大體區別為： MyISAM型別不支援事務處理等高階處理，而InnoDB型別支援。MyISAM型別的表強調的是效能，其執行效率比InnoDB型別更快，但是不支援事務，而InnoDB提供事務支援以及外來鍵等高階資料庫功能。

  • 具體實現的區別：

    1. InnoDB不支援FULLTEXT型別的索引

    2. InnoDB中不儲存表的具體行數，也就是說，執行查詢SQL時，InnoDB要掃描一遍整個表來計算有多少行，而MyISAM只要簡單的讀出儲存好的行數即可，但是當包含where條件時，兩種表的操作是一樣的

    3. 對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他的欄位建立聯合索引

    4. 執行刪除SQL時，InnoDB不會重新建立表，而是一行一行的刪除

    5. LOAD TABLE FROM MASTER操作對InnoDB是不起作用的。解決方法是先把InnoDB表改成MyISAM表，匯入資料後再改成InnoDB表，但是對於使用額外的InnoDB特性（例如外來鍵）的表不適用

  • 構成上的區別：

    1. 每個MyISAM在磁碟上儲存分為三個檔案。第一個檔案的名字以表的名字開始，副檔名指出檔案型別

       • .frm檔案儲存表定義

       • .MYD檔案為資料檔案

       • .MYI檔案為索引檔案

    2. 基於磁碟的資源是InnoDB表空間的資料檔案和它的日誌檔案, InnoDB的表大小隻受限於作業系統檔案的大小，一般為2G

    • 事物處理上的區別：

      1. InnoDB支援事物，MyISAM不支援事物。對於InnoDB每一條SQL語句都預設封裝成事物，自動提交，這樣會影響速度，所以最好把多條SQL語句放在begin和commit之間，組成一個事物。

      2. InnoDB支援外來鍵，而MyISAM不支援。對一個包含外來鍵的InnoDB錶轉換為MyISAM會失敗。

      3. InnoDB是聚集索引，資料檔案是和索引綁在一起的，必須有主鍵，通過主鍵索引效率很高。但是輔助索引需要兩次查詢，先查詢主鍵，然後在通過主鍵查詢到資料，因此，主鍵不應該過大，因為主鍵太大，其他索引也會很大。而MyISAM是非聚焦索引，資料檔案是分離的，索引儲存的是資料檔案的指標。主鍵索引和輔助索引是獨立的。

      4. InnoDB不儲存表的具體行數，執行select count(*) from table時需要全表掃描。而MyISAM用一個變數儲存了整個表的行數，執行上述語句時只需要讀出該變數即可，速度很快。

      5. InnoDB不支援全文索引，而MyISAM不支援全文索引，查詢效率上MyISAM要高

      6. MyISAM型別的表強調的是效能，其執行速度比InnoDB型別更快，但是不支援事物。InnoDB支援事物，外部鍵等高階資料庫功能

      7. 如果執行大量的查詢select操作，MyISAM是更好的選擇

      8. 如果執行大量的insert或者update操作，出於效能方面的考慮，應該使用InnoDB引擎

      9. 執行刪除資料操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，匯入資料執行操作後再改成InnoDB表，但是對於使用額外的InnoDB特性（如外來鍵）的表不適應

    • 對AUTO_INCREMENT的操作

      1. MyISAM為Insert和update操作自動更新，這使得AUTO_INCREMENT列更快（至少10%）。在序列頂的值被刪除之後就不能再利用。（當AUTO_INCREMENT列被定義為多列索引的最後一列，可以出現重複使用從序列頂部刪除的值的情況）

      2. AUTO_INCREMENT的值可以用ALTER或myisamch來重置

      3. 對於AUTO_INCREMENT型別的欄位，InnoDB中必須包含只有該欄位的索引，但是在MyISAM表中，可以和其他的欄位一起建立聯合索引

    • mysql中的鎖：

      1. 鎖是計算機協調多個程式或執行緒對某一資源併發訪問的機制。

      2. Mysql中的鎖分為表鎖和行鎖：顧名思義，表鎖就是鎖住一張表，而行鎖就是鎖住一行。

      3. 表鎖的特點：開銷小，不會產生死鎖，發生鎖衝突的概率高，並且併發度低。

         行鎖的特點：開銷大，會產生死鎖，發生鎖衝突的概率低，併發度高。

      4. 因此MyISAM引擎採用的是表鎖，而InnoDB儲存引擎採用的是行鎖。

  • 如何選擇資料庫引擎

    • 業務是否需要支援事物，如果需要選擇InnoDB，如果不需要可以考慮MyISAM

    • 如果表中絕大多數都只是查詢操作，可以考慮MyISAM，如果讀寫操作頻繁，則使用InnoDB

    • 需要考慮系統崩潰後，MyISAM恢復起來更困難，能否接受

    • MySQL5.5版本開始InnoDB已經成為MySQL的預設引擎（之前是MyISAM）

  • MySQL欄位寬度

    • MySQL型別關鍵字後面的括號內指定整數值的顯示寬度(例如，INT(11))。該可選顯示寬度規定用於顯示寬度小於指定的列寬度的值時從左側填滿寬度。顯示寬度並不限制可以在列內儲存的值的範圍，也不限制超過列的指定寬度的值的顯示。所以INT(1)和INT(11)預設是沒有任何區別的！！！

    • 當結合可選擴充套件屬性ZEROFILL使用時， 預設補充的空格用零代替。例如，對於宣告為INT(5) ZEROFILL的列，值4檢索為00004。 請注意如果在整數列儲存超過顯示寬度的一個值，當MySQL為複雜聯接生成臨時表時會遇到問題，因為在這些情況下MySQL相信資料適合原列寬度。

    • 所有整數型別可以有一個可選(非標準)屬性UNSIGNED。當你想要在列內只允許非負數和該列需要較大的上限數值範圍時可以使用無符號值 。 如果設定了ZEROFILL擴充套件屬性試，預設就有了無符號屬性(UNSIGNED)

    • 所以INT(1)與INT(11)後的括號中的字元表示顯示寬度，整數列的顯示寬度與MySQL需要用多少個字元來顯示該列數值，與該整數需要的儲存空間的大小都沒有關係，INT型別的欄位能儲存的資料上限還是2147483647(有符號型)和4294967295(無符號型)。其實當我們在選擇使用INT的型別的時候，不論是INT(1)還是INT(11)，它在資料庫裡面儲存的都是4個位元組的長度。

    • INT(M) ZEROFILL，加上ZEROFILL後M才表現出不同，比如 INT(3) ZEROFILL,你插入到資料庫裡的是10,則實際插入為010,也就是在前面補充加了一個0.如果INT(3)和INT(10)不加ZEROFILL,則它們沒有什麼區別．Ｍ不是用來限制INT列內儲存值的範圍的．int(M)的最大值和最小值與UNSIGNED有關。

  • 總體來說，兩種型別最主要的區別就是InnoDB支援事物處理與外來鍵和行級鎖。而MyISAM不支援。所以MyISAM往往會被認為只適合在小專案中使用，而如果就方便性和高擴充套件性來說，MyISAM絕對是首選。原因如下：

    1、平臺上承載的大部分專案是讀多寫少的專案，而MyISAM的讀效能是比Innodb強不少的。

  　　2、MyISAM的索引和資料是分開的，並且索引是有壓縮的，記憶體使用率就對應提高了不少。能載入更多索引，而Innodb是索引和資料是緊密捆綁的，沒有使用壓縮從而會造成Innodb比MyISAM體積龐大不小。

  　　3、經常隔1，2個月就會發生應用開發人員不小心update一個表where寫的範圍不對，導致這個表沒法正常用了，這個時候MyISAM的優越性就體現出來了，隨便從當天拷貝的壓縮包取出對應表的檔案，隨便放到一個資料庫目錄下，然後dump成sql再導回到主庫，並把對應的binlog補上。如果是Innodb，恐怕不可能有這麼快速度，別和我說讓Innodb定期用匯出xxx.sql機制備份，因為最小的一個資料庫例項的資料量基本都是幾十G大小。

  　　4、從接觸的應用邏輯來說，select count(*) 和order by 是最頻繁的，大概能佔了整個sql總語句的60%以上的操作，而這種操作Innodb其實也是會鎖表的，很多人以為Innodb是行級鎖，那個只是where對它主鍵是有效，非主鍵的都會鎖全表的。

  　　5、還有就是經常有很多應用部門需要我給他們定期某些表的資料，MyISAM的話很方便，只要發給他們對應那表的frm.MYD,MYI的檔案，讓他們自己在對應版本的資料庫啟動就行，而Innodb就需要匯出xxx.sql了，因為光給別人檔案，受字典資料檔案的影響，對方是無法使用的。

  　　6、如果和MyISAM比insert寫操作的話，Innodb還達不到MyISAM的寫效能，如果是針對基於索引的update操作，雖然MyISAM可能會遜色Innodb,但是那麼高併發的寫，從庫能否追的上也是一個問題，還不如通過多例項分庫分表架構來解決。

  　　7、如果是用MyISAM的話，merge引擎可以大大加快應用部門的開發速度，他們只要對這個merge表做一些select count(*)操作，非常適合大專案總量約幾億的rows某一型別(如日誌，調查統計)的業務表。

  　　8、 當然Innodb也不是絕對不用，用事務的專案就用Innodb的。另外，可能有人會說你MyISAM無法抗太多寫操作，但是可以通過架構來彌補。

• 事物有哪幾個特性：

  1. 原子性

  2. 一致性

  3. 隔離性

  4. 永續性

• InnoDB中的事務隔離級別和鎖的關係

  • 一次封鎖&&兩段鎖

    • 一次封鎖： 因為有大量的併發訪問，為了預防死鎖，一般應用中推薦使用一次封鎖法。就是在方法的開始階段，已經預先知道了會用到哪些資料，然後全部鎖住，在方法執行之後，再全部解鎖。這種方式可以有效的避免迴圈死鎖。但是這種方式在資料庫中卻並不適用，因為在事務開始階段，資料庫並不知道會用到哪些資料。

    • 兩段鎖

      資料庫遵循的是兩段鎖協議，將事務分成兩個階段，加鎖階段和解鎖階段

      雖然這種方式無法避免死鎖，但是兩段鎖協議可以保證事務的併發排程是序列化的（序列化很重要，尤其是在資料庫恢復和備份的時候）

      • 加鎖階段： 在該階段可以進行加鎖操作。在對任何資料進行讀操作之前要申請並獲得S鎖（共享鎖），其他事務可以繼續加共享鎖，但不能加排它鎖。在進行寫操作之前要申請並獲得X鎖（排它鎖），其他事務不能再獲得任何鎖。如果鎖不成功，則事務進入等待狀態，直到加鎖成才能繼續執行

      • 解鎖階段： 當事務釋放了一個封鎖之後，事務進入解鎖極端，在該階段只能進行解鎖不能再進行其他加鎖操作

  • 事務的四種隔離級別

    在資料庫操作中，為了有效保證併發讀取資料的正確性，提出的事務隔離級別。資料庫鎖也是為了構建這些級別存在的。

    • 未提交讀 ： 允許髒讀，也就是可能讀取到其他會話中未提交事務修改的資料

    • 提交讀 ： 只能讀取到已經提交的資料。Oracle等多數資料庫預設都是該級別

    • 可重複讀 ： 可重複讀取資料。在同一個事務內的查詢都是事務開始時保持一致的。在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻讀

    • 序列讀 ： 完全序列化的讀取資料。每次讀都需要獲得表級共享鎖，讀寫相互都會堵

  • MySQL中的鎖

    1. MySQL中鎖的種類有很多，有常見的表鎖和行鎖，也有新加入的Metadata Lock等等。

    2. 表鎖是對一整張表加鎖，雖然可分為讀鎖和寫鎖，但畢竟是鎖住整張表，會導致併發能力下降，一般是做DDL處理時使用

    3. 行鎖是鎖住資料行，這種加速方式比較複雜，但是由於只鎖住有限的資料，對於其他資料不加鎖，所以併發能力強，MySQL一般都是用行鎖來處理併發事務，行鎖可以防止不同事務版本額資料修改提交時造成的資料衝突的情況

    4. 在RC（Read Committed => 讀取提交內容）級別中，資料的讀取都是不加鎖的，但是資料的寫入、修改、刪除是需要加鎖的

    5. 由於MySQL的InnoDB預設是使用RR級別，所以需要先將該session開啟成RC級別，並且設定binlog的模式

    6. 如果一個條件無法通過索引快速過濾，儲存引擎層面就會將所有記錄加鎖後返回，再由MySQL Server層進行過濾

    7. 但在實際使用過程中，MySQL做了一些改進，在MySQL Server過濾條件，發現不滿足後，會呼叫unlock_row方法，把不滿足條件的記錄釋放鎖（違背了二段協議的約束）。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略。這種情況同樣適用於MySQL的預設隔離級別RR。所以對一個資料量很大的表做批量修改的時候，如果無法使用相應的索引，MySQL Server過濾資料的時候特別慢，就會出現雖然沒有修改某些行的資料，但是它們還是被鎖住了的現象。

  • 不可重讀和幻讀的區別：

    1. Repeattable Read（可重讀），這是MySQL中InnoDB預設的隔離級別。可重讀這個概念是一事務的多個例項在併發讀取資料時，會看到同樣的資料行。在MySQL的RR級別中，解決了幻讀的問題

    2. 不可重讀重點在於update和delete，而幻讀的重點在於insert

    3. 如果使用鎖機制來實現這兩種隔離級別，在可重複讀中，該sql第一次讀取到資料後，就將這些資料加鎖，其他事務無法讀取這些資料，就可以實現可重複讀了。但這種方法無法鎖住insert資料，所以當事務A先前讀取了資料或者修改了全部資料，事務B還是可以insert資料提交，這時事務A就會莫名其妙多了一條之前沒有的資料，這就是幻讀，不能通過行鎖來避免。需要Serializable隔離級別，讀用讀鎖，寫用寫鎖，讀鎖和寫鎖互斥，這麼做可以有效避免幻讀、不可重複讀、髒讀等問題，但是會極大的降低資料庫的併發能力。

    4. 不可重複讀和幻讀的最大區別，就在於如何通過鎖機制來解決他們產生的問題。可以使用悲觀鎖機制來處理這兩種問題，但是MySQL、Oracle、PostgreSQL等成熟的資料庫，出於效能考慮，都是使用了樂觀鎖為理論基礎的MVCC(多版本併發控制)來避免這兩種問題

  • 樂觀鎖和悲觀鎖

    • 悲觀鎖 ==> 它指的是對資料被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個資料處理過程中，將資料處於鎖定狀態

      1. 悲觀鎖的實現，往往依靠資料庫提供的鎖機制（也只有資料庫層提供的鎖機制才能真正保證資料訪問的排他性，否則，即使在本系統中實現了加鎖機制，也無法保證外部系統不會修改資料）。

      2. 在悲觀鎖的情況下，為了你保證事務的隔離性，就需要一致性鎖定讀。讀取資料時給加鎖，其它事務無法修改這些資料。修改資料時也要加鎖，其它事務無法讀取這些資料。

      3. 悲觀鎖大多數情況下依靠資料庫的鎖機制實現，以保證操作最大程度的獨立性。但隨之而來的就是資料庫效能的大量開銷，特別是對長事務而言，這樣的開銷往往無法承受

    • 樂觀鎖 ==> 相對於悲觀鎖，樂觀鎖機制採取了更加寬鬆的加鎖機制。樂觀鎖，大多數是基於資料版本（為資料增加一個版本標識）記錄機制實現，再基於資料庫表的版本解決方案中，一般是通過為資料庫表在哪個家一個version欄位來實現。讀取資料時，將此版本一同讀出，之後更新時，對此版本加一。此時，將提交資料的版本資料與資料庫表對應的當前版本資訊進行對比，如果提交資料的版本號大於資料庫表當前版本，則予以更新，否則認為是過期資料

  • MVCC在MySQL的InooDB的實現

    1. MVCC的實現沒有固定的規範，每個資料庫都會有不同的實現方式

    2. 在InnoDB中，會在每行資料後新增兩個額外的隱藏的值來實現MVCC，這兩個值一個記錄這行資料何時被建立，另外一個記錄這行資料何時過期（或者被刪除）。在實際操作中，儲存的並不是時間，而是事務的版本號，每次開啟一個新事務，事務的版本號就會遞增。在可重讀Repeatable reds事務隔離級別下：

       • select時，讀取建立版本號<=當前事務版本號，刪除版本號為空或>當前事務版本號。

       • insert時，儲存當前事務版本號為行的建立版本號

       • delete時，儲存當前事務版本號為行的刪除版本號

       • update時，插入一條新紀錄，儲存當前事務版本號為行建立版本號，同時儲存當前事務版本號到原來刪除的行

    3. 通過MVCC，雖然每行記錄都需要額外的儲存空間，更多的行檢查工作以及一些額外的維護工作，但可以減少鎖的使用，大多數操作都不用加鎖，讀取資料操作很簡單，效能很好，並且也能保證只會讀取到符合標準的行，也只鎖住必要行

  • MySQL中的“讀”與事務隔離級別中的“讀”的區別

    1. 在RR級別中，雖然讓資料變得可重複讀，但是我們讀到的資料可能是歷史資料，是不及時的資料，不是資料庫當前的資料！這在一些對於資料的時效特別敏感的業務中，就可能出現問題。對於這種讀取歷史資料的方式，叫做快照讀，而在讀取資料庫當前版本資料的方式，叫做當前讀。顯然，在MVCC中，快照讀就是select，當前讀是特殊的讀操作，insert/update/delete操作，屬於當前讀，處理的都是當前度的資料，需要加鎖

    2. 事務的隔離級別實際上都是定義了當前讀的級別，MySQL為了減少鎖的處理（包括等待其他鎖）的時間，提升併發能力，引入了快照讀的概念，使得select不用加鎖，而update、insert、delete這些“當前讀”，就需要另外的模組來解決

    3. 當前讀： 事務的隔離級別中雖然定義了讀資料的要求，實際上這也可以說是寫資料的要求。為了解決當前讀中的幻讀問題，MySQL事務使用了Next-Key鎖

    4. Next-key鎖是行鎖和GAP（間隙鎖）的合併，行鎖防止別的事務修改或刪除，GAP鎖防止別的事務新增，行鎖和GAP鎖結合形成的Next-Key鎖共同解決了RR級別在寫資料時的幻讀問題

    5. Serializable級別： 讀操作加共享鎖，寫操作加排他鎖，讀寫互斥。使用的悲觀鎖的理論，實現簡單，資料更加安全，但是併發能力非常差。如果業務併發的特別少或者沒有併發，同時資料及時可靠，可以使用這種模式，在Serializable這個級別，select還是會加鎖的