1 MySQL架構與歷史

 

1.1 MySQL邏輯架構

 

1. MySQL伺服器邏輯架構圖

最上層：不是MySQL獨有，大多數基於網路的客戶端/伺服器的工具或者服務都有類似的架構。 比如連線處理、 授權認證、 安
全等等。

第二層：大多數核心服務，包括解析、分析、優化、快取等，跨儲存引擎的功能都在這一層體現：儲存過程、觸發器、檢視等。

第三層：包含了儲存引擎，負責資料儲存與提取。

 

1.2 併發控制

1.讀寫鎖

共享鎖（shared lock）和排他鎖（exclusive lock）又稱為讀鎖（read lock）和寫鎖（write lock）。

讀鎖：共享，相互不阻塞。

寫鎖： 會阻塞其他讀寫鎖。

 

2.鎖粒度

鎖策略：鎖開銷和資料安全性之間求平衡。

分為 行級鎖（row-level lock）、表級鎖（table lock）

每個MySQL儲存引擎都可以實現自己的鎖策略和鎖粒度。

表鎖：開銷小，鎖整個表。一般alert table 之類的語句使用表鎖。

寫鎖可以插入鎖佇列中讀鎖的前面，反之不行。

 

1.3 事務

事務：一組原子性的SQL查詢，要麼全成功，要麼全失敗。

開啟事務：start transaction或 begin

結束事務：要麼commit提交（持久化保留），要麼rollback回滾（撤銷修改）。

 

ACID特性：A原子性（actomicitty）、C一致性（consistency）、I 隔離性（isolation）、D永續性（durability）。

原子性：要麼全提交成功，要麼全失敗回滾。

一致性：轉賬前後總金額不變。

隔離性：一個事務所作修改在提交前，對其他事務不可見。

永續性：一旦失誤提交，永久儲存到資料庫。

 

隔離級別

read uncommitted（未提交讀）：即使A事務未提交，其他事務也能可見。出現髒讀。較少使用。

read committed（提交讀）：大多數預設級別（不是MySQL級別），只能看見已提交資料，事務A從開始到結束所做的任何修改對其他事務不可見。也被稱為不可重複讀，兩次查詢結果可能不同。

repeatable read（可重複讀）：多次讀結果一樣（MySQL預設級別）。解決髒讀，出現幻讀（事務A讀某個範圍內記錄，另一事務B在該範圍插入新紀錄，A再讀會和原來不一致）。用MVCC（多版本併發控制）和間隙鎖（next-key locking） 策略解決幻讀。

serializable（可序列化）：強制事務序列執行，避免幻讀，每一行記錄都加鎖。

 

InnoDB目前處理死鎖的方法是，將持有最少行級排他鎖的事務回滾，

MyISAM不會出現死鎖，因為不存在事務，即一次獲得全部需要的鎖，要麼全部滿足要麼全部不滿足。

 

MySQL有兩種事務型的儲存引擎：InnoDB 和 NDB Cluster

預設採用自動提交（AUTOCOMMIT）模式，每個事務被當做一個事務執行提交操作。

當AUTOCOMMIT=0時，所有的查詢都在一個事務中，直到顯示執行COMMIT提交或ROLLBACK回滾。

事務都由下層的儲存引擎實現。

 

1.4 多版本併發控制

MVCC：儲存資料在某個時間點的快照實現的。不管多久，每個事務看到的資料都是一致的，但每個事務對同一張表，同一時刻看到的資料可能不一樣。

InnoDB的MVCC， 是通過在每行記錄後面儲存兩個隱藏的列來實現的。 這兩個列， 一個儲存了行的建立時間， 一個儲存行的過期時間（或刪除時間） 。 當然儲存的並不是實際的時間值， 而是系統版本號（system version number） 。 每開始一個新的事務， 系統版本號都會自動遞增。 事務開始時刻的系統版本號會作為事務的版本號， 用來和查詢到的每行記錄的版本號進行比較。 下面看一下在REPEATABLE READ 隔離級別下， MVCC具體是如何操作的。
 

MVCC只在REPEATABLE READ 和READ COMMITTED 兩個隔離級別下工作。 其他兩個隔離級別都和MVCC不相容 ， 因為READ UNCOMMITTED總是讀取最新的資料行， 而不是符合當前事務版本的資料行。而SERIALIZABLE 則會對所有讀取的行都加鎖。
 

1.5 MySQL的儲存引擎

InnoDB採用MVCC來支援高併發， 並且實現了四個標準的隔離級別。 其預設級別是REPEATABLE READ （可重複讀） ， 並且通過間隙鎖（next-key locking） 策略防止幻讀的出現。 間隙鎖使得InnoDB不僅僅鎖定查詢涉及的行， 還會對索引中的間隙進行鎖定， 以防止幻影行的插入。
 

MyISAM支援全文索引、壓縮、空間函式等，不支援事務和行級鎖，且崩潰後無法安全恢復（因為每次修改執行完成時， 不會立刻將修改的索引資料寫入磁碟， 而是會寫到記憶體中的鍵緩衝區（in-memory key buffer） ， 只有在清理鍵緩衝區或者關閉表的時候才會將對應的索引塊寫入到磁碟）。

 

 

日誌型應用：MyISAM或者Archive儲存引擎， 因為它們開銷低， 而且插入速度非常快。

只讀或者大部分情況下只讀的表：不介意MyISAM的崩潰恢復問題， 選用MyISAM引擎是合適的。MyISAM只將數
據寫到記憶體中， 然後等待作業系統定期將資料刷出到磁碟上。

訂單處理：支援事務InnoDB。

電子公告牌和主題討論論壇：經常重新整理時間、榜等。MyISAM快。

CD-ROM應用：MyISAM表或者MyISAM壓縮表，壓縮表只是只讀。

大資料量：Infobright是MySQL資料倉儲最成功的解決方案。

 

儲存引擎轉換：表的儲存引擎轉換成另外一種引擎

1.ALTER TABLE

ALTER TABLE mytable ENGINE=InnoDB;
上述方法適用任何儲存引擎，時間長，按行復制到新表，IO能力要求高。
 

2.匯入與匯出

mysqldump 工具將資料匯出到檔案， 然後修改檔案中CREATE TABLE 語句的儲存引擎選項， 注意同
時修改表名，因為同一個資料庫中不能存在相同的表名，

3.建立與查詢（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;

資料量過大可分批處理，可以在執行的過程中對原表加鎖， 以確保新表和原表的資料一致。
 

1.6 MySQL時間線

舊的版本在高併發時存在問題。

新的版本在單執行緒的時候效能比舊版本更差。

一開始可能無法理解為什麼會這樣， 仔細想想就能明白， 這是一個非常簡單的只讀測試。 新版本的SQL語法更復雜， 針對複雜查詢增加了很多特性和改進， 這對於簡單查詢可能帶來了更多的開銷。 舊版本的程式碼簡單， 對於簡單的查詢反而會更有利
 

1.7 MySQL的開發模式

遵循GPL開源協議，大部分在社群開源；部分效能外掛收費。