20歲的男生窮困潦倒，20歲的女生風(fēng)華正茂，沒有人會一直風(fēng)華正茂，也沒有人會一直窮困潦倒…

一、MySQL索引特性（重點）

1.磁盤、OS、MySQL，在進(jìn)行數(shù)據(jù)IO時三者的關(guān)系

1.
MySQL給用戶提供存取數(shù)據(jù)的服務(wù)，但數(shù)據(jù)在linux機(jī)器的磁盤外設(shè)上進(jìn)行存儲，而磁盤的讀取效率是比較低的，MySQL如何進(jìn)行數(shù)據(jù)存取以提高效率呢？這是一個重要的話題。
在硬件層面上，數(shù)據(jù)塊的基本單位是一個512字節(jié)的扇區(qū)，但OS在和磁盤進(jìn)行IO時，難道就直接以扇區(qū)為基本單位進(jìn)行數(shù)據(jù)IO了嗎？當(dāng)然不是！如果系統(tǒng)直接以硬件提供的扇區(qū)大小進(jìn)行交互，則OS的IO代碼就會和硬件強(qiáng)相關(guān)，但我們并不希望耦合度的提升，因為一旦耦合度較高，硬件改變軟件代碼也得隨之改變。同時單次IO大小為512字節(jié)還是太小了，這會增加IO的次數(shù)，從而導(dǎo)致效率降低，而實際OS的文件系統(tǒng)的基本IO單位并不是扇區(qū)，而是以塊為單位，基本大小是4KB，也就是每次讀寫8個扇區(qū)的大小。
而MySQL在和磁盤進(jìn)行交互時，基本數(shù)據(jù)單元的大小為16KB，在MySQL這里叫做page(注意和系統(tǒng)的page作區(qū)分，系統(tǒng)的page頁是4KB)，雖然我們說的是MySQL和磁盤進(jìn)行交互，但能和磁盤進(jìn)行交互的軟件層肯定只有操作系統(tǒng)，所以MySQL和磁盤每次進(jìn)行16KB大小的page交互，MySQL中的數(shù)據(jù)文件都是以page為單位保存在磁盤當(dāng)中的。
MySQL服務(wù)器在內(nèi)存中運行時，服務(wù)器內(nèi)部會申請一大塊buffer pool的內(nèi)存空間，來進(jìn)行各種數(shù)據(jù)文件page的緩存，也就是通過OS來和磁盤進(jìn)行IO交互，而OS實際在和磁盤進(jìn)行IO時的單位是4KB，但MySQL不管這些事情，MySQL只認(rèn)為自己和磁盤進(jìn)行交互時，就是以16KB大小為單位進(jìn)行交互的。

2.索引的理解

1.
下面是我創(chuàng)建的具有主鍵索引的一張表user，在插入數(shù)據(jù)時，實際我是對id進(jìn)行亂序插入的，但在查詢時，所有記錄卻變成有序的了。這個工作是誰做的呢？這么做的原因又是什么呢？

2.
為何MySQL與磁盤IO交互的基本單位是page呢？用多少加載多少不行嗎？這樣當(dāng)然是不行的，如果你要查詢id為1的記錄，那就需要一次IO，而查詢整表的數(shù)據(jù)就需要進(jìn)行5次IO，這樣的效率會非常低。如果將表中的多條記錄保存在一個page里面，實際IO時直接將一個page加載到buffer pool里面，這樣在后面查詢的時候，就不用再進(jìn)行IO了，直接在內(nèi)存中的buffer pool進(jìn)行讀取就可以，而且根據(jù)局部性原理，下一次訪問的數(shù)據(jù)大概率同樣也在該page里面，那下一次進(jìn)行讀取時，大概率不用在進(jìn)行IO了，直接在當(dāng)前buffer pool中的page內(nèi)讀取數(shù)據(jù)就可以了，這樣的效率就會高了，因為我們知道，導(dǎo)致IO效率低下的主要原因不是單次IO數(shù)據(jù)的大小，而是IO的次數(shù)多少，因為硬件與軟件相比，時間上要差別很多。

3.
我們在理解page時，同樣不能簡單的的將page理解為一個存儲數(shù)據(jù)的內(nèi)存塊，page可以理解為一個結(jié)構(gòu)體對象，保存著表中的一部分?jǐn)?shù)據(jù)，每個page之間用"數(shù)據(jù)結(jié)構(gòu)"連接起來，統(tǒng)一進(jìn)行管理。
單個page內(nèi)部的數(shù)據(jù)存儲是按照有序的方式來進(jìn)行排序的，其實就是用鏈表的方式將有序的數(shù)據(jù)一個個連接起來。

4.
我們知道了單個page內(nèi)部的記錄之間是按照鏈表進(jìn)行連接的，此時在查找某條記錄的時候，就需要逐條遍歷進(jìn)行查找，比如先一個一個的遍歷page，在每個page的內(nèi)部在逐個記錄的進(jìn)行遍歷，與每個記錄進(jìn)行比較，直到比較查找出要搜索的記錄，這樣的效率一定是非常低的。因為無論是page間，還是page內(nèi)部，我們都需要線性遍歷，直到通過比較找到要查找的記錄。

5.
所以，為了提升查找的速度，需要引入頁目錄的概念，通過目錄來快速定位記錄的位置本身，比如一本書有500頁，其中有50條目錄，每個目錄分別管理10頁的內(nèi)容，則50條目錄就可以管理整本書，以前查找某一頁的內(nèi)容，最壞需要查找500次，現(xiàn)在你最多需要查找60次，先遍歷目錄，然后再遍歷目錄所管理的10個頁，所以最壞也就是查找60次，這樣不就提升了查找的效率嗎？

6.
所以為了提升查找的效率，我們可以在單個page內(nèi)部引入頁目錄，以提高在page內(nèi)部查找記錄時的效率。所以這也就可以解釋為什么當(dāng)我們創(chuàng)建具有主鍵索引的表的時候，插入數(shù)據(jù)記錄是默認(rèn)有序的呢？其實就是為了提高搜索效率，因為只有當(dāng)記錄是有序的時候，目錄才有意義。

而當(dāng)表存儲的數(shù)據(jù)過多時，單個page的大小固定是16KB，則一定需要多個page來存儲對應(yīng)的數(shù)據(jù)，多個page之間我們也用鏈表管理起來，但page之間進(jìn)行線性遍歷的時候，也會造成效率低下的問題，因為page在線性遍歷的時候，是需要進(jìn)行磁盤IO的，磁盤IO更費時間，那如何解決這個問題呢？還是之前的思路，引入頁目錄！

我們可以在多個page的基礎(chǔ)上，再加入存儲頁目錄的page，而此時每個page內(nèi)部不再存儲數(shù)據(jù)，而是只存儲目錄項，每個目錄項存儲每個普通page的最小鍵值和目錄頁的地址，所以原來普通頁中存儲的數(shù)據(jù)是用戶數(shù)據(jù)，而目錄頁中存儲的數(shù)據(jù)是普通頁中的最小鍵值和普通頁的地址，每個普通page之間的鍵值也必須是有序的，因為目錄頁要對多個page進(jìn)行管理。
如果頂層的目錄頁過多還需要進(jìn)行遍歷的話，則我們還可以在目錄頁的基礎(chǔ)上再次增加目錄頁，對第二層的目錄頁進(jìn)行管理。

一個page能夠保存的數(shù)據(jù)大小是16KB，一個page所占字節(jié)數(shù)其實也就是16字節(jié)，也就是四個指針，prev和next指向前后的page，一個指針指向存儲用戶數(shù)據(jù)的鏈表的頭結(jié)點，另一個指針指向頁目錄的頭結(jié)點，所以一個目錄頁能夠管理的page個數(shù)就是16×1024÷16，也就是1024個page，轉(zhuǎn)換成數(shù)據(jù)的計量單位就是1024個16KB，也就是16MB的數(shù)據(jù)，最上層的目錄頁能夠管理的第二層的目錄頁的個數(shù)是1024個頁目錄，所以像上面的這樣一棵B+樹最多管理的數(shù)據(jù)就是1024×16MB的數(shù)據(jù)，也就是16GB的數(shù)據(jù)。

7.
真實的B+樹，只有葉子結(jié)點之間用鏈表連接起來，非葉子結(jié)點之間并無連接關(guān)系，通常在查找時，我們都會進(jìn)行范圍查找，葉子節(jié)點之間連接起來的話，是比較方便進(jìn)行范圍查找的，如果不連接起來，則如果當(dāng)前查找的葉子結(jié)點不是目標(biāo)葉子節(jié)點的話，則還需要重新自頂向下進(jìn)行查找，直到找到正確的葉子結(jié)點，這樣的效率很低。
葉子結(jié)點負(fù)責(zé)保存用戶數(shù)據(jù)，非葉子結(jié)點只保存目錄項，每個目錄項負(fù)責(zé)管理一個葉子結(jié)點。所以B+樹是一棵矮胖的樹，因為一層非葉子結(jié)點就可以管理很多的葉子結(jié)點，矮胖對于B+樹來說是很大的優(yōu)勢，因為查找目標(biāo)葉子結(jié)點路上經(jīng)過的結(jié)點數(shù)量越少，就代表從磁盤進(jìn)行IO的page就越少，查找的效率就越高。同時我們還有頁目錄的設(shè)計，這在遍歷數(shù)據(jù)的層面上來看也可以大大提高查找的效率

8.
即使創(chuàng)建出來的表沒有索引，但對表的增刪查改依舊是在表所對應(yīng)的B+樹結(jié)構(gòu)下進(jìn)行的，因為如果你沒有指定表的索引值，表也會有自己的默認(rèn)主鍵，只不過你在查詢的時候，是線性遍歷葉子結(jié)點進(jìn)行查詢的，并沒有利用B+樹的結(jié)構(gòu)進(jìn)行查詢，因為你沒有創(chuàng)建某一列字段為索引值，這也就是為什么沒有創(chuàng)建索引的表在查詢時速度慢的原因，因為時間復(fù)雜度是O(N)
同時每一個表，都會有對應(yīng)的B+樹數(shù)據(jù)結(jié)構(gòu)。

9.

B樹在面臨范圍查找時，優(yōu)勢就沒有了，同時B樹由于每個結(jié)點都會有data值，則每個非葉子結(jié)點管理的葉子節(jié)點會更少一些，所以B樹要比B+更高，自頂向下查找的時候，IO的次數(shù)會更多，效率會更低一些。

3.聚簇索引（索引和數(shù)據(jù)放在一起）和非聚簇索引（索引和數(shù)據(jù)分離存儲）

1.
MyISAM使用非聚簇索引，即將index和data分離存儲，葉子節(jié)點不直接存儲數(shù)據(jù)，而是只存儲數(shù)據(jù)的地址。

2.
MySQL除了建立主鍵索引外，還可以建立其他索引，例如普通索引，唯一鍵索引等等，除主鍵外的索引一般叫做輔助索引，MyISAM的輔助索引和主鍵索引沒什么區(qū)別，葉子節(jié)點同樣存儲的是記錄的地址。

3.
對于InnoDB來說，他的輔助索引和主鍵索引與MyISAM就不一樣了，InnoDB的輔助索引的葉子結(jié)點存儲的不是記錄，而是記錄所對應(yīng)的主鍵值，查找到對應(yīng)的主鍵值之后，再根據(jù)主鍵值回到主鍵索引表進(jìn)行查詢，直到查找到完整的記錄，所以像innodb這樣的聚簇索引，在建立輔助索引后，如果按照輔助索引的鍵值來進(jìn)行查找的話，則在獲得主鍵之后，還要進(jìn)行回表查詢。

4.索引的操作

1.
主鍵索引，唯一索引對應(yīng)的數(shù)據(jù)結(jié)構(gòu)是B+樹，普通索引對應(yīng)的數(shù)據(jù)結(jié)構(gòu)是B樹。

2.
下面是實際在進(jìn)行索引操作時，常用的一些SQL語句，比如刪除索引：alter table table_name drop XXX，構(gòu)建索引：alter table table_name add 索引(column_name)，查詢索引：show index from table_name，創(chuàng)建普通索引：create index index_name on table_name(column_name)

3.
除了給單個列字段創(chuàng)建索引之外，我們還可以給多個列字段同時創(chuàng)建索引，這樣的索引稱之為復(fù)合索引，當(dāng)我們頻繁的用一個字段查找另一個字段的時候，就可以給這兩個字段創(chuàng)建復(fù)合索引。

4.
全文索引要求表的存儲引擎必須是MyISAM，而且默認(rèn)的全文索引只支持英文，不支持中文。
在下面的sql語句創(chuàng)建出來的表里面，如果要查詢有沒有database數(shù)據(jù)，可以通過select * from articles where body like ‘%database%’，但這種查詢的方式并沒有使用到全文索引，這一點我們可以在SQL語句的前面加上explain查看一下，select * from articles where match(title, body) against (‘database’)

二、MySQL事務(wù)管理（重點）

1.什么是事務(wù)？

1.
如果對一張表的CURD不加控制，則在多客戶端，多sql語句提交的情況下，則一定會出現(xiàn)各種邏輯問題，一張票多賣了兩次，所以在mysqld服務(wù)器高并發(fā)的處理來自多個客戶端的sql語句請求時，如果不對sql語句加控制，則一定會出現(xiàn)各種各樣的問題，因為每個客戶端對MySQL服務(wù)器的操作都不是原子的。

2.
所以對表的CURD操作應(yīng)該滿足一些屬性，例如買票的過程應(yīng)該是原子的，買票之間不能互相影響，應(yīng)該隔離開，買完票之后，數(shù)據(jù)應(yīng)該作持久化，買前和買后應(yīng)該都是確定的狀態(tài)，要么成功，要么不成功，要具有回滾的能力，比如買票中途數(shù)據(jù)庫出現(xiàn)了問題，服務(wù)器斷電，數(shù)據(jù)庫被攻擊等等，數(shù)據(jù)庫應(yīng)該將狀態(tài)回滾到買票前的狀態(tài)。
而事務(wù)是由一組DML語句組成，多條DML語句就是為了完成一個任務(wù)，這一組DML語句是一個整體，要么全部執(zhí)行成功，要么全部執(zhí)行不成功，不會出現(xiàn)中間狀態(tài)，而MySQL中一定不止一個事務(wù)在運行，所以多個事務(wù)之間也需要控制，事務(wù)也不簡單的只是一組DML語句的集合，還需要滿足四個屬性。
原子性：一個事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會結(jié)束在中
間某個環(huán)節(jié)。事務(wù)在執(zhí)行過程中發(fā)生錯誤，會被回滾（Rollback）到事務(wù)開始前的狀態(tài)，就像這個
事務(wù)從來沒有執(zhí)行過一樣?；貪L有自動回滾和手動回滾兩種方式，當(dāng)MySQL服務(wù)宕機(jī)時，MySQL自己會自動回滾正在執(zhí)行到一半的事務(wù)
隔離性：數(shù)據(jù)庫允許多個并發(fā)事務(wù)同時對其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個事務(wù)
并發(fā)執(zhí)行時由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級別，包括讀未提交（ Read
uncommitted ）、讀提交（ read committed ）、可重復(fù)讀（ repeatable read ）和串行化
（ Serializable ）
持久性：事務(wù)處理結(jié)束后，對數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會丟失，其實就是將數(shù)據(jù)落盤，對數(shù)據(jù)作持久化。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性沒有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫可以自發(fā)性地完成預(yù)定的工作。
上面的四個性質(zhì)中，一致性其實是由前面的三個性質(zhì)共同實現(xiàn)的，所以只要滿足了前面的三個性質(zhì)，就滿足了事務(wù)的一致性。

3.
只有InnoDB存儲引擎支持事務(wù)。事務(wù)本質(zhì)上是為了應(yīng)用層提供服務(wù)的，應(yīng)用層不會關(guān)心具體事務(wù)實現(xiàn)的細(xì)節(jié)，他們只會使用事務(wù)。

4.
事務(wù)的提交方式一般有兩種，自動提交和手動提交，其實這個提交方式只會影響單條sql語句作為事務(wù)時的提交結(jié)果，我們后面會驗證這個結(jié)論。

2.事務(wù)常見操作方式

1.
讀未提交是事務(wù)隔離級別中最低的，在這樣的隔離級別下，可以很方便的演示事務(wù)的諸多性質(zhì)。

2.
begin之后事務(wù)就啟動了，begin之后的多條sql語句都屬于該事務(wù)內(nèi)的sql語句，在事務(wù)內(nèi)可以savepoint設(shè)置回滾點，MySQL支持定向回滾和直接回滾到最開始，如果沒有設(shè)置回滾點直接rollback的話，則會直接回滾到最開始，此時事務(wù)也會結(jié)束，所以事務(wù)的結(jié)束方式有兩種，一種是commit，另一種就是直接rollback回滾到最開始，此時事務(wù)默認(rèn)結(jié)束，所以回滾操作是事務(wù)結(jié)束的一種方式。

3.
只要事務(wù)commit之后，事務(wù)的操作就會被持久化到磁盤上，數(shù)據(jù)會落盤，不會受到數(shù)據(jù)庫宕機(jī)或客戶端崩潰的影響。
begin之后，也就是開啟一個事務(wù)之后，事務(wù)都是自己手動提交的，并不會自動提交，所以autocommit是ON還是OFF對begin之后，事務(wù)的提交方式都沒有影響。
單條語句在MySQL中會默認(rèn)為一個事務(wù)，autocommit影響的是單條語句作為事務(wù)時的提交方式，平常我們在命令行上敲單個的sql語句時，這一個語句會作為事務(wù)立馬提交，因為autocommit默認(rèn)是ON打開的，但如果將autocommit關(guān)閉之后，可以看到只要沒commit，所有的sql語句操作，右邊的客戶端是看不到的。

4.
所以autocommit影響的是單條sql語句作為事務(wù)時的提交情況。

3.事務(wù)隔離級別

1.
一個事務(wù)的執(zhí)行結(jié)果反饋給用戶的是原子性，即用戶只會感受到事務(wù)執(zhí)行前和執(zhí)行后的狀態(tài)變化，但實際事務(wù)在執(zhí)行時，一定是有過程的，所以多個事務(wù)在執(zhí)行時，可能會互相干擾，所以事務(wù)之間需要隔離，而隔離程度的體現(xiàn)就是隔離級別，像多個讀事務(wù)之間并發(fā)執(zhí)行時，隔離級別就可以降到最低，因為多個讀事務(wù)之間并不會互相影響。
但對于讀寫并發(fā)時，就需要其他的隔離級別來控制多個事務(wù)的執(zhí)行，其實最麻煩的并發(fā)場景就是讀寫并發(fā)了，因為像讀讀并發(fā)就不需要太高隔離級別，直接RU即可，而寫寫并發(fā)必須用最高的隔離級別，只能串行化執(zhí)行，但讀寫并發(fā)就比較麻煩，可能會使用到RC和RR級別。
而隔離級別的實現(xiàn)主要都是通過加鎖來實現(xiàn)的，不同的隔離級別使用不同的鎖

2.
設(shè)置事務(wù)隔離級別有兩種設(shè)置的方式，一種是設(shè)置全局的隔離級別，一種是設(shè)置當(dāng)前會話的隔離級別，會話的隔離級別可以簡寫為@@tx_isolation，每次新起一個MySQL會話時，默認(rèn)就會使用全局的隔離級別作為當(dāng)前會話的配置。

3.
讀未提交是隔離等級最低的隔離級別，雖然并發(fā)度比較高，但幾乎沒有加鎖，同時產(chǎn)生的問題也會比較多，例如臟讀，幻讀，不可重復(fù)讀等并發(fā)問題，臟讀是RU級別最典型的問題，讀取到一個事務(wù)還未提交的數(shù)據(jù)，很容易影響到上層的決策，所以這樣的并發(fā)問題是不可容忍的。

4.
讀提交隔離級別最典型的并發(fā)問題就是不可重復(fù)讀，因為重復(fù)讀可能會讀取到不一樣的結(jié)果，這也會影響上層決策。讀提交就是當(dāng)前事務(wù)可以讀取到其他事務(wù)提交后的內(nèi)容，這本身并沒有什么問題，但A事務(wù)提交后的內(nèi)容不應(yīng)該讓當(dāng)前正在和A事務(wù)并發(fā)的其他事務(wù)看到，應(yīng)該讓A事務(wù)執(zhí)行完之后，其他此時重新啟動的事務(wù)看到A事務(wù)提交后的內(nèi)容，所以讀提交也有他自己的并發(fā)問題。

5.
RR級別也就是可重復(fù)讀級別是MySQL默認(rèn)的隔離級別，只要你重啟mysqld服務(wù)器，隔離級別就會默認(rèn)設(shè)置為RR級別。
在MySQL下可以看到終端B并沒有出現(xiàn)并發(fā)問題，但實際上在其他的數(shù)據(jù)庫中，事務(wù)隔離級別在RR級別下還會有幻讀的問題，只不過MySQL通過間隙鎖+行鎖的方式解決了幻讀的問題，其他的一些數(shù)據(jù)庫沒有解決。
幻讀指的是其他事務(wù)insert的數(shù)據(jù)可能在重復(fù)讀的情況下被讀取出來，因為隔離是對數(shù)據(jù)進(jìn)行加鎖完成的，而insert的數(shù)據(jù)本身并不存在，所以一般加鎖的方式無法解決幻讀問題，但MySQL解決了。
值得注意的是，幻讀指的是其他事務(wù)insert時，重復(fù)讀可能會讀取出來插入數(shù)據(jù)后的表，就像出現(xiàn)了幻覺一樣。如果其他事務(wù)update，delete數(shù)據(jù)，則重復(fù)讀還是可以避免產(chǎn)生并發(fā)問題的，因為他們操縱的是已有的數(shù)據(jù)。
其實在RR級別下，為什么讀取是可重復(fù)讀的呢？sql第一次讀取的時候，就直接給數(shù)據(jù)加上行鎖，保證其他事務(wù)對數(shù)據(jù)無法進(jìn)行update和delete，同時給數(shù)據(jù)加上間隙鎖，如果表有索引，則在當(dāng)前記錄的附近加上間隙鎖，防止insert數(shù)據(jù)的行為。

6.
串行化的隔離級別太簡單粗暴了，無論事務(wù)是執(zhí)行什么操作，都必須被嚴(yán)格強(qiáng)制以串行化的方式執(zhí)行，這樣的并發(fā)度太低，效率很慢，實際中不會采用這樣的方案，可能會產(chǎn)生超時或鎖競爭的問題產(chǎn)生，這種隔離級別太極端了。

7.
從表格就可以看到隔離級別越高，產(chǎn)生的并發(fā)問題就越少，但隨之?dāng)?shù)據(jù)庫的并發(fā)性能也就越低，所以往往需要在安全和性能之間尋找一個平衡點，這個平衡點就是RR可重復(fù)讀隔離級別，同時也是數(shù)據(jù)庫默認(rèn)的隔離級別。
一致性從技術(shù)角度來看，是依靠AID的技術(shù)來實現(xiàn)的，但其實一致性和用戶邏輯強(qiáng)相關(guān)，一致性不僅僅需要在技術(shù)層面上支持好，同樣也需要程序員在用戶邏輯層面維護(hù)好數(shù)據(jù)庫事務(wù)的一致性。

4.MVCC（提高讀寫并發(fā)時數(shù)據(jù)庫的性能）

1.
多版本并發(fā)控制MVCC是一個并發(fā)效率很高的讀寫沖突的控制方式，在并發(fā)讀寫數(shù)據(jù)庫時，MVCC可以做到在讀操作時，不用阻塞寫操作，在寫操作時，不用阻塞讀操作，極大的提高了數(shù)據(jù)庫讀寫并發(fā)時的性能。
此外MVCC也解決了RR級別下的幻讀問題，以及臟讀，不可重復(fù)讀等問題。
MVCC是一把提升數(shù)據(jù)庫性能的利劍。

2.
MVCC為事務(wù)分配單向增長的事務(wù)ID，事務(wù)對數(shù)據(jù)修改時，會保存一個版本，版本與事務(wù)ID關(guān)聯(lián)。
所以每個事務(wù)都有自己的事務(wù)ID，可以根據(jù)事務(wù)ID的大小決定事務(wù)到來的先后順序，事務(wù)ID越大，事務(wù)越新。
mysqld一定會面臨處理多個事務(wù)的情況，所以mysqld要對多個事務(wù)作管理，也就是先描述再組織，所以每個事務(wù)都會對應(yīng)著一個結(jié)構(gòu)體對象。

3.
MVCC實現(xiàn)的表中會有三個隱藏列字段，DB_TRX_ID用于記錄創(chuàng)建表中當(dāng)前這條記錄的事務(wù)ID，或最后一次修改當(dāng)前這條記錄的事務(wù)ID，DB_ROLL_PTR指針指向這條記錄的上一個歷史版本，DB_ROW_ID就是我們很早之前談?wù)撍饕龝r的隱藏主鍵，如果表中沒有主鍵，則MySQL默認(rèn)會創(chuàng)建一棵以DB_ROW_ID為鍵值的聚簇索引，也就是B+樹。
除了上述三個隱藏列字段之外，還有一個標(biāo)志刪除位flag。

4.
undo log也是實現(xiàn)MVCC的一個重要角色，可以將其理解為一個，保存事務(wù)對表中記錄更改的版本鏈的緩沖區(qū)，事務(wù)的隔離和回滾操作的實現(xiàn)都離不開undo log

5.
增刪改都會形成當(dāng)前記錄的歷史版本，select沒必要形成歷史版本，但select在讀取的時候有兩種選擇，一種是讀取最新的記錄，另一種是讀取歷史版本的記錄，也就是當(dāng)前讀和快照讀，快照讀保證了讀寫并發(fā)的安全性和并發(fā)度，不需要通過加鎖來實現(xiàn)，因為快照讀和增刪改操作的數(shù)據(jù)是不同的記錄版本，一個是歷史數(shù)據(jù)，一個是當(dāng)前數(shù)據(jù)。
undo log中的一條條歷史版本，稱為一個個的快照，一旦事務(wù)提交之后，這些快照就會被free掉，所以undo log中的記錄是有進(jìn)有出的，不用擔(dān)心undo log會被記錄打滿。

實現(xiàn)事務(wù)隔離不僅僅需要歷史版本數(shù)據(jù)，還離不開read view，通過read view才可以真正實現(xiàn)出事務(wù)隔離，也就是不同的事務(wù)在select讀的時候，應(yīng)該看到怎樣的版本信息。

讀視圖read view是事務(wù)在第一次執(zhí)行快照讀的時候就產(chǎn)生了的，而read view其實就是類ReadView實例化出來的對象，一般習(xí)慣稱之為快照，在這個快照對象內(nèi)部有四個成員變量是非常重要的，也是實現(xiàn)事務(wù)隔離的重要技術(shù)實現(xiàn)。
m_ids保存read view生成時，系統(tǒng)中正在活躍的事務(wù)ID
up_limit_id保存的是m_ids中事務(wù)ID最小的事務(wù)ID
low_limit_id保存的是當(dāng)前已出現(xiàn)過的事務(wù)ID最大值+1，也就是系統(tǒng)即將分配給下一個事務(wù)的ID
creator_trx_id保存的是創(chuàng)建當(dāng)前記錄的事務(wù)ID
所以我們現(xiàn)在左手有read view，右手有歷史版本鏈中所有的記錄的DB_TRX_ID，而當(dāng)前事務(wù)select讀的時候，應(yīng)該讀取到什么樣的版本記錄，其實就是由read view和歷史版本鏈所決定的。

下面的圖其實就已經(jīng)詮釋了事務(wù)可見性的算法。
如果版本鏈中的某個記錄對應(yīng)的ID大于或等于limit ID，說明這個記錄對應(yīng)的事務(wù)是在創(chuàng)建快照之后新起的事務(wù)，那么這條記錄就不應(yīng)該被創(chuàng)建快照的事務(wù)所看到。
如果版本鏈中的某個記錄對應(yīng)的ID小于up ID或等于creator_trx_id，則說明這個記錄是在創(chuàng)建快照之前早就已經(jīng)提交的事務(wù)，或者這條記錄本身就是由創(chuàng)建快照的事務(wù)所做的更改，則說明這條記錄應(yīng)該被當(dāng)前事務(wù)所看到。
如果版本鏈中的某個記錄對應(yīng)的ID在m_ids里面，那就說明該記錄對應(yīng)的事務(wù)還沒提交完呢，創(chuàng)建read view的事務(wù)不應(yīng)該看到這條記錄，一旦看到那就是臟讀。如果版本鏈中的某個記錄對應(yīng)的ID不在m_ids里面，同時小于limit ID，大于up ID，則說明該事務(wù)已經(jīng)提交了，但為什么他的ID不小于up ID呢，其實是因為該事務(wù)到來的時間比較晚，但該事務(wù)是短事務(wù)，執(zhí)行的很快，快照形成時，該事務(wù)早已執(zhí)行完畢了，所以這樣的版本記錄是可見的。

下面是源碼對應(yīng)的事務(wù)可見性的處理策略，changes_visible是MySQL判斷當(dāng)前事務(wù)快照讀應(yīng)該看到什么樣記錄的一個函數(shù)，trx_id_t是外面?zhèn)鞯囊粋€參數(shù)，該參數(shù)應(yīng)該被不斷更新為版本鏈中的各個記錄所對應(yīng)的事務(wù)id，以判斷該記錄是否該被調(diào)用changes_visible函數(shù)的事務(wù)所看到。
例如當(dāng)id<m_up_limit_id或id= =m_creator_trx_id時，當(dāng)前記錄應(yīng)該被看到，所以返回true，id>=m_low_limit_id，當(dāng)前記錄不應(yīng)該被看到，返回false，或者如果m_ids里面的事務(wù)是空的，同時Id是小于m_low_limit_id的話，則說明該記錄對應(yīng)的事務(wù)是早就已經(jīng)提交了的，是可以看到的。
如果上面的判斷條件都不滿足的話，判斷當(dāng)前記錄對應(yīng)id是否在m_ids里面，如果在則不能看到，返回false，如果不在則可以看到，返回true。
如果當(dāng)前記錄不應(yīng)該被看到，則繼續(xù)遍歷下一個版本鏈中的記錄。

值得注意的是，read view在第一次事務(wù)進(jìn)行快照讀的時候就形成了，在事務(wù)運行期間，像RR級別就會一直使用該read view。
下面的實驗是典型的一個trx_id不在m_ids里面，同時也小于limit_id，大于up_id的情況，這種情況的記錄是可見的，當(dāng)前事務(wù)可以看到。

所以我們可以總結(jié)一下MVCC實現(xiàn)的基本原理，以及事務(wù)快照讀可見性的原理，通過給每個事務(wù)對記錄的增刪改都分配對應(yīng)的歷史版本信息，就像寫時拷貝一樣，只要對記錄作修改，則在undo log中形成該事務(wù)的歷史版本鏈，同時給每個事務(wù)根據(jù)到來順序的不同分配一個逐漸增長的事務(wù)id，當(dāng)事務(wù)在快照讀的時候會首先形成一個快照read view，read view對象內(nèi)部有四個重要的成員變量，分別是up_limit_id，low_limit_id，creator_id，m_ids，通過這四個字段和undo log里面的版本鏈記錄之間的比較，得到創(chuàng)建read view的事務(wù)應(yīng)該看到什么樣的記錄。

5.RR與RC的本質(zhì)區(qū)別

1.
在MVCC下，快照讀并不會加鎖，所以他和寫并不是互斥的。在事務(wù)A作出修改后，提交之前，事務(wù)B形成快照，則修改的記錄對應(yīng)的事務(wù)ID剛好在快照內(nèi)的m_ids里面，則事務(wù)B不應(yīng)該看到事務(wù)A對記錄做出的修改。如果想要看到最新的數(shù)據(jù)，則可以使用加共享鎖的方式來讀取，此時讀取到的數(shù)據(jù)就是最新的，但這并不意味著最新的就一定是正確的，我們不希望一個事務(wù)在運行期間讀取到的結(jié)果發(fā)生不同。

2.
在事務(wù)B快照讀之前，事務(wù)A完成了對記錄的修改并提交，那么事務(wù)B就應(yīng)該在他自己執(zhí)行的整個期間都一直能夠看到事務(wù)A對記錄做出的修改，因為事務(wù)A都已經(jīng)提交了，無論是RR還是RC都應(yīng)該看到。
如果從MVCC實現(xiàn)原理角度來講的話，則可能是兩種情況，一種是事務(wù)A的ID小于up_limit_id或事務(wù)ID不在m_ids里面，也就是說要么事務(wù)A到來的特別早，并提前提交了，形成快照的事務(wù)應(yīng)該看到，或者是事務(wù)A到來的比形成快照的事務(wù)晚，但事務(wù)A是一個短事務(wù)，在當(dāng)前事務(wù)形成快照之前，事務(wù)A就執(zhí)行完并且提交了，那么快照事務(wù)也應(yīng)該看到。所以，無論對應(yīng)哪種情況，事務(wù)B都應(yīng)該看到事務(wù)A對記錄做出的修改。

需要直到的是，事務(wù)讀到的數(shù)據(jù)是新的還是舊的并不重要，事務(wù)在執(zhí)行期間讀到的數(shù)據(jù)始終都不變這才是重要的！

3.
RR與RC的本質(zhì)區(qū)別就是快照的形成，RR級別下事務(wù)第一次形成快照后，則在后面事務(wù)整個的執(zhí)行期間，會一直使用該快照，這也就是為什么RR級別下是可重復(fù)讀的，因為read view對象一直不變。而RC級別下事務(wù)每次快照讀的時候，都會重新生成新的read view，這也就是為什么RC級別下可以看到其他事務(wù)提交之后的內(nèi)容，因為RC級別會生成新的read view，重新進(jìn)行可見性的判斷。
兩者之間的差別再說的本質(zhì)一些，RR級別下第一次快照讀的時候，會new一個read view對象，并且在該事務(wù)執(zhí)行期間一直使用這個read view對象，RC級別下每次快照讀的時候，都會重新new一個read view對象，并delete掉原來的read view對象，所以在該事務(wù)執(zhí)行期間，一直使用不斷更新的read view對象。

RU級別一直都是當(dāng)前讀，沒有加鎖控制，RR和RC都是快照讀（MVCC），串行化也是當(dāng)前讀，不過是加鎖控制的，所以串行化無論是讀讀之間，還是讀寫之間，還是寫寫之間都是需要競爭鎖的，這也就是為什么串行化并發(fā)度低的原因，因為無論什么操作都加鎖。

三、MySQL視圖特性

1.
視圖實際中用的并不多，但我們也稍微了解一下。
創(chuàng)建視圖是根據(jù)select查詢結(jié)果來創(chuàng)建的，create view view_name as select ……，創(chuàng)建出來的視圖其實就是一張表，如果修改視圖中的數(shù)據(jù)，則原表中的數(shù)據(jù)也會隨之被修改，所以一般而言，視圖只用來查詢，不會用來作修改，因為基表中的數(shù)據(jù)會受牽連。
如果你高頻的只查詢一部分表的數(shù)據(jù)，則可以選擇給這一部分?jǐn)?shù)據(jù)創(chuàng)建視圖，下次查詢時直接從視圖中查詢即可。

四、MySQL用戶管理

1.
MySQL安裝好之后會有一個默認(rèn)的database叫做mysql，在該數(shù)據(jù)庫內(nèi)部會存在一個user表，該表中存放了當(dāng)前mysqld服務(wù)下所有的用戶的信息，包括很多的字段，最常用的幾個字段就是host，user，authentication_string，用戶密碼是經(jīng)過password()函數(shù)加密過的，所以我們看不懂。

2.
新建用戶時，要指定用戶名，主機(jī)名，以及登錄時需要認(rèn)證的密碼，新建用戶之后，為了讓我們新建的用戶生效，最好刷新一下權(quán)限flush privileges。
刪除用戶時，需要指定用戶名和host登錄方法。
root用戶可以更改所有用戶的登錄密碼，所以修改秘密時，建議直接使用root用戶的身份來修改所有用戶的密碼。

我們可以指定的給用戶賦予權(quán)限，比如對某一個具體的庫，某一個具體的表，賦予查詢，增加，刪除，修改等權(quán)限，賦予權(quán)限時也可以指定密碼，不過盡量在創(chuàng)建用戶時就指定好密碼，不要在賦權(quán)時指定密碼。回收權(quán)限可以使用revoke指令來回收。

五、API和圖形化界面的客戶端

1.
除了我們之前一直用的命令行式的客戶端之外，還可以使用C語言連接數(shù)據(jù)庫，使用API的方式來作為客戶端訪問數(shù)據(jù)庫服務(wù)，我們可以直接使用yum install -y mysql-community-server下載，一般下載的時候會自動給我們把開發(fā)包下載好，如果沒有開發(fā)包，則可以使用yum install -y mysql-devel手動下載開發(fā)包，下載安裝好之后我們就可以使用vscode，以API的方式來連接并使用數(shù)據(jù)庫了。

安裝好之后，在/usr/include/mysql下會存在C語言連接數(shù)據(jù)庫時使用的頭文件，在/lib64/mysql下會存在著連接數(shù)據(jù)庫需要的動態(tài)庫和靜態(tài)庫。

2.
準(zhǔn)備好開發(fā)環(huán)境之后，我們嘗試連接一下數(shù)據(jù)庫。
使用mysql.h頭文件，指明編譯器搜索頭文件時的路徑。連接庫的時候，需要指明鏈接器的鏈接路徑，以及需要鏈接的庫的名稱，這些字段都需要在makefile里面確定好。

鏈接庫前必須先調(diào)用mysql_init進(jìn)行初始化，mysql_init會返回一個類似于文件描述符的東西，使用myfd就可以對數(shù)據(jù)庫進(jìn)行操作。初始化好之后，調(diào)用mysql_real_connect進(jìn)行數(shù)據(jù)庫的連接，連接時需要傳遞對應(yīng)的參數(shù)，例如密碼，用戶名，登陸方式，database名稱，端口號等等

mysql_query用于給數(shù)據(jù)庫下發(fā)MySQL命令，第一個參數(shù)為myfd，也就是那個文件描述符，下發(fā)的指令為增刪改時，我們返回給客戶端的結(jié)果只需要是Query OK or Query failed 即可，這樣處理起來并不難，我們可以通過API的方式自己實現(xiàn)一個類似于命令行式的客戶端，難度并不大，只需要調(diào)用mysql_query即可，難的是select查詢語句，因為查詢是需要返回命令行處理結(jié)果的，所以想要實現(xiàn)查詢命令的下發(fā)，僅僅有mysql_query是不夠的，還需要其他API的幫助。

下面我們來實現(xiàn)當(dāng)下發(fā)的語句為查詢語句時，如何打印出查詢sql的返回結(jié)果。
mysql_store_result會將語句的執(zhí)行結(jié)果放到res里面，我們只需要將信息從res提取出來并打印到顯示器上即可，mysql_num_rows用于獲取結(jié)果集的行數(shù)，mysql_nums_fields用于獲取結(jié)果集的列數(shù)，mysql_fetch_fields用于獲取列名是什么，返回的指針會指向一個一維數(shù)組，一維數(shù)組中的內(nèi)容就是各個列字段的名稱。mysql_fetch_row獲取結(jié)果集中的內(nèi)容，連續(xù)調(diào)用mysql_fetch_row，他會像迭代器一樣，自動幫我們跳轉(zhuǎn)到下一行，并將下一行的地址返回到line里面，所以我們依次打印出每行的內(nèi)容即可。

mysql_fetch_field用起來不如mysql_fetch_fields方便，后者直接返回一個一維數(shù)組即可，我們遍歷這個數(shù)組將數(shù)組的內(nèi)容輸出即可，這樣比較方便。

下面是具體的代碼實現(xiàn)，別忘了關(guān)閉mysql鏈接和釋放掉結(jié)果集res，res是動態(tài)申請的，所以也要釋放。

下面是執(zhí)行查詢語句后的結(jié)果

3.
MySQL Workbench用起來只能說還湊合，感覺還是不如直接敲命令行爽，不過我們實際工作當(dāng)中肯定用的都是圖形化界面來訪問數(shù)據(jù)庫服務(wù)。使用的成本也比較低，大家可以自己下載一個玩一玩

文章來源地址http://www.zghlxwxcb.cn/news/detail-621253.html

到了這里，關(guān)于【MySQL】MySQL索引、事務(wù)、用戶管理的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！