#wmd-preview h1 {
color: #0077bb; /* 將標題改為藍色 */
}

mysql基礎概念筆記     part1

mysql 基礎概念 基礎原理，邏輯架構，事務，并發控制，讀寫鎖

1、前言

    作為一個運維工程師，mysql怎么強調其重要性都不為過，因此mysql的基礎概念應該好好地梳理一下。 畢竟基礎不牢地動山搖。

1.1mysql簡介

 1、MySQL是一個關系型數據庫管理系統，由瑞典MySQL AB 公司開發，目前屬于 Oracle（被收購） 
 2、MySQL是目前最流行的關系型數據庫管理系統，在 WEB 應用方面MySQL是最好的 RDBMS (Relational DatabaseManagement System，關系數據庫管理系統) 應用軟件之一。
 3、 MySQL是一種關聯數據庫管理系統，關聯數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度并提高了靈活性。
 4、MySQL所使用的 SQL 語言是用于訪問數據庫的最常用標準化語言。                                   5、MySQL 軟件采用了雙授權政策：它分為社區版和商業版。由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇MySQL作為網站數據庫。由于其社區版的性能卓越，搭配 PHP 和 Apache（nginx） 可組成良好的開發環境

2、mysql簡單歷史

簡單來說，MySQL數據庫的發展可以概括為三個階段：

1、初期開源數據庫階段。

2、Sun MySQL階段。

3、Oracle MySQL階段。

2.1mysql與MariaDB

mysql與MariaDB的區別簡而言之就是：
    （1）因為mysql被sun公司給收購了，而sun公司又被oracle公司給收購了，而oracle是一個商業數據庫公司，創始人害怕開源的mysql變成不開源，因此用mysql的源代碼創立了MariaDB。
    （2）MariaDB名字來源于創始人的女兒。
    （3）centos7yum源中MariaDB已經替代了mysql

3、mysql邏輯架構

下圖展示了mysql的邏輯架構圖：

（1）最上層的一層結構是客戶端，這并非mysql所獨有，大多數基于網絡的客戶端/服務器的工具或者服務都有類似的架構。如授權認證，鏈接處理，安全等等。
         （2）第二層架構包含了大多數mysql的核心服務功能，
         其中包括查詢、解析、分析、優化、緩存以及所有內置函數，
         所有跨數據引擎的功能都在這一層實現：存儲過程、觸發器、視圖等等。
         （3）第三層包含了存儲引擎。
         在mysql中存儲引擎負責數據的存儲和提取。
         每個存儲引擎都有其優勢和劣勢。
         服務器通過API與存儲引擎進行通信。
         這些接口屏蔽了不同存儲引擎之間的差異，使得這些差異對上層的查詢透明。
         存儲引擎API包含幾十個底層模型，
         用于執行諸如 “開始一個事務”或者“根據主鍵提取一行數據”等操作。
         但是存儲引擎不會去解析SQL，不同存儲引擎之間也不會相互通信，而只是簡單的響應上層服務器的請求

3.1連接管理與安全性

    (1)每個客戶端連接都會在服務器進程中擁有一個線程，
    這個連接的查詢只會在這個單獨的線程中執行，
    該線程只能輪流在某個CPU核心或者CPU運行。
    服務器會負責緩存線程，因此不需要為每一個新建的連接創建或者銷毀線程。
    (2)當客戶端連接mysql服務器時,服務器會對客戶端進行認證。
       該認證基于用戶名、密碼、原始主機信息。
       一旦客戶端連接成功，服務器會繼續驗證客戶端是否有執行某些操作的權限。

3.2優化與執行
     (1)mysql會解析查詢，并創建內部數據結構（解析樹）然后對其進行各種優化。
     (2)這些優化包括：重寫查詢、決定表的讀取順序、選擇合適的索引。
     (3)優化器并不關心表使用的是什么存儲引擎，但是存儲引擎對優化查詢有影響
     (4)其中對于“SELECT”語句，在解析查詢時，會先檢查查詢緩存（Query Cache），
     如果能在其中找到結果，則服務器不執行整個查詢解析、優化、執行的過程。直接從緩存中返回數據。（cache is key）

4.0并發控制

無論何時，只要有多個查詢在同一時刻修改數據，都會產生并發控制的問題。
   當同一時刻兩個進程同時對一個文件進行操作，這時會發生什么情況？
     顯然文件數據會出錯。
     這時我們就需要通過鎖來防止數據被破壞。如果一個進程試圖對文件進行操作，但是這時已經又一個進程在對該文件進行操作了。
     這時該文件已經被鎖住，這時該進程若要對文件進行操作需等待解鎖。

4.1讀寫鎖

（1）有時候對一個文件進行并發讀取也沒有什么問題，因為讀取不會修改數據，所以不會出錯。  
 但是如果當一個文件正在被一個讀取時，另一個用戶試圖對該文件進行刪除操作，這時就可能會產生很多問題。
 而在mysql中也有類似問題，因為mysql支持多用戶并行處理。
 （2）解決這些問題的方法就是并發控制：在處理并發讀或者并發寫的的時候，可以通過實現一個有兩種類型的鎖組成的鎖系統來解決問題。
 （3）這些鎖通常被稱為共享鎖（shared lock）和排他鎖（exclusive lock）.
         這些鎖也可以稱為讀鎖（read lock）和寫鎖（write lock）   （4）讀鎖：讀鎖是共享的，也可以說是互相不阻塞，多個用戶在同一時刻可以同時讀取一個資源而互不干擾。
 （5）寫鎖：寫鎖是排他的，也就是說一個寫鎖會阻塞其他的寫鎖和讀鎖，這是基于安全才這樣考慮。
        這樣能確保在給定的時間里，只有一個用戶能執行寫入操作，并防止其他用戶讀取正在寫入的同一資源。  （6）在實際的數據庫系統中，每時每刻都在發生鎖定，當用戶修改某一個數據時，mysql會通過鎖防止其他用戶讀取同一數據。

4.2鎖粒度和鎖策略

(1)封鎖粒度，數據庫中為了實現并發控制而采用封鎖技術，封鎖對象的大小稱為封鎖粒度（Granularity）。
 (2)鎖定的粒度與系統的并發度和并發控制的開銷密切相關。一般地，鎖定的粒度越大，需要鎖定的對象就越少，可選擇性就越小，并發度就越小，開銷就越?。环粗i定的粒度越小，需要鎖定的對象就越多，可選擇性就越大，并發度就越大，開銷就越大。
 (3)一種可以提升共享資源并發性的方式就是讓鎖對象更有選擇性——即只鎖定需要修改的部分數據，而不是所有資源。
 (4)更理想的的方式是：只對會修改的數據片進行鎖定。
 (5)加鎖也需要消耗資源:鎖的各種操作都會增加系統開銷。如果數據庫系統花費大量的時間來管理鎖而不是，那么數據庫系統的性能可能受到影響。
 (6)所謂的鎖策略，就是在系統開銷和安全性上求平衡，但是這種平衡也會影響到性能。 (7)大多數商業數據庫并沒有提供更多的鎖策略供用戶選擇
 一般都是在表上施加行級鎖（row——level lock）,并以各種復雜的方式進行實現。    以便在鎖比較多的情況下。
 (8)而在mysql中提供了多種選擇,每一種Mysql存儲引擎都可以實現自己的鎖策略和鎖粒度。
     在存儲引擎的設計中，鎖管理是一個非常重要的決定。 (9)將鎖粒度固定在某一個級別，可以為某些特定應用提供更好地性能，但同時也會失去一些應用場景下的良好支持。
 (10)幸運的是Mysql提供了支持多個存儲引擎的架構，所以不需要單一的通用解決方案。以下是兩種最重要的鎖策略：
         表鎖（table lock）
         行級鎖（row lock）

4.2.1 表鎖

（1）表鎖是Mysql中最基本的鎖策略，并且是系統開銷最小的策略。
 （2）表鎖會鎖定整張表。一個用戶在對表進行寫入操作（插入、更新等）前，先需要獲得寫鎖。
     這時mysql將會阻塞其他用戶對該表的所有讀寫操作。 （3）只有當該用戶釋放寫鎖時，其他讀取的用戶才能獲得讀鎖，讀鎖之間是相互不阻塞的。
 （4）寫鎖有比讀鎖更高的優先級，因此一個寫鎖請求可能會被插入到讀鎖的隊列的前面。
     即：寫鎖可以插入到鎖隊列中讀鎖的前面，反之讀鎖不能插入到寫鎖的前面。

4.2.2行級鎖

（1）行級鎖可以最大程度的支持并發處理（同時也帶來了最大的的鎖開銷）
 （2）在innoDB和XtraDB中以及其他一些存儲引擎中實現了行級鎖。
 （3）行級鎖只在存儲引擎中實現，z在Mysql服務器層沒有實現。

5.0事務

（1）事務是一種原子性的SQL語句查詢，或者說一個獨立的工作單元。
 （2）事務確?！叭绻麛祿煲婺艹晒Φ膶祿鞈迷摻M查詢的全部語句，那么就執行該組查詢”
 反之“如果其中有任何一條語句因為崩潰或者其他原因無法執行，則所有的語句都不會執行”
 （3）總結為：事務內的語句要么全部執行成功，要不全部執行失敗。

5.1 事務的重要意義

以一個經典的例子來舉例事務的必要性：
     假設一個銀行的數據庫有兩張表：支票（checking）和儲蓄（savings）?，F在如果要從用戶Jane的支票賬戶轉移轉移200美元到她的儲蓄賬戶。
 那么至少需要以下3個步驟：
     （1）檢查支票賬戶的余額是否大于200美元。
     （2）如果為真則，從支票賬戶減去200美元。
     （3）在儲蓄賬戶余額中增加200美元。 顯而易見，這3步必須打包在一個事務中，任何一個步驟失敗，必須全部回滾。 假設，在執行到第3步時服務器崩潰了，這時會發生什么情況？——用戶很可能會損失200美元。又或者在執行第二步和第三步之間時，另外一個進程要刪除支票賬戶的所有余額，這時又會發生什么情況？——銀行很可能白白給用戶200美元。
 所以除非數據庫通過ACID測試，否則空談數據并沒有意義。

5.2ACID

ACID表示：原子性（atomicity）、一致性（consistency）、隔離性（Isolation）、持久性（durability）
     A：原子性（atomicity）整個事務中的所有操作要么全部成功執行，要么全部失敗后回滾；
     C：一致性（consistency）數據庫總是從一個一致性狀態轉換為另一個一致性狀態；
     I：隔離性（Isolation）一個事務所做出的操作在提交之前，是不能為其它所見；隔離有多種隔離級別；
     D：持久性（durability）一旦事務提交，其所做的修改會永久保存于數據庫中；
 （1）事務的ACID特性保證了銀行不會弄丟你的錢，但是要在應用邏輯中要實現這點非常難，一個兼容ACID的數據庫系統，想要做很多復雜但用戶可能并沒有察覺到的工作，才能保證ACID的實現。
 （2）就像鎖粒度的升級會增加系統的開銷一樣，一個實現了ACID的數據庫系統會比沒有的占用更多的系統資源。
 （3）mysql的優點可以根據不同的業務來選擇事務型的引擎或者非事務型引擎。

5.2事務隔離級別

在SQL標準當中定義了四種隔離級別：

READ UNCOMMITTED (讀未提交):zai在該級別，事務中的修改即使沒提交，對其他事務來說都是可見的。事務可以讀取未提交的數據，這也被稱為臟讀（Drity

Read）；除非真的非常有必要，在實際應用中很少使用。   READ COMMITTED
 (讀提交):在大多數數據庫系統的默認隔離級別都是該級別（Mysql不是）。該級別保證了“一個事務開始時，只能看見已經提交的事務所做的修改，換句話說，一個事務開始直到提交前，所做的任何修改對其他事務都是不可見的。但是該級別會產生不可重復讀即執行同樣的兩次查詢可能得到不同的結果”
   REPEATABLE READ
 (可重讀):該級別解決了臟讀問題，且保證了再同一個事務中多次讀取同樣的記錄的結果是一致的即解決了不可重復讀的問題。但是該隔離級別還無法解決幻讀的問題。
   幻讀指當某個事務在讀取某個范圍內的記錄時，另一個事務又在該范圍內插入了新的記錄，當之前的事務再次讀取該范圍的記錄時會產生幻行（Phantom
 Row）,InnoDB和XtraDB存儲引擎通過多版本并發控制（MVCC）解決了幻讀的問題。    該級別是Mysql的默認事務隔離級別。
   SERIALIZABILE
 (可串行化):該級別是最高的事務隔離級別。他通過強制事務串行執行避免了前面說的幻讀問題。簡單地說該級別會在讀取的每行數據上都加鎖，因此可能導致帶來的超時和鎖爭用的問題。
   實際應用中很少采用該級別，只有非常需要確保數據一致性且可以接受沒有并發的情況下可以使用。

5.2.1事務并發問題

臟讀（Dirty Read）：
     A 看到 B 進行中更新的數據，并以此為根據繼續執行相關的操作；B 回滾，導致 A 操作的是臟數據。

不可重復讀（Non-repeatable Read）：
A 先查詢一次數據，然后 B 更新之并提交，A 再次查詢，得到和上一次不同的查詢結果。

幻讀（Phantom Read）：
A 查詢一批數據，B 插入或刪除了某些記錄并提交，A 再次查詢，發現結果集中出現了上次沒有的記錄，或者上次有的記錄消失了。

第二類丟失更新 (覆蓋丟失)：
A 和 B 更新同一條記錄并提交，后提交的數據將覆蓋先提交的，通常這是沒問題的，但是在某些情況下，如在程序中自增自減、程序中的讀-改-全量更新，就會出現并發問題。這類問題更像是應用層面的，不屬于DB范疇。

5.2.2死鎖
 （1）死鎖是指兩個或者多個事務在同一資源上互相占用，且請求鎖定對方占用的資源，從而導致的惡性現象。
 （2）當多個事務試圖以不同的順序鎖定資源時，就會產生死鎖。 （3）多個事務同時鎖定同一個資源時，也會產生死鎖。
 （4）產生死鎖的后果就是：請求的資源會會被各個事務互相鎖定，然后所有事務又等待對方釋放鎖,同時又持有對方需要的鎖，這時就陷入了惡性循環了。除非有外部因素介入才可能解除死鎖。
 （5）為了解決該問題，數據庫系統實現了各種死鎖檢測機制和死鎖超時機制。越復雜的系統越能檢測到死鎖的循環依賴，并立即返回一個錯誤。
 還有另一種方式，就是當查詢的時間達到設定的鎖等待超時時間后放棄鎖請求，這種方法不太好
 （7）InnoDB目前處理死鎖的方法是：將持有最少行級排它鎖的事務進行回滾（這是相對比較簡單的事務回滾算法）
 （8）鎖的行為和順序是和存儲引擎相關的：
     1、以同樣的順序執行語句,有些引擎會產生死鎖，而有些不會。
     2、所以死鎖的產生有兩種原因：
         真正的數據沖突，這個很難避免
         存儲引擎的實現方式 （9）死鎖發生后，只有回滾部分或者完全的事務，才能打破死鎖。
     對于事務型的數據庫系統，這是無法避免的
     因此應用程序在設計時必須考慮如何處理死鎖
     大多數情況下只需要重新執行因死鎖回滾的事務即可。

5.3事務日志

（1）事務日志可以提高事務的執行效率。使用事務日志，存儲引擎在修改表的數據時就只需要xiugai修改其內存拷貝，再把該修改行為記錄到硬盤上的事務日志，而不用每次都把修改的數據本身持久到硬盤上。
 （2）事務日志的寫入類型為“追加”，因此其操作為“順序IO”，不需要像隨機I/O一樣需要在磁盤的多個地方移動磁頭，因此采用asw事務日志的方式相對快多了
 （3）此日志通常也被稱為“預寫式日志(write ahead logging)”
 （4）事務日志持久后，內存中修改的數據，可以在后臺慢慢刷回磁盤 （5）以上是大多數存儲引擎的實現方式。
 （6）如果數據的修改已經記錄到事務日志并持久化，但是數據本身還沒有寫回磁盤，這時服務器崩潰，存儲引擎在重啟時能夠自動恢復這部分修改的數據，具體的恢復方式視存儲引擎而定。

5.4中的事務

（1）MySQL提供了兩種事務型的存儲引擎：InnoDB和NDB cluster.
 （2）一些第三方存儲引擎也支持事務，比較知名的有XtraDB和PBXT.

5.4.1 AUTOCOMMIT

（1）MySQL默認采用AUTOCOMMIT模式。 （2）當不是顯式的開始一個事務，則每一個查詢都被當成一個事務執行提交操作。
 （3）你可以通過設置AUTOCOMMIT變量來開關該功能。

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

（1）MySQL服務器層不管理事務，事務是由下層的存儲引擎實現的。所以在同一個事務中，使用多種存儲引擎是不可靠的。
 （2）如果在事務中混合使用了事務型和非事務型的表，在正常提交的情況下不會有什么問題。
 （3）但是，一旦事務需要回滾，非事務型的表上的變更就無法撤銷，這會導致數據庫處于不一致的狀態，這樣非常難以修復，事務的最終執行結果將難以確定。
 （4）因此嚴禁混用存儲引擎。

5.4.3 顯式鎖定和隱式鎖定

（1）隱式鎖：當多個客戶端并發訪問同一個數據的時候，為了保證數據的一致性，數據庫管理系統會自動的為該數據加鎖、解鎖，這種被稱為隱式鎖。隱式鎖無需開發人員維護（包括鎖粒度、加鎖時機、解鎖時機等）

（2）顯式鎖：當在某些特殊的情況下需要開發人員手動的進行加鎖、解鎖，這種鎖方式被稱為顯式鎖。對于顯式鎖而言，開發人員不僅要確定鎖的粒度，還需要確定加鎖的時機（何時加鎖）、解鎖的時機（何時解鎖）以及所的類型。
 （3）InnoDB存儲引擎采用二階段加鎖協議（Two-PhaseLocking），事務中只加鎖不釋放，事務結束一起釋放。

5.4.4 MVCC 多版本并發控制

（1）MVCC（Multi-VersionConcurrencyControl，多版本并發控制）：
為一條記錄維護多個不同的snapshot，并記錄各snapshot對應的版本號（事務ID），每個事務可以讀到的snapshot是受限的，從而隔離其他事務的并發動作。
（2）MVCC并發控制中，讀操作分為兩類：
   快照讀 (snapshot read)與當前讀 (current read)。
   前者讀取的是記錄的snapshot(有可能是歷史版本)，不用加鎖；
   后者讀取的是記錄的最新版本，且會加上鎖，保證其他事務不會并發修改這條記錄。
（3）快照讀：
           普通的select均為快照讀，不用加鎖
    當前讀：
               select… lock in shared mode: 讀鎖
               select… for update: 寫鎖
               DML（insert/delete/update）：寫鎖
（4）MVCC 只工作在READ COMMITTED和 REPEATABLE READ兩個隔離級別下
   其他兩個隔離級別都不能實現MVCC。
   因為READ UNCOMMITTED總是讀取最新的數據行，而不是符合當前事務版本的行。
   因為SERIALIZABILE會對所有的讀取的行加鎖。