快一步收藏：Mysql數(shù)據(jù)庫核心知識(shí)匯總

快一步收藏：Mysql數(shù)據(jù)庫核心知識(shí)匯總

毫無疑問，MySQL是當(dāng)下最流行的開源數(shù)據(jù)庫。憑借強(qiáng)大的性能和易于使用性，它已被Google、Facebook、YouTube、百度、網(wǎng)易和新浪等大型互聯(lián)網(wǎng)公司所應(yīng)用。更有統(tǒng)計(jì)，世界上一流的互聯(lián)網(wǎng)公司中，排名前20的有80%都是MySQL的忠有用戶。今日我就帶大家一起來學(xué)習(xí)MySQL數(shù)據(jù)庫的核心學(xué)問。

一、Mysql規(guī)律架構(gòu)

Mysql規(guī)律架構(gòu)主要分三層：

第一層：負(fù)責(zé)連接處理，授權(quán)認(rèn)證，平安等等

1、每個(gè)客戶端連接都會(huì)在服務(wù)器進(jìn)程中擁有一個(gè)線程，服務(wù)器維護(hù)了一個(gè)線程池，因此不需要為每一個(gè)新建的連接創(chuàng)建或者銷毀線程；

2、當(dāng)客戶端連接到Mysql服務(wù)器時(shí)，服務(wù)器對其進(jìn)行認(rèn)證，通過用戶名和密碼認(rèn)證，也可以通過SSL證書進(jìn)行認(rèn)證；

3、一旦客戶端連接勝利，服務(wù)器會(huì)連續(xù)驗(yàn)證客戶端是否具有執(zhí)行某個(gè)特定查詢的權(quán)限。

其次層：負(fù)責(zé)編譯并優(yōu)化SQL

1、這一層包括查詢解析，分析，優(yōu)化，緩存以及全部的的內(nèi)置函數(shù)；

2、對于SELECT語句，在解析查詢前，服務(wù)器會(huì)先檢查查詢緩存，假如能在其中找到對應(yīng)的查詢結(jié)果，則無需再進(jìn)行查詢解析、優(yōu)化等過程，直接返回查詢結(jié)果；

3、全部跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn):存儲(chǔ)過程，觸發(fā)器，視圖。

第三層：是存儲(chǔ)引擎

1、存儲(chǔ)引擎負(fù)責(zé)在MySQL中存儲(chǔ)數(shù)據(jù)、提取數(shù)據(jù)；

2、存儲(chǔ)引擎通過API與上層進(jìn)行通信，這些API屏蔽了不同存儲(chǔ)引擎之間的差異，使得這些差異對上層查詢過程透亮?????；

3、存儲(chǔ)引擎不會(huì)去解析SQL，不同存儲(chǔ)引擎之間也不會(huì)相互通信，而只是簡潔地響應(yīng)上層服務(wù)器的懇求。

二、主從復(fù)制

主從復(fù)制原理，簡言之，就三步曲：

1、主數(shù)據(jù)庫有個(gè)binlog二進(jìn)制文件，紀(jì)錄了全部增刪改Sql語句。（binlog線程）

2、從數(shù)據(jù)庫把主數(shù)據(jù)庫的binlog文件的sql語句復(fù)制過來。（io線程）

3、從數(shù)據(jù)庫的relaylog重做日志文件中再執(zhí)行一次這些sql語句。（Sql執(zhí)行線程）

三、InnoDB文件存儲(chǔ)結(jié)構(gòu)

從物理意義上講，InnoDB表由共享表空間文件（ibdata1）、獨(dú)占表空間文件（ibd）、表結(jié)構(gòu)文件（.frm）、以及日志文件（redo文件等）組成。

四、表結(jié)構(gòu)文件

在MYSQL中建立任何一張數(shù)據(jù)表，在其數(shù)據(jù)名目對應(yīng)的數(shù)據(jù)庫名目下都有對應(yīng)表的.frm文件,.frm文件是用來保存每個(gè)數(shù)據(jù)表的元數(shù)據(jù)(meta)信息，包括表結(jié)構(gòu)的定義等，.frm文件跟數(shù)據(jù)庫存儲(chǔ)引擎無關(guān)，也就是任何存儲(chǔ)引擎的數(shù)據(jù)表都必需有.frm文件。

五、表空間結(jié)構(gòu)

1、表空間（tablespace)

全部數(shù)據(jù)都放在表空間中。假如開啟了innodb_file_per_table選項(xiàng)，則InnoDB會(huì)為每張表開拓一個(gè)表空間。但是需要留意的是表空間存放的只是數(shù)據(jù)、索引和插入緩沖bitmap頁，其他數(shù)據(jù)比如undo信息，插入緩沖索引頁，系統(tǒng)事務(wù)信息，二次寫緩沖還是會(huì)放在原來的共享表空間內(nèi)。

假如rollback后，共享表空間不會(huì)自動(dòng)收縮，但是會(huì)推斷空間是否需要（比如undo空間），假如不需要的話，會(huì)將這些空間標(biāo)記為可用空間，供下次undo使用。

2、段(segment)

表空間由各個(gè)段組成，比如數(shù)據(jù)段，索引段，回滾段等。

3、區(qū)(extent)

區(qū)由連續(xù)的頁組成，在任何狀況下區(qū)的大小都是1M。InnoDB存儲(chǔ)引擎一次從磁盤申請也許4-5個(gè)區(qū)(4-5M)。在默認(rèn)狀況下，頁的大小為16KB，即一個(gè)區(qū)中有也許64個(gè)連續(xù)的頁。

4、頁(page)

InnoDB磁盤管理的最小單位。B樹節(jié)點(diǎn)=一個(gè)物理Page（16K），數(shù)據(jù)按16KB切片為Page并編號(hào)，編號(hào)可映射到物理文件偏移(16K*N），B+樹葉子節(jié)點(diǎn)前后形成雙向鏈表。Page分為幾種類型，數(shù)據(jù)頁和索引頁就是其中最為重要的兩種類型。

六、緩沖池

InnoDB存儲(chǔ)引擎是基于磁盤存儲(chǔ)的，并將其中的記錄根據(jù)頁的方式進(jìn)行管理，但是由于CPU速度和磁盤速度之間的鴻溝，基于磁盤的數(shù)據(jù)庫系統(tǒng)通常使用緩沖池記錄來提高數(shù)據(jù)庫的的整體性能。

在數(shù)據(jù)庫中進(jìn)行讀取操作，首先將從磁盤中讀到的頁放在緩沖池中，下次再讀相同的頁中時(shí)，首先推斷該頁是否在緩沖池中。若在緩沖池中，稱該頁在緩沖池中被命中，直接讀取該頁。否則，讀取磁盤上的頁。

七、重做日志

默認(rèn)狀況在數(shù)據(jù)庫數(shù)據(jù)文件夾下會(huì)有兩個(gè)文件，ib_logfile0/ib_logfile1,這就是重做日志文件，記錄了對于InnoDB存儲(chǔ)引擎的事務(wù)日志。

每個(gè)Innodb存儲(chǔ)引擎至少有1個(gè)重做日志文件組，每個(gè)組至少包含2個(gè)重做日志文件(ib_logfile0,ib_logfile1)。

可以通過設(shè)置多個(gè)鏡像日志組(mirroredloggroups)，將不同組放到不同磁盤，提高重做日志的高可用性。

日志組中的文件大小是全都的，以循環(huán)的方式運(yùn)行。文件1寫滿時(shí)，切換到文件2，文件2寫滿時(shí)，再次切換到文件1.日志組中的文件大小是全都的，以循環(huán)的方式運(yùn)行。文件1寫滿時(shí)，切換到文件2，文件2寫滿時(shí)，再次切換到文件1(從頭寫入）。

為了保證數(shù)據(jù)的平安性，事務(wù)進(jìn)行中時(shí)會(huì)不斷的產(chǎn)生redolog，在事務(wù)提交時(shí)進(jìn)行一次flush操作，保存到磁盤中,redolog是根據(jù)挨次寫入的，磁盤的挨次讀寫的速度遠(yuǎn)大于隨機(jī)讀寫。當(dāng)數(shù)據(jù)庫或主機(jī)失效重啟時(shí)，會(huì)依據(jù)redolog進(jìn)行數(shù)據(jù)的恢復(fù)，假如redolog中有事務(wù)提交，則進(jìn)行事務(wù)提交修改數(shù)據(jù)。這樣實(shí)現(xiàn)了事務(wù)的原子性、全都性和長久性。

對于寫入重寫日志文件的操作不是直接寫，而是先寫入一個(gè)重做日志緩沖（redolopgbuffer）中，然后根據(jù)肯定的條件寫入日志文件。

當(dāng)對應(yīng)事務(wù)的臟頁寫入到磁盤之后，redolog的使命也就完成了，重做日志占用的空間就可以重用（被掩蓋）。

通過innodb_log_buffer_size可以配置重寫日志緩沖的的大小。

從日志緩沖寫入磁盤有兩個(gè)時(shí)間點(diǎn)：

1、主線程每秒都會(huì)將重做日志緩沖寫入磁盤的重做日志文件，不論事務(wù)是否已經(jīng)提交;

2、另外一個(gè)是由參數(shù)innodb_flush_log_at_trx_mit掌握，表示在事務(wù)提交時(shí)，處理重做日志;

參數(shù)innodb_flush_log_at_trx_mit可設(shè)的值有0、1、2。0代表當(dāng)提交事務(wù)時(shí)，并不將事務(wù)的重做日志寫入磁盤上的日志文件，而是等待主線程每秒的刷新。而1和2不同的地方在于：1是在mit時(shí)將重做日志緩沖同步寫到磁盤；2是重做日志異步寫到磁盤，即不能完全保證mit時(shí)確定會(huì)寫入重做日志文件，只是有這個(gè)動(dòng)作。

八、回滾日志

除了重做記錄redolog外，當(dāng)進(jìn)行數(shù)據(jù)修改時(shí)還會(huì)記錄undolog，undolog用于數(shù)據(jù)的撤回操作，它記錄了修改的反向操作，比如，插入對應(yīng)刪除，修改對應(yīng)修改為原來的數(shù)據(jù)，通過undolog可以實(shí)現(xiàn)事務(wù)回滾，并且可以依據(jù)undolog回溯到某個(gè)特定的版本的數(shù)據(jù)，實(shí)現(xiàn)MVCC，也即非鎖定讀。

事務(wù)開頭之前，將當(dāng)前的版本生成undolog，undo也會(huì)產(chǎn)生redo來保證undolog的牢靠性，事務(wù)提交之后，undolog并不能立馬被刪除，而是放入待清理的鏈表，由purge線程推斷是否由其他事務(wù)在使用undo段中表的上一個(gè)事務(wù)之前的版本信息，打算是否可以清理undolog的日志空間。

默認(rèn)狀況下undo文件是保持在共享表空間的，也即ibdatafile文件中，當(dāng)數(shù)據(jù)庫中發(fā)生一些大的事務(wù)性操作的時(shí)候，要生成大量的undo信息，全部保存在共享表空間中的。因此共享表空間可能會(huì)變的很大，默認(rèn)狀況下，也就是undo日志使用共享表空間的時(shí)候，被“撐大”的共享表空間是不會(huì)也不能自動(dòng)收縮的。

九、ACID

ACID是事務(wù)的四大特性。

1、原子性(Atomicity)

一個(gè)事務(wù)必需被視為一個(gè)不行分割的最小工作單元，整個(gè)事務(wù)中的全部操作要么全部提交勝利，要么全部失敗回滾，對于一個(gè)事務(wù)來說，不行能只執(zhí)行其中的一部分操作，這就是事務(wù)的原子性。

2、全都性(Consistency)

數(shù)據(jù)庫總是從一個(gè)全都性的狀態(tài)轉(zhuǎn)換到另一個(gè)全都性的狀態(tài)。

3、隔離性(Isolation)

一個(gè)事務(wù)所做的修改在最終提交以前，對其他事務(wù)是不行見的。

4、長久性(Durability)

一旦事務(wù)提交，則其所做的修改不會(huì)永久保存到數(shù)據(jù)庫

十、事務(wù)的隔離性問題

假如不考慮事務(wù)的隔離性，會(huì)消失以下問題：

1、臟讀

一個(gè)事務(wù)在更新一條記錄，未提交前，其次個(gè)事務(wù)讀到了第一個(gè)事務(wù)更新后的記錄，那么其次個(gè)事務(wù)就讀到了臟數(shù)據(jù)，會(huì)產(chǎn)生對第一個(gè)未提交數(shù)據(jù)的依靠。一旦第一個(gè)事務(wù)回滾，那么其次個(gè)事務(wù)讀到的數(shù)據(jù)，將是錯(cuò)誤的臟數(shù)據(jù)。

2、幻讀

一個(gè)事務(wù)按相同的查詢條件查詢之前檢索過的數(shù)據(jù)，確發(fā)覺檢索出來的結(jié)果集條數(shù)變多或者削減（由其他事務(wù)插入、刪除的），類似產(chǎn)生幻覺。

3、不行重復(fù)讀（虛讀）

一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的一段時(shí)間后，再次讀取這個(gè)數(shù)據(jù)，發(fā)覺其讀取出來的數(shù)據(jù)內(nèi)容已經(jīng)發(fā)生了轉(zhuǎn)變，就是不行重復(fù)讀。

幻讀和不行重復(fù)讀的區(qū)分在于幻讀是數(shù)據(jù)條數(shù)發(fā)生了變化（插入、刪除），而不行沖突讀在于數(shù)據(jù)發(fā)生了更新，前后讀取的結(jié)果不全都。

十一、事務(wù)隔離級(jí)別

臟讀、不行重讀度、幻讀，其實(shí)都是數(shù)據(jù)庫的全都性問題，必需由肯定的事務(wù)隔離機(jī)制來解決，InnoDB下的事務(wù)隔離級(jí)別有以下四種：

1、讀未提交(Readunmitted)

一個(gè)事務(wù)可以讀取到另一個(gè)事務(wù)未提交的修改。這會(huì)帶來臟讀、幻讀、不行重復(fù)讀問題。（基本沒用）

2、讀已提交(RC,ReadCommit)

一個(gè)事務(wù)只能讀取另一個(gè)事務(wù)已經(jīng)提交的修改。其避開了臟讀，但仍舊存在不行重復(fù)讀和幻讀問題。

3、可重復(fù)讀(RR,RepeatableRead)

同一個(gè)事務(wù)中多次讀取相同的數(shù)據(jù)返回的結(jié)果是一樣的。其避開了臟讀和不行重復(fù)讀問題，但幻讀依舊存在。

4、串行化(Serializable)

事務(wù)串行執(zhí)行。避開了以上全部問題。MySQL默認(rèn)的級(jí)別是:Repeatableread可重復(fù)讀，級(jí)別越高，數(shù)據(jù)越平安，但性能越低。

十二、MVCC

MVCC(Mutil-VersionConcurrencyControl)，多版本并發(fā)掌握，是為了查詢一些正在被另一個(gè)事務(wù)更新的行，并且可以看到它們被更新之前的值。這是一個(gè)可以用來增加并發(fā)性的強(qiáng)大的技術(shù)，由于這樣的一來的話查詢就不用等待另一個(gè)事務(wù)釋放鎖。

MVCC的實(shí)現(xiàn)是通過保存數(shù)據(jù)在某個(gè)時(shí)間點(diǎn)的快照(redolog)來實(shí)現(xiàn)的。這意味著一個(gè)事務(wù)無論運(yùn)行多長時(shí)間，在同一個(gè)事務(wù)里能夠看到數(shù)據(jù)全都的視圖。依據(jù)事務(wù)開頭的時(shí)間不同，同時(shí)也意味著在同一個(gè)時(shí)刻不同事務(wù)看到的相同表里的數(shù)據(jù)可能是不同的。

在每一行數(shù)據(jù)中額外保存兩個(gè)隱蔽的列：當(dāng)前行創(chuàng)建時(shí)的版本號(hào)和刪除時(shí)的版本號(hào)（可能為空）。這里的版本號(hào)并不是實(shí)際的時(shí)間值，而是系統(tǒng)版本號(hào)。每開頭新的事務(wù)，系統(tǒng)版本號(hào)都會(huì)自動(dòng)遞增。事務(wù)開頭時(shí)刻的系統(tǒng)版本號(hào)會(huì)作為事務(wù)的版本號(hào)，用來和查詢每行記錄的版本號(hào)進(jìn)行比較。

每個(gè)事務(wù)又有自己的版本號(hào)，這樣事務(wù)內(nèi)執(zhí)行CRUD操作時(shí)，就通過版本號(hào)的比較來達(dá)到數(shù)據(jù)版本掌握的目的。

默認(rèn)的隔離級(jí)別（REPEATABLEREAD）下，增刪查轉(zhuǎn)變成了這樣：

SELECT：讀取創(chuàng)建版本小于或等于當(dāng)前事務(wù)版本號(hào)，并且刪除版本為空或大于當(dāng)前事務(wù)版本號(hào)的記錄。這樣可以保證在讀取之前記錄是存在的。

INSERT：將當(dāng)前事務(wù)的版本號(hào)保存至行的創(chuàng)建版本號(hào)

UPDATE：新插入一行，并以當(dāng)前事務(wù)的版本號(hào)作為新行的創(chuàng)建版本號(hào)，同時(shí)將原記錄行的刪除版本號(hào)設(shè)置為當(dāng)前事務(wù)版本號(hào)

DELETE：將當(dāng)前事務(wù)的版本號(hào)保存至行的刪除版本號(hào)

十三、InnoDB索引結(jié)構(gòu)

Mysql索引用的B+樹作為數(shù)據(jù)結(jié)構(gòu)；Mysql中B+Tree在經(jīng)典B+Tree的基礎(chǔ)上進(jìn)行了優(yōu)化，增加了挨次訪問指針。在B+Tree的每個(gè)葉子節(jié)點(diǎn)增加一個(gè)指向相鄰葉子節(jié)點(diǎn)的指針，就形成了帶有挨次訪問指針的B+Tree。這樣就提高了區(qū)間訪問性能：假如要查詢key為從18到49的全部數(shù)據(jù)記錄，當(dāng)找到18后，只需順著節(jié)點(diǎn)和指針挨次遍歷就可以一次性訪問到全部數(shù)據(jù)節(jié)點(diǎn)，極大提到了區(qū)間查詢效率(無需返回上層父節(jié)點(diǎn)重復(fù)遍歷查找削減IO操作)。

MyISAMInnoDB都使用B+Tree索引結(jié)構(gòu)。但是底層索引存儲(chǔ)不同，MyISAM采納非聚簇索引，而InnoDB采納聚簇索引。

聚簇索引：索引和數(shù)據(jù)文件為同一個(gè)文件。

非聚簇索引：索引和數(shù)據(jù)文件分開的索引。

MyISAM索引原理：采納非聚簇索引-MyISAMmyi索引文件和myd數(shù)據(jù)文件分別，索引文件僅保存數(shù)據(jù)記錄的指針地址。葉子節(jié)點(diǎn)data域存儲(chǔ)指向數(shù)據(jù)記錄的指針地址。

MyISAM索引根據(jù)B+Tree搜尋，假如指定的Key存在，則取出其data域的值，然后以data域值-數(shù)據(jù)指針地址去讀取相應(yīng)數(shù)據(jù)記錄。幫助索引和主索引在結(jié)構(gòu)上沒有任何區(qū)分，只是主索引要求key是唯一的，而幫助索引的key可以重復(fù)。

InnoDB索引采納聚簇索引，InnoDB數(shù)據(jù)索引文件為一個(gè)idb文件，表數(shù)據(jù)文件本身就是主索引，相鄰的索引接近存儲(chǔ)。葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄(數(shù)據(jù)+主索引)。葉子節(jié)點(diǎn)直接存儲(chǔ)數(shù)據(jù)記錄，以主鍵id為key,葉子節(jié)點(diǎn)中直接存儲(chǔ)數(shù)據(jù)記錄。(底層存儲(chǔ)結(jié)構(gòu):**frm-表定義、ibd:innoDB數(shù)據(jù)索引文件)

由于InnoDB采納聚簇索引結(jié)構(gòu)存儲(chǔ)，索引InnoDB的數(shù)據(jù)文件需要根據(jù)主鍵聚集，因此InnoDB要求表必需有主鍵(MyISAM可以沒有)。假如沒有指定mysql會(huì)自動(dòng)選擇一個(gè)可以唯一表示數(shù)據(jù)記錄的列作為主鍵，假如不存在這樣的列，mysql自動(dòng)為InnoDB表生成一個(gè)隱含字段(6個(gè)字節(jié)長整型)作為主鍵。InnoDB的全部幫助索引都引用數(shù)據(jù)記錄的主鍵作為data域。

十四、InnoDB鎖類型

1、加鎖機(jī)制

樂觀鎖與悲觀鎖是兩種并發(fā)掌握的思想，可用于解決丟失更新問題。

2、樂觀鎖

每次去取數(shù)據(jù)，都