存儲(chǔ)引擎(Storeage Engine)
MySql提供了多種引擎可供選擇耕腾，我們最常用的就是InnoDB,MyISAM.
如果用MySql的SHOW ENGINES\G查一下蛆封，MySQL其實(shí)在不斷的豐富引擎類型，提供不同類型碎捺，不同場景路鹰、不同特性的需求，所以隨著MySql版本不同收厨，提供可供選擇的引擎也不同晋柱。
以MySql 5.7版本支持存儲(chǔ)引擎列表(MySQL 5.7 Supported Storage Engines)：
那自從MySql5.5版本之后，默認(rèn)的引擎就是InnoDB.
各存儲(chǔ)引擎的特性簡要對(duì)比(Storage Engines Feature Summary)：
InnoDB主要特性
可以總結(jié)一下我們常用InnnoDB的主要特性：
從中可以看出:
1.InnoDB支持行級(jí)鎖;
2.也支持全文索引;
3.B-tree Index;
4.MVCC支持诵叁；
5.最重要的是支持事務(wù)雁竞；
MySql隔離級(jí)別
MySql InnoDB存儲(chǔ)引擎默認(rèn)隔離級(jí)別為:Repeadtable read.
索引（index）
索引是加快數(shù)據(jù)查詢的性能。
MySQL InnoDB存儲(chǔ)引擎是用B-tree來實(shí)現(xiàn)索引的功能拧额。
平常用到索引有PRIMARY KEY, UNIQUE, INDEX碑诉。
聚簇索引與非聚簇索引
索引分為聚簇索引和非聚簇索引兩種，
聚簇索引是按照數(shù)據(jù)存放的物理位置為順序的侥锦，而非聚簇索引中數(shù)據(jù)和索引不在同一個(gè)地方进栽；聚簇索引能提高多行檢索的速度，而非聚簇索引對(duì)于單行的檢索很快恭垦，一個(gè)表中只能有一個(gè)聚簇索引快毛。
對(duì)于MyISAM是非聚簇索引格嗅，InnoDB是聚簇索引。
非聚簇索引主要是中索引和數(shù)據(jù)是放在不同的地方唠帝，索引中的葉子節(jié)點(diǎn)存放的是真實(shí)數(shù)據(jù)的地址屯掖。
聚簇索引，對(duì)于主索引而言索引和數(shù)據(jù)是在一起的没隘，索引中葉子節(jié)點(diǎn)中既存放在關(guān)鍵字懂扼，又存放了數(shù)據(jù)。對(duì)于輔助索引而言右蒲，索此葉子節(jié)點(diǎn)中存放了對(duì)應(yīng)數(shù)據(jù)的主鍵值。
InnoDB的的輔索引的葉子節(jié)點(diǎn)存放的是KEY字段加主鍵值赶熟。因此瑰妄，通過輔索引查詢首先查到是主鍵值，然后InnoDB再根據(jù)查到的主鍵值通過主鍵索引找到相應(yīng)的數(shù)據(jù)塊映砖。而MyISAM的輔索引葉子節(jié)點(diǎn)存放的還是列值與行號(hào)的組合间坐，葉子節(jié)點(diǎn)中保存的是數(shù)據(jù)的物理地址。所以可以看出MYISAM的主鍵索引和輔索引沒有任何區(qū)別邑退，主鍵索引僅僅只是一個(gè)叫做PRIMARY的唯一竹宋、非空的索引，且MYISAM引擎中可以不設(shè)主鍵地技。
對(duì)于MyISAM是非聚簇索引蜈七，InnoDB是聚簇索引。
InnoDB將通過主鍵聚集數(shù)據(jù)莫矗，如果沒有定義主鍵飒硅，Innodb會(huì)選擇第一個(gè)非空的唯一索引代替，如果沒有非空唯一索引作谚，Innodb會(huì)隱式定義一個(gè)6字節(jié)的rowid主鍵來作為聚集索引三娩。
聯(lián)合索引與最左前綴匹配原理
高效使用索引的首要條件是知道什么樣的查詢會(huì)使用到索引，這個(gè)問題和B+Tree中的“最左前綴原理”有關(guān)妹懒，下面通過例子說明最左前綴原理雀监。
聯(lián)合索引
先說一下聯(lián)合索引的概念。MySQL中的索引可以以一定順序引用多個(gè)列眨唬，這種索引叫做聯(lián)合索引会前，一般來說一個(gè)聯(lián)合索引是一個(gè)有序元組<a1, a2, …, an>，其中各個(gè)元素均為數(shù)據(jù)表的一列单绑。另外回官，單列索引可以看成聯(lián)合索引元素?cái)?shù)為1的特例。
假如有一張employee表有employee_no,title,name,campany,age 五個(gè)字段搂橙，此時(shí)如果以如下三個(gè)字段（順序也一致）employee_no,title,age上建立聯(lián)合索引歉提。
那么當(dāng)我們查詢的時(shí)候笛坦，如下幾種情況可以使用建立的索引：1）查詢條件里有提供employee_no；2）employee_no+title苔巨；3）employee_no,title,age這三種情況版扩，可以用到剛剛創(chuàng)建的聯(lián)合索引。
在以下幾種情況用不到剛建立的聯(lián)合索引：
1）查詢條件只有age;2)查詢條件只有title侄泽；3)查詢條件有title和age;4）查詢條件有employee_no和age(此種情況employee_no用到索引礁芦，但是age不能使用索引)
以上總結(jié)的情況只是最基本上的情況，實(shí)際中一定要結(jié)合最左匹配的實(shí)際情況來分析悼尾。
比如以下兩種情況都提供了employee_no和title查詢柿扣，但是一種使用索引，一種不能使用索引：
select * from employee where employee_no='xxxx' and title like 'xxx%'//title能使用索引闺魏；
select * from employee where employee_no='xxxx' and title like '%xx'//title不能使用索引
鎖（MySql InnoDB）
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
下文中主要是針對(duì)InnoDB引擎的鎖的摘要筆記未状。
MVCC
MySQL后續(xù)的版本中增加了MVCC (Multiversion Concurrency Control)，即多版本并發(fā)控制技術(shù), 它使得大部分支持行鎖的事務(wù)引擎析桥，不再單純的使用行鎖來進(jìn)行數(shù)據(jù)庫的并發(fā)控制司草，取而代之的是把數(shù)據(jù)庫的行鎖與行的多個(gè)版本結(jié)合起來，只需要很小的開銷,就可以實(shí)現(xiàn)非鎖定讀泡仗，從而大大提高數(shù)據(jù)庫系統(tǒng)的并發(fā)性能埋虹。
數(shù)據(jù)庫鎖定機(jī)制，從簡單來說就是數(shù)據(jù)庫為了保證數(shù)據(jù)的一致性娩怎，而使各種共享資源在被并發(fā)訪問變得有序所設(shè)計(jì)的一種規(guī)則搔课。MySQL數(shù)據(jù)庫存在多種數(shù)據(jù)存儲(chǔ)引擎，每種存儲(chǔ)引擎所針對(duì)的應(yīng)用場景特點(diǎn)都不太一樣峦树，所以提供的鎖機(jī)制也不一樣辣辫。MySQL使用了三種類型（級(jí)別）的鎖機(jī)制：表級(jí)鎖，行級(jí)鎖和頁級(jí)鎖魁巩。
共享鎖和排它鎖(Shared and Exclusive Locks)
InnoDB存儲(chǔ)引擎在row level鎖上實(shí)現(xiàn)了共享鎖(shared lock,又稱S鎖)和排它鎖(exclusive locks急灭，又稱X鎖)。
共享鎖(shared lock,又稱S鎖)：允許持此鎖的事務(wù)去讀取(read)此行谷遂。
通過如下語句可以對(duì)加一把共享鎖
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE //共享鎖(S)
排它鎖(exclusive locks葬馋，又稱X鎖)：允許持有此鎖的事務(wù)去更新(update)或刪除(delete)此行操作。
通過如下語句可以加排它鎖
SELECT * FROM table_name WHERE ... FOR UPDATE //排他鎖(X)
假設(shè)有兩個(gè)事務(wù)t1和t2
如果事務(wù)t1獲取了一個(gè)元組的共享鎖肾扰，事務(wù)t2還可以立即獲取這個(gè)元組的共享鎖畴嘶，但不能立即獲取這個(gè)元組的排它鎖（必須等到t1釋放共享鎖之后）。
如果事務(wù)t1獲取了一個(gè)元組的排它鎖集晚，事務(wù)t2不能立即獲取這個(gè)元組的共享鎖窗悯，也不能立即獲取這個(gè)元組的排它鎖（必須等到t1釋放排它鎖之后）
意向鎖（intenion lock）
意向鎖是一種表鎖，鎖定的粒度是整張表偷拔，分為意向共享鎖(IS)和意向排它鎖(IX)兩類蒋院。意向共享鎖表示一個(gè)事務(wù)有意對(duì)數(shù)據(jù)上共享鎖或者排它鎖亏钩。“有意”這兩個(gè)字表達(dá)的意思比較微妙欺旧，說的明白點(diǎn)就是指事務(wù)想干這個(gè)事但還沒真去干姑丑。意向鎖是MySql自己加的。
幾種鎖的兼容和互斥關(guān)系
加鎖使用匯總
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE //共享鎖(S)
SELECT * FROM table_name WHERE ... FOR UPDATE //排他鎖(X)
1.對(duì)于UPDATE辞友、DELETE和INSERT語句栅哀，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)；
2.對(duì)于普通SELECT語句称龙，InnoDB不會(huì)加任何鎖留拾；
在MVCC并發(fā)控制中，讀操作可以分成兩類：快照讀 (snapshot read)與當(dāng)前讀 (current read)茵瀑〖渫裕快照讀，讀取的是記錄的可見版本 (有可能是歷史版本)马昨，不用加鎖。當(dāng)前讀扛施，讀取的是記錄的最新版本鸿捧，并且，當(dāng)前讀返回的記錄疙渣，都會(huì)加上鎖匙奴，保證其他事務(wù)不會(huì)再并發(fā)修改這條記錄。
以MySQL InnoDB為例：
快照讀：簡單的select操作妄荔，屬于快照讀泼菌，不加鎖。(當(dāng)然也有例外)
select * from table where ?;
當(dāng)前讀：特殊的讀操作啦租，插入/更新/刪除操作哗伯，屬于當(dāng)前讀，需要加鎖篷角。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的語句焊刹，都屬于當(dāng)前讀，讀取記錄的最新版本恳蹲。并且虐块，讀取之后，還需要保證其他并發(fā)事務(wù)不能修改當(dāng)前記錄嘉蕾，對(duì)讀取記錄加鎖贺奠。其中，除了第一條語句错忱，對(duì)讀取記錄加S鎖 (共享鎖)外儡率，其他的操作挂据，都加的是X鎖 (排它鎖)。
MySql行鎖的實(shí)現(xiàn)
InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的喉悴，只有通過索引條件檢索數(shù)據(jù)棱貌，InnoDB才使用行級(jí)鎖，否則箕肃，InnoDB將使用表鎖婚脱。
在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性勺像，不然的話障贸，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能吟宦。
使用行鎖的的幾點(diǎn)注意：
1.在不通過索引條件查詢的時(shí)候篮洁，InnoDB確實(shí)使用的是表鎖，而不是行鎖殃姓。
2.由于MySQL的行鎖是針對(duì)索引加的鎖袁波，不是針對(duì)記錄加的鎖，所以雖然是訪問不同行的記錄蜗侈，但是如果是使用相同的索引鍵篷牌，是會(huì)出現(xiàn)鎖沖突的。
3.當(dāng)表有多個(gè)索引的時(shí)候踏幻，不同的事務(wù)可以使用不同的索引鎖定不同的行枷颊，另外，不論是使用主鍵索引该面、唯一索引或普通索引夭苗，InnoDB都會(huì)使用行鎖來對(duì)數(shù)據(jù)加鎖。
4.即便在條件中使用了索引字段隔缀，但是否使用索引來檢索數(shù)據(jù)是由MySQL通過判斷不同執(zhí)行計(jì)劃的代價(jià)來決定的题造，如果MySQL認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表蚕泽，它就不會(huì)使用索引晌梨，這種情況下InnoDB將使用表鎖，而不是行鎖须妻。因此仔蝌，在分析鎖沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃荒吏，以確認(rèn)是否真正使用了索引敛惊。
間隙鎖（Next-Key鎖）
當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)绰更，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖瞧挤；
對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄锡宋，叫做“間隙（GAP)”，InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖特恬，這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）执俩。
假如emp表中只有101條記錄，其empid的值分別是 1,2,...,100,101癌刽，下面的SQL：
mysql> select * from emp where empid > 100 for update;
是一個(gè)范圍條件的檢索役首，InnoDB不僅會(huì)對(duì)符合條件的empid值為101的記錄加鎖，也會(huì)對(duì)empid大于101（這些記錄并不存在）的“間隙”加鎖显拜。
通過索引實(shí)現(xiàn)鎖定的方式還存在其他幾個(gè)較大的性能隱患：
1.當(dāng)Query無法利用索引的時(shí)候衡奥，InnoDB會(huì)放棄使用行級(jí)別鎖定而改用表級(jí)別的鎖定，造成并發(fā)性能的降低远荠；
2.當(dāng)Query使用的索引并不包含所有過濾條件的時(shí)候矮固，數(shù)據(jù)檢索使用到的索引鍵所只想的數(shù)據(jù)可能有部分并不屬于該Query的結(jié)果集的行列，但是也會(huì)被鎖定譬淳，因?yàn)殚g隙鎖鎖定的是一個(gè)范圍档址，而不是具體的索引鍵；
3.當(dāng)Query在使用索引定位數(shù)據(jù)的時(shí)候邻梆，如果使用的索引鍵一樣但訪問的數(shù)據(jù)行不同的時(shí)候（索引只是過濾條件的一部分）辰晕，一樣會(huì)被鎖定。
GAP鎖确虱，就是Repeatable Read隔離級(jí)別，相對(duì)于Repeatable Commited隔離級(jí)別替裆，不會(huì)出現(xiàn)幻讀的關(guān)鍵校辩。GAP鎖鎖住的不是位置，也不是記錄本身辆童，而是兩條記錄之間的GAP宜咒。所謂幻讀，就是同一個(gè)事務(wù)把鉴，連續(xù)做兩次當(dāng)前讀 (例如：select * from t1 where id = 10 for update;)故黑，那么這兩次當(dāng)前讀返回的是完全相同的記錄 (記錄數(shù)量一致，記錄本身也一致)庭砍，第二次的當(dāng)前讀场晶，不會(huì)比第一次返回更多的記錄 (幻象)。
如何保證兩次當(dāng)前讀返回一致的記錄怠缸，那就需要在第一次當(dāng)前讀與第二次當(dāng)前讀之間诗轻，其他的事務(wù)不會(huì)插入新的滿足條件的記錄并提交。為了實(shí)現(xiàn)這個(gè)功能揭北，GAP鎖應(yīng)運(yùn)而生扳炬。
查看數(shù)據(jù)庫的行鎖情況
mysql> show status like 'InnoDB_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
InnoDB_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量吏颖；
InnoDB_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長度；
InnoDB_row_lock_time_avg：每次等待所花平均時(shí)間恨樟；
InnoDB_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最常的一次所花的時(shí)間半醉；
InnoDB_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)；
MYSQL InnoDB的索引和鎖
Mysql的鎖機(jī)制解讀
MySql-兩階段加鎖協(xié)議
排它鎖
共享鎖
表鎖
行數(shù)
gap鎖
Mysql的record鎖劝术、gap鎖缩多、next-key鎖
存儲(chǔ)引擎
mysql數(shù)據(jù)表存儲(chǔ)引擎類型及特性
事務(wù)相關(guān)
- mysql事務(wù)隔
MySql鎖示例分析
說明：以下轉(zhuǎn)自簡小鹿奔跑ing 的 MySQL 加鎖處理分析
參考：
mysql數(shù)據(jù)表存儲(chǔ)引擎類型及特性
MYSQL InnoDB的索引和鎖
MySQL的btree索引和hash索引的區(qū)別
B-tree、B+tree夯尽、B*tree
MySQL優(yōu)化之BTree索引使用規(guī)則
如何理解并正確使用MySql索引
Btree,B-Tree,B+Tree,B*Tree
《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎》讀書筆記五-鎖瞧壮、索引及事務(wù)
MySQL索引背后的數(shù)據(jù)結(jié)構(gòu)及算法原理
參考：
MySQL鎖詳解
MySQL 加鎖處理分析
一個(gè)最不可思議的MySQL死鎖分析
初步理解MySQL的gap鎖
MySql 5.7 官方文檔
MySql5.7官方文檔之Clustered and Secondary Indexes
MySql 5.7官方文檔之InnoDB Locking
MySql 5.7官方文檔之Transaction Isolation Levels
