好久沒更新簡書了，最近遇上一些事情拖慢了讀書進(jìn)度，望見諒！由于本書篇幅較大，所以我決定今后每看完一章就更新一章的筆記，我會(huì)一如既往的和大家一起共同走下去，我相信讀書路上你我同在

激萌.jpg

mysql流程

1. 客戶端發(fā)起對mysql數(shù)據(jù)庫服務(wù)器的連接操作，mysql服務(wù)器內(nèi)部通過連接池維護(hù)客戶端連接，每一個(gè)客戶端的查詢操作都建立在一個(gè)獨(dú)立的mysql連接上
2. 當(dāng)客戶端發(fā)起sql查詢時(shí)，mysql服務(wù)器首先會(huì)在查詢緩存中查找是否有已經(jīng)存在的查詢記錄，有就返回，否則，開始解析客戶端發(fā)來的sql查詢語句并進(jìn)行分析優(yōu)化，之后通過API調(diào)用存儲引擎獲取查詢結(jié)果，并將結(jié)果保存到緩存中，同時(shí)返回給客戶端
   下面是整個(gè)流程的簡略圖

mysql請求流程.png

并發(fā)控制

只要存在同一時(shí)刻多個(gè)查詢修改同一數(shù)據(jù)的情況，就會(huì)有并發(fā)問題產(chǎn)生，在 mysql中可以有兩個(gè)層面的并發(fā)控制：服務(wù)器層和存儲引擎層
一種解決的方式是通過加鎖機(jī)制來解決，但是加鎖機(jī)制并不能支持并發(fā)的處理，因?yàn)樵谌我庖粋€(gè)時(shí)刻只有一個(gè)進(jìn)程可以進(jìn)行操作，在大容量高并發(fā)系統(tǒng)中，這是一個(gè)瓶頸

讀寫鎖

讀取數(shù)據(jù)沒有并發(fā)的問題產(chǎn)生，但是在讀取數(shù)據(jù)的同時(shí)對數(shù)據(jù)進(jìn)行操作比如更新，刪除操作等，那么會(huì)導(dǎo)致未知的異常情況
可以通過兩種類型的鎖來解決上面的問題，它們是：共享鎖與排他鎖，也叫讀鎖與寫鎖
讀鎖：讀鎖是共享的，多個(gè)客戶同一時(shí)刻讀取同一資源，互相不干擾
寫鎖：排他的，一個(gè)寫鎖會(huì)阻塞其他寫鎖和讀鎖的操作

鎖粒度

提高共享資源并發(fā)性的方式就是讓鎖定的資源對象更有選擇性，盡量只鎖定需要修改的部分?jǐn)?shù)據(jù)，而不是所有資源
下面提供了mysql的兩種最重要的鎖策略

表鎖

表鎖是mysql最基本的鎖策略，并且是開銷最小的策略，它會(huì)鎖定整張表
用戶在對表進(jìn)行寫操作(增、刪、改)前需要先獲得寫鎖，這會(huì)阻塞其他用戶對該表的所有讀寫操作，沒有寫鎖時(shí)，其他讀取的用戶才能獲得讀鎖，讀鎖之間是不相互阻塞的，表鎖基于服務(wù)器層建立鎖

行級鎖

行級鎖可以最大程度的支持并發(fā)處理，它是由存儲引擎實(shí)現(xiàn)的，它一次只鎖定事務(wù)需要訪問的行記錄

事務(wù)

mysql數(shù)據(jù)庫的ACID特性

原子性(automicity):事務(wù)中的sql被當(dāng)成是一個(gè)不可分割的單位，整個(gè)事務(wù)要么全部成功，要么全部失敗
一致性(consistency):數(shù)據(jù)庫總是從一個(gè)狀態(tài)跳轉(zhuǎn)到另一個(gè)狀態(tài)，如果轉(zhuǎn)賬來說，在用戶A轉(zhuǎn)出200塊錢但是用戶B還沒有收到錢的時(shí)候出現(xiàn)了問題，那么用戶A的賬戶應(yīng)該還是原來余額，轉(zhuǎn)賬成功后用戶A才少了200塊，B多了200塊
隔離性(isolation): 不同事務(wù)之間應(yīng)該是相互獨(dú)立的，在A用戶轉(zhuǎn)出200塊錢之前，用戶查詢的A用戶余額與A用戶轉(zhuǎn)出之后但事務(wù)還沒有結(jié)束查詢到的A的余額通常來說應(yīng)該是一致的
持久性(durability): 事務(wù)提交的修改操作將永久保存，即使數(shù)據(jù)庫發(fā)生崩潰，但重啟之后也會(huì)看到已經(jīng)修改之后的結(jié)果，但這種持久性并不是說就一定是永久的，持久性也分為了很多個(gè)級別

隔離級別

未提交讀(read uncommitted): 事務(wù)A讀取到事務(wù)B修改但是還沒有提交的數(shù)據(jù)，也稱為臟讀
提交讀(read committed): 事務(wù)A不能讀取到事務(wù)B還沒有提交的數(shù)據(jù)，但是可以讀取到已經(jīng)提交的數(shù)據(jù)，這就導(dǎo)致事務(wù)A可能在事務(wù)B沒開始之前讀取的數(shù)據(jù)與事務(wù)B提交之后的數(shù)據(jù)結(jié)果不一致，因?yàn)槭聞?wù)B很有可能會(huì)修改事務(wù)A讀取的數(shù)據(jù)，也稱為不可重復(fù)讀
可重復(fù)讀(repeatable committed): 保證事務(wù)A在事務(wù)范圍中讀取到的一段范圍中的記錄結(jié)果一致的，但是這不可避免其他事務(wù)可能會(huì)在這個(gè)范圍中插入新記錄，這樣就導(dǎo)致事務(wù)A讀出來的結(jié)果會(huì)有多余的記錄，在innodb中采用間隙鎖來防止幻行的出現(xiàn)
序列讀(serializable):多個(gè)事務(wù)串行讀取
未提交度、提交度針對單條記錄，重復(fù)讀針對的是范圍數(shù)據(jù)

死鎖

多個(gè)事務(wù)（至少兩個(gè)）在同一資源上相互引用，并請求對方已經(jīng)鎖定占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象，這就是死鎖
為了解決這個(gè)問題，數(shù)據(jù)庫提供了各種死鎖檢測和死鎖超時(shí)機(jī)制
一種是通過檢測的方式檢查是否存在死鎖，如果存在就立即返回錯(cuò)誤，另一種是當(dāng)查詢達(dá)到鎖等待超時(shí)的設(shè)定后放棄請求，innodb目前處理死鎖的方式是將持有最少行級排它鎖的事務(wù)進(jìn)行回滾
鎖的行為和順序和存儲引擎相關(guān)，以同樣的順序執(zhí)行語句，不同的存儲引擎可能會(huì)產(chǎn)生死鎖，有些又不會(huì)，當(dāng)產(chǎn)生死鎖之后，只能通過回滾其中一方事務(wù)才能打破死鎖僵局

事務(wù)屬性

自動(dòng)提交設(shè)置

mysql默認(rèn)情況是自動(dòng)提交的，通過
show variables like ‘%commit%’;

Variable_name value
autocommit     on

可以通過set autocommit = 1;設(shè)置開啟或者關(guān)閉自動(dòng)提交
當(dāng)關(guān)閉了自動(dòng)提交之后，mysql就必須通過rollback或者commit才能提交一個(gè)事務(wù)執(zhí)行的結(jié)果
修改非事務(wù)類型表（myISAM或者內(nèi)存表）不會(huì)有影響，相當(dāng)于autocommit=on

隔離級別設(shè)置

通過select @@tx_isolation;或者
show variables like ‘%isolation%’;來查看當(dāng)前mysql的隔離級別
通過set tx_isolation = ‘read uncommitted | read committed | repeatable read | serializable’或者
set [session/global] transaction isolation level [ read uncommitted | read committed | repeatable read | serializable ]
來設(shè)置當(dāng)前事物隔離級別
session針對會(huì)話，global針對整個(gè)數(shù)據(jù)庫

mysql隱式和顯示鎖定

在事務(wù)執(zhí)行過程中，隨時(shí)都可以鎖定，這是由mysql根據(jù)不同的隔離級別自動(dòng)加鎖的，也可以通過顯示的指定鎖定
select ... lock in share mode # 樂觀鎖
select ... for update # 悲觀鎖
以上兩種查詢并不屬于sql規(guī)范

mysql事務(wù)是在存儲引擎層面實(shí)現(xiàn)的，所以不同的存儲引擎可能效果會(huì)不太一樣
mysql也支持lock tables和unlock tables但是它并不能代替事務(wù)處理，如果要使用事務(wù)，還是要選擇支持事務(wù)處理的存儲引擎

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

全稱multiply version consistency control
MVCC可以理解為行級鎖的變種，但是它并不需要加鎖控制，所以更高效，雖然與行級鎖實(shí)現(xiàn)不同，但是他們都實(shí)現(xiàn)了非阻塞式讀操作，同時(shí)也只是鎖定了必要的行
MVCC是通過保存在某一個(gè)時(shí)間點(diǎn)上的快照來實(shí)現(xiàn)的，每個(gè)事務(wù)從不同的時(shí)刻開始對同一張表的看到的數(shù)據(jù)可能是不一樣的
MVCC根據(jù)不同的存儲引擎，實(shí)現(xiàn)方式有所不同，分兩種：樂觀與悲觀

innoDB的多版本并發(fā)控制原理：
innoDB的多版本是在行的后面保存兩個(gè)隱藏列實(shí)現(xiàn)的，一個(gè)記錄行的創(chuàng)建時(shí)間，一個(gè)記錄行的刪除時(shí)間，當(dāng)然這兩個(gè)列并不是記錄真正的時(shí)間，而是記錄了系統(tǒng)版本號，每開始一個(gè)事務(wù)，系統(tǒng)版本號都會(huì)自動(dòng)增加，而事務(wù)開始時(shí)刻的系統(tǒng)版本號會(huì)作為事務(wù)的版本號，用來和查詢到的每行的記錄版本號做比較
結(jié)合上面的兩個(gè)列分析CRUD操作的具體實(shí)現(xiàn)
select
innoDB會(huì)根據(jù)以下兩個(gè)條件查詢結(jié)果記錄
1.只查找行創(chuàng)建版本早于(小于等于)當(dāng)前事務(wù)版本號的數(shù)據(jù)行，這樣可以確保事務(wù)讀取的記錄要么是在事務(wù)之前就已經(jīng)存在，要么就是在事務(wù)中創(chuàng)建的
2.行的刪除版本要么沒有定義，要么大于當(dāng)前版本號，這可以確保事務(wù)讀取到的行，在事務(wù)開始之前未被刪除
insert
為每條插入的記錄保存當(dāng)前系統(tǒng)版本號為行版本號
delete
為每條刪除的記錄保存當(dāng)前系統(tǒng)版本號為行刪除版本號
update
innoDB插入一條新行，并保存當(dāng)前系統(tǒng)版本號為行版本號，同時(shí)保存系統(tǒng)版本號到原來行的刪除記錄，作為行刪除標(biāo)識，update = insert + delete;

mysql存儲引擎

mysql將每一個(gè)數(shù)據(jù)庫保存為數(shù)據(jù)目錄下的一個(gè)子目錄，創(chuàng)建表時(shí)，會(huì)在該子目錄下創(chuàng)建一個(gè)和表同名的.frm文件，保存表的定義與結(jié)構(gòu)
查看user表狀態(tài)
show table status like ‘user’ \G
查看user表具體字段信息
desc user
查看創(chuàng)建表時(shí)的語句結(jié)構(gòu)
show create table user;

查看表狀態(tài).png

這里需要說明的一項(xiàng)是Data_free,對于myISAM引擎的數(shù)據(jù)庫來說，刪除并不會(huì)真正釋放原來占用的空間，所以這里表示的是已經(jīng)刪除的行與后續(xù)可以被insert利用的空間

innoDB存儲引擎

innoDB作為mysql的默認(rèn)存儲引擎，它將表數(shù)據(jù)存放在單獨(dú)的表空間里面
(在windows中，這里以test/user表為例)
E:\MySQL\ProgramData\MySQL Server 5.5\data
在data數(shù)據(jù)目錄中存放有test數(shù)據(jù)庫子文件夾，里面用于存放test數(shù)據(jù)庫中的各個(gè)表的結(jié)構(gòu)定義.frm以及索引信息.ibd，在上層目錄中ibdata1為所有表共享的表空間

innodb表空間

當(dāng)innodb引擎中表的數(shù)據(jù)發(fā)生巨大變化時(shí)，該文件的大小也將隨之發(fā)生變化，這一步是自動(dòng)產(chǎn)生的。

innoDB采用MVCC(多版本并發(fā)控制)策略來支持高并發(fā)，支持四個(gè)隔離級別,READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ(default) | SERIALIZABLE ,并通過間隙鎖防止幻讀，間隙鎖不僅鎖定查詢中的行，同時(shí)也會(huì)對索引中的間隙進(jìn)行鎖定，不允許幻行插入進(jìn)來。

innoDB索引通過聚簇索引創(chuàng)建，聚簇索引對主鍵的查詢性能很高，但是二級索引(也就是非主鍵索引)中必須包含主鍵列，如果表中的索引比較多的話，主鍵索引應(yīng)該盡可能的小

innoDB通過一些機(jī)制和工具支持熱備份，其他引擎是不支持的，其他引擎如果要備份數(shù)據(jù)，就必須先停止對表的寫入操作
innoDB支持鎖粒度更小的行級鎖(存儲引擎實(shí)現(xiàn))

myISAM存儲引擎

myISAM不支持行級鎖及事務(wù)，崩潰后無法修復(fù)
myISAM會(huì)將表存儲在三個(gè)文件中: .frm，.MYD，.MYI，.frm用于存儲表結(jié)構(gòu)，.myd用于存儲數(shù)據(jù)，.myi用于存儲索引

myisam表結(jié)構(gòu)存儲

myISAM只會(huì)將數(shù)據(jù)寫入到內(nèi)存中，然后等待os定期將數(shù)據(jù)刷到磁盤上，myISAM刪除記錄并不會(huì)馬上更新數(shù)據(jù)文件.myd的大小，如果想要立馬看到效果，可以通過使用optimize table來手動(dòng)更新才能看到效果

加鎖與并發(fā)

myISAM支持加鎖機(jī)制，但是加鎖是在整張表上進(jìn)行的。讀取時(shí)，對所有讀到的表加共享鎖，寫入時(shí)對寫入的表加排他鎖，但是在讀取查詢時(shí)，也允許向表中插入新的記錄

修復(fù)

對于myISAM引擎表，可以進(jìn)行手工或者自動(dòng)檢查和修復(fù)，與事務(wù)支持的表修復(fù)有所區(qū)別，執(zhí)行表的修復(fù)可能會(huì)丟失一部分?jǐn)?shù)據(jù)，通過check table tablename查看表的錯(cuò)誤，通過repair table tablename 修復(fù)錯(cuò)誤的表，但是這個(gè)過程通常很漫長，即使mysql關(guān)閉也可以通過myisamchk命令檢查

轉(zhuǎn)換表的引擎

有三種方式可以修改表的引擎

Alter Table

alter table mytable ENGINE=InnoDB #注意大小寫
該方法適用于任何引擎，但是執(zhí)行很慢，因?yàn)樗鼘⒃碇械臄?shù)據(jù)復(fù)制到新表中，并加上讀鎖，一個(gè)替代方案是采用導(dǎo)入導(dǎo)出方法手動(dòng)進(jìn)行表復(fù)制
在轉(zhuǎn)換表的過程中可能會(huì)丟失一些表的特性，比如講InnoDB表轉(zhuǎn)化為myISAM再轉(zhuǎn)化為InnoDB,那么原來表中的外鍵會(huì)消失

導(dǎo)入導(dǎo)出(mysqldump)

使用mysqldump工具將數(shù)據(jù)導(dǎo)出到文件，然后修改文件中create table語句的存儲引擎選項(xiàng)，注意同時(shí)修改表名，同一個(gè)數(shù)據(jù)庫中不允許出現(xiàn)多個(gè)相同表名的數(shù)據(jù)表，即使是不同的存儲引擎，另外，mysqldump會(huì)在每一個(gè)create table前面添加drop table語句

創(chuàng)建與查詢(create insert)

綜合第一種的高效和第二種的安全，不需要導(dǎo)出整個(gè)表的數(shù)據(jù)，首先要建立一個(gè)新的存儲引擎表，然后利用insert .... select ...的語法來導(dǎo)數(shù)據(jù)
mysql> create table innodb_table like user;
mysql> alter table innodb_table set ENGINE=InnoDB
mysql> insert into innodb_table select * from user;
這樣就把user表從myisam改變成innodb引擎的innodb_table表了
如果表中的數(shù)據(jù)量比較大，那么可能需要分批進(jìn)行處理
mysql> start transaction;
mysql> insert into innodb_table select * from user where id between x and y;
mysql> commit;
還可以通過pt-online-schema-change的工具來完成上述操作