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還是會加鎖的