高性能MySQL學習總結一

一、MySQL邏輯架構

　　第一層的服務不是MySQL獨有的，大多數是基于網路的客戶端/服務端的工具，如連接處理、授權認證、安全等等，

　　第二層就是MySQL的核心功能，包括查詢決議、分析、優化、快取以及所有的內置函式，所有的跨存盤引擎的功能都在這一層實作：存盤程序、觸發器、視圖等，

　　第三層包含了存盤引擎，主要負責MySQL中資料的存盤和提取，

1.連接管理與安全

　　每個客戶端連接都會服務器行程中擁有一個執行緒，這個連接的查詢只會在這個單獨的執行緒中執行，該執行緒只能輪流在某個CPU核心或者CPU中運行，服務器會快取執行緒，所以不需要對每個連接新建或者銷毀一個執行緒（可以復用快取中的執行緒），

　　當客戶端連接到MySQL時服務器需要對其進行認證，如果認證成功，服務器會繼續驗證該客戶是否有執行某個待定查詢的權限，

2.優化與執行

　　MySQL會決議查詢，并且創建內部資料結構（決議樹），然后對其進行各種優化，包括重寫查詢SQL、決定表的讀取順序，以及選擇合適的索引等，

　　對于一個SELECT查詢陳述句來說：在決議查詢之前，服務器會先檢查查詢快取，如果能夠在其中找到對應的查詢，服務器會直接回傳結果，不需要再執行查詢決議、優化和執行整個程序，

二、并發控制

1.讀寫鎖

　　比如有一張表，多個執行緒對這張表進行操作，加入剛好有一個執行緒在對一條資料更改，另一個執行緒也對其進行讀取操作，那么進行讀操作的這個執行緒可能讀到的資料是不正確的，怎么解決這個問題呢？這時就用到了并發控制，主要是通過兩種鎖來控制的：讀鎖（共享鎖）、寫鎖（排他鎖）

　　讀鎖是共享的，或者說是相互不阻塞的，即對個執行緒讀取同一個資源，互不干擾；寫鎖是排他的，也就是說只要有一個執行緒對其進行寫操作，那么其他的讀和寫執行緒將都會被阻塞，只有這樣才能保證才能保證在給定的時間內只有一個執行緒在寫操作，并防止其他執行緒讀取正在寫入的資料，

2.鎖粒度

　　最理想的情況是：需要寫入哪個物件就鎖定哪個物件，盡量只鎖定需要修改的部分資料，而不是所有的資料，在任何時候，在給定的資源上，鎖定的資料越少，系統的并發程度越高，

　　但是還有一個問題，加鎖也會消耗資源，鎖的各種操作：獲得鎖、檢查鎖是否已經解除、釋放鎖等，都會增加系統的開銷，如果花費大量的時間來管理鎖，而不是存取資料，那么系統的性能將會受到影響，

　　所謂的鎖策略，就是在鎖的開銷和資料的完全性之間尋求平衡，

　　表鎖是MySQL中最基本的鎖策略，并且是開銷最小的策略（意味著并發程度低），但是表鎖的缺點是在執行插入、洗掉、更改的操作時會使其他的用戶執行緒阻塞，（注意：讀鎖之間是互不影響的），另外寫鎖比讀鎖的優先級更高，寫鎖可以插到讀鎖中，但是讀鎖卻不能插到寫鎖中，

　　行級鎖：可以最大程度的支持并發處理（但是同時也帶來了更大的鎖開銷）最典型的就是InnoDB存盤引擎，行級鎖只在存盤引擎層實作，

三、事務

　　最重要的事務終于到啦，事務即ACID（原子性、一致性、隔離性、持久性），在開始講這四個特性之前我們先舉一個最經典的例子：某賬戶存了1000元，現在用戶A要往里面存200元、用戶B要往出來取200元，

　　原子性：一個事務必須被視為不可分割的最小作業單元，整個事務的所有操作要么全部提交完成，要么全部失敗回滾，對于一個事務來說不可能執行其中的一部分，那上面例子來說：A要從賬戶取200需要執行以下四步操作：

1 START TRANSACTION;
2 SELECT  '金額'  FROM  '賬戶表'  WHERE   user_id = 1221221;
3 UPDATE  '賬戶表'  SET  '金額'  =  '金額'  - 200  WHERE user_id = 1221221;
4 COMMIT;

　　解釋：1.開啟事務 ，2.查詢賬戶有沒有200元 ，3.扣除金額  ， 4.提交事務，

　　原子性就是指這四步完整執行不可再分，

　　一致性：資料庫從一個一致性的狀態轉換為另一個一致性的狀態，就上面那個例子：如果在第三步執行完系統崩潰，賬戶中的金額還是1000元，因為一個事務沒有執行完，在最終提交前是不會保存到資料庫中的，

　　隔離性：通常來講一個事務在最終提交前，對其他事務是不可見的，就之前例子來說，第三步執行完，在沒提交之前B賬戶來看到的金額仍然是1000元，是不可見的，

　　持久性：事務一旦提交其所做的修改就會永遠的被保存到資料庫中，即使系統崩潰也不會丟失，

　　一個實作了事務的資料庫相比于沒有實作事務的資料庫它需要更強的CPU處理能力、更大的記憶體、更多的磁盤空間，對于有些不需要事務的查詢類應用選擇一個非事務型的存盤引擎可以獲得更高的性能，

1.隔離級別

一共有四種隔離級別先簡單做個介紹：

A.READ  UNCOMMIT（讀未提交）

　　在這個隔離級別中，事務的修改，即使沒有提交，對其他的事務也是可見的，事務可以讀到未提交的資料，這些資料也被稱為臟讀，

B.READ  COMMIT（讀已提交）（不可重復讀）

　　大多數資料庫系統的默認隔離級別都是讀已提交，但是MySQL不是，MySQL的默認隔離級別是可重復讀，簡單說就是一個事務開始時只能 “看見” 已經提交的事務所做的修改，換句話說，一個事務從開始到提交這個程序中所做的所有修改對其他事務是不可見的，這個級別對于兩次執行相同情況的查詢，可能會得到不同的結果，

C.REPEATABLE  READ（可重復讀）

　　該級別保證了在同一個事務中多次讀取同樣的記錄結果是一致的，但是它無法解決幻讀的問題，

D.SERIALIZABLE（可串行化）

　　是最高的隔離級別，它通過強制事務串行化執行，避免了所有問題，他會在讀取的每一行記錄上面都加鎖，所以可能導致大量的超時與鎖等待的問題，并發低，

講幾個概念：

1.臟讀：A用戶把錢取出沒有提交，B用戶讀取到修改了的金額為800，過一會A因為例外事務回滾，B再次讀取發現值變為1000，兩次不一樣，第一次讀到的是臟資料，

2.不可以重復讀：B讀到資料為1000后，A修改資料為800并且提交，B再次讀取為800，兩次資料不一致，

3.幻讀：與不可重復讀類似：B讀到一個表里有10條記錄，A插了一條并提交，B再次讀為11條，感覺發生幻覺一樣，

不同隔離級別引發的問題：

 2.死鎖

多個事務在同一資源上相互占有，并且請求鎖定對方占用的資源，從而導致惡性回圈的現象，

資料庫系統實作了各種死鎖檢測和死鎖超時機制，InnoDB目前處理死鎖的方法是：將持有最少行排它鎖的事務進行回滾，

3.事務日志

事務日志可以提高事務的效率，使用事務日志時：存盤引擎在修改表資料時只需要修改其記憶體拷貝，再把該修改行為記錄到持久在硬碟上的事務日志中，不用每次將修改的資料本身持久到硬碟，事務日志采用追加的方式，寫日志的操作是磁盤上的一小塊區域內的順序I/O.事務日志持久后，記憶體中被修改的資料將在后臺慢慢刷回到磁盤，所以一共需要兩次磁盤I/O:一次寫日志磁盤I/O,一次刷回磁盤I/O.

4.MySQL中的事務

1.自動提交：MySQL默認采用自動提交的方式，比如每個查詢就是一個事務執行完自動提交，

2.非事務型存盤引擎如：MyISAM無回滾功能，

四、多版本并發控制MVCC

　　可以認為MVCC是行級鎖的一個變種，它在很多情況下避免了加鎖，MVCC的實作是通過保存資料在某個時間的快照來實作的，

　　InnoDB的MVCC，是通過在每行記錄后面保存兩個隱藏的列來實作的，這兩個列一個保存了行的創建時間，一個保存了行的洗掉時間，存盤的不是真正時間而是系統的版本號，每個新事物開始系統版本號自動遞增，事物開始時刻的版本號作為事物的版本號，用來和查詢到的每行記錄版本號進行比較，

　　MVCC只在可重復讀和讀已提交兩個隔離級別下作業，其他兩個級別都和MVCC不兼容，因為讀未提交總是讀取最新的資料行，不符合當前事物版本的資料行，可串行化會對所有行加鎖，

　　我的理解其實MVCC就跟CAS是一個原理，就是一個比較并交換的程序，

標籤：其他

上一篇：sqlserver 腳本和批處理指令小結

下一篇：關于mysql面試題(持續更新)