本章概要描述MySQL服務(wù)器架構(gòu)、各種存儲引擎間的主要區(qū)別及區(qū)別的重要性

回顧MySQL歷史背景、基準(zhǔn)測試，通過簡化細(xì)節(jié)和演示案例來討論MySQL的原理

正文：

MySQL架構(gòu)可在多種不同場景中應(yīng)用，可嵌入到應(yīng)用程序中農(nóng)，支持?jǐn)?shù)據(jù)倉庫、內(nèi)容索引、部署軟件、高可用冗余系統(tǒng)、在線事務(wù)處理系統(tǒng)等；

MySQL最重要的特性是他的存儲引擎架構(gòu)，使得查詢處理及其他系統(tǒng)任務(wù)和數(shù)據(jù)存儲、提取分離；

1.1MySQL邏輯架構(gòu)

1.2并發(fā)控制

鎖粒度：

鎖策略：在鎖開銷和數(shù)據(jù)安全性間尋求平衡，每個存儲引擎可實現(xiàn)指定鎖策略和粒度

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

行級鎖：row lock 最大程度支持并發(fā) 最大的鎖開銷 在存儲引擎層（以自己的方式）實現(xiàn)

1.3事務(wù)

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

隔離級別：

四種，每種規(guī)定了事務(wù)中所作的修改，較低的隔離可以執(zhí)行更高的并發(fā)、開銷也更低

READ UNCOMMITTED未提交讀

事務(wù)中的修改及時沒有提交，對其他事務(wù)也是可見的；事務(wù)讀取未提交的數(shù)據(jù)：臟讀；很少使用

READ COMMITTED提交讀

almost庫默認(rèn)隔離級別，非MySQL；事務(wù)從開始到結(jié)束只看見已提交的事務(wù)所作的修改，本身所做的修改對其他事務(wù)不可見；不可重復(fù)讀：兩次執(zhí)行同樣的查詢，結(jié)果可能不一樣（其他事務(wù)的修改）

REPEATABLE READ可重復(fù)讀

MySQL默認(rèn)，解決了臟讀，同一事務(wù)多次讀同樣結(jié)果；幻讀：當(dāng)某個事務(wù)在讀取某個范圍內(nèi)的記錄時、另一個事務(wù)在該范圍內(nèi)插入新的記錄，當(dāng)前事務(wù)再次讀取該范圍記錄、幻行

SERIALIZABLE:可串行化

最高，強制事務(wù)串行執(zhí)行，避免幻讀問題，讀取每行數(shù)據(jù)時加鎖（可導(dǎo)致大量超時和鎖爭用），很少使用

死鎖

1、兩個多個事務(wù)在同一個資源上相互占用并請求鎖定對方占用的資源；

2、多個事務(wù)試圖以不同的順序鎖定資源，可能產(chǎn)生死鎖；

3、多個事務(wù)同時鎖定同一個資源；

鎖的行為和順序和存取引擎相關(guān)，同樣的順序執(zhí)行語句，一些存儲引擎會產(chǎn)生死鎖一些不會；

死鎖產(chǎn)生的雙重原因：因為真正的數(shù)據(jù)沖突(很難避免），因為存儲引擎的實現(xiàn)方式導(dǎo)致；

死鎖發(fā)送后，只有部分或完全回滾其中一個事務(wù)，才能打破死鎖：InnoDB即回滾持有最少行級排他鎖的事務(wù)；

1.3.4MySQL中的事務(wù):存儲引擎實現(xiàn)

MySQL兩種事務(wù)型存儲引擎：InnoDB、NDB Cluster

自動提交AUTOCOMMIT；

默認(rèn)采用自動提交模式，如果不顯式開始一個事務(wù)，則每個查詢都被當(dāng)做一個事務(wù)執(zhí)行提交操作，可通過AUTOCOMMIT變量來啟用=1 =ON 、禁用=0 =OFF（all查詢都在一個事務(wù)中直到顯式commit rollback）事務(wù)結(jié)束同時開始新的事務(wù)，修改這個變量對非事務(wù)型表沒有任何影響；

MySQL可以通過set transaction isolation level設(shè)置隔離級別，新的級別在下一個事務(wù)開始時生效，配置文件設(shè)置整個庫的，也可只改變當(dāng)前會話的隔離級別

set session transaction isolation level read committed;

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

1.4多版本并發(fā)控制MVCC

數(shù)據(jù)庫MySQL、Oracle、postgresql等都實現(xiàn)了MVCC，各自實現(xiàn)機制不同【源】

MVCC:每個連接到數(shù)據(jù)庫的讀、在某個瞬間看到的是數(shù)據(jù)庫的快照，寫操作在提交之前對外不可見；【源】

更新時，將舊數(shù)據(jù)標(biāo)記為過時且在別處增加新版本的數(shù)據(jù)（多個版本的數(shù)據(jù)，只有一個最新），容許讀取之前的數(shù)據(jù)

特點：

1、每行數(shù)據(jù)都存在一個版本，每次數(shù)據(jù)更新時都更新該版本

2、修改時copy出當(dāng)前版本、隨意修改，各事務(wù)間不干擾

3、保存時比較版本號，成功commit則覆蓋原紀(jì)錄，失敗則放棄rollback

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

1.5MySQL存儲引擎

mysql將每個數(shù)據(jù)庫保存位數(shù)據(jù)目錄下的一個子目錄，創(chuàng)建表示，mysql在子目錄下創(chuàng)建與表同名的.frm文件保存表的定義，不同存儲引擎保存數(shù)據(jù)和索引的方式不同，但表的定義在MySQL服務(wù)層同一處理；

InnoDB:默認(rèn)事務(wù)型引擎、最重要、廣泛使用

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

數(shù)據(jù)存儲在由InnoDB管理的表空間中，由一系列數(shù)據(jù)文件組成；

使用MVCC支持高并發(fā)，并實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別，默認(rèn)是REPEATABLE READ可重復(fù)讀，通過間隙鎖next-key locking防止幻讀，間隙鎖使得InnoDB鎖定查詢設(shè)計的行還鎖定索引中的間隙防止喚影行；

間隙鎖：

當(dāng)使用范圍條件并請求鎖時，InnoDB給符合條件的已有數(shù)據(jù)記錄的索引項加鎖，對應(yīng)鍵值在條件范圍內(nèi)但是不存在的記錄（間隙）加鎖，間隙鎖:【源】

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

InnoDB對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖；

1、上面的例子，如果不使用間隙鎖，如果其他事務(wù)插入大于100的記錄，本事務(wù)再次執(zhí)行則幻讀，但是會造成鎖等待，在并發(fā)插入比較多時、要盡量優(yōu)化業(yè)務(wù)邏輯，使用相等條件來訪問更新數(shù)據(jù)，避免使用范圍條件；

2、 在使用相等條件請求給一個不存在的記錄加鎖時，也會使用間隙鎖，當(dāng)我們通過參數(shù)刪除一條記錄時，如果參數(shù)在數(shù)據(jù)庫中不存在，庫會掃描索引，發(fā)現(xiàn)不存在，delete語句獲得一個間隙鎖，庫向左掃描掃到第一個比給定參數(shù)小的值，向右掃描到第一個比給定參數(shù)大的值，構(gòu)建一個區(qū)間，鎖住整個區(qū)間內(nèi)數(shù)據(jù)；【源】

1.5.2MyIsSAM存儲引擎

全文索引、壓縮、空間函數(shù)，不支持事務(wù)和行級鎖，崩潰后無法安全恢復(fù)

存儲：

將表存儲在兩個文件中：數(shù)據(jù).MYD、索引文件.MYI

表可以包含動態(tài)或靜態(tài)（長度固定）行，MySQL據(jù)表定義來決定采用何種行格式

表如是變長行，默認(rèn)配置只能處理256TB數(shù)據(jù)（指向記錄的指針長度6字節(jié)），改變表指針長度，修改表的MAX_ROWS和AVG_ROW_LENGTH，兩者相乘=表可到達的max大小，修改會導(dǎo)致重建整個表、表all索引；

特性：

1、對整張表加鎖，讀、共享鎖，寫、排他鎖，但在讀的同時可從表中插入新記錄：并發(fā)插入

2、修復(fù)：可手工、自動執(zhí)行檢查和修復(fù)操作，CHECK TABLE mytable檢查表錯誤，REPAIR TABLE mytable進行修復(fù)，執(zhí)行修復(fù)可能會丟失些數(shù)據(jù)，如果服務(wù)器關(guān)閉，myisamchk命令行根據(jù)檢查和修復(fù)操作；

3、索引特性：支持全文索引，基于分詞創(chuàng)建的索引，支持復(fù)雜查詢

4、延遲更新索引鍵Delayed Key Write,如果指定了DELAY_KEY_WRITE選項，每次修改完，不會立即將修改的索引數(shù)據(jù)寫入磁盤，寫入到內(nèi)存的鍵緩沖區(qū)，清理此區(qū)或關(guān)閉表時將對應(yīng)的索引塊寫入到磁盤，提升寫性能，但是在庫或主機崩潰時造成索引損壞、需要執(zhí)行修復(fù)操作

壓縮表：

表在創(chuàng)建并導(dǎo)入數(shù)據(jù)后，不再修改，比較適合，可使用myisampack對MyISAM表壓縮（打包），壓縮表不能修改（除非先解除壓縮、修改數(shù)據(jù)、再次壓縮）；減少磁盤空間占用、磁盤IO，提升查詢性能，也支持只讀索引；

現(xiàn)在的硬件能力，讀取壓縮表數(shù)據(jù)時解壓的開銷不大，減少IO帶來的好處大得多，壓縮時表記錄獨立壓縮，讀取單行時不需要解壓整個表

性能：

設(shè)計簡單，緊密格式存儲；典型的性能問題是表鎖的問題，長期處于locked狀態(tài)：找表鎖

1.5.3內(nèi)建的其他存儲引擎

Archive：適合日志和數(shù)據(jù)采集類應(yīng)用，針對高速插入和壓縮優(yōu)化，支持行級鎖和專業(yè)緩存區(qū)，緩存寫利用zlib壓縮插入的行，select掃描全表；

Blackhole：復(fù)制架構(gòu)和日志審核，其服務(wù)器記錄blackhole表日志，可復(fù)制數(shù)據(jù)到備庫 日志；

CSV：數(shù)據(jù)交換機制，將CSV文件作為MySQL表來處理，不支持索引；

Federated：訪問其他MySQL服務(wù)器的代理，創(chuàng)建遠(yuǎn)程mysql的客戶端連接將查詢傳輸?shù)竭h(yuǎn)程服務(wù)器執(zhí)行，提取發(fā)送需要的數(shù)據(jù)，默認(rèn)禁用；

Memory：快速訪問不會被修改的數(shù)據(jù)，數(shù)據(jù)保存在內(nèi)存、不IO，表結(jié)構(gòu)重啟后還在但數(shù)據(jù)沒了

1、查找 或 映射 表 ，2、緩存周期性聚合數(shù)據(jù)， 3、保存數(shù)據(jù)分析中產(chǎn)生的中間數(shù)據(jù)

支持hash索引，表級鎖，查找快并發(fā)寫入性能低，不支持BLOB/TEXT類型的列，每行長度固定，內(nèi)存浪費

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

NDB集群引擎：

1.5.4第三方存儲引擎

OLTP類:

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

PBXT：ACID/MVCC，引擎級別的復(fù)制、外鍵約束，較復(fù)雜架構(gòu)對固態(tài)存儲SSD適當(dāng)支持，較大值類型BLOB優(yōu)化

TokuDB：大數(shù)據(jù)，高壓縮比，大數(shù)據(jù)量創(chuàng)大量索引

RethinkDB:固態(tài)存儲

面向列的

列單獨存儲，壓縮效率高

Infobright：大數(shù)據(jù)量，數(shù)據(jù)分析、倉庫應(yīng)用設(shè)計的，高度壓縮，按照塊（一組元數(shù)據(jù)）排序；塊結(jié)構(gòu)準(zhǔn)索引，不支持索引（量大索引也沒用），如查詢無法再存儲層使用面向列的模式執(zhí)行，則需要在服務(wù)器層轉(zhuǎn)換成按行處理

社區(qū)存儲引擎：***

1.5.5選擇合適的引擎

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

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

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

2、備份：定期關(guān)閉服務(wù)器來執(zhí)行備份，該因素可忽略；在線熱備份，InnoDB

3、崩潰恢復(fù)：數(shù)據(jù)量較大，MyISAM崩后損壞概率比InnoDB高很多、恢復(fù)速度慢

4、持有的特性：

1.5.6轉(zhuǎn)換表的引擎

ALTER TABLE:最簡單

ALTER TABLE mytable ENGINE=InnoDB

此會執(zhí)行很長時間，MySQL按行將數(shù)據(jù)從原表復(fù)制到新表中，在復(fù)制期間可能會消耗掉系統(tǒng)all的I/O能力，同時原表上加讀鎖；會失去和原引擎相關(guān)的all特性

導(dǎo)出與導(dǎo)入：

mysqldump工具將數(shù)據(jù)導(dǎo)出到文件，修改文件中CREATE_TABLE語句的存儲引擎選項，同時修改表名（同一個庫不能存在相同的表名），mysqldump默認(rèn)會自動在CREATE_TABLE語句前加上DROP TABLE語句

創(chuàng)建與查詢：CREATE SELECT

綜合上述兩種方法：先建新存儲引擎表，利用INSERT……SELECT語法導(dǎo)數(shù)

CREATE TABLE innodb_table LIKE myisam_table
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
數(shù)據(jù)量大的話，分批處理（放事務(wù)中）

1.6MySQL時間線Timeline

早期MySQL破壞性創(chuàng)新，有諸多限制，且很多功能只能說是二流的，但特性支持和較低的使用成本，使受歡迎；5.x早起引入視圖、存儲過程等，期望成為“企業(yè)級”數(shù)據(jù)庫，但不算成功，5.5顯著改善

1.7MySQL開發(fā)模式

遵循GPL開源協(xié)議，全部源代碼開發(fā)給社區(qū)，部分插件收費；

1.8總結(jié)

mysql分層架構(gòu)，上層是服務(wù)器層的訪問和查詢執(zhí)行引擎，下層存儲引擎（最重要）

相關(guān)文章：

【MySQL數(shù)據(jù)庫】第二章解讀：MySQL基準(zhǔn)測試

【MySQL數(shù)據(jù)庫】第三章解讀：服務(wù)器性能剖析（上）

以上就是【MySQL數(shù)據(jù)庫】第一章解讀：MySQL架構(gòu)與歷史的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！