【MySQL資料庫】第一章解讀：MySQL架構與曆史

前言：

本章概要描述MySQL伺服器架構、各種儲存引擎間的主要區別及區別的重要性

回顧MySQL曆史背景、基準測試，通過簡化細節和示範案例來討論MySQL的原理

本文：

MySQL架構可在多種不同情境中應用，可嵌入到應用程式中農，支援資料倉儲、內容索引、部署軟體、高可用冗餘系統、線上交易處理系統等；

MySQL最重要的特性是他的儲存引擎架構，使得查詢處理及其他系統任務和資料存放區、提取分離；

1.1MySQL邏輯架構

1.2並發控制

鎖粒度：

鎖策略：在鎖開銷和資料安全性間尋求平衡，每個儲存引擎可實現指定鎖策略和粒度

表鎖：table lock 最基本的 開銷最小 鎖定整表

行級鎖：row lock 最大程度支援並發 最大的鎖開銷 在儲存引擎層（以自己的方式）實現

1.3事務

獨立工作單元，一組原子性SQL查詢

隔離等級：

四種，每種規定了事務中所作的修改，較低的隔離可以執行更高的並發、開銷也更低

READ UNCOMMITTED未提交讀

事務中的修改及時沒有提交，對其他事務也是可見的；事務讀取未提交的資料：髒讀；很少使用

READ COMMITTED提交讀

almost庫預設隔離等級，非MySQL；事務從開始到結束只看見已提交的事務所作的修改，本身所做的修改對其他事務不可見；不可重複讀取：兩次執行同樣的查詢，結果可能不一樣（其他事務的修改）

REPEATABLE READ可重複讀

MySQL預設，解決了髒讀，同一事務多次讀同樣結果；幻讀：當某個事務在讀取某個範圍內的記錄時、另一個事務在該範圍內插入新的記錄，當前事務再次讀取該範圍記錄、幻行

SERIALIZABLE:可序列化

最高，強制事務串列執行，避免幻讀問題，讀取每行資料時加鎖（可導致大量逾時和鎖爭用），很少使用

死結

1、兩個多個事務在同一個資源上相互佔用並請求鎖定對方佔用的資源；

2、多個事務試圖以不同的順序鎖定資源，可能產生死結；

3、多個事務同時鎖定同一個資源；

鎖的行為和順序和存取引擎相關，同樣的順序執行語句，一些儲存引擎會產生死結一些不會；

死結產生的雙重原因：因為真正的資料衝突(很難避免），因為儲存引擎的實現方式導致；

死結發送後，只有部分或完全復原其中一個事務，才能打破死結：InnoDB即復原持有最少行級獨佔鎖定的事務；

1.3.4MySQL中的事務:儲存引擎實現

MySQL兩種事務型儲存引擎：InnoDB、NDB Cluster

自動認可AUTOCOMMIT；

預設採用自動認可模式，如果不顯式開始一個事務，則每個查詢都被當做一個事務執行提交操作，可通過AUTOCOMMIT變數來啟用=1 =ON 、禁用=0 =OFF（all查詢都在一個事務中直到顯式commit rollback）事務結束同時開始新的事務，修改這個變數對非事務型表沒有任何影響；

MySQL可以通過set transaction isolation level設定隔離等級，新的層級在下一個事務開始時生效，設定檔設定整個庫的，也可只改變當前會話的隔離等級

set session transaction isolation level read committed;

建議：不管何時都不要顯示執行LOCK TABLES ，不管使用的是什麼儲存引擎

1.4多版本並發控制MVCC

資料庫MySQL、Oracle、postgresql等都實現了MVCC，各自實現機制不同【源】

MVCC:每個串連到資料庫的讀、在某個瞬間看到的是資料庫的快照，寫操作在提交之前對外不可見；【源】

更新時，將舊資料標記為過時且在別處增加新版本的資料（多個版本的資料，只有一個最新），容許讀取之前的資料

特點：

1、每行資料都存在一個版本，每次資料更新時都更新該版本

2、修改時copy出目前的版本、隨意修改，各事務間不干擾

3、儲存時比較版本號碼，成功commit則覆蓋原紀錄，失敗則放棄rollback

4、只在REPEATABLE READ 和READ COMMITTED兩個隔離等級下工作

1.5MySQL儲存引擎

mysql將每個資料庫儲存位元據目錄下的一個子目錄，建立表示，mysql在子目錄下建立與表同名的.frm檔案儲存表的定義，不同儲存引擎儲存資料和索引的方式不同，但表的定義在MySQL服務層同一處理；

InnoDB:預設事務型引擎、最重要、廣泛使用

處理大量短期事務；其效能和自動崩潰恢複特性、非事務型儲存的需求中也很流行

資料存放區在由InnoDB管理的資料表空間中，由一系列資料檔案組成；

使用MVCC支援高並發，並實現了四個標準的隔離等級，預設是REPEATABLE READ可重複讀，通過間隙鎖next-key locking防止幻讀，間隙鎖使得InnoDB鎖定查詢設計的行還鎖定索引中的間隙防止喚影行；

間隙鎖：

當使用範圍條件並請求鎖時，InnoDB給合格已有資料記錄的索引項目加鎖，對應索引值在條件範圍內但是不存在的記錄（間隙）加鎖，間隙鎖:【源】

//如emp表中有101條記錄，其empid的值分別是 1,2,...,100,101Select * from  emp where empid > 100 for update;

InnoDB對合格empid值為101的記錄加鎖，也會對empid大於101（這些記錄並不存在）的“間隙”加鎖；

1、上面的例子，如果不使用間隙鎖，如果其他事務插入大於100的記錄，本事務再次執行則幻讀，但是會造成鎖等待，在並發插入比較多時、要盡量最佳化商務邏輯，使用相等條件來訪問更新資料，避免使用範圍條件；

2、 在使用相等條件請求給一個不存在的記錄加鎖時，也會使用間隙鎖，當我們通過參數刪除一條記錄時，如果參數在資料庫中不存在，庫會掃描索引，發現不存在，delete語句獲得一個間隙鎖，庫向左掃描掃到第一個比給定參數小的值，向右掃描到第一個比給定參數大的值，構建一個區間，鎖住整個區間內資料；【源】

1.5.2MyIsSAM儲存引擎

全文索引、壓縮、空間函數，不支援事務和行級鎖，崩潰後無法安全恢複

儲存：

將表格儲存體在兩個檔案中：資料.MYD、索引檔案.MYI

表可以包含動態或靜態（長度固定）行，MySQL據表定義來決定採用何種行格式

表如是變長行，預設配置只能處理256TB資料（指向記錄的指標長度6位元組），改變表指標長度，修改表的MAX_ROWS和AVG_ROW_LENGTH，兩者相乘=表可到達的max大小，修改會導致重建整個表、表all索引；

特性：

1、對整張表加鎖，讀、共用鎖定，寫、獨佔鎖定，但在讀的同時可從表中插入新記錄：並發插入

2、修複：可手工、自動執行檢查和修複操作，CHECK TABLE mytable檢查表錯誤，REPAIR TABLE mytable進行修複，執行修複可能會丟失些資料，如果伺服器關閉，myisamchk命令列根據檢查和修複操作；

3、索引特性：支援全文索引，基於分詞建立的索引，支援複雜查詢

4、延遲更新索引鍵Delayed Key Write,如果指定了DELAY_KEY_WRITE選項，每次修改完，不會立即將修改的索引資料寫入磁碟，寫入到記憶體的鍵緩衝區，清理此區或關閉表時將對應的索引塊寫入到磁碟，提升寫效能，但是在庫或主機崩潰時造成索引損壞、需要執行修複操作

壓縮表：

表在建立並匯入資料後，不再修改，比較適合，可使用myisampack對MyISAM表壓縮（打包），壓縮表不能修改（除非先解除壓縮、修改資料、再次壓縮）；減少磁碟空間佔用、磁碟IO，提升查詢效能，也支援唯讀索引；

現在的硬體能力，讀取壓縮表資料時解壓的開銷不大，減少IO帶來的好處大得多，壓縮時表記錄獨立壓縮，讀取單行時不需要解壓整個表

效能：

設計簡單，緊密格式儲存；典型的效能問題是表鎖的問題，長期處於locked狀態：找表鎖

1.5.3內建的其他儲存引擎

Archive：適合日誌和資料擷取類應用，針對高速插入和壓縮最佳化，支援行級鎖和專業緩衝區，緩衝寫利用zlib壓縮插入的行，select掃描全表；

Blackhole：複製架構和日誌審核，其伺服器記錄blackhole表日誌，可複製資料到備庫 日誌；

CSV：資料交換器制，將CSV檔案作為MySQL表來處理，不支援索引；

Federated：訪問其他MySQL伺服器的代理，建立遠程mysql的用戶端串連將查詢傳輸到遠程伺服器執行，提取發送需要的資料，預設禁用；

Memory：快速存取不會被修改的資料，資料儲存在記憶體、不IO，表結構重啟後還在但資料沒了

1、尋找 或 映射 表 ，2、緩衝周期性彙總資料， 3、儲存資料分析中產生的中間資料

支援hash索引，表級鎖，尋找快並發寫入效能低，不支援BLOB/TEXT類型的列，每行長度固定，記憶體浪費

Merge：myisam變種，多個myisam合并的虛擬表

NDB叢集引擎：

1.5.4第三方儲存引擎

OLTP類:

XtraDB基於InnoDB改進，效能、可測量性、操作靈活

PBXT：ACID/MVCC，引擎層級的複製、外鍵約束，較複雜架構對固態儲存SSD適當支援，較大實值型別BLOB最佳化

TokuDB：大資料，高壓縮比，大資料量創大量索引

RethinkDB:固態儲存

面向列的

列單獨儲存，壓縮效率高

Infobright：大資料量，資料分析、倉庫應用設計的，高度壓縮，按照塊（一組中繼資料）排序；塊結構准索引，不支援索引（量大索引也沒用），如查詢無法再儲存層使用面向列的模式執行，則需要在伺服器層轉換成按行處理

社區儲存引擎：***

1.5.5選擇合適的引擎

除非需要用到某些InnoDB不具備的特性，且無辦法可以替代，否則優先選擇InnoDB引擎

不要混合使用多種儲存引擎，如果需要不同的儲存引擎：

1、事務：需要事務支出，InnoDB XtraDB；不需要 主要是select insert 那MyISAM

2、備份：定期關閉伺服器來執行備份，該因素可忽略；線上熱備份，InnoDB

3、崩潰恢複：資料量較大，MyISAM崩後損壞機率比InnoDB高很多、恢複速度慢

4、持有的特性：

1.5.6轉換表的引擎

ALTER TABLE:最簡單

ALTER TABLE mytable ENGINE=InnoDB

此會執行很長時間，MySQL按行將資料從原表複製到新表中，在複製期間可能會消耗掉系統all的I/O能力，同時原表上加讀鎖；會失去和原引擎相關的all特性

匯出與匯入：

mysqldump工具將資料匯出到檔案，修改檔案中CREATE_TABLE語句的儲存引擎選項，同時修改表名（同一個庫不能存在相同的表名），mysqldump預設會自動在CREATE_TABLE語句前加上DROP TABLE語句

建立與查詢：CREATE SELECT

綜合上述兩種方法：先建新儲存引擎表，利用INSERT……SELECT文法導數

CREATE TABLE innodb_table LIKE myisam_tableALTER TABLE innodb_table ENGINE=InnoDB;INSERT INTO innodb_table SELECT * FROM myisam_table;資料量大的話，分批處理（放事務中）

1.6MySQL時間軸Timeline

早期MySQL破壞性創新，有諸多限制，且很多功能只能說是二流的，但特性支援和較低的使用成本，使受歡迎；5.x早起引入視圖、預存程序等，期望成為“企業級”資料庫，但不算成功，5.5顯著改善

1.7MySQL開發模式

遵循GPL開源協議，全部原始碼開發給社區，部分外掛程式收費；

1.8總結

mysql分層架構，上層是伺服器層的訪問和查詢執行引擎，下層儲存引擎（最重要）

相關文章：

【MySQL資料庫】第二章解讀：MySQL基準測試

【MySQL資料庫】第三章解讀：伺服器效能剖析（上）