0.基礎(chǔ)概念

a.MyISAM VS InnoDB

簡單來說：

• MyISAM是5.5版本之前的默認(rèn)引擎，不支持事務(wù)；不支持行級(jí)鎖；崩潰后無法安全恢復(fù)；適合讀密集的場景；
• InnoDB是5.5版本之后的默認(rèn)引擎，支持事務(wù)；支持行級(jí)鎖；崩潰后可以安全恢復(fù)(crash-safe)。

詳細(xì)介紹

1. 是否支持行級(jí)鎖？
   MyISAM只支持表級(jí)鎖，InnoDB支持表級(jí)鎖和行級(jí)鎖，默認(rèn)為行級(jí)鎖；
2. 是否支持事務(wù)和奔潰后的安全恢復(fù)？
   MyISAM強(qiáng)調(diào)的是性能，每次查詢都是原子操作，其執(zhí)行速度比Innodb更快，但是不支持事務(wù)。
   InnoDB支持事務(wù)和奔潰后的修復(fù)能力；
3. 是否支持外鍵？
   MyISAM不支持外鍵；InnoDB支持外鍵；
4. 是否支持MVCC？
   僅InnoDB支持。
5. MyISAM一定比InnoDB快嗎？
   不一定，某些使用了聚簇索引或者訪問的數(shù)據(jù)都在內(nèi)存中的時(shí)候，InnoDB比MyISAM要快。

1.索引

1.1 基礎(chǔ)知識(shí)

1.1.1索引的分類

索引也可以分為普通索引和唯一性索引

• 普通索引:最基本的索引，它沒有任何限制
• 唯一性索引:與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是組合索引，則列值的組合必須唯一。

聚集索引和非聚集索引

a.聚集索引

定義：數(shù)據(jù)行的物理順序與列值（一般是主鍵的那一列）的邏輯順序相同，一個(gè)表中只能擁有一個(gè)聚集索引。
數(shù)據(jù)行的物理順序與列值的順序相同，如果我們查詢id比較靠后的數(shù)據(jù)，那么這行數(shù)據(jù)的地址在磁盤中的物理地址也會(huì)比較靠后。而且由于物理排列方式與聚集索引的順序相同，所以也就只能建立一個(gè)聚集索引了。

索引的葉子節(jié)點(diǎn)就是對(duì)應(yīng)的數(shù)據(jù)節(jié)點(diǎn)，可以直接獲取到對(duì)應(yīng)的全部列的數(shù)據(jù)，而非聚集索引在索引沒有覆蓋到對(duì)應(yīng)的列的時(shí)候需要進(jìn)行二次查詢.

b.非聚集索引

定義：該索引的邏輯順序與磁盤上行的物理存儲(chǔ)順序不同，一個(gè)表中可以擁有多個(gè)非聚集索引。
葉子節(jié)點(diǎn)存的是字段的值，通過這個(gè)非聚集索引的鍵值找到對(duì)應(yīng)的聚集索引字段的值，再通過聚集索引鍵值找到表的某行

1.1.2 索引的缺點(diǎn)

• 雖然索引大大提高了查詢速度，但是卻會(huì)降低更新表的速度，如對(duì)表進(jìn)行INSERT、UPDATE和DELETE。因?yàn)楦卤頃r(shí)，MySQL不僅要保存數(shù)據(jù)，還要維護(hù)索引文件。
• 建立索引會(huì)占用磁盤空間。

1.2 B+樹

索引結(jié)構(gòu)有BTree索引和哈希索引，在絕大多數(shù)需求為單條記錄查詢的時(shí)候，選擇哈希索引，查詢性能最快，其他場景還是使用BTree索引。

1.2.1 B+樹的誕生過程

1.需求

根據(jù)某個(gè)值查找數(shù)據(jù)，比如 select * from user where id=1234；
根據(jù)區(qū)間值來查找某些數(shù)據(jù)，比如 select * from user where id > 1234 and id < 2345。

2.數(shù)據(jù)結(jié)構(gòu)選型

• 散列表。散列表的查詢性能很好，時(shí)間復(fù)雜度是 O(1)。但是，散列表不能支持按照區(qū)間快速查找數(shù)據(jù)。
• 平衡二叉查找樹。盡管平衡二叉查找樹查詢的性能也很高，時(shí)間復(fù)雜度是 O(logn)。而且，對(duì)樹進(jìn)行中序遍歷，我們還可以得到一個(gè)從小到大有序的數(shù)據(jù)序列，但這仍然不足以支持按照區(qū)間快速查找數(shù)據(jù)。
• 跳表。跳表是在鏈表之上加上多層索引構(gòu)成的。它支持快速地插入、查找、刪除數(shù)據(jù)，對(duì)應(yīng)的時(shí)間復(fù)雜度是 O(logn)。并且，跳表也支持按照區(qū)間快速地查找數(shù)據(jù)。我們只需要定位到區(qū)間起點(diǎn)值對(duì)應(yīng)在鏈表中的結(jié)點(diǎn)，然后從這個(gè)結(jié)點(diǎn)開始，順序遍歷鏈表，直到區(qū)間終點(diǎn)對(duì)應(yīng)的結(jié)點(diǎn)為止，這期間遍歷得到的數(shù)據(jù)就是滿足區(qū)間值的數(shù)據(jù)。

3.推演過程

3.1 二叉樹的改造

為了讓二叉查找樹支持按照區(qū)間來查找數(shù)據(jù)，對(duì)它進(jìn)行改造：

• 樹中的節(jié)點(diǎn)并不存儲(chǔ)數(shù)據(jù)本身，而是只作為索引。

• 我們把每個(gè)葉子節(jié)點(diǎn)串在一條鏈表上，鏈表中的數(shù)據(jù)是從小到大有序的。

  
  
  image.png
  
  

  改造之后，如果我們要求某個(gè)區(qū)間的數(shù)據(jù)。我們只需要拿區(qū)間的起始值，在樹中進(jìn)行查找，當(dāng)查找到某個(gè)葉子節(jié)點(diǎn)之后，我們?cè)夙樦湵硗蟊闅v，直到鏈表中的結(jié)點(diǎn)數(shù)據(jù)值大于區(qū)間的終止值為止。所有遍歷到的數(shù)據(jù)，就是符合區(qū)間值的所有數(shù)據(jù)。

  
  
  image.png

3.2 B+樹的誕生

• 二叉樹是搜索效率最高的，但是實(shí)際上大多數(shù)的數(shù)據(jù)庫存儲(chǔ)卻并不使用二叉樹。其原因是，索引不止存在內(nèi)存中，還要寫到磁盤上。
• 我們要為幾千萬、上億的數(shù)據(jù)構(gòu)建索引，如果將索引存儲(chǔ)在內(nèi)存中，盡管內(nèi)存訪問的速度非常快，查詢的效率非常高，但是，占用的內(nèi)存會(huì)非常多。
• 為了節(jié)省內(nèi)存，如果把樹存儲(chǔ)在硬盤中，那么每個(gè)節(jié)點(diǎn)的讀?。ɑ蛘咴L問），都對(duì)應(yīng)一次磁盤 IO 操作。樹的高度就等于每次查詢數(shù)據(jù)時(shí)磁盤 IO 操作的次數(shù)。比起內(nèi)存讀寫操作，磁盤 IO 操作非常耗時(shí)，所以我們優(yōu)化的重點(diǎn)就是盡量減少磁盤 IO 操作，也就是盡量降低樹的高度。
• 為了讓一個(gè)查詢盡量少地讀磁盤，就必須讓查詢過程訪問盡量少的數(shù)據(jù)塊。那么，我們就不應(yīng)該使用二叉樹，而是要使用“N 叉”樹。這里，“N 叉”樹中的“N”取決于數(shù)據(jù)塊的大小。對(duì)于相同個(gè)數(shù)的數(shù)據(jù)構(gòu)建 N 叉樹索引，N 叉樹中的 N 越大，那樹的高度就越小，那 N 叉樹中的 N 是不是越大越好呢？到底多大才最合適呢？

不管是內(nèi)存中的數(shù)據(jù)，還是磁盤中的數(shù)據(jù)，操作系統(tǒng)都是按頁（一頁大小通常是 4KB）來讀取的，一次會(huì)讀一頁的數(shù)據(jù)。如果要讀取的數(shù)據(jù)量超過一頁的大小，就會(huì)觸發(fā)多次 IO 操作。所以，我們?cè)谶x擇 N 大小的時(shí)候，要盡量讓每個(gè)節(jié)點(diǎn)的大小等于一個(gè)頁的大小。讀取一個(gè)節(jié)點(diǎn)，只需要一次磁盤 IO 操作。

1.2.2 索引的分析

數(shù)據(jù)的寫入過程，會(huì)涉及索引的更新，這是索引導(dǎo)致寫入變慢的主要原因。
對(duì)于一個(gè) B+ 樹來說，N 值是根據(jù)頁的大小事先計(jì)算好的，也就是說，每個(gè)節(jié)點(diǎn)最多只能有 N 個(gè)子節(jié)點(diǎn)。在往數(shù)據(jù)庫中寫入數(shù)據(jù)的過程中，這樣就有可能使索引中某些節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)超過 N，這個(gè)節(jié)點(diǎn)的大小超過了一個(gè)頁的大小，讀取這樣一個(gè)節(jié)點(diǎn)，就會(huì)導(dǎo)致多次磁盤 IO 操作。我們?cè)撊绾谓鉀Q這個(gè)問題呢？

我們只需要將這個(gè)節(jié)點(diǎn)分裂成兩個(gè)節(jié)點(diǎn)。但是，節(jié)點(diǎn)分裂之后，其上層父節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)就有可能超過 N 個(gè)。不過這也沒關(guān)系，我們可以用同樣的方法，將父節(jié)點(diǎn)也分裂成兩個(gè)節(jié)點(diǎn)。這種級(jí)聯(lián)反應(yīng)會(huì)從下往上，一直影響到根節(jié)點(diǎn)。這個(gè)分裂過程，你可以結(jié)合著下面這個(gè)圖一塊看，會(huì)更容易理解（圖中的 B+ 樹是一個(gè)三叉樹。我們限定葉子節(jié)點(diǎn)中，數(shù)據(jù)的個(gè)數(shù)超過 2 個(gè)就分裂節(jié)點(diǎn)；非葉子節(jié)點(diǎn)中，子節(jié)點(diǎn)的個(gè)數(shù)超過 3 個(gè)就分裂節(jié)點(diǎn)）。

image.png

實(shí)際上，不光寫入數(shù)據(jù)會(huì)變慢，刪除數(shù)據(jù)也會(huì)變慢。這是為什么呢？

我們?cè)趧h除某個(gè)數(shù)據(jù)的時(shí)候，也要對(duì)應(yīng)的更新索引節(jié)點(diǎn)。頻繁的數(shù)據(jù)刪除，就會(huì)導(dǎo)致某些節(jié)點(diǎn)中，子節(jié)點(diǎn)的個(gè)數(shù)變得非常少，長此以往，如果每個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)都比較少，勢必會(huì)影響索引的效率。在 B+ 樹中，這個(gè)閾值等于 N/2。如果某個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)小于 N/2，我們就將它跟相鄰的兄弟節(jié)點(diǎn)合并。不過，合并之后節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)有可能會(huì)超過 N。針對(duì)這種情況，我們可以借助插入數(shù)據(jù)時(shí)候的處理方法，再分裂節(jié)點(diǎn)。

1.2.3 B+樹 VS B樹

B+ 樹的特點(diǎn)：

• 每個(gè)節(jié)點(diǎn)中子節(jié)點(diǎn)的個(gè)數(shù)不能超過 N，也不能小于 N/2；
• 根節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)可以不超過 N/2，這是一個(gè)例外；
• N 叉樹只存儲(chǔ)索引，并不真正存儲(chǔ)數(shù)據(jù)，這個(gè)有點(diǎn)兒類似跳表；
• 通過鏈表將葉子節(jié)點(diǎn)串聯(lián)在一起，這樣可以方便按區(qū)間查找；
• 一般情況，根節(jié)點(diǎn)會(huì)被存儲(chǔ)在內(nèi)存中，其他節(jié)點(diǎn)存儲(chǔ)在磁盤中。

B 樹跟 B+ 樹的不同點(diǎn)主要集中在這幾個(gè)地方：

• B+ 樹中的節(jié)點(diǎn)不存儲(chǔ)數(shù)據(jù)，只是索引，而 B 樹中的節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)；
• B 樹中的葉子節(jié)點(diǎn)并不需要鏈表來串聯(lián)。也就是說，B 樹只是一個(gè)每個(gè)節(jié)點(diǎn)的子節(jié)點(diǎn)個(gè)數(shù)不能小于 N/2 的 N 叉樹。

1.3 InnoDB索引模型

在 InnoDB 中，表都是根據(jù)主鍵順序以索引的形式存放的，這種存儲(chǔ)方式的表稱為索引組織表。
InnoDB 使用了 B+ 樹索引模型，所以數(shù)據(jù)都是存儲(chǔ)在 B+ 樹中的。每一個(gè)索引在 InnoDB 里面對(duì)應(yīng)一棵 B+ 樹。每張表都對(duì)應(yīng)了好幾課B+樹。

image.png

• 主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)。在 InnoDB 里，主鍵索引也被稱為聚簇索引。
• 非主鍵索引的葉子節(jié)點(diǎn)內(nèi)容是主鍵的值。在 InnoDB 里，非主鍵索引也被稱為二級(jí)索引（secondary index）。

基于主鍵索引和普通索引的查詢有什么區(qū)別？
如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹；
如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜索 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜索一次。這個(gè)過程稱為回表。也就是說，基于非主鍵索引的查詢需要多掃描一棵索引樹。
因此，我們?cè)趹?yīng)用中應(yīng)該盡量使用主鍵查詢。

1.4 索引優(yōu)化

1.4.1 覆蓋索引

在這個(gè)查詢里面，索引 k 已經(jīng)“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。由于覆蓋索引可以減少樹的搜索次數(shù)，顯著提升查詢性能，所以使用覆蓋索引是一個(gè)常用的性能優(yōu)化手段。

案例:在一個(gè)市民信息表上，是否有必要將身份證號(hào)和名字建立聯(lián)合索引？
答:如果有一個(gè)高頻的查詢需求，根據(jù)身份證號(hào)查詢名字，那么建立聯(lián)合索引，利用覆蓋索引的優(yōu)勢，就不需要再回表了，效率更高

1.4.2 最左前綴

不只是索引的全部定義，只要滿足最左前綴，就可以利用索引來加速檢索。
這個(gè)最左前綴可以是聯(lián)合索引的最左 N 個(gè)字段，也可以是字符串索引的最左 M 個(gè)字符

1.4.3 索引下推

對(duì)索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。

2.事務(wù)

2.1 ACID

• 原子性(A):事務(wù)執(zhí)行的最小單位，不允許分割；要么全部執(zhí)行，要么都不執(zhí)行；
• 一致性(C):執(zhí)行數(shù)據(jù)前后，數(shù)據(jù)保持一致；事務(wù)應(yīng)確保數(shù)據(jù)庫的狀態(tài)從一個(gè)一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€(gè)一致狀態(tài)。
• 隔離性(I):并發(fā)訪問數(shù)據(jù)庫的時(shí)候，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間的數(shù)據(jù)庫是獨(dú)立的。
• 持久性(D):一個(gè)事務(wù)提交以后，對(duì)數(shù)據(jù)庫的影響是持久的，即使數(shù)據(jù)庫發(fā)生故障也不會(huì)對(duì)其有影響。

2.2 并發(fā)帶來的問題

• 臟讀(Dirty read )：一個(gè)事務(wù)讀到了另外一個(gè)事務(wù)還沒有提交的事務(wù)；
• 不可重復(fù)讀(Unrepeatableread):一個(gè)事務(wù)內(nèi)多次讀取某個(gè)數(shù)據(jù)，但是另外一個(gè)事務(wù)修改了數(shù)據(jù)，導(dǎo)致第一個(gè)事務(wù)前后兩次讀到的數(shù)據(jù)不一致。

不可重復(fù)讀，是指在數(shù)據(jù)庫訪問中，一個(gè)事務(wù)范圍內(nèi)兩個(gè)相同的查詢卻返回了不同數(shù)據(jù)。這是由于查詢時(shí)系統(tǒng)中其他事務(wù)修改的提交而引起的。比如事務(wù)T1讀取某一數(shù)據(jù)，事務(wù)T2讀取并修改了該數(shù)據(jù)，T1為了對(duì)讀取值進(jìn)行檢驗(yàn)而再次讀取該數(shù)據(jù)，便得到了不同的結(jié)果。

• 幻讀(Phantom read):T1讀取了幾行數(shù)據(jù)后，T2插入了幾條數(shù)據(jù)，T1在后續(xù)的查詢中就會(huì)發(fā)現(xiàn)多了原本不存在的數(shù)據(jù)，就好像發(fā)生了幻覺一樣。

幻讀是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時(shí)發(fā)生的一種現(xiàn)象，例如第一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，比如這種修改涉及到表中的“全部數(shù)據(jù)行”。同時(shí)，第二個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入“一行新數(shù)據(jù)”。那么，以后就會(huì)發(fā)生操作第一個(gè)事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣.一般解決幻讀的方法是增加范圍鎖RangeS，鎖定檢鎖范圍為只讀，這樣就避免了幻讀。

總結(jié):在并發(fā)訪問情況下，可能會(huì)出現(xiàn)臟讀、不可重復(fù)讀和幻讀等讀現(xiàn)象，為了應(yīng)對(duì)這些問題，主流數(shù)據(jù)庫都提供了鎖機(jī)制，并引入了事務(wù)隔離級(jí)別的概念。

2.2隔離性與隔離級(jí)別

• 讀未提交是指，一個(gè)事務(wù)還沒提交時(shí)，它做的變更就能被別的事務(wù)看到。
• 讀提交是指，一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到。
• 可重復(fù)讀是指，一個(gè)事務(wù)執(zhí)行過程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀隔離級(jí)別下，未提交變更對(duì)其他事務(wù)也是不可見的。
• 串行化，顧名思義是對(duì)于同一行記錄，“寫”會(huì)加“寫鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫鎖沖突的時(shí)候，后訪問的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行,所有事務(wù)依次執(zhí)行。

MySQL的隔離級(jí)別是可重復(fù)讀，但是對(duì)于InnoDB引擎在可重復(fù)讀的隔離級(jí)別下，使用的算法是Next-Key Lock鎖算法，避免了幻讀，且不會(huì)有任何的性能損失。

如何實(shí)現(xiàn)？

數(shù)據(jù)庫里面會(huì)創(chuàng)建一個(gè)視圖，訪問的時(shí)候以視圖的邏輯結(jié)果為準(zhǔn)。

1. 在“可重復(fù)讀”隔離級(jí)別下：這個(gè)視圖是在事務(wù)啟動(dòng)時(shí)創(chuàng)建的，整個(gè)事務(wù)存在期間都用這個(gè)視圖。
2. 在“讀提交”隔離級(jí)別下，這個(gè)視圖是在每個(gè) SQL 語句開始執(zhí)行的時(shí)候創(chuàng)建的。
3. “讀未提交”隔離級(jí)別下直接返回記錄上的最新值，沒有視圖概念；
4. “串行化”隔離級(jí)別下直接用加鎖的方式來避免并行訪問。

什么時(shí)候需要“可重復(fù)讀”的場景呢？
假設(shè)你在管理一個(gè)個(gè)人銀行賬戶表。
一個(gè)表存了賬戶余額，一個(gè)表存了賬單明細(xì)。
到了月底你要做數(shù)據(jù)校對(duì)，也就是判斷上個(gè)月的余額和當(dāng)前余額的差額，是否與本月的賬單明細(xì)一致。
你一定希望在校對(duì)過程中，即使有用戶發(fā)生了一筆新的交易，也不影響你的校對(duì)結(jié)果。這時(shí)候使用“可重復(fù)讀”隔離級(jí)別就很方便。事務(wù)啟動(dòng)時(shí)的視圖可以認(rèn)為是靜態(tài)的，不受其他事務(wù)更新的影響。

事務(wù)隔離級(jí)別的實(shí)現(xiàn)

在 MySQL 中，實(shí)際上每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作。記錄的最新值，通過回滾操作，都可以得到前一個(gè)狀態(tài)的值。

image.png

在查詢這條記錄的時(shí)候，不同時(shí)刻啟動(dòng)的事務(wù)會(huì)有不同的 read-view。如圖中看到的，在視圖 A、B、C 里面，這一個(gè)記錄的值分別是 1、2、4，同一條記錄在系統(tǒng)中可以存在多個(gè)版本，就是數(shù)據(jù)庫的多版本并發(fā)控制（MVCC）。對(duì)于 read-view A，要得到 1，就必須將當(dāng)前值依次執(zhí)行圖中所有的回滾操作得到。同時(shí)你會(huì)發(fā)現(xiàn)，即使現(xiàn)在有另外一個(gè)事務(wù)正在將 4 改成 5，這個(gè)事務(wù)跟 read-view A、B、C 對(duì)應(yīng)的事務(wù)是不會(huì)沖突的。

3.SQL語句的執(zhí)行

3.1 查詢語句

image.png

1.連接器

作用劃分:建立連接、獲取權(quán)限、維持和管理連接。

1.1權(quán)限

• 一個(gè)用戶成功建立連接以后，即使用管理員賬戶對(duì)這個(gè)用戶的權(quán)限進(jìn)行修改，也不會(huì)影響已經(jīng)存在連接的權(quán)限。修改完成后，只有再新建的連接才會(huì)使用新的權(quán)限設(shè)置。

1.2 長連接

• 建立連接的過程是比較消耗資源的，所以在使用中盡量減少建立連接的動(dòng)作，即盡量使用長連接。
• 使用長連接后內(nèi)存漲的特別快的原因？
  -- MySQL在執(zhí)行過程中臨時(shí)使用的內(nèi)存是管理在連接對(duì)象里面的，這些資源會(huì)在連接斷開的時(shí)候才釋放。如果長連接累積下來，可能會(huì)導(dǎo)致內(nèi)存占用過大，被系統(tǒng)強(qiáng)行殺掉(OOM),從現(xiàn)象上看就是MySQL異常重啟。
• 如何解決長連接造成的大內(nèi)存呢？
  • 定期斷開長連接。使用一段時(shí)間或者判斷程序里面執(zhí)行一個(gè)占用內(nèi)存的大查詢后，斷開連接，之后要查詢?cè)僦匦逻B接。
  • 在每次執(zhí)行一個(gè)比較大的操作后，執(zhí)行mysql_reset_connection 來初始化連接資源。這個(gè)過程不需要重連和重新做權(quán)限校驗(yàn)，但是會(huì)將連接恢復(fù)到剛剛創(chuàng)建完的狀態(tài)。

2.查詢緩存

弊端

• 查詢緩存的失效非常頻繁，只要有對(duì)表的更新，這個(gè)表上所有的查詢緩存都會(huì)被清空。
• 對(duì)于更新頻繁的數(shù)據(jù)庫來說，查詢緩存的命中率會(huì)非常的低；

適合場景
適合靜態(tài)表，很長時(shí)間才會(huì)更新一次，比如一個(gè)系統(tǒng)的配置表；

3.分析器

詞法分析

你輸入的是由多個(gè)字符串和空格組成的一條 SQL 語句，MySQL 需要識(shí)別出里面的字符串分別是什么，代表什么。
MySQL 從你輸入的"select"這個(gè)關(guān)鍵字識(shí)別出來，這是一個(gè)查詢語句。它也要把字符串“T”識(shí)別成“表名 T”，把字符串“ID”識(shí)別成“列 ID”。

語法分析

根據(jù)詞法分析的結(jié)果，語法分析器會(huì)根據(jù)語法規(guī)則，判斷你輸入的這個(gè) SQL 語句是否滿足 MySQL 語法。

如果表 T 中沒有字段 k，而你執(zhí)行了這個(gè)語句 select * from T where k=1, 那肯定是會(huì)報(bào)“不存在這個(gè)列”的錯(cuò)誤： “Unknown column ‘k’ in ‘where clause’”。
這個(gè)就是在分析器中的報(bào)錯(cuò)。

4.優(yōu)化器

經(jīng)過了分析器，MySQL 就知道你要做什么了。在開始執(zhí)行之前，還要先經(jīng)過優(yōu)化器的處理。

• 優(yōu)化器是在表里面有多個(gè)索引的時(shí)候，決定使用哪個(gè)索引；
• 或者在一個(gè)語句有多表關(guān)聯(lián)（join）的時(shí)候，決定各個(gè)表的連接順序。

5.執(zhí)行器

MySQL 通過分析器知道了你要做什么，通過優(yōu)化器知道了該怎么做，于是就進(jìn)入了執(zhí)行器階段，開始執(zhí)行語句。

• 開始執(zhí)行的時(shí)候，要先判斷一下你對(duì)這個(gè)表 T 有沒有執(zhí)行查詢的權(quán)限，如果沒有，就會(huì)返回沒有權(quán)限的錯(cuò)誤。
• 如果有權(quán)限，就打開表繼續(xù)執(zhí)行。打開表的時(shí)候，執(zhí)行器就會(huì)根據(jù)表的引擎定義，去使用這個(gè)引擎提供的接口。

3.2 更新語句

1.整體流程

image.png

• 執(zhí)行器先找引擎取 ID=2 這一行。(ID 是主鍵，引擎直接用樹搜索找到這一行)。如果 ID=2 這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中，就直接返回給執(zhí)行器；否則，需要先從磁盤讀入內(nèi)存，然后再返回。
• 執(zhí)行器拿到引擎給的行數(shù)據(jù)，把這個(gè)值加上 1，比如原來是 N，現(xiàn)在就是 N+1，得到新的一行數(shù)據(jù)，再調(diào)用引擎接口寫入這行新數(shù)據(jù)。
• 引擎將這行新數(shù)據(jù)更新到內(nèi)存中，同時(shí)將這個(gè)更新操作記錄到 redo log 里面，此時(shí) redo log 處于 prepare 狀態(tài)。然后告知執(zhí)行器執(zhí)行完成了，隨時(shí)可以提交事務(wù)。
• 執(zhí)行器生成這個(gè)操作的 binlog，并把 binlog 寫入磁盤。
• 執(zhí)行器調(diào)用引擎的提交事務(wù)接口，引擎把剛剛寫入的 redo log 改成提交（commit）狀態(tài)，更新完成。

2. redo log vs binlog

2.1 redo log

問題:如果MySQL每一次更新操作都寫進(jìn)磁盤，同時(shí)磁盤也要找到對(duì)應(yīng)的那條記錄，然后更新。整個(gè)過程的IO成本、查找成本都很高；

如何解決：WAL(Write Ahead Logging):先寫日志，再寫磁盤

• 當(dāng)有一條記錄需要更新的時(shí)候，InnoDB首先會(huì)把記錄寫到redo log，然后更新到內(nèi)存中，整個(gè)過程就算完成了。
• InnoDB會(huì)在適當(dāng)?shù)臅r(shí)候，將redo log中的數(shù)據(jù)更新寫入磁盤。(適當(dāng)?shù)臅r(shí)候:1.系統(tǒng)比較空閑的時(shí)候；2.redo log空間已滿的時(shí)候)

crash-safe的能力

• 有了 redo log，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟，之前提交的記錄都不會(huì)丟失，這個(gè)能力稱為 crash-safe

2.2 bin log

redo log是InnoDB獨(dú)有的日志。
server層也有自己的日志，binlog，即歸檔日志，沒有crash-safe的能力。

2.3 二者對(duì)比

• redo log 是InnoDB獨(dú)有的日志，具有crash-safe的能力；binlog 即歸檔日志，是所有搜索引擎都可以使用的，沒有crash-safe的能力；
• redo log 是物理日志，是某個(gè)數(shù)據(jù)頁上面做了什么修改，記錄這個(gè)頁 “做了什么改動(dòng)”；bin log是邏輯日志，記錄的是這個(gè)語句的原始邏輯，比如某表某行的某個(gè)字段加1
• redo log 大小的固定的，可能會(huì)存儲(chǔ)滿；bin log是可以追加寫的，沒有大小的限制，即有歸檔的概念；

3. 相關(guān)問題

3.1怎樣讓數(shù)據(jù)庫恢復(fù)到半個(gè)月內(nèi)任意一秒的狀態(tài)？

前提:binlog 會(huì)記錄所有的邏輯操作，并且是采用“追加寫”的形式。

• 備份系統(tǒng)中一定會(huì)保存最近半個(gè)月的所有 binlog;
• 同時(shí)系統(tǒng)會(huì)定期做整庫備份。這里的“定期”取決于系統(tǒng)的重要性，可以是一天一備，也可以是一周一備。
  如何操作:
  當(dāng)需要恢復(fù)到指定的某一秒時(shí)，比如某天下午兩點(diǎn)發(fā)現(xiàn)中午十二點(diǎn)有一次誤刪表，需要找回?cái)?shù)據(jù)，那你可以這么做：
• 首先，找到最近的一次全量備份，如果你運(yùn)氣好，可能就是昨天晚上的一個(gè)備份，從這個(gè)備份恢復(fù)到臨時(shí)庫；
• 然后，從備份的時(shí)間點(diǎn)開始，將備份的 binlog 依次取出來，重放到中午誤刪表之前的那個(gè)時(shí)刻；
• 這樣你的臨時(shí)庫就跟誤刪之前的線上庫一樣了，然后你可以把表數(shù)據(jù)從臨時(shí)庫取出來，按需要恢復(fù)到線上庫去；

3.2 為什么日志需要“兩階段提交”?

一句話總結(jié)：簡單說，redo log 和 binlog 都可以用于表示事務(wù)的提交狀態(tài)，而兩階段提交就是讓這兩個(gè)狀態(tài)保持邏輯上的一致。

仍然用前面的 update 語句來做例子。假設(shè)當(dāng)前 ID=2 的行，字段 c 的值是 0，再假設(shè)執(zhí)行 update 語句過程中在寫完第一個(gè)日志后，第二個(gè)日志還沒有寫完期間發(fā)生了 crash，會(huì)出現(xiàn)什么情況呢？

1. 先寫 redo log 后寫 binlog。假設(shè)在 redo log 寫完，binlog 還沒有寫完的時(shí)候，MySQL 進(jìn)程異常重啟。由于redo log 寫完之后，系統(tǒng)即使崩潰，仍然能夠把數(shù)據(jù)恢復(fù)回來，所以恢復(fù)后這一行 c 的值是 1。但是由于 binlog 沒寫完就 crash 了，這時(shí)候 binlog 里面就沒有記錄這個(gè)語句。因此，之后備份日志的時(shí)候，存起來的 binlog 里面就沒有這條語句。然后你會(huì)發(fā)現(xiàn)，如果需要用這個(gè) binlog 來恢復(fù)的話，由于這個(gè)語句的 binlog 丟失，就會(huì)少了這一次更新，恢復(fù)出來的這一行 c 的值就是 0，與原庫的值不同。
2. 先寫 binlog 后寫 redo log。如果在 binlog 寫完之后 crash，由于 redo log 還沒寫，崩潰恢復(fù)以后這個(gè)事務(wù)無效，所以這一行 c 的值是 0。但是 binlog 里面已經(jīng)記錄了“把 c 從 0 改成 1”這個(gè)日志。所以，在之后用 binlog 來恢復(fù)的時(shí)候就多了一個(gè)事務(wù)出來，恢復(fù)出來的這一行 c 的值就是 1，與原庫的值不同。

3.3各個(gè)時(shí)間點(diǎn)奔潰的問題引申：

1 prepare階段 2 寫binlog 3 commit
當(dāng)在2之前崩潰時(shí)
重啟恢復(fù)：后發(fā)現(xiàn)沒有commit，回滾。備份恢復(fù)：沒有binlog 。
一致
當(dāng)在3之前崩潰
重啟恢復(fù)：雖沒有commit，但滿足prepare和binlog完整，所以重啟后會(huì)自動(dòng)commit。備份：有binlog. 一致

3.4 bin log與crash safe的關(guān)系

CrashSafe指MySQL服務(wù)器宕機(jī)重啟后，能夠保證：

• 所有已經(jīng)提交事務(wù)的數(shù)據(jù)仍然存在。
• 所有沒有提交事務(wù)的數(shù)據(jù)自動(dòng)回滾。
  Innodb通過Redo Log和redo Log可以保證以上兩點(diǎn)。

為了保證嚴(yán)格的CrashSafe，必須要在每個(gè)事務(wù)提交的時(shí)候，將Redo Log寫入硬件存儲(chǔ)。這樣做會(huì)犧牲一些性能，但是可靠性最好

使用2PC協(xié)議。
事務(wù)的協(xié)調(diào)者Binlog
Binlog在2PC中充當(dāng)了事務(wù)的協(xié)調(diào)者。由Binlog來通知InnoDB引擎來執(zhí)行prepare，commit或者rollback的步驟。事務(wù)提交的整個(gè)過程如下：

1. 協(xié)調(diào)者準(zhǔn)備階段(Prepare Phase)
   告訴引擎做Prepare，InnoDB更改事務(wù)狀態(tài)，并將Redo Log刷入磁盤。
2. 協(xié)調(diào)者提交階段(Commit Phase)
   2.1 記錄協(xié)調(diào)者日志，即Binlog日志。
   2.2 告訴引擎做commit。
   注意：記錄Binlog是在InnoDB引擎Prepare(即Redo Log寫入磁盤)之后，這點(diǎn)至關(guān)重要。

恢復(fù)前事務(wù)的狀態(tài)
在恢復(fù)開始前事務(wù)有以下幾種狀態(tài)：

• InnoDB中已經(jīng)提交
  根據(jù)前面2PC的過程，可知Binlog中也一定記錄了該事務(wù)。所以這種事務(wù)是一致的不需要處理。
• InnoDB中是prepared狀態(tài)，Binlog中有該事務(wù)的Events。
  需要通知InnoDB提交這些事務(wù)。
• InnoDB中是prepared狀態(tài)，Binlog中沒有該事務(wù)的Events。
  因?yàn)锽inlog還沒記錄，需要通知InnoDB回滾這些事務(wù)。

CrashSafe的寫盤次數(shù)
保證CrashSafe就要設(shè)置下面兩個(gè)參數(shù)為1：
sync_binlog=1
innodb_flush_log_at_trx_commit=1

• sync_binlog
  sync_binlog是控制Binlog寫盤的，1表示每次都寫。由于Binlog使用了組提交(Group Commit)的機(jī)制，它代表一組事務(wù)提交時(shí)必須要將Binlog文件寫入硬件存儲(chǔ)1次。
• innodb_flush_log_at_trx_commit的寫盤次數(shù)
  這個(gè)變量是用來控制InnoDB commit時(shí)寫盤的方法的。現(xiàn)在commit被分成了兩個(gè)階段，到底在哪個(gè)階段寫盤，還是兩個(gè)階段都要寫盤呢？
• Prepare階段時(shí)需要寫盤
  2PC要求在Prepare時(shí)就要將數(shù)據(jù)持久化，只有這樣，恢復(fù)時(shí)才能提交已經(jīng)記錄了Xid_log_event的事務(wù)。
• Commit階段時(shí)不需要寫盤
  如果Commit階段不寫盤，會(huì)造成什么結(jié)果呢？已經(jīng)Cmmit了的事務(wù)，在恢復(fù)時(shí)的狀態(tài)可能是Prepared。由于恢復(fù)時(shí)，Prepared的事務(wù)可以通過Xid_log_event來提交事務(wù)，所以在恢復(fù)后事務(wù)的狀態(tài)就是正確的。因此在Commit階段不需要寫盤。

4.鎖

當(dāng)并發(fā)事務(wù)同時(shí)訪問一個(gè)資源時(shí)，有可能導(dǎo)致數(shù)據(jù)不一致，因此需要一種機(jī)制來將數(shù)據(jù)訪問順序化，以保證數(shù)據(jù)庫數(shù)據(jù)的一致性。鎖就是其中的一種機(jī)制。在計(jì)算機(jī)科學(xué)中，鎖是在執(zhí)行多線程時(shí)用于強(qiáng)行限制資源訪問的同步機(jī)制，即用于在并發(fā)控制中保證對(duì)互斥的要求。

MyISAM采用為表級(jí)鎖；
InnoDB支持表級(jí)鎖和行級(jí)鎖，默認(rèn)為行級(jí)鎖；

4.1表級(jí)鎖與行級(jí)鎖

• 表級(jí)鎖：粒度最大的鎖，實(shí)現(xiàn)簡單，資源消耗比較小，不會(huì)出現(xiàn)死鎖；但是并發(fā)度最低，不適合高并發(fā)場景。表級(jí)鎖定分為表共享讀鎖（共享鎖）與表獨(dú)占寫鎖（排他鎖）。
• 行級(jí)鎖：粒度最小的鎖，只對(duì)當(dāng)前操作的行進(jìn)行加鎖，適合并發(fā)場景，降低沖突的概率；加鎖開銷大，加鎖慢，容易出現(xiàn)死鎖。行級(jí)鎖分為共享鎖 和 排他鎖。

4.1.1行級(jí)鎖

1.行級(jí)鎖

InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。
InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖。

行級(jí)鎖的算法有三種:

• Record Lock:對(duì)索引項(xiàng)加鎖，鎖定符合條件的行。其他事務(wù)不能修改和刪除加鎖項(xiàng)；
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，不包括記錄本身；
• Next-key Lock:行鎖+間隙鎖，包含記錄本身，解決了幻讀；

2.死鎖

image.png

當(dāng)出現(xiàn)死鎖以后，有兩種策略：

• 一種策略是，直接進(jìn)入等待，直到超時(shí)。這個(gè)超時(shí)時(shí)間可以通過參數(shù) innodb_lock_wait_timeout (時(shí)間默認(rèn)是50s)來設(shè)置。
• 另一種策略是，發(fā)起死鎖檢測，發(fā)現(xiàn)死鎖后，主動(dòng)回滾死鎖鏈條中的某一個(gè)事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù) innodb_deadlock_detect 設(shè)置為 on，表示開啟這個(gè)邏輯。

主動(dòng)死鎖檢測:具有額外的負(fù)擔(dān)，每當(dāng)一個(gè)事務(wù)被鎖的時(shí)候，就要看看它所依賴的線程有沒有被別人鎖住，如此循環(huán)，最后判斷是否出現(xiàn)了循環(huán)等待，也就是死鎖。

有多種方法可以避免死鎖，這里介紹常見的三種
1、如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序訪問表，可以大大降低死鎖機(jī)會(huì)。
2、在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；
3、對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過表級(jí)鎖定來減少死鎖產(chǎn)生的概率；

3.行級(jí)鎖的小結(jié)

• InnoDB查詢使用的是Next-key Lock；
• Next-Key Lock為了解決幻讀的問題；
• 當(dāng)查詢的索引有唯一屬性時(shí)，Next-key 降級(jí)為Record Key
• GAP 鎖的目的就是為了防止幻讀，阻止多個(gè)事務(wù)將數(shù)據(jù)插入到同一范圍內(nèi)；

4.1.2 表級(jí)鎖使用場景

• 事務(wù)更新大表中的大部分?jǐn)?shù)據(jù)直接使用表級(jí)鎖效率更高；
• 事務(wù)比較復(fù)雜，使用行級(jí)索很可能引起死鎖導(dǎo)致回滾。

4.2 共享鎖（s）和排他鎖（X）-->行鎖

行級(jí)鎖可以進(jìn)一步劃分為共享鎖（s）和排他鎖（X）。

共享鎖(Share Lock)

共享鎖又稱讀鎖，是讀取操作創(chuàng)建的鎖。其他事務(wù)可以并發(fā)讀取數(shù)據(jù)，但任何事務(wù)都不能對(duì)數(shù)據(jù)進(jìn)行修改（獲取數(shù)據(jù)上的排他鎖），直到已釋放所有共享鎖。
如果事務(wù)T對(duì)數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對(duì)A再加共享鎖，不能加排他鎖。獲準(zhǔn)共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)。
用法
SELECT ... LOCK IN SHARE MODE;
在查詢語句后面增加LOCK IN SHARE MODE，Mysql會(huì)對(duì)查詢結(jié)果中的每行都加共享鎖，當(dāng)沒有其他線程對(duì)查詢結(jié)果集中的任何一行使用排他鎖時(shí)，可以成功申請(qǐng)共享鎖，否則會(huì)被阻塞。其他線程也可以讀取使用了共享鎖的表，而且這些線程讀取的是同一個(gè)版本的數(shù)據(jù)。

排他鎖（eXclusive Lock）

排他鎖又稱寫鎖，如果事務(wù)T對(duì)數(shù)據(jù)A加上排他鎖后，則其他事務(wù)不能再對(duì)A加任任何類型的鎖。獲準(zhǔn)排他鎖的事務(wù)既能讀數(shù)據(jù)，又能修改數(shù)據(jù)。其它用戶只能查詢但不能更新被加鎖的數(shù)據(jù)行。
用法
SELECT ... FOR UPDATE;
在查詢語句后面增加FOR UPDATE，Mysql會(huì)對(duì)查詢結(jié)果中的每行都加排他鎖，當(dāng)沒有其他線程對(duì)查詢結(jié)果集中的任何一行使用排他鎖時(shí)，可以成功申請(qǐng)排他鎖，否則會(huì)被阻塞。
對(duì)于insert、update、delete，InnoDB會(huì)自動(dòng)給涉及的數(shù)據(jù)加排他鎖（X）；對(duì)于一般的Select語句，InnoDB不會(huì)加任何鎖，事務(wù)可以通過以下語句給顯示加共享鎖或排他鎖。
共享鎖：SELECT ... LOCK IN SHARE MODE;
排他鎖：SELECT ... FOR UPDATE;

4.3 讀鎖和寫鎖--》表鎖

讀鎖：共享鎖
寫鎖：排它鎖，排它鎖，互斥鎖
本文提到的讀鎖和寫鎖都是MySQL數(shù)據(jù)庫的MyISAM引擎支持的表鎖的。

MyISAM 存儲(chǔ)引擎只支持表鎖，MySQL 的表級(jí)鎖有兩種模式:表共享讀鎖(Table Read Lock)和表獨(dú)占寫鎖(Table Write Lock)。

對(duì)于讀操作，可以增加讀鎖，一旦數(shù)據(jù)表被加上讀鎖，其他請(qǐng)求可以對(duì)該表再次增加讀鎖，但是不能增加寫鎖。（當(dāng)一個(gè)請(qǐng)求在讀數(shù)據(jù)時(shí)，其他請(qǐng)求也可以讀，但是不能寫，因?yàn)橐坏┝硗庖粋€(gè)線程寫了數(shù)據(jù)，就會(huì)導(dǎo)致當(dāng)前線程讀取到的數(shù)據(jù)不是最新的了。這就是不可重復(fù)讀現(xiàn)象）

對(duì)于寫操作，可以增加寫鎖，一旦數(shù)據(jù)表被加上寫鎖，其他請(qǐng)求無法在對(duì)該表增加讀鎖和寫鎖。（當(dāng)一個(gè)請(qǐng)求在寫數(shù)據(jù)時(shí)，其他請(qǐng)求不能執(zhí)行任何操作，因?yàn)樵诋?dāng)前事務(wù)提交之前，其他的請(qǐng)求無法看到本次修改的內(nèi)容。否則就可能產(chǎn)生臟讀、不可重復(fù)讀和幻讀）

讀鎖和寫鎖都是阻塞鎖。
如果t1對(duì)數(shù)據(jù)表增加了寫鎖，這時(shí)t2請(qǐng)求對(duì)數(shù)據(jù)表增加寫鎖，這時(shí)候t2并不會(huì)直接返回，而是會(huì)一直處于阻塞狀態(tài)，直到t1釋放了對(duì)表的鎖，這時(shí)t2便有可能加鎖成功，獲取到結(jié)果。

另外兩個(gè)表級(jí)鎖：IS和IX（InnoDB）

意向鎖:當(dāng)一個(gè)事務(wù)在需要獲取資源鎖定的時(shí)候，如果遇到自己需要的資源已經(jīng)被排他鎖占用的時(shí)候，該事務(wù)可以在需要鎖定行的表上面添加一個(gè)合適的意向鎖。如果自己需要一個(gè)共享鎖，那么就在表上面添加一個(gè)意向共享鎖。而如果自己需要的是某行（或者某些行）上面添加一個(gè)排他鎖的話，則先在表上面添加一個(gè)意向排他鎖。意向共享鎖可以同時(shí)并存多個(gè)，但是意向排他鎖同時(shí)只能有一個(gè)存在。

InnoDB另外的兩個(gè)表級(jí)鎖：
意向共享鎖（IS）： 表示事務(wù)準(zhǔn)備給數(shù)據(jù)行記入共享鎖，事務(wù)在一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖（IX）： 表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排他鎖，事務(wù)在一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

注意：
這里的意向鎖是表級(jí)鎖，表示的是一種意向，僅僅表示事務(wù)正在讀或?qū)懩骋恍杏涗?，在真正加行鎖時(shí)才會(huì)判斷是否沖突。意向鎖是InnoDB自動(dòng)加的，不需要用戶干預(yù)。
IX，IS是表級(jí)鎖，不會(huì)和行級(jí)的X，S鎖發(fā)生沖突，只會(huì)和表級(jí)的X，S發(fā)生沖突。

4.4 悲觀鎖和樂觀鎖

樂觀并發(fā)控制(樂觀鎖)和悲觀并發(fā)控制（悲觀鎖）是并發(fā)控制主要采用的技術(shù)手段。

樂觀鎖與悲觀鎖只是人們定義出來的概念。針對(duì)于不同的業(yè)務(wù)場景，應(yīng)該選用不同的并發(fā)控制方式。在DBMS中，悲觀鎖正是利用數(shù)據(jù)庫本身提供的鎖機(jī)制來實(shí)現(xiàn)的。

4.4.1 悲觀鎖

這種借助數(shù)據(jù)庫鎖機(jī)制在修改數(shù)據(jù)之前先鎖定，再修改的方式被稱之為悲觀并發(fā)控制（又名“悲觀鎖”，Pessimistic Concurrency Control，縮寫“PCC”）。
加鎖流程:

1. 在對(duì)任意記錄進(jìn)行修改前，先嘗試為該記錄加上排他鎖（exclusive locking）。
2. 如果加鎖失敗，說明該記錄正在被修改，那么當(dāng)前查詢可能要等待或者拋出異常。 具體響應(yīng)方式由開發(fā)者根據(jù)實(shí)際需要決定。
3. 如果成功加鎖，那么就可以對(duì)記錄做修改，事務(wù)完成后就會(huì)解鎖了。
4. 其間如果有其他對(duì)該記錄做修改或加排他鎖的操作，都會(huì)等待我們解鎖或直接拋出異常。

4.3.2樂觀鎖

在關(guān)系數(shù)據(jù)庫管理系統(tǒng)里，樂觀并發(fā)控制（又名“樂觀鎖”，Optimistic Concurrency Control，縮寫“OCC”）是一種并發(fā)控制的方法。它假設(shè)多用戶并發(fā)的事務(wù)在處理時(shí)不會(huì)彼此互相影響，各事務(wù)能夠在不產(chǎn)生鎖的情況下處理各自影響的那部分?jǐn)?shù)據(jù)。在提交數(shù)據(jù)更新之前，每個(gè)事務(wù)會(huì)先檢查在該事務(wù)讀取數(shù)據(jù)后，有沒有其他事務(wù)又修改了該數(shù)據(jù)。如果其他事務(wù)有更新的話，正在提交的事務(wù)會(huì)進(jìn)行回滾。

實(shí)現(xiàn)數(shù)據(jù)版本有兩種方式，第一種是使用版本號(hào)，第二種是使用時(shí)間戳。
版本號(hào)的實(shí)現(xiàn):

數(shù)據(jù)版本,為數(shù)據(jù)增加的一個(gè)版本標(biāo)識(shí)。當(dāng)讀取數(shù)據(jù)時(shí)，將版本標(biāo)識(shí)的值一同讀出，數(shù)據(jù)每更新一次，同時(shí)對(duì)版本標(biāo)識(shí)進(jìn)行更新。當(dāng)我們提交更新的時(shí)候，判斷數(shù)據(jù)庫表對(duì)應(yīng)記錄的當(dāng)前版本信息與第一次取出來的版本標(biāo)識(shí)進(jìn)行比對(duì)，如果數(shù)據(jù)庫表當(dāng)前版本號(hào)與第一次取出來的版本標(biāo)識(shí)值相等，則予以更新，否則認(rèn)為是過期數(shù)據(jù)。

//修改商品庫存 
update item set quantity=quantity - 1 where id = 1 and quantity - 1 > 0 

如何選擇

在樂觀鎖與悲觀鎖的選擇上面，主要看下兩者的區(qū)別以及適用場景就可以了。
1、樂觀鎖并未真正加鎖，效率高。一旦鎖的粒度掌握不好，更新失敗的概率就會(huì)比較高，容易發(fā)生業(yè)務(wù)失敗。
2、悲觀鎖依賴數(shù)據(jù)庫鎖，效率低。更新失敗的概率比較低。
隨著互聯(lián)網(wǎng)三高架構(gòu)（高并發(fā)、高性能、高可用）的提出，悲觀鎖已經(jīng)越來越少的被使用到生產(chǎn)環(huán)境中了，尤其是并發(fā)量比較大的業(yè)務(wù)場景。

4.5

MySQL 提供了一個(gè)加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當(dāng)你需要讓整個(gè)庫處于只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線程的以下語句會(huì)被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句。

全局鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個(gè)表都 select 出來存成文本。

官方自帶的邏輯備份工具是 mysqldump。當(dāng) mysqldump 使用參數(shù)–single-transaction 的時(shí)候，導(dǎo)數(shù)據(jù)之前就會(huì)啟動(dòng)一個(gè)事務(wù)，來確保拿到一致性視圖。而由于 MVCC 的支持，這個(gè)過程中數(shù)據(jù)是可以正常更新的。

你一定在疑惑，有了這個(gè)功能，為什么還需要 FTWRL 呢？
single-transaction 方法只適用于所有的表使用事務(wù)引擎的庫。如果有的表使用了不支持事務(wù)的引擎，那么備份就只能通過 FTWRL 方法。

5.其他

5.1大表優(yōu)化

1. 限定數(shù)據(jù)的范圍

務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語句；

2. 讀寫分離

主庫寫，從庫讀

image.png

一主多從，讀寫分離，主動(dòng)同步，是一種常見的數(shù)據(jù)庫架構(gòu)，一般來說：

• 主庫，提供數(shù)據(jù)庫寫服務(wù)
• 從庫，提供數(shù)據(jù)庫讀服務(wù)
• 主從之間，通過某種機(jī)制同步數(shù)據(jù)，例如mysql的binlog
  本質(zhì)上解決的問題:
• 大部分互聯(lián)網(wǎng)業(yè)務(wù)讀多寫少，數(shù)據(jù)庫的讀往往最先成為性能瓶頸，從而可以線性提升數(shù)據(jù)庫讀性能；
• 通過消除讀寫鎖沖突提升數(shù)據(jù)庫寫性能；

小結(jié)

一句話，主要解決“數(shù)據(jù)庫讀性能瓶頸”問題，在數(shù)據(jù)庫扛不住讀的時(shí)候，通常讀寫分離，通過增加從庫線性提升系統(tǒng)讀性能。

缺點(diǎn)

如果數(shù)據(jù)庫讀寫分離：

• 數(shù)據(jù)庫連接池需要區(qū)分：讀連接池，寫連接池
• 如果要保證讀高可用，讀連接池要實(shí)現(xiàn)故障自動(dòng)轉(zhuǎn)移
• 有潛在的主庫從庫一致性問題

優(yōu)化建議

• 如果面臨的是“讀性能瓶頸”問題，增加緩存可能來得更直接，更容易一點(diǎn)
• 關(guān)于成本，從庫的成本比緩存高不少

2垂直劃分

簡單來說垂直拆分是指數(shù)據(jù)表列的拆分，把一張列比較多的表拆分為多張表。
優(yōu)點(diǎn)：優(yōu)化表的結(jié)構(gòu)，易于維護(hù)；可以使列的數(shù)據(jù)變小，查詢時(shí)候的block塊減少，減少I/O數(shù)；
缺點(diǎn)：主鍵冗余；引起Join操作(應(yīng)用層完成)；垂直分區(qū)讓事務(wù)更加復(fù)雜；
主要依據(jù)以下幾點(diǎn)：
（1）將長度較短，訪問頻率較高的屬性盡量放在一個(gè)表里，這個(gè)表暫且稱為主表；
（2）將字段較長，訪問頻率較低的屬性盡量放在一個(gè)表里，這個(gè)表暫且稱為擴(kuò)展表；
（3）經(jīng)常一起訪問的屬性，也可以放在一個(gè)表里；

為何這么垂直拆分可以提升性能？
（1）數(shù)據(jù)庫有自己的內(nèi)存緩沖池，會(huì)將磁盤上的數(shù)據(jù)load到緩沖池里；
（2）數(shù)據(jù)庫緩沖池，以row為單位緩存數(shù)據(jù)；
（3）在內(nèi)存有限的情況下，在數(shù)據(jù)庫緩沖池里緩存短row，就能緩存更多的數(shù)據(jù)；
（4）在數(shù)據(jù)庫緩沖池里緩存高頻訪問row，就能提升緩存命中率，減少磁盤的訪問；

3.水平劃分

image.png

水平切分，也是一種常見的數(shù)據(jù)庫架構(gòu)，一般來說：

• 每個(gè)數(shù)據(jù)庫之間沒有數(shù)據(jù)重合，沒有類似binlog同步的關(guān)聯(lián)；
• 所有數(shù)據(jù)并集，組成全部數(shù)據(jù)；
• 會(huì)用算法，來完成數(shù)據(jù)分割，例如“取?！?，或者根據(jù)業(yè)務(wù)標(biāo)識(shí)；

水平切分架構(gòu)究竟解決什么問題？

大部分互聯(lián)網(wǎng)業(yè)務(wù)數(shù)據(jù)量很大，單庫容量容易成為瓶頸，如果希望：

• 線性降低單庫數(shù)據(jù)容量
• 線性提升數(shù)據(jù)庫寫性能

小結(jié):

一句話總結(jié)，水平切分主要解決“數(shù)據(jù)庫數(shù)據(jù)量大”問題，在數(shù)據(jù)庫容量扛不住的時(shí)候，通常水平切分。
一個(gè)水平切分集群中的每一個(gè)數(shù)據(jù)庫，通常稱為一個(gè)“分片”。

保持?jǐn)?shù)據(jù)表結(jié)構(gòu)不變，通過某種策略存儲(chǔ)數(shù)據(jù)分片。這樣每一片數(shù)據(jù)分散到不同的表或者庫中，達(dá)到了分布式的目的。 水平拆分可以支撐非常大的數(shù)據(jù)量。

水平拆分能夠支持非常大的數(shù)據(jù)量存儲(chǔ)，拆分會(huì)帶來邏輯、部署、運(yùn)維的各種復(fù)雜度 ，一般的數(shù)據(jù)表在優(yōu)化得當(dāng)?shù)那闆r下支撐千萬以下的數(shù)據(jù)量是沒有太大問題的。
數(shù)據(jù)庫分片的兩種常見方案：
客戶端代理： 分片邏輯在應(yīng)用端，封裝在jar包中，通過修改或者封裝JDBC層來實(shí)現(xiàn)。
中間件代理： 在應(yīng)用和數(shù)據(jù)中間加了一個(gè)代理層。分片邏輯統(tǒng)一維護(hù)在中間件服務(wù)中。 我們現(xiàn)在談的 Mycat 、360的Atlas、網(wǎng)易的DDB等等都是這種架構(gòu)的實(shí)現(xiàn)。

5.2 todoread

1. 一條SQL語句執(zhí)行得很慢的原因有哪些
2. 數(shù)據(jù)庫調(diào)優(yōu)
3. 緩沖池(buffer pool)
   4.面試問題100問
   5.https://mp.weixin.qq.com/s/2A14OKApo-DDKAVaRbnkHg