好久沒更新簡書了，最近遇上一些事情拖慢了讀書進度炼鞠，望見諒缘滥！由于本書篇幅較大，所以我決定今后每看完一章就更新一章的筆記谒主，我會一如既往的和大家一起共同走下去朝扼，我相信讀書路上你我同在
mysql流程
- 客戶端發(fā)起對
mysql數(shù)據(jù)庫服務器的連接操作，mysql服務器內(nèi)部通過連接池維護客戶端連接霎肯，每一個客戶端的查詢操作都建立在一個獨立的mysql連接上 - 當客戶端發(fā)起
sql查詢時吟税，mysql服務器首先會在查詢緩存中查找是否有已經(jīng)存在的查詢記錄，有就返回姿现，否則肠仪，開始解析客戶端發(fā)來的sql查詢語句并進行分析優(yōu)化，之后通過API調(diào)用存儲引擎獲取查詢結果备典，并將結果保存到緩存中异旧，同時返回給客戶端
下面是整個流程的簡略圖
并發(fā)控制
只要存在同一時刻多個查詢修改同一數(shù)據(jù)的情況，就會有并發(fā)問題產(chǎn)生提佣，在 mysql中可以有兩個層面的并發(fā)控制：服務器層和存儲引擎層
一種解決的方式是通過加鎖機制來解決吮蛹，但是加鎖機制并不能支持并發(fā)的處理荤崇，因為在任意一個時刻只有一個進程可以進行操作，在大容量高并發(fā)系統(tǒng)中潮针，這是一個瓶頸
讀寫鎖
讀取數(shù)據(jù)沒有并發(fā)的問題產(chǎn)生术荤，但是在讀取數(shù)據(jù)的同時對數(shù)據(jù)進行操作比如更新，刪除操作等每篷，那么會導致未知的異常情況
可以通過兩種類型的鎖來解決上面的問題瓣戚，它們是：共享鎖與排他鎖，也叫讀鎖與寫鎖
讀鎖：讀鎖是共享的焦读，多個客戶同一時刻讀取同一資源子库，互相不干擾
寫鎖：排他的，一個寫鎖會阻塞其他寫鎖和讀鎖的操作
鎖粒度
提高共享資源并發(fā)性的方式就是讓鎖定的資源對象更有選擇性矗晃，盡量只鎖定需要修改的部分數(shù)據(jù)仑嗅，而不是所有資源
下面提供了mysql的兩種最重要的鎖策略
表鎖
表鎖是mysql最基本的鎖策略，并且是開銷最小的策略张症，它會鎖定整張表
用戶在對表進行寫操作(增仓技、刪、改)前需要先獲得寫鎖俗他，這會阻塞其他用戶對該表的所有讀寫操作脖捻，沒有寫鎖時，其他讀取的用戶才能獲得讀鎖拯辙，讀鎖之間是不相互阻塞的郭变，表鎖基于服務器層建立鎖
行級鎖
行級鎖可以最大程度的支持并發(fā)處理颜价，它是由存儲引擎實現(xiàn)的涯保，它一次只鎖定事務需要訪問的行記錄
事務
mysql數(shù)據(jù)庫的ACID特性
原子性(automicity):事務中的sql被當成是一個不可分割的單位，整個事務要么全部成功周伦，要么全部失敗
一致性(consistency):數(shù)據(jù)庫總是從一個狀態(tài)跳轉(zhuǎn)到另一個狀態(tài)夕春，如果轉(zhuǎn)賬來說，在用戶A轉(zhuǎn)出200塊錢但是用戶B還沒有收到錢的時候出現(xiàn)了問題专挪，那么用戶A的賬戶應該還是原來余額及志，轉(zhuǎn)賬成功后用戶A才少了200塊，B多了200塊
隔離性(isolation): 不同事務之間應該是相互獨立的寨腔，在A用戶轉(zhuǎn)出200塊錢之前速侈，用戶查詢的A用戶余額與A用戶轉(zhuǎn)出之后但事務還沒有結束查詢到的A的余額通常來說應該是一致的
持久性(durability): 事務提交的修改操作將永久保存，即使數(shù)據(jù)庫發(fā)生崩潰迫卢，但重啟之后也會看到已經(jīng)修改之后的結果倚搬，但這種持久性并不是說就一定是永久的，持久性也分為了很多個級別
隔離級別
未提交讀(read uncommitted): 事務A讀取到事務B修改但是還沒有提交的數(shù)據(jù)乾蛤，也稱為臟讀
提交讀(read committed): 事務A不能讀取到事務B還沒有提交的數(shù)據(jù)每界，但是可以讀取到已經(jīng)提交的數(shù)據(jù)捅僵，這就導致事務A可能在事務B沒開始之前讀取的數(shù)據(jù)與事務B提交之后的數(shù)據(jù)結果不一致，因為事務B很有可能會修改事務A讀取的數(shù)據(jù)眨层，也稱為不可重復讀
可重復讀(repeatable committed): 保證事務A在事務范圍中讀取到的一段范圍中的記錄結果一致的庙楚，但是這不可避免其他事務可能會在這個范圍中插入新記錄，這樣就導致事務A讀出來的結果會有多余的記錄趴樱，在innodb中采用間隙鎖來防止幻行的出現(xiàn)
序列讀(serializable):多個事務串行讀取
未提交度馒闷、提交度針對單條記錄，重復讀針對的是范圍數(shù)據(jù)
死鎖
多個事務（至少兩個）在同一資源上相互引用伊佃，并請求對方已經(jīng)鎖定占用的資源窜司，從而導致惡性循環(huán)的現(xiàn)象，這就是死鎖
為了解決這個問題航揉，數(shù)據(jù)庫提供了各種死鎖檢測和死鎖超時機制
一種是通過檢測的方式檢查是否存在死鎖塞祈，如果存在就立即返回錯誤，另一種是當查詢達到鎖等待超時的設定后放棄請求帅涂，innodb目前處理死鎖的方式是將持有最少行級排它鎖的事務進行回滾
鎖的行為和順序和存儲引擎相關议薪，以同樣的順序執(zhí)行語句，不同的存儲引擎可能會產(chǎn)生死鎖媳友，有些又不會斯议，當產(chǎn)生死鎖之后，只能通過回滾其中一方事務才能打破死鎖僵局
事務屬性
自動提交設置
mysql默認情況是自動提交的醇锚，通過
show variables like ‘%commit%’;
Variable_name value
autocommit on
可以通過set autocommit = 1;設置開啟或者關閉自動提交
當關閉了自動提交之后哼御，mysql就必須通過rollback或者commit才能提交一個事務執(zhí)行的結果
修改非事務類型表（myISAM或者內(nèi)存表）不會有影響，相當于autocommit=on
隔離級別設置
通過select @@tx_isolation;或者
show variables like ‘%isolation%’;來查看當前mysql的隔離級別
通過set tx_isolation = ‘read uncommitted | read committed | repeatable read | serializable’或者
set [session/global] transaction isolation level [ read uncommitted | read committed | repeatable read | serializable ]
來設置當前事物隔離級別
session針對會話焊唬，global針對整個數(shù)據(jù)庫
mysql隱式和顯示鎖定
在事務執(zhí)行過程中恋昼，隨時都可以鎖定，這是由mysql根據(jù)不同的隔離級別自動加鎖的赶促，也可以通過顯示的指定鎖定
select ... lock in share mode # 樂觀鎖
select ... for update # 悲觀鎖
以上兩種查詢并不屬于sql規(guī)范
mysql事務是在存儲引擎層面實現(xiàn)的液肌，所以不同的存儲引擎可能效果會不太一樣
mysql也支持lock tables和unlock tables但是它并不能代替事務處理，如果要使用事務鸥滨，還是要選擇支持事務處理的存儲引擎
多版本并發(fā)控制(MVCC)
全稱multiply version consistency control
MVCC可以理解為行級鎖的變種嗦哆，但是它并不需要加鎖控制，所以更高效婿滓，雖然與行級鎖實現(xiàn)不同老速，但是他們都實現(xiàn)了非阻塞式讀操作，同時也只是鎖定了必要的行
MVCC是通過保存在某一個時間點上的快照來實現(xiàn)的凸主，每個事務從不同的時刻開始對同一張表的看到的數(shù)據(jù)可能是不一樣的
MVCC根據(jù)不同的存儲引擎橘券，實現(xiàn)方式有所不同，分兩種：樂觀與悲觀
innoDB的多版本并發(fā)控制原理：
innoDB的多版本是在行的后面保存兩個隱藏列實現(xiàn)的，一個記錄行的創(chuàng)建時間约郁，一個記錄行的刪除時間缩挑，當然這兩個列并不是記錄真正的時間，而是記錄了系統(tǒng)版本號鬓梅，每開始一個事務供置，系統(tǒng)版本號都會自動增加，而事務開始時刻的系統(tǒng)版本號會作為事務的版本號绽快，用來和查詢到的每行的記錄版本號做比較
結合上面的兩個列分析CRUD操作的具體實現(xiàn)
select
innoDB會根據(jù)以下兩個條件查詢結果記錄
1.只查找行創(chuàng)建版本早于(小于等于)當前事務版本號的數(shù)據(jù)行芥丧，這樣可以確保事務讀取的記錄要么是在事務之前就已經(jīng)存在，要么就是在事務中創(chuàng)建的
2.行的刪除版本要么沒有定義坊罢，要么大于當前版本號续担，這可以確保事務讀取到的行，在事務開始之前未被刪除
insert
為每條插入的記錄保存當前系統(tǒng)版本號為行版本號
delete
為每條刪除的記錄保存當前系統(tǒng)版本號為行刪除版本號
update
innoDB插入一條新行活孩，并保存當前系統(tǒng)版本號為行版本號物遇，同時保存系統(tǒng)版本號到原來行的刪除記錄，作為行刪除標識憾儒，update = insert + delete;
mysql存儲引擎
mysql將每一個數(shù)據(jù)庫保存為數(shù)據(jù)目錄下的一個子目錄询兴，創(chuàng)建表時，會在該子目錄下創(chuàng)建一個和表同名的.frm文件起趾，保存表的定義與結構
查看user表狀態(tài)
show table status like ‘user’ \G
查看user表具體字段信息
desc user
查看創(chuàng)建表時的語句結構
show create table user;
這里需要說明的一項是Data_free,對于myISAM引擎的數(shù)據(jù)庫來說诗舰，刪除并不會真正釋放原來占用的空間，所以這里表示的是已經(jīng)刪除的行與后續(xù)可以被insert利用的空間
innoDB存儲引擎
innoDB作為mysql的默認存儲引擎训裆，它將表數(shù)據(jù)存放在單獨的表空間里面
(在windows中眶根，這里以test/user表為例)
E:\MySQL\ProgramData\MySQL Server 5.5\data
在data數(shù)據(jù)目錄中存放有test數(shù)據(jù)庫子文件夾，里面用于存放test數(shù)據(jù)庫中的各個表的結構定義.frm以及索引信息.ibd边琉，在上層目錄中ibdata1為所有表共享的表空間
當innodb引擎中表的數(shù)據(jù)發(fā)生巨大變化時属百，該文件的大小也將隨之發(fā)生變化，這一步是自動產(chǎn)生的艺骂。
innoDB采用MVCC(多版本并發(fā)控制)策略來支持高并發(fā)诸老，支持四個隔離級別,READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ(default) | SERIALIZABLE ,并通過間隙鎖防止幻讀隆夯，間隙鎖不僅鎖定查詢中的行钳恕，同時也會對索引中的間隙進行鎖定，不允許幻行插入進來蹄衷。
innoDB索引通過聚簇索引創(chuàng)建忧额，聚簇索引對主鍵的查詢性能很高，但是二級索引(也就是非主鍵索引)中必須包含主鍵列愧口，如果表中的索引比較多的話睦番，主鍵索引應該盡可能的小
innoDB通過一些機制和工具支持熱備份，其他引擎是不支持的，其他引擎如果要備份數(shù)據(jù)托嚣，就必須先停止對表的寫入操作
innoDB支持鎖粒度更小的行級鎖(存儲引擎實現(xiàn))
myISAM存儲引擎
myISAM不支持行級鎖及事務巩检，崩潰后無法修復
myISAM會將表存儲在三個文件中: .frm，.MYD示启，.MYI兢哭，.frm用于存儲表結構，.myd用于存儲數(shù)據(jù)夫嗓，.myi用于存儲索引
myISAM只會將數(shù)據(jù)寫入到內(nèi)存中迟螺，然后等待os定期將數(shù)據(jù)刷到磁盤上，myISAM刪除記錄并不會馬上更新數(shù)據(jù)文件.myd的大小舍咖，如果想要立馬看到效果矩父，可以通過使用optimize table來手動更新才能看到效果
加鎖與并發(fā)
myISAM支持加鎖機制，但是加鎖是在整張表上進行的排霉。讀取時窍株，對所有讀到的表加共享鎖，寫入時對寫入的表加排他鎖攻柠，但是在讀取查詢時夹姥，也允許向表中插入新的記錄
修復
對于myISAM引擎表，可以進行手工或者自動檢查和修復辙诞，與事務支持的表修復有所區(qū)別辙售，執(zhí)行表的修復可能會丟失一部分數(shù)據(jù)，通過check table tablename查看表的錯誤飞涂，通過repair table tablename 修復錯誤的表旦部，但是這個過程通常很漫長，即使mysql關閉也可以通過myisamchk命令檢查
轉(zhuǎn)換表的引擎
有三種方式可以修改表的引擎
Alter Table
alter table mytable ENGINE=InnoDB #注意大小寫
該方法適用于任何引擎较店，但是執(zhí)行很慢士八，因為它將原表中的數(shù)據(jù)復制到新表中，并加上讀鎖梁呈，一個替代方案是采用導入導出方法手動進行表復制
在轉(zhuǎn)換表的過程中可能會丟失一些表的特性婚度，比如講InnoDB表轉(zhuǎn)化為myISAM再轉(zhuǎn)化為InnoDB,那么原來表中的外鍵會消失
導入導出(mysqldump)
使用mysqldump工具將數(shù)據(jù)導出到文件，然后修改文件中create table語句的存儲引擎選項官卡，注意同時修改表名蝗茁，同一個數(shù)據(jù)庫中不允許出現(xiàn)多個相同表名的數(shù)據(jù)表，即使是不同的存儲引擎寻咒，另外哮翘，mysqldump會在每一個create table前面添加drop table語句
創(chuàng)建與查詢(create insert)
綜合第一種的高效和第二種的安全，不需要導出整個表的數(shù)據(jù)毛秘，首先要建立一個新的存儲引擎表饭寺，然后利用insert .... select ...的語法來導數(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ù)量比較大阻课，那么可能需要分批進行處理
mysql> start transaction;
mysql> insert into innodb_table select * from user where id between x and y;
mysql> commit;
還可以通過pt-online-schema-change的工具來完成上述操作
