MySQL邏輯架構簡介

2019 年 10 月 4 日
筆記

本文鏈接：https://blog.csdn.net/u014427391/article/details/100170265

文章目錄

一、邏輯結構簡介

二、連接管理

三、解析優化查詢

四、鎖機制

4.1 讀鎖和寫鎖

4.2 鎖粒度

五、事務

5.1 事務特性

5.2 隔離級別

5.3、事務日誌

六、MySQL的存儲引擎

最近在看《高性能MySQL》一書，並做了筆記，整理成博客，分享出來：

一、邏輯結構簡介

Mysql服務器邏輯架構圖，圖來自《高性能MySQL》一書：

第一層結構主要處理客戶端與mysql服務端的連接、授權認證、安全等；第二層是Mysql服務端的核心，功能包括查詢解析、分析、優化、緩存等，存儲過程、觸發器、視圖等都在這一層實現；第三層的存儲引擎主要負責數據存儲和提取，存儲引擎不會去解析sql，不同存儲引擎之間不會通訊，只會簡單地響應上層服務器的請求

二、連接管理

每個客戶端連接成功，都會在服務器進程中擁有一個線程，服務器會緩存線程，該線程只能輪流在某個CPU中運行，所以不需要創建和銷毀線程

三、解析優化查詢

對於SELECT查詢語句，解析查詢之前會先查詢緩存，如果緩存能找到是不會去解析的，如果緩存查找不到，就會重現解析查詢，創建解析樹，然後對其進行查詢優化、決定表的讀取順序、選擇合適的索引等

四、鎖機制

數據庫引進鎖機制是為了控制並發，舉個例子，電商搶購案例，加入某類商品的庫存只有一件了，然後這時候有兩個用戶在搶這個商品，按照邏輯，一個用戶購物成功，庫存就應該減1的，然後事務並發沒控制好，這種情況是很容易出現商品沒庫存了，然後還可以下單的案例，這種是不合理的，這時候可以用數據庫的鎖機制來做，一個用戶在下單時，鎖存商品庫存的表，操作成功後才釋放鎖，另外一個用戶才可以操作

4.1 讀鎖和寫鎖

然後介紹一下mysql的讀鎖(read lock)和寫鎖(write lock)，讀鎖也可以稱作共享鎖(shared lock)，寫鎖稱為排他鎖(exclusive lock)，讀鎖是共享的，是允許多個用戶同個時刻同時讀取同個資源的，寫鎖是排他的，這也是出於安全策略考慮的，寫鎖會阻塞其它的讀鎖和寫鎖，然後操作完成後才釋放鎖，接着其它的鎖才可以操作

4.2 鎖粒度

鎖按照讀寫可以分為讀鎖和寫鎖，按照加鎖的粒度就可以分為表鎖(table lock)和行鎖(row level lock)

表鎖：是MySQL中最基本的鎖策略，也是開銷最小的策略，表鎖就是鎖住整張表，用戶對數據表進行寫操作(更新、刪除、新增等)時，會獲得寫鎖，然後因為加了表鎖，所以其他用戶是不可以對錶進行讀寫操作的，只有等寫鎖釋放之後
行鎖：行鎖是可以最大程度保證並發處理，不過鎖開銷也是最大的。並不是所有的庫引擎都可以支持行鎖的，InnoDB是可以支持的，不過MyISAM不支持的

五、事務

5.1 事務特性

提到事務肯定會想到事務的4個特性ACID，要保證業務的正常使用，必須保證ACID，ACID表示原子性(atomicity)、一致性(consistency)、隔離性(isolation)、持久性(durability)，一個運行良好的事務系統也是要求具備這些特徵

原子性(atomicity)：一個事務必須被視為一個不可分割的最小工作單位，整個事務中的所有操作要麼全部提交成功，要麼全部失敗回滾，不能只執行一部分操作
一致性(consistency)：一致性要求數據庫總是從一個一致性的狀態轉換為另外一個一致性的狀態，比如銀行轉賬的例子，一個用戶轉賬賬號里減了200元，另外一個收賬賬號必須增加，一個失敗數據必須全部回退狀態，要保證事務一致性
隔離性(isolation)：一般來說，一個事務所做的修改在提交之前對其它事務來說都是不可見的
持久性(durability)：事務一旦提交，所做的狀態就會永久保存在數據庫中，即使系統奔潰，修改的數據也不會丟

5.2 隔離級別

SQL標準中定義四種隔離級別，每種存儲引擎實現的隔離級別是不同的

READ UNCOMMITTED(未提交讀) 在READ UNCOMMITTED級別，事務即使沒提交，對其它事務也是可見，允許事務讀取未提交的數據，這也被稱為臟讀(Dirty Read)，所以在實際生產中很少用
READ COMMIT(提交讀) 大部分數據庫系統的默認隔離級別都是READ COMMIT，很明顯這種隔離級別只能讀取到已經提交事務的數據
REPEATABLE READ(可重複讀) REPEATABLE READ可以解決臟讀的問題，不過不能解決幻讀問題，所謂幻讀是指，在讀取某個範圍數據時候，另外一個事務又向這個範圍寫數據了，當之前事務再次讀取這個範圍數據時候就會產生幻讀
SERIALIZABLE(可串行化) SERIALIZABLE是最高的隔離級別，SERIALIZABLE強制事務串行執行，可以避免幻讀問題，其實保證SERIALIZABLE是常用行鎖的方式來保證一致性的，SERIALIZABLE會在每一行的數據上都加上鎖，SERIALIZABLE雖然可以保證事務安全性，不過服務器性能不好也會導致鎖爭用問題

設置隔離級別，可以通過SQL：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5.3、事務日誌

事務日誌可以用於提高事務的效率，有了事務日誌，數據存儲引擎在修改表數據時就只需要修改其內存拷貝，然後再將修改的行為數據持久化到硬盤，而不用修改數據本身再持久化到硬盤。事務日誌採用的是追加的方式

注意：Mysql中能支持事務的存儲引擎只有兩種：InnoDB和NDB Cluster。Mysql中默認採用自動提交的模式，oracle寫數據的話，都是需要自己手動提交事務的，而mysql不需要，因為默認的是自動提交事務的

SQL查詢，1或者ON表示啟動，0或者OFF表示關閉

SHOW VARIABLES LIKE 'AUTOCOMMIT';

可以通過SQL來開啟關閉自動提交：

SET AUTOCOMMIT =1;

多版本控制 MySQL支持事務的存儲引擎有InnoDB和NDB Cluster，而這些存儲引擎實現事務一致性並不是通過簡單的行級鎖，因為前面也說到，行級而且可以很好的保證事務一致性，但是性能並不是很好，會導致鎖競爭的情況，所以mysql這些事務型的存儲引擎採用的是多版本並發控制(MVCC)，多版本並發控制可以說是行級鎖的變種，但是多版本並發控制相對的開銷是比較低的，多版本控制是通過保存在某個時間點的快照信息來實現的，InnoDB是通過在每行記錄後面保存兩個隱藏的列來實現的，這兩個列，一個保存行的創建時間，一個保存行的過期時間，當然不是具體的時間值，而是系統版本號(system version number)，每開始一個事務，系統版本號都會自動遞增的

六、MySQL的存儲引擎

Mysql的事務回滾等處理是給存儲引擎負責的，下面介紹一下幾種常見的MySQL存儲引擎

InnoDB存儲引擎 InnoDB存儲引擎是mysql默認的存儲引擎，也是使用最廣泛的存儲引擎，前面也說了，InnoDB是一種可以支持事務的存儲引擎，InnoDB是mysql 5.1才開始出現的，之前名稱是稱為InnoDB plugin，直到被oracle收購mysql後，才正式命名為InnoDB。InnoDB採用MVCC來支持事務一致性和並發，並且實現了4個基本的隔離級別，默認級別是PREATABLE READ(可重複讀)，並且通過間隙鎖(next-key loking)策略來防止歡讀的出現，
MyISAM存儲引擎在MySQL5.1之前版本，MyISAM是默認的存儲引擎，MyISAM提供了全文索引、壓縮、空間函數等，但是MyISAM是不支持事務和行級鎖的，MyISAM會將表存儲在兩個文件中，數據文件和索引文件，拓展名分別是.MYD和.MYI，MyISAM雖然沒有行級鎖，不過是可以支持表鎖的，所以在一些業務系統還是可以做到並發控制的

來自尚硅谷老師歸納的圖：

Archive存儲引擎 Archive存儲引擎只支持INSERT和SELECT操作，在Mysql5.1版本之前不支持索引的，Archive引擎會緩存所有的寫數據，利用zlib對寫入的數據進行壓縮，所以需要的磁盤/IO比MyISAM更少的，但是SELECT是全表掃描的，因此Archive表適合用於日誌和數據採集類的應用，Archive引擎是支持行級鎖的
Blackhole存儲引擎

Blackhole引擎沒有存儲機制，不做保存處理，但是服務器還是會記錄Blackhole表的日誌，所以可以用於複製數據到備庫，或者用於記錄日誌

CSV存儲引擎 CSV引擎可以將普通的CSV文件作為MySql的表處理，可以在數據庫運行時拷貝文件，將Excel表另存為CSV文件，然後就可以直接複製到數據庫，不過這種表是不支持索引的
Federated存儲引擎 Federated引擎是訪問其它服務器的代理，會創建一個到遠程 MySQL 服務器的客戶端連接，並將查詢傳輸到遠程服務器執行，然後提取或者發送需要的數據。
Memory存儲引擎 Memory引擎適用與需要快速訪問數據，這些數據是保存在內存的，Memory支持hash索引，所以查詢速度非常快，不過不支持BLOB或者TXT類型數據，是用表鎖做並發的
Merge存儲引擎 Merge引擎是由多個MyISAM表合併而來的表，可以用於存儲日誌或者用於當數據存庫
NDB集群存儲引擎這是Mysql推出的可以用於集群的存儲引擎，提供了SQL和NDB協議之間的接口，原先是收購了NDB數據庫，然後發展來的
自定義存儲引擎 Mysql的存儲引擎是支持可以自己開發和修改的，比如阿里的mysql就不是用默認InnoDB

圖來自尚硅谷老師歸納：

修改存儲引擎SQL:

ALTER TABLE mytable ENGINE = InnoDB;