MySQL索引
什么是索引
索引是一種數(shù)據(jù)結(jié)構(gòu)，可以幫助我們快速查找數(shù)據(jù)
MySQL官方對(duì)索引的定義為:索引(index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)(有序)。在數(shù)據(jù)之外，數(shù)據(jù) 庫(kù)系統(tǒng)還維護(hù)者滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用(指向)數(shù)據(jù)， 這樣就可以在這些數(shù) 據(jù)結(jié)構(gòu)上實(shí)現(xiàn)高級(jí)查找算法，這種數(shù)據(jù)結(jié)構(gòu)就是索引。
索引都有哪些數(shù)據(jù)結(jié)構(gòu)
索引可能有三種數(shù)據(jù)結(jié)構(gòu)哈希表、有序數(shù)組和N叉樹。MySQL索引使用的是B+樹（InnoDB存儲(chǔ)引擎）
索引的原理
一般來(lái)說(shuō)，索引本身也很大，不可能全部存儲(chǔ)在內(nèi)存中，因此索引往往以索引文件的形式存儲(chǔ)的磁盤上。這樣的話，索引查找過(guò)程中就要產(chǎn)生磁盤I/O消耗，相對(duì)于內(nèi)存存取，I/O存取的消耗要高幾個(gè)數(shù)量級(jí)，所以評(píng)價(jià)一個(gè)數(shù)據(jù)結(jié)構(gòu)作為索引的優(yōu)劣最重要的指標(biāo)就是在查找過(guò)程中磁盤I/O操作次數(shù)的漸進(jìn)復(fù)雜度。
換句話說(shuō)，索引的結(jié)構(gòu)組織要盡量減少查找過(guò)程中磁盤I/O的存取次數(shù)。
索引底層是怎么實(shí)現(xiàn)的
Hash索引
哈希索引是采用一定的哈希算法，將鍵值換算成新的哈希值，映射到對(duì)應(yīng)的槽位上，然后存儲(chǔ)在hash表中。
如果兩個(gè)或多個(gè)鍵值，映射到一個(gè)相同的槽位上，他們就會(huì)產(chǎn)生Hash沖突，也叫Hash碰撞，可以通過(guò)鏈表來(lái)解決。
Hash索引特點(diǎn)
優(yōu)點(diǎn)：
查詢效率高，在沒(méi)有產(chǎn)生hash沖突的情況下，通常只需要一次檢索就可以了，效率通常要高于B+樹索引。
缺點(diǎn)：
1.Hash索引只能用于對(duì)等比較(=，in)，不支持范圍查詢(between，>，<,…），即無(wú)法進(jìn)行大小比較
2.無(wú)法利用索引完成排序操作（Hash索引是無(wú)序排列的）
存儲(chǔ)引擎支持
在MySQL中，支持hash索引的是Memory引擎，而InnoDB中具有自適應(yīng)hash功能（利用方法將b+樹索引轉(zhuǎn)化為hash索引），hash索引是存儲(chǔ)引擎根據(jù)B+Tree索引在指定條件下自動(dòng)構(gòu)建的。
二叉樹索引
二叉樹索引分為左子樹，右子樹，根節(jié)點(diǎn)，左子樹要比根節(jié)點(diǎn)小，右子樹要比根節(jié)點(diǎn)大
二叉樹缺點(diǎn)：順序插入時(shí)，會(huì)形成一個(gè)鏈表（如右圖），查詢性能大大降低。很多數(shù)據(jù)量的情況下，層次較深，檢索速度慢。
平衡二叉樹
特點(diǎn)：
它的左子樹和右子樹都是平衡二叉樹
左子樹比中間小，右子樹比中間值大
左子樹和右子樹的深度之差的絕對(duì)值不超過(guò)1
缺點(diǎn)：
a、插入操作需要旋轉(zhuǎn)
b、支持范圍查詢，但回旋查詢效率較低，比如要查找大于8的，會(huì)回旋到父節(jié)點(diǎn)7、10。
c、如果存放幾百條數(shù)據(jù)的情況下，樹高度越高，查詢效率會(huì)越慢
B樹（多路平衡查找樹）
B樹：葉子節(jié)點(diǎn)和非葉子節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)，數(shù)據(jù)結(jié)構(gòu)為有序數(shù)組+平衡多叉樹，也叫b-樹。
m為樹的叉數(shù)
每個(gè)節(jié)點(diǎn)最多有m-1個(gè)關(guān)鍵字(可以存有的鍵值對(duì))。
根節(jié)點(diǎn)最少可以只有1個(gè)關(guān)鍵字。
非根節(jié)點(diǎn)至少有m/2個(gè)關(guān)鍵字。
每個(gè)節(jié)點(diǎn)中的關(guān)鍵字都按照從小到大的順序排列，每個(gè)關(guān)鍵字的左子樹中的所有關(guān)鍵字都小于它，而右子樹中的所有關(guān)鍵字都大于它。
所有葉子節(jié)點(diǎn)都位于同一層，或者說(shuō)根節(jié)點(diǎn)到每個(gè)葉子節(jié)點(diǎn)的長(zhǎng)度都相同。每個(gè)節(jié)點(diǎn)都存有索引和數(shù)據(jù)，也就是對(duì)應(yīng)的key和value。
b樹優(yōu)點(diǎn)： 二叉平衡樹的基礎(chǔ)上，使加載一次節(jié)點(diǎn)，可以加載更多路徑數(shù)據(jù)，同時(shí)把查詢范圍縮減到更小
樹的每一個(gè)節(jié)點(diǎn)都包含key和value。
所以，經(jīng)常訪問(wèn)的元素可能離根節(jié)點(diǎn)更近，把頻繁訪問(wèn)的數(shù)據(jù)放在靠近根節(jié)點(diǎn)的地方將會(huì)大大提高熱點(diǎn)數(shù)據(jù)的查詢效率。
缺點(diǎn)： 業(yè)務(wù)數(shù)據(jù)的大小可能遠(yuǎn)遠(yuǎn)超過(guò)了索引數(shù)據(jù)的大小，每次為了查找對(duì)比計(jì)算，需要把數(shù)據(jù)加載到內(nèi)存以及 CPU 高速緩存中時(shí)，都要把索引數(shù)據(jù)和無(wú)關(guān)的業(yè)務(wù)數(shù)據(jù)全部查出來(lái)。本來(lái)一次就可以把所有索引數(shù)據(jù)加載進(jìn)來(lái)，現(xiàn)在卻要多次才能加載完。如果所對(duì)比的節(jié)點(diǎn)不是所查的數(shù)據(jù)，那么這些加載進(jìn)內(nèi)存的業(yè)務(wù)數(shù)據(jù)就毫無(wú)用處，全部拋棄。
B+樹
以m階B+樹為例
一個(gè)m階的B+樹具有如下幾個(gè)特征：
有k個(gè)子樹的中間節(jié)點(diǎn)包含有k個(gè)元素（B樹中是k-1個(gè)元素），每個(gè)元素不保存數(shù)據(jù)，只用來(lái)索引，所有數(shù)據(jù)都保存在葉子節(jié)點(diǎn)。
所有的葉子結(jié)點(diǎn)中包含了全部元素的信息，及指向含這些元素記錄的指針，且葉子結(jié)點(diǎn)本身依關(guān)鍵字的大小自小而大順序鏈接。
所有的中間節(jié)點(diǎn)元素都同時(shí)存在于子節(jié)點(diǎn)，在子節(jié)點(diǎn)元素中是最大（或最?。┰?。
B+樹只有葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)(B+數(shù)中有兩個(gè)頭指針：一個(gè)指向根節(jié)點(diǎn)，另一個(gè)指向關(guān)鍵字最小的葉節(jié)點(diǎn))，葉子節(jié)點(diǎn)包含了這棵樹的所有數(shù)據(jù)，所有的葉子結(jié)點(diǎn)使用鏈表相連，便于區(qū)間查找和遍歷，所有非葉節(jié)點(diǎn)起到索引作用
image.png
B+樹優(yōu)于B樹原因
b+樹的中間節(jié)點(diǎn)不保存數(shù)據(jù)，可以容納更多的節(jié)點(diǎn)元素
所有的葉子結(jié)點(diǎn)使用鏈表相連，有助于區(qū)間查找和遍歷
B+樹的內(nèi)部節(jié)點(diǎn)只存放鍵，不存放值，因此，一次讀取，可以在內(nèi)存頁(yè)中獲取更多的鍵，有利于更快地縮小查找范圍。 B+樹的葉節(jié)點(diǎn)由一條鏈相連，因此，當(dāng)需要進(jìn)行一次全數(shù)據(jù)遍歷的時(shí)候，B+樹只需要使用O(logN)時(shí)間找到最小的一個(gè)節(jié)點(diǎn)，然后通過(guò)鏈進(jìn)行O(N)的順序遍歷即可。而B樹則需要對(duì)樹的每一層進(jìn)行遍歷，這會(huì)需要更多的內(nèi)存置換次數(shù)，因此也就需要花費(fèi)更多的時(shí)間
B樹的話，就需要進(jìn)行每一層的遞歸遍歷
相鄰的元素可能在內(nèi)存中不相鄰，所以緩存命中性沒(méi)有B+樹好
什么是覆蓋索引
索引是高效找到行的一個(gè)方法，但是一般數(shù)據(jù)庫(kù)也能使用索引找到一個(gè)列的數(shù)據(jù)，因此它不必讀取整個(gè)行。b+索引葉子節(jié)點(diǎn)存儲(chǔ)了它們索引的數(shù)據(jù)；當(dāng)能通過(guò)讀取索引就可以得到想要的數(shù)據(jù)，那就不需要讀取行了。一個(gè)索引包含了（或覆蓋了）滿足查詢結(jié)果的數(shù)據(jù)就叫做覆蓋索引。
1.覆蓋索引是一種數(shù)據(jù)查詢方式，不是索引類型
2.在索引數(shù)據(jù)結(jié)構(gòu)中，通過(guò)索引值可以直接找到要查詢字段的值，而不需要通過(guò)主鍵值回表查詢，那么就叫覆蓋索引
3.查詢的字段被使用到的索引樹全部覆蓋到
舉個(gè)栗子：
在上圖中，id為主鍵索引，name為唯一索引
假如你執(zhí)行命令
select id,name from eclass where id=1;
此時(shí)由于id和name字段都在索引樹中，所以這就是覆蓋索引查詢
什么是聚簇索引
聚簇索引是物理索引，數(shù)據(jù)表就是按順序存儲(chǔ)的，物理上是連續(xù)的。
一般情況下主鍵會(huì)默認(rèn)創(chuàng)建聚簇索引
將索引與數(shù)據(jù)放在一起，當(dāng)你找到索引后，也就找到對(duì)應(yīng)的數(shù)據(jù)了。每張表只能建立一個(gè)聚簇索引，但是該索引可以包含多個(gè)列（一般使用的是主鍵等不經(jīng)常更新的列）
非聚簇索引：數(shù)據(jù)儲(chǔ)存于索引分開，葉節(jié)點(diǎn)指向了對(duì)應(yīng)的數(shù)據(jù)行。輔助索引訪問(wèn)數(shù)據(jù)時(shí)需要二次查找。輔助索引存儲(chǔ)的不是行的物理位置，而是主鍵的值。而通過(guò)輔助索引首先找到的就是主鍵的值，再通過(guò)主鍵的值找到數(shù)據(jù)行對(duì)應(yīng)的數(shù)據(jù)頁(yè)，最后才能找到對(duì)應(yīng)行。
聯(lián)合索引
我們都知道索引的底層是一顆B+樹，那么聯(lián)合索引當(dāng)然還是一顆B+樹，只不過(guò)聯(lián)合索引的健值數(shù)量不是一個(gè)，而是多個(gè)。構(gòu)建一顆B+樹只能根據(jù)一個(gè)值來(lái)構(gòu)建，因此數(shù)據(jù)庫(kù)依據(jù)聯(lián)合索引最左的字段來(lái)構(gòu)建B+樹。使用聯(lián)合索引，如果想要命中索引，需要按照建立索引時(shí)的字段順序挨個(gè)使用，否則無(wú)法命中索引。。
最左匹配原則
顧名思義：最左優(yōu)先，以最左邊的為起點(diǎn)任何連續(xù)的索引都能匹配上。同時(shí)遇到范圍查詢(>、<、between、like)就會(huì)停?匹配。
mysql創(chuàng)建復(fù)合索引的規(guī)則是?先對(duì)復(fù)合索引最左邊的字段的數(shù)據(jù)進(jìn)?排序，在此基礎(chǔ)上，再對(duì)后?的字段進(jìn)?排序，這樣第?個(gè)字段是絕對(duì)有序的，后?的字段就是?序的了，?般情況下第?個(gè)字段進(jìn)?條件判斷是?不到索引的，可能出現(xiàn)type是index類型的，這就是mysql 最左前綴的原因。
舉個(gè)栗子：假設(shè)創(chuàng)建了順序?yàn)椋╝，b，c）的索引
1.當(dāng)查詢條件為a=1 and b=1 或b=1 and a=1（查詢優(yōu)化器會(huì)調(diào)換a與b的位置），這時(shí)候都可以走索引。
當(dāng)查詢條件為a=1 and b>1 and c=1 時(shí) ，由于是范圍查找，a和b走索引，c不走索引
創(chuàng)建索引的原則
創(chuàng)建索引，肯定是有利于我們的查詢效率的，如果無(wú)效地創(chuàng)建索引，只會(huì)浪費(fèi)我們的內(nèi)存和執(zhí)行程序的效率，因此創(chuàng)建索引是有原則的
首先應(yīng)考慮對(duì)where 和 order by 涉及到的列上建立索引
對(duì)一個(gè)存在大量更新操作的表，所建索引的數(shù)目一般不超過(guò)3個(gè)，最多不超過(guò)5個(gè)，索引雖說(shuō)提高了訪問(wèn)速度，但太多索引會(huì)影響數(shù)據(jù)的更新操作,并且索引本身會(huì)占用存儲(chǔ)空間。
建立唯一索引。唯一索引能夠更快速地幫助我們進(jìn)行數(shù)據(jù)定位；
為經(jīng)常需要進(jìn)行查詢操作的字段建立索引；
更新頻繁的列不宜設(shè)置索引，索引列不能參與計(jì)算
數(shù)據(jù)量小的表不要使用索引
重復(fù)數(shù)據(jù)多的字段不宜設(shè)置索引，如性別男和女。
在建立索引的時(shí)候，要考慮索引的最左匹配原則（在使用SQL語(yǔ)句時(shí)，如果where部分的條件不符合最左匹配原則，可能導(dǎo)致索引失效，或者不能完全發(fā)揮建立的索引的功效）；
如果建立的單個(gè)索引查詢數(shù)據(jù)很多，查詢得到的數(shù)據(jù)的區(qū)分度不大，則考慮建立合適的聯(lián)合索引；
盡量考慮字段值長(zhǎng)度較短的字段建立索引，如果字段值太長(zhǎng)，會(huì)降低索引的效率
創(chuàng)建索引的優(yōu)缺點(diǎn)
優(yōu)點(diǎn)
1.大大加快數(shù)據(jù)的查詢速度
2.使用分組和排序進(jìn)行數(shù)據(jù)查詢時(shí)，可以顯著減少查詢時(shí)分組和排序的時(shí)間
3.創(chuàng)建唯一索引，能夠保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性
4.在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面，可以加速表和表之間的連接
5.通過(guò)使用索引，可以在查詢的過(guò)程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
缺點(diǎn)
1.創(chuàng)建索引和維護(hù)索引需要消耗時(shí)間并且隨著數(shù)據(jù)量的增加，時(shí)間也會(huì)增加
索引需要占據(jù)磁盤空間
2.對(duì)數(shù)據(jù)表中的數(shù)據(jù)進(jìn)行增加，修改，刪除時(shí)，索引也要?jiǎng)討B(tài)的維護(hù)，降低了維護(hù)的速度
MySQL存儲(chǔ)引擎
數(shù)據(jù)庫(kù)存儲(chǔ)引擎是數(shù)據(jù)庫(kù)底層軟件組織，數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)使用數(shù)據(jù)引擎進(jìn)行創(chuàng)建、查詢、更新和刪除數(shù)據(jù)。
不同的存儲(chǔ)引擎提供不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平等功能。現(xiàn)在許多不同的數(shù)據(jù)庫(kù)管理系統(tǒng)都支持多種不同的數(shù)據(jù)引擎。MySQL的核心就是存儲(chǔ)引擎。
用戶可以根據(jù)不同的需求為數(shù)據(jù)表選擇不同的存儲(chǔ)引擎
可以使用 SHOW ENGINES命令 可以查看Mysql的所有執(zhí)行引擎我們 可以到默認(rèn)的執(zhí)行引擎是innoDB支持事務(wù)，行級(jí)鎖定和外鍵。
MySQL默認(rèn)的存儲(chǔ)引擎是InnoDB。
分類
1.MylSAM：Mysql 5.5之前的默認(rèn)數(shù)據(jù)庫(kù)引擎，最為常用。擁有較高的插入，查詢速度，但不支持事務(wù)
2.InnoDB：事務(wù)型速記的首選引擎，支持ACID事務(wù)，支持行級(jí)鎖定，MySQL5.5成為默認(rèn)數(shù)據(jù)庫(kù)引擎
3.Memory:所有數(shù)據(jù)置于內(nèi)存的存儲(chǔ)引擎，擁有極高的插入，更新和查詢效率。但是會(huì)占用和數(shù)據(jù)量成正比的內(nèi)存空間。并且其內(nèi)容會(huì)在MYSQL重新啟動(dòng)是會(huì)丟失。
4.Archive：非常適合存儲(chǔ)大量的獨(dú)立的，作為歷史記錄的數(shù)據(jù)。因?yàn)樗鼈儾唤?jīng)常被讀取。5.Archive擁有高效的插入速度，但其對(duì)查詢的支持相對(duì)較差
6.Federated:將不同的MySQL服務(wù)器聯(lián)合起來(lái)，邏輯上組成一個(gè)完整的數(shù)據(jù)庫(kù)。非常適合分布式應(yīng)用
7.CSV：邏輯上由逗號(hào)分割數(shù)據(jù)的存儲(chǔ)引擎。它會(huì)在數(shù)據(jù)庫(kù)子目錄里為每個(gè)數(shù)據(jù)表創(chuàng)建一個(gè).csv文件。這是一種普通文本文件，每個(gè)數(shù)據(jù)行占用一個(gè)文本行。CSV存儲(chǔ)引擎不支持索引。
8.BlackHole：黑洞引擎，寫入的任何數(shù)據(jù)都會(huì)消失，一般用于記錄 binlog做復(fù)制的中繼
ERFORMANCE_SCHEMA存儲(chǔ)引擎該引擎主要用于收集數(shù)據(jù)庫(kù)服務(wù)器性能參數(shù)。
9.Mrg_Myisam Merge存儲(chǔ)引擎，是一組Mylsam的組合，也就是說(shuō)，他將Mylsam引擎的多個(gè)表聚合起來(lái)，但是他的內(nèi)部沒(méi)有數(shù)據(jù)，真正的數(shù)據(jù)依然是Mylsam引擎的表中，但是可以直接進(jìn)行查詢、刪除更新等操作。
InnoDB 和 MylSAM存儲(chǔ)引擎區(qū)別
1.InnoDB支持事務(wù)，MyISAM不支持，這一點(diǎn)是非常之重要。事務(wù)是一種高級(jí)的處理方式，如在一些列增刪改中只要哪個(gè)出錯(cuò)還可以回滾還原，而MyISAM就不可以了。
2.MyISAM適合查詢以及插入為主的應(yīng)用，InnoDB適合頻繁修改以及涉及到安全性較高的應(yīng)用
InnoDB支持外鍵，MyISAM不支持
3.從MySQL5.5.5以后，InnoDB是默認(rèn)引擎
4.InnoDB不支持FULLTEXT類型的索引
5.InnoDB中不保存表的行數(shù)，如select count() from table時(shí)，InnoDB需要掃描一遍整個(gè)表來(lái)計(jì)6.算有多少行，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是，當(dāng)count()語(yǔ)句包含where條件時(shí)MyISAM也需要掃描整個(gè)表
7.對(duì)于自增長(zhǎng)的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引
8.清空整個(gè)表時(shí)，InnoDB是一行一行的刪除，效率非常慢。MyISAM則會(huì)重建表
9.InnoDB支持行鎖（某些情況下還是鎖整表）
圖片表示
MySQL事務(wù)
什么是事務(wù)
在MySQL中的事務(wù)Transaction是由存儲(chǔ)引擎實(shí)現(xiàn)的，在MySQL中，只有InnoDB存儲(chǔ)引擎才支持事務(wù)。
事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的SQL語(yǔ)句要么全部執(zhí)行，要么全部不執(zhí)行。事務(wù)用來(lái)管理DDL、DML、DCL操作，比如insert，update，delete語(yǔ)句，默認(rèn)是自動(dòng)提交的。
事務(wù)操作
開啟事務(wù)：Start Transaction
任何一條DML語(yǔ)句(insert、update、delete)執(zhí)行，標(biāo)志事務(wù)的開啟命令：BEGIN或 START TRANSACTION
提交事務(wù)：Commit Transaction
成功的結(jié)束，將所有的DML語(yǔ)句操作歷史記錄和底層硬盤數(shù)據(jù)來(lái)一次同步命令：COMMIT
回滾事務(wù)：Rollback Transaction
失敗的結(jié)束，將所有的DML語(yǔ)句操作歷史記錄全部清空
命令：ROLLBACK
set autocommit =0 禁止自動(dòng)提交事務(wù)
set autocommit =1 開啟自動(dòng)提交事務(wù)
事務(wù)的特性
1.原子性（Atomicity）：事務(wù)是一個(gè)不可分割的整體，事務(wù)開始后的所有操作，要么全部完
成，要么全部不做
2.一致性（Consistency）：系統(tǒng)從一個(gè)正確的狀態(tài)，遷移到另一個(gè)正確的狀態(tài)
3.隔離性（Isolation）：每個(gè)事務(wù)的對(duì)象對(duì)其他事務(wù)的操作對(duì)象互相分離，事務(wù)提交前對(duì)其
他事務(wù)不可見(jiàn)
4.持久性（Durability）：事務(wù)一旦提交，則其結(jié)果是永久性的
事務(wù)的隔離級(jí)別
讀未提交(Read uncommitted)
一個(gè)事務(wù)可以讀取另一個(gè)未提交事務(wù)的數(shù)據(jù)，最低級(jí)別，任何情況都無(wú)法保證，會(huì)造成臟讀。
讀已提交(Read committed)
一個(gè)事務(wù)要等另一個(gè)事務(wù)提交后才能讀取數(shù)據(jù)，可避免臟讀的發(fā)生，會(huì)造成不可重復(fù)讀。
可重復(fù)讀(Repeatable read) I
就是在開始讀取數(shù)據(jù)(事務(wù)開啟)時(shí)，不再允許修改操作，可避免臟讀、不可重復(fù)讀的發(fā)生，但是會(huì)造成幻讀。
串行(Serializable)
是最高的事務(wù)隔離級(jí)別，在該級(jí)別下，事務(wù)串行化順序執(zhí)行，可以避免臟讀、不可重復(fù)讀與幻讀。但是這種事務(wù)隔離級(jí)別效率低下，比較耗數(shù)據(jù)庫(kù)性能，一般不使用。
Mysql的默認(rèn)隔離級(jí)別是Repeatable read （可重復(fù)讀）。
1.臟讀：強(qiáng)調(diào)的是第二個(gè)事務(wù)讀到的數(shù)據(jù)不夠新
栗子：假設(shè)老板今天給你發(fā)這個(gè)月的工資，本來(lái)要給你發(fā)2w元，結(jié)果手一抖發(fā)了20w元，這時(shí)老板還沒(méi)有提交事務(wù)，而你查詢到你的賬戶里多了20w元，老板總覺(jué)得不對(duì)勁，總感覺(jué)少了些什么，最后發(fā)現(xiàn)后回滾事務(wù)，給你發(fā)了2w，然后這時(shí)你打開賬戶發(fā)現(xiàn)只有2w塊錢。
2.不可重復(fù)讀：同一事務(wù)，兩次讀取到的數(shù)據(jù)不一樣
栗子：前提同第一個(gè)，老板給你發(fā)了2w元工資，你看到賬戶里多了2w塊錢，此時(shí)老板覺(jué)得你這個(gè)月做的還不錯(cuò)，給你加了1w塊錢獎(jiǎng)勵(lì)金并提交了事務(wù)，而此時(shí)你再次查詢時(shí)發(fā)現(xiàn)是3w塊錢，這就造成了在一個(gè)事務(wù)中讀取到的事務(wù)不一致。
3.幻讀：重點(diǎn)在于新增或刪除，同樣的條件，第一次和第二次讀出來(lái)的記錄數(shù)不一樣
栗子：和你相同薪資的人有10人，此時(shí)讀數(shù)據(jù)讀到的事務(wù)為10人，此時(shí)突然增加了一條工資和你們一樣的人，提交事務(wù)后記錄為11人，因此產(chǎn)生了幻讀。
MySQL 鎖機(jī)制
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制(避免爭(zhēng)搶)。
在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源(如CPU、RAM、I/O等)的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。
從對(duì)數(shù)據(jù)操作的粒度分：
1.表鎖：操作時(shí)，會(huì)鎖定整個(gè)表。
2.行鎖：操作時(shí)，會(huì)鎖定當(dāng)前操作行。
從對(duì)數(shù)據(jù)操作的類型分：
1.讀鎖(共享鎖)：針對(duì)同一份數(shù)據(jù)，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響。
2.寫鎖(排它鎖)：當(dāng)前操作沒(méi)有完成之前，它會(huì)阻斷其他寫鎖和讀鎖。
其中，InnoDB表鎖和行鎖都支持，而MyISAM只支持表鎖
MySQL鎖的特性
表級(jí)鎖 偏向MyISAM存儲(chǔ)引擎，開銷小，加鎖快不會(huì)出現(xiàn)死鎖：鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。
行級(jí)鎖 偏向InnoDB存儲(chǔ)引擎，開銷大，加鎖慢；會(huì)出現(xiàn)死；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
從上述特點(diǎn)可見(jiàn)，很難籠統(tǒng)地說(shuō)哪種鎖更好，只能就具體應(yīng)用的特點(diǎn)來(lái)說(shuō)哪種鎖更合適！僅從鎖的角度來(lái)說(shuō)：表級(jí)鎖更適合于以查詢?yōu)橹?，只有少量按索引條件更新數(shù)據(jù)的應(yīng)用，如Web應(yīng)用；
而行級(jí)鎖則更適合于有大量按索引條件并發(fā)更新少量不同數(shù)據(jù)，同時(shí)又有并查詢的應(yīng)用，如一些在線事務(wù)處理(OLTP)系統(tǒng)。
如何加鎖
MyISAM表鎖
MyISAM存儲(chǔ)引擎只支持表鎖
如何加表鎖
MyISAM在執(zhí)行查詢語(yǔ)句(SELECT)前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作(UPDATE、DELETE、INSERT等)前，會(huì)自動(dòng)給涉及的表加寫鎖，這個(gè)過(guò)程并不需要用戶干預(yù)，因此，用戶一般不需要直接用LOCK TABLE 命令給MyISAM表顯式加鎖。
加讀鎖： lock table table_name read;
加寫鎖：lock table table_name write;
InnoDB行鎖：
行鎖特點(diǎn)：偏向InnoDB存儲(chǔ)引擎，開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。
InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)；二是采用了行級(jí)鎖。
行鎖模式：
InnoDB 實(shí)現(xiàn)了以下兩種類型的行鎖。
共享鎖(S) 又稱為讀鎖，簡(jiǎn)稱S鎖，共享鎖就是多個(gè)事務(wù)對(duì)于同一數(shù)據(jù)可以共享一把鎖，都能訪問(wèn)到數(shù)據(jù)，但是只能讀不能修改。
排他鎖（X） 又稱為寫鎖，簡(jiǎn)稱x鎖，排他鎖就是不能與其他鎖并存，如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)就行讀取和修改。
對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖(X)；
對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖；
共享鎖（S）：SELECT ★ FROM table name WHERE ... LOCK IN SHARE MODE
排他鎖（X）：SELECT ★ FROM table name WHERE ... FOR UPDATE
死鎖
死鎖是指兩個(gè)或多個(gè)事務(wù)在同一資源上相互占用，并請(qǐng)求鎖定對(duì)方的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。
常見(jiàn)的解決死鎖的方法
1.如果不同程序會(huì)并發(fā)存取多個(gè)表，盡量約定以相同的順序訪問(wèn)表，可以大大降低死鎖機(jī)會(huì)。
2.在同一個(gè)事務(wù)中，盡可能做到一次鎖定所需要的所有資源，減少死鎖產(chǎn)生概率；
3.對(duì)于非常容易產(chǎn)生死鎖的業(yè)務(wù)部分，可以嘗試使用升級(jí)鎖定顆粒度，通過(guò)表級(jí)鎖定來(lái)減少死鎖產(chǎn)生的概率；
