1.SQL查詢操作

select的“另類”用法

我們通常習(xí)慣select + from從數(shù)據(jù)表中讀取數(shù)據(jù)，不過實(shí)際上select并不一定要去讀取數(shù)據(jù)庫中的內(nèi)容硼控。

比如：

select 1+1; 返回2

select now(); 返回當(dāng)前時(shí)間

select本身代表要返回的內(nèi)容隔显，至于與數(shù)據(jù)庫表中存的數(shù)據(jù)是否有關(guān)并不重要户誓。

同時(shí)MySQL支持基本四則運(yùn)算夷家，所以可以利用這兩個(gè)特性來實(shí)現(xiàn)：

統(tǒng)計(jì)A隊(duì)列的案件數(shù)量比B隊(duì)列的案件數(shù)量差異：

select
   (select count(id) from assignment where queue='A' and `status`='ACTIVE' and active=1)
-
   (select count(id) from assignment where queue='B' and `status`='ACTIVE' and active=1);

where和having

where和having都有對(duì)數(shù)據(jù)進(jìn)行篩選的功能，通常的使用習(xí)慣是where跟在from后面义屏，而having跟在group by后面闽铐，那么是不是非得這么用呢兄墅？二者的區(qū)別在哪里呢察迟？

where表示基于數(shù)據(jù)表的列參數(shù)的限制條件扎瓶，即where a=b語句中的a必須是數(shù)據(jù)表中的列秕岛。

having表示基于select語句返回的變量的限制條件继薛，即having a=b語句中的a必須在select中出現(xiàn)遏考。

來看下面幾種場(chǎng)景：

select * from table where a=XXX;
select * from table having a=XXX;
// 二者均可使用灌具，效果一樣
 
select a,b,c from table where d=xxx;  // ok
select a,b,c from table having d=xxx; // 錯(cuò)誤譬巫，d不在select選擇之中咖楣，無法用來having
 
 
select a, count(b) from table where count(b)>100;    // 錯(cuò)誤，where只能跟表中存在的列芦昔，無法對(duì)聚合诱贿、函數(shù)操作后的變量進(jìn)行篩選
select a, count(b) from table group by a having count(b)>100;   // ok

distinct和group by

distinct()函數(shù)和group by 語句都有用來去重的效果，但對(duì)應(yīng)的使用場(chǎng)景有些差異咕缎。

distinct的作用就是單純的去重珠十，必須緊跟著select的后面，否則會(huì)報(bào)錯(cuò)宵睦。當(dāng)select中涉及的到所有元素都重復(fù)時(shí)墅诡，只返回1條記錄桐智。有時(shí)和count連用，統(tǒng)計(jì)不重復(fù)的數(shù)量说庭。

select distinct(a) from table;   // 返回1然磷，2，3刊驴，5姿搜，6，7，9...
 
select distinct(a),b from table; // 返回 (1,2),(1,3),(2,1),(3,5),(3,6)....
 
select a,distinct(b) from table; // error
 
select count(distinct(a)) from table; // 統(tǒng)計(jì)a不重復(fù)的記錄數(shù)量

group by的作用是做分組匯總統(tǒng)計(jì)滔蝉，必須配合聚合函數(shù)（count、sum、avg等）使用，否則無意義。返回結(jié)果中，被group by的參數(shù)每個(gè)只有1行届良。

select avg(b) from table group by a   //對(duì)每一個(gè)a的條件查詢b的平均值
 
select count(distinct(c)) from table group by a  //對(duì)每個(gè)a的分組統(tǒng)計(jì)不同的c列值的數(shù)量

關(guān)聯(lián)查詢

當(dāng)from語句選擇了2張或以上的表時(shí)爪模，返回內(nèi)容是兩張表的笛卡爾積，總數(shù)是二者數(shù)據(jù)量的乘積声滥！

因此為了避免數(shù)據(jù)量的爆炸眉撵，關(guān)聯(lián)查詢多表時(shí)必須要加聯(lián)結(jié)條件。

基本句式是：

select a ·聯(lián)結(jié)方式· b on ·聯(lián)結(jié)條件·

聯(lián)結(jié)方式有內(nèi)聯(lián)結(jié)落塑、外聯(lián)結(jié)纽疟、左聯(lián)結(jié)、右聯(lián)結(jié)等憾赁，詳見下圖：

關(guān)聯(lián)查詢

修改操作涉及自身查詢

有時(shí)候在做update污朽、delete操作時(shí)需要結(jié)合select子句確定修改的范圍。當(dāng)select子句查詢涉及要修改的表格本身時(shí)龙考，要特別注意：不能select出數(shù)據(jù)蟆肆，再在同一個(gè)表中做update、delete操作晦款。

來看一個(gè)例子：a表中現(xiàn)在出現(xiàn)了b列同一個(gè)值有多條ACTIVE的記錄的錯(cuò)誤情況炎功，現(xiàn)在要將這些記錄全部設(shè)為EXPIRE。

下面是錯(cuò)誤的示范：

UPDATE a SET `status` = 'EXPIRE'
WHERE b IN (
 SELECT b FROM a
 WHERE `status` = 'ACTIVE'
 GROUP BY b
 HAVING count(*) > 1
);

執(zhí)行報(bào)錯(cuò)： You can't specify target table 'a' for update in FROM clause

要正常實(shí)現(xiàn)該功能缓溅，需要在update和select操作直接加一層臨時(shí)表蛇损，臨時(shí)表里存放從原表select出來的數(shù)據(jù)，update原表時(shí)坛怪，子查詢讀取臨時(shí)表的數(shù)據(jù)淤齐，從而避免報(bào)錯(cuò)。

正確寫法：

UPDATE a SET `status` = 'EXPIRE'
WHERE b IN (
    SELECT temp.b from (
        SELECT b from a
        WHERE `status` = 'ACTIVE'
        GROUP BY b
        HAVING count(*) > 1
    ) as temp
);

這里從a表中查詢出來的b全部放進(jìn)了temp表中袜匿，update的時(shí)候IN語句讀取temp表中的值更啄，避免了兩個(gè)操作指向同一張表產(chǎn)生報(bào)錯(cuò)。

2.InnoDB引擎和索引的技巧

InnoDB引擎的b+樹索引

如果留心過數(shù)據(jù)庫表信息的話肯定能發(fā)現(xiàn)居灯，我們用的數(shù)據(jù)庫默認(rèn)都是InnoDB引擎的祭务。MySQL建立數(shù)據(jù)庫表時(shí)，除非顯示聲明穆壕，否則建立的都是InnoDB引擎待牵。InnoDB的優(yōu)勢(shì)在于事務(wù)安全性和更細(xì)粒度的行級(jí)鎖，詳見下一章喇勋。

InnoDB引擎的索引機(jī)制是b+樹，簡稱b樹索引偎行。至于什么是b+樹川背，請(qǐng)自行百度贰拿。

.

.

.

算了，還是講下吧熄云。膨更。。缴允。

查詢一般有三類：順序查詢（慢）荚守、二分查詢（快）和hash查詢（最快，但是費(fèi)空間练般，除了內(nèi)存機(jī)制的應(yīng)用矗漾，一般不會(huì)使用）。順序查詢和二分查詢有著數(shù)量級(jí)的性能差距薄料，對(duì)于數(shù)據(jù)量大的情況差異尤其明顯敞贡。
為了更快的定位查詢到需要的數(shù)據(jù)，就要想辦法把慢的順序查詢變?yōu)榭斓亩植樵兩阒埃@就是索引的本質(zhì)誊役。

InnoDB中的索引分為主鍵索引（聚簇索引）和二級(jí)索引，其中主鍵索引和其他數(shù)據(jù)是存放在一起的谷市，二級(jí)索引有單獨(dú)的索引樹蛔垢。

先來看主鍵索引。

為了實(shí)現(xiàn)二分查詢迫悠，MySQL規(guī)定鹏漆，主鍵索引必須唯一并按照升序排列（否則沒法二分）。

數(shù)據(jù)存儲(chǔ)的的基本單位是“頁”及皂，一頁大小為16k甫男，為連續(xù)的存儲(chǔ)空間。

連續(xù)就好辦了验烧，由于規(guī)定了主鍵唯一升序板驳，要在一頁內(nèi)查找某個(gè)主鍵，就可以先讀取中間地址（更準(zhǔn)確的說法是槽位）存儲(chǔ)的數(shù)據(jù)碍拆，拿主鍵來比較若治，如果比中間的主鍵值大，那在后半空間里再去一半位置地址的數(shù)據(jù)比較感混，以此類推端幼。

當(dāng)一頁存不下的時(shí)候，就需要開一個(gè)新的頁弧满，但頁和頁之間的地址未必是連續(xù)的婆跑，二者通過雙向鏈表關(guān)聯(lián)。所以當(dāng)數(shù)據(jù)量大庭呜，不止一頁的時(shí)候如何實(shí)現(xiàn)二分呢滑进？

答案是建立一個(gè)目錄頁犀忱，目錄頁和存儲(chǔ)數(shù)據(jù)的頁結(jié)構(gòu)上其實(shí)是一樣一樣的，存儲(chǔ)的內(nèi)容是主鍵和其對(duì)應(yīng)的頁的地址扶关。這樣查詢的過程變成：先在目錄頁中二分查找主鍵阴汇，找到對(duì)應(yīng)數(shù)據(jù)頁的地址，進(jìn)入數(shù)據(jù)頁再次進(jìn)行二分查找节槐。

若數(shù)據(jù)量很大搀庶，目錄頁也不止一個(gè)怎么辦？那就再建立一個(gè)指向目錄頁的二級(jí)目錄頁铜异。

整個(gè)過程就像是根據(jù)頁碼查目錄哥倔，先搜索章節(jié)、再搜索小節(jié)熙掺、再搜索小小節(jié)未斑。

詳見下圖：
比如我要搜id=26的數(shù)據(jù)，首先<62找到目錄頁17币绩，然后<50找到數(shù)據(jù)頁37蜡秽，在頁37中最后二分法找到26的記錄。

主鍵索引樹

這個(gè)形狀不就是樹結(jié)構(gòu)嗎缆镣？來看一下這個(gè)樹有什么特點(diǎn)：

• 首先芽突，所有的具體數(shù)據(jù)都存在葉子節(jié)點(diǎn)
• 非葉子節(jié)點(diǎn)只存儲(chǔ)Key值和通往下一級(jí)節(jié)點(diǎn)的地址

我們管具有這種特點(diǎn)的樹叫做b+樹。InnoDB引擎通過b+樹實(shí)現(xiàn)索引的二分查詢董瞻，從而提升查詢性能寞蚌。

那手動(dòng)建立的其他索引呢？

答案是钠糊，每建立一個(gè)索引挟秤，就建一顆對(duì)應(yīng)的b+樹，并按照該索引字段升序排列（MySQL不止是數(shù)值可以排序抄伍，任何類型的值都可以艘刚，比如字符串是根據(jù)字母排列順序來排序）。

在按索引查詢時(shí)截珍，就在對(duì)應(yīng)的索引樹上進(jìn)行二分查詢操作攀甚，和主鍵樹一樣。

除主鍵外的所有索引都是二級(jí)索引岗喉，其b+樹和主鍵b+樹唯一的區(qū)別在于葉子節(jié)點(diǎn)：主鍵樹的葉子節(jié)點(diǎn)存放主鍵和具體記錄的數(shù)據(jù)秋度；二級(jí)索引樹的葉子節(jié)點(diǎn)存放索引和主鍵。

還有一種比較特別的二級(jí)索引钱床，里面包括多個(gè)列的值荚斯，稱為聯(lián)合索引。一個(gè)聯(lián)合索引也是一個(gè)b+數(shù)，內(nèi)部的順序先按照建立索引的第一個(gè)字段排序鲸拥，相同情況下再按照第二個(gè)字段排序拐格，以此類推僧免。

二級(jí)索引是怎么查詢數(shù)據(jù)的呢刑赶？首先通過二級(jí)索引樹二分查詢找到對(duì)應(yīng)的主鍵id，再拿這些主鍵id去主鍵索引樹上查詢對(duì)應(yīng)的數(shù)據(jù)懂衩，這個(gè)過程稱為“回表”撞叨。

所以，根據(jù)主鍵id查數(shù)據(jù)走一次索引浊洞，根據(jù)普通索引查數(shù)據(jù)要走兩次索引牵敷。

如何使用索引

索引將順序查詢升級(jí)為二分查詢，使得性能可以大大提升法希，但索引也不是萬能的枷餐，要用索引之前首先得知道使用的代價(jià)：

• 每建一個(gè)索引都要建一個(gè)b+樹，占空間是肯定的了苫亦，如果索引的值很大的話毛肋，更容易產(chǎn)生分頁現(xiàn)象，b+樹就需要更多的層屋剑，占空間就更大润匙；
• 不能“管殺不管埋”，建了索引樹還得維護(hù)唉匾，當(dāng)表內(nèi)插入孕讳、變更、刪除數(shù)據(jù)時(shí)巍膘，對(duì)應(yīng)的索引樹可能也需要同時(shí)更新厂财，這樣會(huì)拖慢數(shù)據(jù)庫操作的性能；
• 對(duì)于二級(jí)索引的使用峡懈，可能查出多個(gè)主鍵id璃饱，而這些主鍵id未必是連續(xù)的，所以回表查詢時(shí)是隨機(jī)I/O逮诲，比順序I/O的性能會(huì)差很多帜平，可能得不償失；

所以梅鹦，一個(gè)表上建的索引越多裆甩，占用空間就越大，增刪改記錄時(shí)的時(shí)間性能就越差齐唆。因此要建立索引嗤栓，就要保證其能被充分的利用。

網(wǎng)上對(duì)于什么情況下可以命中索引，什么情況下不行的列舉較為零碎茉帅，不好理解叨叙，但事實(shí)上只要理解了索引的b+樹結(jié)構(gòu)原理，判斷能否命中索引其實(shí)不難堪澎。
先來看幾個(gè)這輩子也別想命中索引的操作：

select * from table where a!=xxx       // !=, not in 這種操作顯然是沒法通過二分查找來快速定位的
 
select * from table where a*2=xxx      // 對(duì)索引變量做運(yùn)算會(huì)導(dǎo)致mysql把a(bǔ)*2作為整體來篩選，無法使用b+樹樱蛤，函數(shù)處理同理昨凡，所以對(duì)于字符串類型的變量保存數(shù)字的列要特別小心。
 
select * from table order by a         // 排序本身不是問題蚂四，問題在于沒有where子句遂赠，是全部數(shù)據(jù)的排序解愤，走索引只會(huì)增加回表操作開銷乎莉，不如全表搜索惋啃。
 
select * from table order by a, b desc limit 10   // 如果有多個(gè)字段做排序边灭，排序的方式需要一樣，才能方便的從b+樹上正著取或者倒著取數(shù)據(jù)称簿。
 
select * from table where b like '%xxx%'    // 字符串按照字母順序來排序惰帽，前綴做通配會(huì)導(dǎo)致無法判斷其大小范圍该酗。
 
 
聯(lián)合索引（a,b,c）的情況：
 
select * from table where b=xxx           // 聯(lián)合索引按建立索引的順序在b+樹上進(jìn)行升序排列士嚎，所以不使用a的情況下直接用b莱衩，由于a的值不確定笨蚁，無法命中
 
select * from table where a=xxx and c=xxx  // 只能命中索引a九火，原理同上
 
select * from table where a>xxx and b=xxx  // 還是只能命中索引a岔激，因?yàn)閍進(jìn)行的是范圍操作虑鼎，會(huì)篩選出多個(gè)值键痛，無法在同一顆索引樹上方便的匹配第二個(gè)字段絮短，系統(tǒng)會(huì)把b字段的匹配變?yōu)轫樞蛩阉?

理解了索引基于b+樹的搜索方式，就比較容易找正確的使用姿勢(shì)了：

• 表本身的數(shù)據(jù)量要大杉允，否則二分查詢節(jié)約的時(shí)間不如回表操作費(fèi)的時(shí)間叔磷；
• 索引盡量小改基，節(jié)約b+樹空間和搜索層次數(shù)咖为，降低維護(hù)成本躁染；
• 索引的取值盡可能多（唯一就更好了）褐啡，避免索引樹上查到大量主鍵id，在回表過程中浪費(fèi)時(shí)間低飒；
• 基于二分查詢的原理褥赊，表達(dá)式應(yīng)該是等值匹配拌喉、IN匹配、大小判斷或者用于排序端仰、分組操作荔烧，如果是字符串匹配鹤竭，前綴要準(zhǔn)確臀稚；
• 索引列要單獨(dú)出現(xiàn)三痰，不要做任何算術(shù)酒觅、函數(shù)運(yùn)算舷丹；
• 若有多個(gè)字段會(huì)同時(shí)使用颜凯，可以建立聯(lián)合索引症概，一是減少b+樹的數(shù)量節(jié)約空間彼城；二是因?yàn)橐淮尾樵冎粫?huì)用一個(gè)索引樹，聯(lián)合索引效率更高调炬；
• 對(duì)于聯(lián)合索引缰泡，建立時(shí)要按照使用頻率順序來建立棘钞，避免出現(xiàn)前一個(gè)未使用而使用后一個(gè)的情況泼返；
• 排序情況下一定要限制數(shù)量或者條件符隙，不用全表排序；
• 如果可以综芥，只搜索索引字段膀藐，避免select *额各，索引覆蓋的情況下只需要搜索索引樹吧恃，免去了回表操作痕寓；
• 對(duì)于IN操作呻率，可以拆解成多個(gè)等值匹配礼仗，用UNION算符并連結(jié)果；
  等等.....

MySQL自帶的explain是分析查詢性能的好工具。

explain

這里主要看type和key這倆字段惠啄。

key表示這個(gè)查詢用到的索引。

type表示這個(gè)查詢的索引使用效率趋距。type大概有以下幾種：

• const 使用主鍵索引匹配节腐，或者唯一索引匹配非null值摘盆。精確匹配一條孩擂，速度最快类垦。
• ref 使用普通二級(jí)索引匹配米苹。會(huì)匹配出多個(gè)主鍵進(jìn)行回表查詢蘸嘶。
• range 使用索引進(jìn)行范圍查找。
• index 使用了不帶索引的字段進(jìn)行條件篩選雪情。會(huì)在索引匹配后順序判斷每一條是否符合條件尘执。
• all 無法命中索引誊锭，全表掃描弥锄。

3.事務(wù)&數(shù)據(jù)庫鎖

事務(wù)和MVCC

數(shù)據(jù)庫的變更操作是個(gè)“危險(xiǎn)”的事情籽暇，尤其是對(duì)于需要幾個(gè)語句共同合作完成的操作（例如案件的出催），可能受到硬件性故障例如斷網(wǎng)斷電、并發(fā)影響卤恳，導(dǎo)致執(zhí)行一半中斷或者中途數(shù)據(jù)錯(cuò)亂的問題。

所以我們要確保這樣一個(gè)操作場(chǎng)景本今，要么一次性執(zhí)行完成不受影響挪凑，要么干脆別執(zhí)行躏碳。這種操作系列稱之為事務(wù)肄渗。

事務(wù)具備四個(gè)基本特性，稱為ACID：

• Atomicity 原子性惑申，該操作不可分割打斷人芽，保障可以一次性執(zhí)行完成
• Consistency 一致性，所有的操作要滿足約束條件（唯一惕味、not null等）
• Isolation 隔離性，事務(wù)之間不能相互干擾躺同，引起數(shù)據(jù)錯(cuò)亂（臟寫）
• Durability 持久性， 已經(jīng)完成的事務(wù)所修改的數(shù)據(jù)不會(huì)因任何故障丟失

InnoDB引擎支持事務(wù)操作，以begin開始涛救，以commit提交結(jié)束。

在高并發(fā)情況下，為了性能考慮摊灭，事務(wù)間的隔離性有時(shí)會(huì)做出一點(diǎn)取舍集嵌。引起數(shù)據(jù)錯(cuò)亂的臟寫肯定是要杜絕的怜珍，但還有幾種情況有時(shí)要求就沒有那么嚴(yán)格：

• 臟讀：事務(wù)A讀到了事務(wù)B修改過的數(shù)據(jù)今豆，但此時(shí)事務(wù)B未提交
• 不可重復(fù)讀：事務(wù)A以同樣的查詢語句查詢，而過程中有其他事務(wù)修改了數(shù)據(jù)插掂，引起每次查詢的結(jié)果不同
• 幻讀：事務(wù)A以同樣的查詢語句查詢，而過程中有其他事務(wù)插入了滿足查詢條件的新數(shù)據(jù)，導(dǎo)致A查出了“不該查出”的新數(shù)據(jù)

MySQL支持4種隔離性級(jí)別的設(shè)置：

MySQL事務(wù)隔離級(jí)別

隔離性最差的read uncommitted 會(huì)直接讀取最新的版本，而serializable會(huì)使用加鎖的方式。

剩下倆會(huì)通過MVCC（multi-version concurrency control）來實(shí)現(xiàn)，其主要依賴版本鏈和ReadView仅孩。

可以對(duì)比參照git的代碼版本控制和tag京腥，事務(wù)對(duì)數(shù)據(jù)的修改就可以看成是各種分支，每一個(gè)事務(wù)有自己對(duì)應(yīng)的版本位置，ReadView是在某個(gè)時(shí)刻對(duì)版本鏈打的tag队塘，兩個(gè)級(jí)別的區(qū)別主要在于打tag的時(shí)機(jī)要求淋袖。

鎖的性質(zhì)：共享鎖 pk 排他鎖

加鎖是隔離性最強(qiáng)的方法。對(duì)于讀和寫操作拜姿，我們對(duì)隔離性的要求又有一些不同：

由于讀操作不改變數(shù)據(jù)，所以A在讀的時(shí)候，并不排斥B也來讀；
寫操作會(huì)直接影響數(shù)據(jù)，所以A在寫的時(shí)候，即不能讓B來寫，也不能讓B來讀瘪菌。

所以师妙，讀操作的事務(wù)是可以同時(shí)進(jìn)行的，而寫操作事務(wù)之間珠洗、寫操作和讀操作則是互斥排他的膊爪。

我們把讀操作加的鎖稱為共享鎖（S），寫操作加的鎖稱為排他鎖（X）。（p.s 讀操作也可以顯示聲明加排它鎖，select.....for update，一般不會(huì)這么干）

S鎖和X鎖的兼容性

加鎖票腰、解鎖操作：
一個(gè)讀操作事務(wù)開始時(shí)，先判斷是否有X鎖，如果有則進(jìn)入S鎖等待隊(duì)列等待吟税，沒有則加上S鎖并開始執(zhí)行；
一個(gè)寫操作事務(wù)開始是，先判斷是否有鎖，如有則進(jìn)入X鎖等待隊(duì)列等待术荤，沒有則加上X鎖并開始執(zhí)行；
當(dāng)前事務(wù)完成并釋放鎖后刚照，先把X等待隊(duì)列中的X鎖出隊(duì)列執(zhí)行操作（防止寫操作“饑餓”）。

鎖的顆粒度：表級(jí)鎖 pk 行級(jí)鎖

顧名思義喧兄，表級(jí)鎖是對(duì)整張表上鎖无畔，而行級(jí)鎖是對(duì)一條記錄上鎖。一般情況下吠冤，行級(jí)鎖對(duì)并發(fā)的支持會(huì)好于表級(jí)所浑彰，比如有兩個(gè)寫任務(wù)，針對(duì)不同的記錄拯辙，行級(jí)鎖可以并行而表級(jí)鎖會(huì)阻塞郭变。

不過說句公道話颜价，表級(jí)鎖并不是一無是處，因?yàn)槠涓?nèi)存诉濒。尤其是一個(gè)事務(wù)需要訪問表中大量數(shù)據(jù)或者做group by的時(shí)候周伦，用表鎖只要維護(hù)一個(gè)鎖，而用行鎖要維護(hù)N個(gè)鎖未荒。
此外专挪，大量insert操作時(shí)，表鎖性能也會(huì)優(yōu)于行鎖片排。InnoDB主鍵自增就是通過表級(jí)鎖完成的寨腔。

InnoDB引擎同時(shí)支持表級(jí)鎖和行級(jí)鎖，而其他引擎如MyISAM只支持表級(jí)鎖率寡。
原因還是在于b+索引樹迫卢，InnoDB引擎可以對(duì)索引上鎖，當(dāng)然同時(shí)也反過來要求使用行級(jí)鎖必須要用索引勇劣。

有行鎖和表鎖共存時(shí)就會(huì)引出一個(gè)問題靖避，如果要對(duì)表加鎖，就需要判斷表中的記錄是否被加了行鎖比默，總不能遍歷查詢判斷每一行有沒有加鎖吧幻捏？

遍歷是不可能遍歷的，這輩子都不可能命咐。為了解決這個(gè)問題篡九，需要引入一個(gè)“意向”鎖。

意向鎖也分為共享和排他兩類醋奠，簡稱IS和IX榛臼。有IS鎖意味著表中有行級(jí)別S鎖；有IX鎖意味著表中有行級(jí)別X鎖窜司。

意向鎖是表級(jí)別的沛善，但其本身不鎖定任何表或者行，只是用來快速判斷表內(nèi)是否有記錄被鎖著塞祈，所以意向鎖之間是兼容的金刁。有10個(gè)意向鎖就說明里面有10個(gè)行級(jí)操作正在進(jìn)行。

InnoDB表級(jí)別鎖的兼容性：

完整的鎖兼容性

完整版的上鎖過程：

• 行級(jí)讀操作议薪，先判斷是否有表級(jí)X鎖或?qū)?yīng)行的X鎖尤蛮，若有則等待；沒有則給表加IS鎖斯议，給對(duì)應(yīng)行加S鎖并執(zhí)行操作产捞。
• 行級(jí)寫操作，先判斷是否有表鎖或者對(duì)應(yīng)行鎖哼御，若有則等待坯临；沒有則給表加IX鎖焊唬，給對(duì)應(yīng)行加X鎖并執(zhí)行操作。
• 行級(jí)操作完成后看靠，釋放行鎖及其加上的表意向鎖求晶。
• 表級(jí)讀操作，先判斷是否有表級(jí)X鎖或IX鎖衷笋，若有則等待；沒有則給表加S鎖并執(zhí)行操作矩屁。
• 表級(jí)寫操作辟宗，先判斷是否有表級(jí)鎖或意向鎖，若有則等待吝秕；沒有則給表加X鎖執(zhí)行操作泊脐。
• 表級(jí)操作完成后，釋放表級(jí)鎖烁峭。

行級(jí)鎖的功能：記錄鎖 pk 間隙鎖

這二者還是比較好理解的容客。

記錄鎖鎖定的是一條數(shù)據(jù)記錄本身，對(duì)于臟讀约郁、不可重復(fù)讀的情況缩挑，是某個(gè)特定的記錄被其他事務(wù)修改引起的，所以只要鎖定這提條數(shù)據(jù)防止其他事務(wù)來更新就可以鬓梅。

對(duì)于幻讀供置，是由于新插入的記錄引起的，由于無法預(yù)測(cè)哪些記錄會(huì)插入绽快，所以依靠鎖數(shù)據(jù)是無法避免的芥丧。這時(shí)需要再加一個(gè)間隙鎖。

間隙鎖的作用是鎖定上鎖行和主鍵前一條數(shù)據(jù)之間的空間坊罢，防止有插入操作续担。例如目前表中兩條連續(xù)記錄id分別是10和15，那在15的數(shù)據(jù)上加間隙鎖活孩，可以防止插入id在11-14范圍內(nèi)的數(shù)據(jù)物遇。

supermum是mysql里的虛擬最大行記錄，對(duì)于當(dāng)前表里主鍵最大的記錄诱鞠，在supermum上加間隙鎖可以防止后續(xù)更大主鍵的記錄插入挎挖。