本章概要描述MySQL服務(wù)器架構(gòu)、各種存儲(chǔ)引擎間的主要區(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最重要的特性是他的存儲(chǔ)引擎架構(gòu)，使得查詢處理及其他系統(tǒng)任務(wù)和數(shù)據(jù)存儲(chǔ)、提取分離；

1.1MySQL邏輯架構(gòu)

1.2并發(fā)控制

鎖粒度：

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

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

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

1.3事務(wù)

獨(dú)立工作單元，一組原子性SQL查詢

隔離級(jí)別：

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

READ UNCOMMITTED未提交讀

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

READ COMMITTED提交讀

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

REPEATABLE READ可重復(fù)讀

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

SERIALIZABLE:可串行化

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

死鎖

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

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

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

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

死鎖產(chǎn)生的雙重原因：因?yàn)檎嬲�的�?shù)據(jù)沖突(很難避免），因?yàn)榇鎯?chǔ)引擎的實(shí)現(xiàn)方式導(dǎo)致；

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

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

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

自動(dòng)提交AUTOCOMMIT；

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

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

set session transaction isolation level read committed;

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

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

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

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

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

特點(diǎn)：

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

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

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

4、只在REPEATABLE READ 和READ COMMITTED兩個(gè)隔離級(jí)別下工作

1.5MySQL存儲(chǔ)引擎

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

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

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

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

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

間隙鎖：

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

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

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

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

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

1.5.2MyIsSAM存儲(chǔ)引擎

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

存儲(chǔ)：

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

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

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

特性：

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

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

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

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

壓縮表：

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

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

性能：

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

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

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

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

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

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

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

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

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

Merge：myisam變種，多個(gè)myisam合并的虛擬表

NDB集群引擎：

1.5.4第三方存儲(chǔ)引擎

OLTP類:

XtraDB基于InnoDB改進(jìn)，性能、可測量性、操作靈活

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

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

RethinkDB:固態(tài)存儲(chǔ)

面向列的

列單獨(dú)存儲(chǔ)，壓縮效率高

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

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

1.5.5選擇合適的引擎

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

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

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

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

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

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

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

綜合上述兩種方法：先建新存儲(chǔ)引擎表，利用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時(shí)間線Timeline

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

1.7MySQL開發(fā)模式

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

1.8總結(jié)

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

相關(guān)文章：

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

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

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