MySQL系列-- 1.MySQL架構

王亦信Ashin發表於2017-10-22
原文網址 : https://juejin.im/post/59ec528bf265da43333d8bb0
MySql架構

MySQL架構

[TOC]

1. 邏輯架構

MySQL邏輯架構圖
MySQL邏輯架構圖

  • 第一層為客戶端的連線認證，C/S都有此架構
  • 第二層為伺服器層，包含MySQL的大多數核心服務功能
  • 第三層包含了儲存引擎，伺服器通過API與其通訊，API規避了不同儲存引擎的差異，不同儲存引擎也不會互相通訊，另外儲存引擎不會去解析SQL(InnoDB是例外，它會解析外來鍵定義，因為伺服器本身沒有實現該功能)

1.1 連線管理及安全性

  • 每個客戶端在伺服器程式中擁有一個執行緒

  • 伺服器會負責快取執行緒，不需要為每一個新建的連線建立或銷燬執行緒(5.5以後版本提供了執行緒池，可使用少量執行緒來服務大量連線)

  • 伺服器基於使用者名稱、原始主機資訊和密碼對客戶端進行認證，連線成功後會驗證某個特定操作的許可權。

    ​

1.2 優化和執行

  • MySQL會解析查詢，建立內部資料結構(解析樹)，並對其進行各種優化(重寫查詢、決定表的讀取順序、選擇適合的索引)
  • 使用者可以通過特殊的關鍵字提示(hint)優化器，影響MySQL的決策過程。也可以請求優化器解釋(explain)優化過程的各個因素，便於使用者重構查詢和schema，修改相關配置
  • 優化器不關心表使用的儲存引擎，但是儲存引擎對優化查詢有影響。優化器會請求儲存引擎提供容量或某個具體操作的開銷資訊，已經表資料的統計資訊等。
  • 對於SELECT語句，在解析查詢前，伺服器會先檢查查詢快取(Query Cache)。

2. 併發控制

兩個層面的併發控制: 伺服器層和儲存引擎層

2.1 讀寫鎖

  • 共享鎖(shared lock), 讀鎖(read lock)：共享，相互不阻塞
  • 排他鎖(exclusive lock), 寫鎖(write lock)：排他(會阻塞其它的讀寫鎖)

2.2 鎖粒度

  • 一種提供共享資源的併發性：讓鎖定物件更有選擇性。儘量只鎖定需要修改的部分資料\資料片，鎖定的資料量越少，併發程度越高。
  • 鎖的各種操作(獲得鎖，檢查鎖是否解除，釋放鎖)需要消耗資源。
  • 鎖策略:
    • 表鎖(table lock)：
      • 最基本、開銷最小的策略。伺服器層實現
      • 鎖定整張表。寫鎖阻塞其它鎖，讀鎖互不阻塞。
      • 特定場景下，表鎖也可能有良好的效能。如READ LOCAL表鎖支援某些型別的併發寫操作。寫鎖比讀鎖有更高優先順序，可能會被插入到讀鎖佇列的前面。
      • 儲存引擎可以管理自己的鎖，伺服器層還是會使用各種有效的表鎖去實現不同的目的。如，伺服器會為ALTER TABLE等語句使用表鎖，而忽略儲存引擎的鎖機制。
    • 行級鎖(row lock):
      • 最大程度地支援併發處理，也帶來了最大的鎖開銷。
      • 只在儲存引擎實現(InnnoDB和XtraDB等)

3. 事務

  • 事務是一組原子性的SQL查詢，或者說一個獨立的工作單元。
  • 事務的ACID:
    • 原子性(atomicty)：一個不可分割的最小工作單元。
    • 一致性(consistency)：資料庫總是從一個一致性狀態轉換到另一個一致性的狀態。
    • 隔離性(isolation)：一個事務所做的修改在最終提交以前，對其他事務是不可見的。
    • 永續性(durability)：事務提交後，其所做的修改會永久儲存到資料庫中。(沒有100%的永續性永續性保證策略)

3.1 隔離線的隔離級別

SQL標準中的四種隔離級別:

  • READ UNCOMMITED(未提交讀)：
    • 事務中的修改，即使沒有提交，對其它事務也是可見的。
    • 事務讀取未提交的資料，稱為髒讀(Dirty READ)。
    • 效能並不比其它級別好太多，但確少了很多好處，不推薦。
  • READ COMMITED(提交讀)、不可重複讀(nonrepeatable read)：
    • 大多數資料庫的預設級別，但MySQL不是。
    • 一個事務只能讀取已提交的事務所做的修改，換句話說，一個事務從開始直到提交之前，所做的任何修改對其它事務都是不可見的。
    • 可能會導致虛讀，如事務A兩次讀取資料，事務B在這之間修改了資料，這兩次讀取會有不一樣的結果，即可讀取其它事務的增刪改
  • REPEATABLE READ(可重複讀)：
    • MySQL預設級別。
    • 解決髒讀問題，保證在同一個事務中多次讀取同樣記錄的結果是一致的。
    • 可能會導致幻讀(Phantom Read)，事務A讀取並修改資料，事務B也在該範圍修改了資料(插入或刪除)，事務A再次讀取該範圍的資料，發現了幻行(Phantom Row)，即可讀取其它事務的增刪
  • SERIALIZABLE(可序列化)：
    • 最高階別的隔離級別，強制事務序列執行。
    • 在讀取的每一行資料上都加鎖，導致大量的超時和鎖爭用問題。
隔離級別 髒讀可能性 不可重複讀可能性 幻讀可能性 加鎖讀
未提交讀 Yes Yes Yes No
提交讀 No Yes Yes No
可重複讀 No No Yes No
可序列化 No No No Yes

3.2 死鎖

  • 死鎖是指兩個或多個事務在同一個資源上相互佔用，並請求鎖定對方佔用的資源，從而導致惡性迴圈的現象。
  • 產生原因：
    • 真正的資料衝突
    • 儲存引擎的實現方式
  • 解決方案(部分或完全回滾其中一個事務)：
    • 死鎖檢測。InnoDB處理死鎖的方法：將持有最少行級的排它鎖的事務進行回滾(比較簡單的回滾演算法)
    • 死鎖超時機制，超時後放棄對鎖的請求

3.3 事務日誌(預寫式日誌，Write-Ahead Logging)

  • 使用事務日誌，儲存引擎修改表資料，只需修改其記憶體拷貝，再將該行為記錄到持久在硬碟的事務日誌中。
  • 大多數儲存引擎的實現方案，修改資料需寫兩次磁碟(第一次為日誌記錄，第二次為資料)
  • 優點：
    • 提高事務的效率
    • 速度快。採用追加方式，寫日誌的操作是磁碟一小塊區域的順序IO，而不是多區域的隨機IO。

3.4 MySQL中的事務

事務型的儲存引擎：MySQL的如InnoDB和NDB Cluster，第三方的如XtraDB和PBXT。

  • 自動提交(AUTOCOMMIT):

    • MySQL的預設模式。如不顯示地開始一個事務，每個查詢都被當作一個事務執行提交操作。

    • 在當前連線可以通過設定AUTOCOMMIT變數來啟用或禁用自動提交模式。當禁用時，所有的查詢都是在一個事務中，直到顯式地執行COMMIT或ROLLBACK，該事務結束後又開始另一個新的事務。

      mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
      mysql> SET AUTOCOMMIT = 1;複製程式碼

    • 對非事務型地表，如MyISAM或者記憶體表，不會有任何影響。因為這類表沒有COMMIT或ROLLBACK的概念，也就是說一直處於AUTOCOMMIT模式。

    • 在資料定義語言(DDL)中，如ALTER TABLE、LOCK TABLES等導致大量資料改變的操作，會在執行之前強制COMMIT提交當前事務。

      SQL型別
      SQL型別

    • MySQL可以在當前會話設定隔離級別，新的隔離級別會在下一個事務開始的事後生效。

      mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;複製程式碼

    • MySQL能夠識別所有的4個ANSI隔離級別，InnoDB引擎支援所有的隔離級別。

  • 在事務中混合使用儲存引擎

    • MySQL伺服器層不管理事務，事務是由下層的儲存引擎實現的。所以在同一個事務，使用多種儲存引擎不可靠。
    • 如在事務中混合使用儲存引擎，如InnoDB和MyISAM，正常情況下不會有問題，但需要回滾時，非事務型的表上的變更無法撤銷，導致資料庫處於不一致的狀態，而且MySQL對非事務型的表操作不會有提示。
    • 總結：最好不要在一個事務中混合使用儲存引擎

  • 隱式和顯式鎖定

    • 隱式鎖定：

      • InnoDB採用兩階段鎖定協議(two-phase locking protocol)。事務執行過程中，隨時都可以執行鎖定，鎖只在COMMIT或ROLLBACK時被釋放，並且所有的鎖都在同一時間釋放。InnoDB會根據隔離級別在需要的時候自動加鎖。

    • 顯式鎖定：

      • MySQL可通過特定語句進行顯式鎖定，這些語句不屬於SQL規範，儘量避免使用

        SELECT ... LOCK IN SHARE MODE
        SELECT ... FOR UPDATE複製程式碼

    • 其它鎖定

      • MySQL支援LOCK TABLES和UNLOCK TABLES語句，這是在伺服器層實現的，和儲存引擎無關。並不能代替事務處理。在InnoDB使用，會嚴重影響效能，效果沒有它的行級鎖好
      • 建議：除了事務中禁用AUTOCOMMIT，可以使用LOCK TABLES之外，其餘任何使用都不要顯式地執行，不管使用地是什麼儲存引擎。

4. 多版本併發控制(MVVC)

MySQL的大多數儲存引擎，Oracle以及PostgreSQL都實現MVVC，但實現的機制不盡相同，典型的有樂觀併發控制和悲觀併發控制。

  • 目的：

    • 鄙棄簡單地行級鎖，可視為行級鎖的變種，提升併發效能。
    • 在很多情況下避免了加鎖操作，開銷更低。
    • 實現了非阻塞的讀操作，寫操作也只鎖定必要的行

  • 實現原理：

    • 儲存資料在某個時間點的快照來實現。
    • 不管執行多長時間，每一個事務看到的資料都是一致的。
    • 根據事務開始時間的不同，每一個事務對同一張表，同一個時刻看到的資料可能是不一樣的。

  • InnoDB簡化版的MVVC工作原理:

    ​ 通過在每行記錄後面儲存兩個隱藏的列，一列儲存了行的建立時間，一個儲存行的過期時間。儲存的並不是實際的時間值，而是系統版本號。每一個新的事務，系統版本號都會自動遞增。事務開始時刻的系統版本號會作為事務的版本號，用來和查詢到的每行記錄的版本號進行比較。

    ​ 只在REPEATABLE READ和READ COMMITED兩個隔離級別下工作。

    ​ 優點：大多數讀操作不用加鎖，讀操作簡單，效能很好，也保證只會讀到符合標準的行。

    ​ 缺點：每行記錄都需要額外的儲存空間，需要更多的行檢查及額外的維護工作。

    在REPETABLE READ隔離級別下，MVVC的工作：

    • SELECT：

      根據以下兩個條件檢查每行記錄，符合的記錄才能返回查詢結果

      • 只查詢版本號小於等於當前事務版本號的資料行。確保資料行是在事務開始前已經存在的，或者是事務本身插入或者修改過的。
      • 行的刪除版本號要麼未定義，要麼大於當前事務版本號。確保資料行在事務讀取前未被刪除。

    • INSERT:

      為新插入的每一資料行儲存當前系統版本號為行版本號

    • DELETE

      為刪除的每一行儲存當前系統版本號作為行刪除標識。

    • UPDATE:

      插入一條新的記錄，儲存當前系統版本號為行版本號，儲存當前系統版本號到原來的行作為行刪除標識。

5. MySQL儲存引擎

​ 在檔案系統中，MySQL將每個資料庫(schemaa)儲存為資料目錄下的一個子目錄，將資料庫的每個表在該子目錄下建立一個和表同名的.frm檔案儲存表的定義。

-- 表路徑：/MySQL_data/MyDB/Mytalbe.frm
mysql> SHOW TABLE STATUS LIKE 'MyTABLE'\G;複製程式碼

5.1 InnoDB儲存引擎

MySQL預設事務型儲存引擎，擁有良好的效能和自動崩潰恢復特性。

  • 設計目的：處理大量的短期(short-lived)事務(短期事務大部分情況是正常提交的，很少被回滾)
  • 概覽：
    • 資料儲存在表空間(tablespace)中，由InnoDB管理的黑盒子，有一系列的資料檔案組成。
    • 採用MVVC支援高併發，實現四個標準的隔離級別，預設為REPEATABLE READ，並且通過間隙鎖(next-key locking)策略使得InnoDB鎖定查詢涉及的行，還會對索引中的間隙進行鎖定，防止幻讀出現。
    • 基於聚簇索引建立，對主鍵查詢有很高的效能。但是二級索引(secondary index，非主鍵索引)必須包含主鍵列，如主鍵索引過大，其它的所有索引都會很大。
    • 從磁碟讀取資料採用可預測性預讀、自動在記憶體中建立hash索引以加速讀操作的自適應索引(adaptive hash index)、加速插入操作的插入緩衝區(insert buffer)
    • 通過一些機制和工具支援真正的熱備份
    • 建議：有空閱讀官方手冊中的"InnoDB事務模型和鎖"一節

5.2 MyISAM儲存引擎

​ MySQL5.1及之前版本的預設儲存引擎。支援全文索引、壓縮、空間函式(GIS)，不支援事務和行級鎖，並且崩潰後無法安全恢復。對於只讀資料，或者表比較小，可以忍受修復(repair)操作，可以考慮MyISAM。

  • 儲存：表以.MYD和.MYI的資料檔案和索引檔案儲存在檔案系統。
  • 特性：
    • 加鎖與併發：對整張表而不是特定行加鎖。讀取時對讀到的表加共享鎖，寫入時則加排它鎖。支援併發插入(CONCURRENT INSERT)，在讀取查詢的同時，也可以插入新的資料。
    • 修復：與事務恢復以及崩潰恢復是不同的概念。速度慢，可能會導致資料丟失。通過CHECK TABLE mytable 檢查表的錯誤，REPAIR TABLE mytable 進行修復。
    • 索引特性：支援全文索引，這是基於分詞建立的索引。即使是BOLB和TEXT等長欄位，也可以基於前500個字元建立索引。
    • 延遲更新索引鍵(Delayed Key Write)：如指定了DELAY_KEY_WRITE，每次修改執行完成時，不會將修改的索引資料寫入磁碟而是寫到記憶體中的鍵快取區(in-memory key buffer)，只有在清理鍵快取區或關閉表的時候才會寫入磁碟。可極大提升寫入效能，但可能在資料庫或主機崩潰時造成索引損壞而執行修復操作。
  • 壓縮表：只進行讀操作可採用壓縮表，極大減少磁碟佔用空間以及IO，從而提升查詢效能。
  • 效能：設計簡單，資料以緊密格式儲存，在某些場景下的效能很好。最典型的效能問題為表鎖。

5.3 MySQL內建的其它儲存引擎

  • Archive引擎：非事務型對告訴插入和壓縮做優化的引擎。支援INSERT、SELECT和索引，每次SELECT都需要全表掃描，並阻止其它SELECT執行，以實現一致性讀；支援行級鎖和專用緩衝區，實現高併發插入。適合日誌和資料採集類應用。
  • Blackhole引擎：沒有實現任何的儲存機制，因為它丟棄所有插入的資料，不做儲存，但是伺服器會記錄Blackhole表的日誌，所以可以用於複製資料到備庫，或者簡單地記錄到日誌。適合特殊的複製架構和日誌稽核，但並不推薦。
  • CSV引擎：將CSV檔案作為MySQL表來處理，但不支援索引。適合作為一種資料交換的機制。
  • Federated引擎：訪問其它MySQL伺服器的代理，建立一個遠端MySQL的客戶端連線，將查詢傳到遠端伺服器執行，然後提取或傳送需要的資料。
  • Memory引擎：
    • 所有資料儲存在記憶體中，不需要磁碟IO，比MyISAM快一個數量級。
    • 支援Hash索引，但是是表級鎖，因此併發寫入效能低
    • 不支援BOLB和TEXT的列，並且每行的長度是固定的。
    • 適合快速地訪問資料，並且這些資料不會修改，重啟以後丟失也沒關係(資料會丟失，表結構仍保留)。
    • 應用場景：
      • 用於查詢(lookup)或者對映(mapping)表
      • 用於快取週期性聚合資料(periodically aggregated data)的結果
      • 用於儲存資料分析中產生的中間資料
    • MySQL執行查詢中需要使用臨時表來儲存中間結果，使用的就是Memory表，如果資料太大或者含有BLOB或TEXT欄位，則使用MyISAM表。
    • Memory表並不是CREATE TEMPORARY mytable 建立的表，後者可以使用任何的儲存引擎。
  • Merge引擎：棄用
  • NDB叢集引擎：參加MySQL叢集

5.4 選擇合適的儲存引擎

  • 除非需要用到某些InnoDB不具備的特性，並且沒有其它辦法可以代替，否則都應該優先選擇InnoDB引擎。
  • 不要混合使用多種儲存引擎。
  • 考慮因素：
    • 事務
    • 備份
    • 崩潰恢復
    • 特有的特性
  • 應用場景：
    • 日誌型應用：
      • 使用MyISAM或Archive儲存引擎
      • 採用主備架構，用於讀寫分離
      • 日誌記錄表的明顯包含日期資訊

5.5 轉換表的引擎

轉換引擎會失去與原引擎相關的特性。

  • ALTER TABLE

    mysql> ALTER TABLE mytable ENGINE = InnoDB;

    需執行很長實際，資料庫將資料從原表複製到一個新表，會消耗掉系統所有的IO能力。

  • 匯出和匯入

    使用mysqldump，修改其中的CREATE TABLE語句。

  • 建立和查詢(CREATE和SELECT)

    mysql> CREATE TABLE innodb_table LIKE myisam_table;
    mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
    mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
    -- 如資料量過大，可分批處理。
    mysql> START TRANSACTION;
    mysql> INSERT INTO innodb_table SELECT * FROM myisam_table where id BETWEEN x AND y;
    mysql> COMMIT;
    -- 如需要可對原表加鎖，保證資料一致性。複製程式碼

    ​

相關文章