參考文章；
https://www.oreilly.com/library/view/high-performance-mysql/9781449332471/ch01.html

三級(jí)架構(gòu)

三級(jí)架構(gòu).png

第一級(jí)向外提供連接施无，身份認(rèn)證辉词，安全維護(hù)等；
第二級(jí)：是mysql server的核心猾骡，核心功能就在這瑞躺；
第三級(jí):存儲(chǔ)引擎，數(shù)據(jù)真正存放的地方兴想；

第二級(jí)和存儲(chǔ)引擎幢哨，通過存儲(chǔ)引擎提供的api 交互；

mysql支持多個(gè)存儲(chǔ)引擎嫂便；

連接管理和安全認(rèn)證

第一層：
每個(gè)connect都會(huì)在服務(wù)進(jìn)程中開啟一個(gè)線程捞镰，
connect的所有操作都是在這個(gè)線程內(nèi)完成的，
并且這個(gè)線程會(huì)被緩存起來多次使用毙替；
因此每次連接不需要銷毀connect岸售，
新的連接可以反復(fù)使用已經(jīng)創(chuàng)建的這個(gè)connect.

認(rèn)證管理：每次連接都要認(rèn)證：
認(rèn)證有 username:password和ssl兩種方式，
并且服務(wù)會(huì)控制用戶的執(zhí)行權(quán)限蔚龙；

第二層：解析冰评，優(yōu)化和執(zhí)行

執(zhí)行sql語句，緩存數(shù)據(jù)木羹，優(yōu)化sql等

并發(fā)控制

MySQL has to do this at two levels: the server level and the storage engine level.

mysql是如何處理并發(fā)讀寫的：
并發(fā)：要求的是多任務(wù)同時(shí)執(zhí)行處理，同時(shí)執(zhí)行解孙；

共享鎖：讀鎖：允許多個(gè)線程同時(shí)讀取坑填，前提是沒有寫數(shù)據(jù)；
獨(dú)占鎖弛姜；寫鎖脐瑰，阻止所有讀取操作，只能允許一個(gè)線程寫數(shù)據(jù)廷臼；

鎖粒度：

鎖一行苍在；
鎖多行；
鎖整張表荠商；

提高共享資源并發(fā)性的一種方法是更有選擇性地鎖定什么寂恬。而不是鎖定整個(gè)資源，只鎖定包含您需要更改的數(shù)據(jù)的部分莱没。更好的是初肉，只鎖定您計(jì)劃更改的確切數(shù)據(jù)。最大限度地減少您一次鎖定的數(shù)據(jù)量饰躲，只要它們不相互沖突牙咏，就可以同時(shí)更改給定資源臼隔。

問題是鎖消耗資源。每個(gè)鎖定操作 - 獲取鎖定妄壶，檢查鎖定是否空閑摔握，釋放鎖定等等 - 都有開銷。如果系統(tǒng)花費(fèi)太多時(shí)間來管理鎖而不是存儲(chǔ)和檢索數(shù)據(jù)丁寄，那么性能就會(huì)受到影響盒发。

鎖定策略是鎖定開銷和數(shù)據(jù)安全之間的折衷，并且該折衷會(huì)影響性能狡逢。大多數(shù)商業(yè)數(shù)據(jù)庫服務(wù)器都沒有給您太多選擇：
您在表中獲得了所謂的行級(jí)鎖定宁舰，通過各種通常很復(fù)雜的方法來提供許多鎖定的良好性能。

另一方面奢浑，MySQL確實(shí)提供了選擇蛮艰。它的存儲(chǔ)引擎可以實(shí)現(xiàn)自己的鎖定策略和鎖定粒度。
鎖管理是存儲(chǔ)引擎設(shè)計(jì)中非常重要的決策;
將粒度固定在一定水平可以為某些用途提供更好的性能雀彼，但使該引擎不太適合其他目的壤蚜。
由于MySQL提供多個(gè)存儲(chǔ)引擎，因此不需要單一的通用解決方案徊哑。
讓我們來看看兩個(gè)最重要的鎖定策略袜刷。

鎖類型

表鎖

MySQL中最基本的鎖定策略，以及開銷最低的策略是表鎖莺丑。表鎖類似于前面描述的郵箱鎖：它鎖定整個(gè)表著蟹。當(dāng)客戶端希望寫入表（插入，刪除梢莽，更新等）時(shí)萧豆，它會(huì)獲取寫鎖定。這樣可以防止所有其他讀寫操作昏名。當(dāng)沒有人寫數(shù)據(jù)時(shí)涮雷，讀數(shù)據(jù)可以獲得讀鎖，這與其他讀鎖不沖突轻局。

表鎖在特定情況下具有良好性能的變化洪鸭。例如，READ LOCAL表鎖允許某些類型的并發(fā)寫操作仑扑。

寫鎖也具有比讀鎖更高的優(yōu)先級(jí)览爵，因此即使讀取器已經(jīng)在隊(duì)列中，寫鎖的請(qǐng)求也將前進(jìn)到鎖隊(duì)列的前面（寫鎖可以超過隊(duì)列中的讀鎖夫壁，但讀鎖不能提前過去寫鎖）拾枣。

雖然存儲(chǔ)引擎可以管理自己的鎖，但MySQL本身也使用各種鎖，這些鎖實(shí)際上是用于各種目的的表級(jí)梅肤。例如ALTER TABLE司蔬，無論存儲(chǔ)引擎如何，服務(wù)器都會(huì)對(duì)語句使用表級(jí)鎖姨蝴。

行鎖

提供最大并發(fā)性（并且承載最大開銷）的鎖定樣式是使用行鎖俊啼。
此策略眾所周知的行級(jí)鎖定可在InnoDB和XtraDB存儲(chǔ)引擎中使用。
行鎖在存儲(chǔ)引擎中實(shí)現(xiàn)左医，而不是在服務(wù)器中實(shí)現(xiàn)（如果需要授帕，請(qǐng)參考邏輯體系結(jié)構(gòu)圖）。
服務(wù)器完全不知道存儲(chǔ)引擎中實(shí)現(xiàn)的鎖浮梢，正如您將在本章后面和整本書中看到的那樣跛十，存儲(chǔ)引擎都以自己的方式實(shí)現(xiàn)鎖定。

事務(wù)

事務(wù)是一組以原子方式處理的SQL queries秕硝，作為單個(gè)工作單元芥映。
這么一組sql queries，要么全部成功远豺，要么全部失敗奈偏。

原子性
事務(wù)必須作為單個(gè)不可分割的工作單元運(yùn)行，以便應(yīng)用或回滾整個(gè)事務(wù)躯护。當(dāng)事務(wù)是原子的時(shí)惊来，就沒有部分完成的事務(wù)：它是全部或全部。

一致性

數(shù)據(jù)庫應(yīng)始終從一個(gè)一致狀態(tài)移動(dòng)到下一個(gè)狀態(tài)棺滞。在我們的示例中裁蚁，一致性確保第3行和第4行之間的崩潰不會(huì)導(dǎo)致$ 200從支票帳戶中消失。由于事務(wù)從未提交检眯，因此事務(wù)的所有更改都不會(huì)反映在數(shù)據(jù)庫中厘擂。

隔離性
在事務(wù)完成之前，事務(wù)的結(jié)果通常對(duì)其他事務(wù)是不可見的锰瘸。這樣可以確保如果銀行帳戶摘要在第3行之后但在我們的示例中第4行之前運(yùn)行，則它仍會(huì)在支票帳戶中看到$ 200昂灵。當(dāng)我們討論隔離級(jí)別時(shí)避凝，你會(huì)理解為什么我們說通常是 不可見的。

持久性
一旦提交眨补，事務(wù)的更改是永久性的管削。

隔離級(jí)別

SQL標(biāo)準(zhǔn)定義了四個(gè)隔離級(jí)別，其中包含特定的規(guī)則撑螺，在事務(wù)內(nèi)外都可以看到更改含思。
較低的隔離級(jí)別通常允許更高的并發(fā)性并具有更低的開銷。
注意
每個(gè)存儲(chǔ)引擎實(shí)現(xiàn)的隔離級(jí)別略有不同，如果您習(xí)慣使用其他數(shù)據(jù)庫產(chǎn)品含潘，它們不一定符合您的預(yù)期（因此饲做，我們不會(huì)在本節(jié)中詳細(xì)介紹）。您應(yīng)該閱讀您決定使用的任何存儲(chǔ)引擎的手冊(cè)遏弱。

單獨(dú)的增刪改查等一條sql語句必須是原子操作盆均，也就是或行在同一時(shí)刻只能被一個(gè)線程寫入;每一行在寫數(shù)據(jù)的時(shí)候，都是被鎖起來的漱逸，同一時(shí)間每一行只允許一個(gè)線程執(zhí)行寫數(shù)據(jù)泪姨。

臟讀：讀取未提交的數(shù)據(jù)也稱為臟讀。

一個(gè)事務(wù)包含多條sql語句饰抒，以轉(zhuǎn)賬為例肮砾，

1   START TRANSACTION;
2   SELECT balance FROM checking WHERE customer_id = 10233276;
3   UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;
4   UPDATE savings  SET balance = balance + 200.00 WHERE customer_id = 10233276;
5   COMMIT;

2，3袋坑，4每條執(zhí)行都是原子操作仗处，也就是當(dāng)前行被鎖起來了，只允許一個(gè)線程寫數(shù)據(jù)咒彤；

當(dāng)執(zhí)行了3疆柔，后續(xù)sql還沒執(zhí)行呢，事務(wù)還沒提交呢镶柱，這個(gè)時(shí)候用戶customer_id = 10233276的戶頭上就消失了200美金旷档，如果4執(zhí)行失敗，那問題就大了歇拆。這就是臟讀鞋屈。

不可重復(fù)讀：運(yùn)行相同的語句兩次但是查看的數(shù)據(jù)是不同的；

為什么故觅？因?yàn)槭聞?wù)對(duì)外不可見厂庇，舉例兩個(gè)事務(wù)1，事務(wù)2
事務(wù)2在事務(wù)1開始前讀了數(shù)據(jù)输吏，
恰巧事務(wù)1修改的就是事務(wù)2要讀取的數(shù)據(jù)权旷，當(dāng)事務(wù)1執(zhí)行并提交，
事務(wù)2再次查詢贯溅，這次查詢到的是事務(wù)1提交后的數(shù)據(jù)拄氯，
針對(duì)事務(wù)2來說，兩次查詢它浅，但是兩次查詢出來的數(shù)據(jù)不一樣译柏。

大多數(shù)數(shù)據(jù)庫系統(tǒng)（但不是MySQL！）的默認(rèn)隔離級(jí)別是READ COMMITTED姐霍。
它滿足前面使用的簡單隔離定義：事務(wù)只會(huì)看到那些在開始時(shí)已經(jīng)提交的事務(wù)所做的更改鄙麦，并且在其提交之前典唇，其他更改將不會(huì)被其他人看到。

這個(gè)級(jí)別仍然允許所謂的不可重復(fù)的閱讀胯府。
這意味著您可以運(yùn)行相同的語句兩次并查看不同的數(shù)據(jù)介衔。

幻讀：保證同一事務(wù)中兩次讀取的數(shù)據(jù)相同，但是第二次的讀取數(shù)據(jù)依然是第一次版本的數(shù)據(jù)盟劫，只是“看起來相同”而已夜牡；

因?yàn)檫€是不能阻止其他事務(wù)在這個(gè)多行數(shù)據(jù)范圍內(nèi)插入新數(shù)據(jù)和修改舊數(shù)據(jù)；

REPEATABLE READ 解決了READ UNCOMMITTED允許的問題侣签。
它保證事務(wù)讀取的任何行在同一事務(wù)中的后續(xù)讀取中“看起來相同”塘装，但理論上它仍然允許另一個(gè)棘手的問題：幻影讀。
簡單地說影所，當(dāng)您選擇某些行范圍時(shí)蹦肴，可能會(huì)發(fā)生幻像讀取，另一個(gè)事務(wù)會(huì)在該范圍中插入新行猴娩，然后再次選擇相同的范圍; 然后你會(huì)看到新的“幽靈”行阴幌。

InnoDB和XtraDB使用多版本并發(fā)控制來解決幻像讀取問題，我們將在本章后面解釋卷中。

image.png

死鎖：只要不是多行范圍鎖定矛双，都是有死鎖的可能。

一個(gè) 死鎖是指兩個(gè)或多個(gè)事務(wù)相互持有并請(qǐng)求鎖定相同資源蟆豫，從而創(chuàng)建依賴關(guān)系循環(huán)议忽。當(dāng)事務(wù)嘗試以不同的順序鎖定資源時(shí)會(huì)發(fā)生死鎖。只要多個(gè)事務(wù)鎖定相同的資源十减，它們就會(huì)發(fā)生栈幸。例如，考慮針對(duì)StockPrice表運(yùn)行的這兩個(gè)事務(wù)：

Transaction #1

START TRANSACTION;
UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
COMMIT;

Transaction #2

START TRANSACTION;
UPDATE StockPrice SET high  = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
UPDATE StockPrice SET high  = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
COMMIT;

如果你運(yùn)氣不好帮辟，每個(gè)事務(wù)都會(huì)執(zhí)行第一個(gè)查詢并更新一行數(shù)據(jù)速址，并將其鎖定在進(jìn)程中。然后由驹，每個(gè)事務(wù)將嘗試更新其第二行芍锚，但卻發(fā)現(xiàn)它已被鎖定。這兩個(gè)事務(wù)將永遠(yuǎn)等待彼此完成蔓榄，除非有什么干預(yù)來打破僵局闹炉。

為了解決這個(gè)問題，數(shù)據(jù)庫系統(tǒng)實(shí)現(xiàn)了各種形式的死鎖檢測(cè)和超時(shí)润樱。更復(fù)雜的系統(tǒng)，例如InnoDB存儲(chǔ)引擎羡棵，會(huì)注意到循環(huán)依賴并立即返回錯(cuò)誤壹若。這可能是一件好事 - 否則，死鎖會(huì)表現(xiàn)為非常慢的查詢。其他人將在查詢超過鎖定等待超時(shí)后放棄店展，這并不總是好的养篓。方式InnoDB當(dāng)前處理死鎖是為了回滾具有最少獨(dú)占行鎖的事務(wù)（其中一個(gè)近似的度量標(biāo)準(zhǔn)將是最容易回滾的）。

鎖定行為和順序是特定于存儲(chǔ)引擎的赂蕴，因此某些存儲(chǔ)引擎可能會(huì)在某些語句序列上死鎖柳弄，即使其他語句也不會(huì)。死鎖具有雙重性質(zhì)：由于真正的數(shù)據(jù)沖突概说，有些是不可避免的碧注，有些是由存儲(chǔ)引擎的工作原理引起的。

如果不部分或全部回滾其中一個(gè)交易糖赔，就不能破解死鎖萍丐。它們是事務(wù)系統(tǒng)中的事實(shí)，您的應(yīng)用程序應(yīng)該設(shè)計(jì)為處理它們放典。許多應(yīng)用程序可以從一開始就簡單地重試它們的事務(wù)逝变。

事務(wù)記錄：就是我考慮的災(zāi)害情況下：斷電，數(shù)據(jù)庫的記錄恢復(fù)

事務(wù)記錄有助于提高事務(wù)處理效率奋构。
每次發(fā)生更改時(shí)壳影，存儲(chǔ)引擎都可以更改其內(nèi)存中的數(shù)據(jù)副本，而不是更新磁盤上的表弥臼。
這非逞邕郑快。然后醋火，存儲(chǔ)引擎可以將更改記錄寫入事務(wù)日志悠汽，該日志位于磁盤上，因此是持久的芥驳。

這也是一個(gè)相對(duì)較快的操作柿冲，因?yàn)楦郊尤罩臼录婕按疟P的一個(gè)小區(qū)域中的順序I / O，而不是許多地方的隨機(jī)I / O.
然后兆旬，稍后假抄，進(jìn)程可以更新磁盤上的表。

因此丽猬，大多數(shù)使用這種技術(shù)的存儲(chǔ)引擎（稱為預(yù)寫日志記錄）最后將更改寫入磁盤兩次宿饱。

如果在將更新寫入事務(wù)日志之后但在對(duì)數(shù)據(jù)本身進(jìn)行更改之前發(fā)生崩潰，則存儲(chǔ)引擎仍可在重新啟動(dòng)時(shí)恢復(fù)更改脚祟∶裕恢復(fù)方法因存儲(chǔ)引擎而異。

我的方法：
第一步：日志記錄第一行：寫下事務(wù)sql由桌；
第二步： 向磁盤寫數(shù)據(jù)为黎，持久化數(shù)據(jù)邮丰；

第三步：日志記錄 第二行：ack

當(dāng)在第二步發(fā)生了災(zāi)害：斷電，事務(wù)要寫的數(shù)據(jù)沒有完全執(zhí)行铭乾，就不會(huì)有第三步ack剪廉，當(dāng)恢復(fù)數(shù)據(jù)的時(shí)候，只有看到日志記錄中這個(gè)事務(wù)有 ack記錄炕檩，那么才能認(rèn)定事務(wù)提交并寫入成功斗蒋，否則的話，不會(huì)恢復(fù)沒有ack的事務(wù)笛质；

多版本并發(fā)控制

InnoDB通過在每行中存儲(chǔ)兩個(gè)額外的隱藏值來實(shí)現(xiàn)MVCC泉沾，這些值記錄了創(chuàng)建行以及何時(shí)過期（或刪除）。該行不存儲(chǔ)發(fā)生這些事件的實(shí)際時(shí)間经瓷，而是存儲(chǔ)每個(gè)事件發(fā)生時(shí)的系統(tǒng)版本號(hào)爆哑。這是一個(gè)每次交易開始時(shí)遞增的數(shù)字。每個(gè)事務(wù)在其開始時(shí)保留其自己的當(dāng)前系統(tǒng)版本記錄舆吮。每個(gè)查詢都必須根據(jù)事務(wù)的版本檢查每一行的版本號(hào)揭朝。讓我們看看當(dāng)事務(wù)隔離級(jí)別設(shè)置為時(shí)，這如何適用于特定操作REPEATABLE READ：

InnoDB必須檢查每一行以確保它符合兩個(gè)標(biāo)準(zhǔn)：

1. InnoDB必須找到至少與事務(wù)一樣舊的行的版本（即色冀，其版本必須小于或等于事務(wù)的版本）潭袱。這可確保在事務(wù)開始之前存在行，或者事務(wù)創(chuàng)建或更改行锋恬。

2. 行的刪除版本必須未定義或大于事務(wù)的版本屯换。這可確保在事務(wù)開始之前未刪除該行。

通過兩個(gè)測(cè)試的行可以作為查詢的結(jié)果返回与学。

InnoDB使用新行記錄當(dāng)前系統(tǒng)版本號(hào)彤悔。

InnoDB將當(dāng)前系統(tǒng)版本號(hào)記錄為行的刪除ID。

InnoDB使用新行版本的系統(tǒng)版本號(hào)寫入該行的新副本索守。它還將系統(tǒng)版本號(hào)寫為舊行的刪除版本晕窑。

所有這些額外記錄保留的結(jié)果是大多數(shù)讀取查詢永遠(yuǎn)不會(huì)獲得鎖定。他們只是盡可能快地讀取數(shù)據(jù)卵佛，確保只選擇符合條件的行杨赤。缺點(diǎn)是存儲(chǔ)引擎必須在每行存儲(chǔ)更多數(shù)據(jù)，在檢查行時(shí)執(zhí)行更多工作截汪，以及處理一些額外的內(nèi)務(wù)操作疾牲。

MVCC只適用于REPEATABLE READ和READ COMMITTED 隔離級(jí)別。 READ UNCOMMITTED不是MVCC兼容的^{[ 7 ]衙解，}因?yàn)椴樵儾粫?huì)讀取適合其事務(wù)版本的行版本; 他們閱讀最新版本阳柔，無論如何。SERIALIZABLE 不是MVCC兼容的蚓峦，因?yàn)樽x取鎖定它們返回的每一行盔沫。

java連接池和mysql線程

java客戶端都是先創(chuàng)建固定數(shù)量的連接池医咨，
而連接池就對(duì)應(yīng)了mysql的線程，

舉例架诞，連接池中連接數(shù)量是8個(gè)連接，
那對(duì)應(yīng)就是8個(gè)線程干茉，
當(dāng)連接池都被占用谴忧，加入連接池等待隊(duì)列，
所以說角虫，mysql根本就沒啥事務(wù)隊(duì)列沾谓， mysql就8個(gè)線程而已，
再借助版本并發(fā)控制（mvvc）戳鹅，
寫鎖是獨(dú)占鎖均驶，寫數(shù)據(jù)時(shí)，不允許其他線程讀數(shù)據(jù)和寫數(shù)據(jù)枫虏，

讀鎖是共享鎖妇穴，

問題01：怎么保證java客戶端先后發(fā)出的兩次事務(wù)，最后數(shù)據(jù)庫的數(shù)據(jù)是最后一次修改后的最新數(shù)據(jù)呢隶债？

考慮網(wǎng)絡(luò)延遲腾它，用戶最先發(fā)出的請(qǐng)求，因?yàn)榫W(wǎng)絡(luò)延遲死讹，第二個(gè)到達(dá)服務(wù)端瞒滴；

服務(wù)端發(fā)送給數(shù)據(jù)庫的事務(wù)請(qǐng)求，也因?yàn)榫W(wǎng)絡(luò)延遲赞警，第一個(gè)發(fā)出的事務(wù)請(qǐng)求妓忍，反而第二個(gè)到達(dá)；

比如現(xiàn)在a 地址余額不足愧旦，
用戶向a地址轉(zhuǎn)賬100元世剖，然后用戶在a地址花掉100元，
但是因?yàn)榫W(wǎng)絡(luò)延遲忘瓦，導(dǎo)致用戶先花掉100元在先搁廓，而轉(zhuǎn)賬100元到地址a在后；

沒辦法避免的耕皮，只能通過業(yè)務(wù)手段區(qū)分境蜕；

比如轉(zhuǎn)賬操作，都是有到賬時(shí)間的凌停，不能說我還沒到賬粱年，你就可以花錢，
開發(fā)人員對(duì)未來可能的數(shù)據(jù)變化是不知道的罚拟，
只能盯著過去已經(jīng)存在的數(shù)據(jù)台诗，也就是從表中讀取數(shù)據(jù)完箩；
對(duì)未來的數(shù)據(jù)，不用管拉队；

只看你當(dāng)前余額弊知，未到賬的余額，不計(jì)入的粱快，

比如轉(zhuǎn)賬秩彤，余額充足你就轉(zhuǎn)賬唄，

場(chǎng)景03:修改商品數(shù)據(jù)
常見04事哭；修改用戶信息
針對(duì)這種場(chǎng)景也是需要前臺(tái)業(yè)務(wù)流程的引導(dǎo)的漫雷；

問題02:怎么保證并發(fā)修改同一條數(shù)據(jù)，最終結(jié)果就是最新提交的數(shù)據(jù)呢鳍咱？
答案：mysql管不了你的業(yè)務(wù)邏輯降盹，mysql只知道有幾個(gè)線程，
現(xiàn)在這個(gè)線程要執(zhí)行什么任務(wù)谤辜，是阻塞蓄坏，還是讀，還是寫每辟，

共享鎖和獨(dú)占鎖
reentrantreadwritelock example

image.png

業(yè)務(wù)邏輯需要你自己在開發(fā)中設(shè)計(jì)胳岂，你就不應(yīng)該讓并發(fā)修改相同數(shù)據(jù)的事務(wù)出現(xiàn)，這是你業(yè)務(wù)邏輯的問題舔稀；

java客戶端只需要?jiǎng)?chuàng)建數(shù)據(jù)庫連接池就行乳丰，把控業(yè)務(wù)邏輯；