上次我們討論了MySQL的運(yùn)行流程及原理，字段設(shè)計(jì)撵幽，存儲(chǔ)引擎和查詢緩存盐杂。

MySQL面試知識(shí)點(diǎn)追命連環(huán)問（一）

這次我們繼續(xù)來追命連環(huán)問關(guān)于事務(wù)厉斟，索引强衡，SQL優(yōu)化等相關(guān)的內(nèi)容漩勤。準(zhǔn)備好了嗎链快？

• 事務(wù)

• 索引

• SQL優(yōu)化

• 常見問題

1. MySQL事務(wù)

面試官：你知道事務(wù)嗎眉尸？

我：知道霉祸。事務(wù)（Transaction）是訪問和更新數(shù)據(jù)庫(kù)的程序執(zhí)行單元丝蹭；

事務(wù)中可能包含一個(gè)或多個(gè)sql語(yǔ)句奔穿，這些語(yǔ)句要么都執(zhí)行，要么都不執(zhí)行缅茉。

事務(wù)主要有四大特性蔬墩。即ACID：原子性拇颅，一致性，隔離性和持久性岸夯。

原子性：不可分割的操作單元们妥，事務(wù)中所有操作旅赢，要么全部成功惑惶；要么回滾到執(zhí)行事務(wù)之前的狀態(tài)僵控。
一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后鱼冀，數(shù)據(jù)庫(kù)的完整性約束沒有被破壞千绪。
隔離性：事務(wù)操作之間彼此獨(dú)立和透明互不影響荸型。如果一個(gè)事務(wù)處理后的結(jié)果稿静，影響了其他事務(wù)，那么其他事務(wù)會(huì)撤回妈嘹。
持久性：事務(wù)一旦提交，其結(jié)果就是永久的他去。即便發(fā)生系統(tǒng)故障灾测，也能恢復(fù)。

面試官：嗯四大特性說的沒錯(cuò)秦爆，那你知道高并發(fā)場(chǎng)景下事務(wù)可能會(huì)出現(xiàn)的問題嗎等限？

我：事務(wù)并發(fā)執(zhí)行的話確實(shí)會(huì)產(chǎn)生一些問題。比如說：幻讀筹误，臟讀纫事，不可重復(fù)讀丽惶。因?yàn)楦綦x性臟寫是不會(huì)發(fā)生的钾唬。

臟讀：一個(gè)事務(wù)讀取到另一個(gè)未提交事務(wù)修改的數(shù)據(jù)抡秆。

session A：查詢的止，得到某條數(shù)據(jù)session B：修改某條數(shù)據(jù)诅福，但是最后回滾掉啦session A：在sessionB修改某條數(shù)據(jù)之后氓润，在回滾之前，讀取了該條記錄

對(duì)于session A來說崩溪，讀到了session回滾之前的臟數(shù)據(jù)

不可重復(fù)讀：多次讀取的數(shù)據(jù)內(nèi)容不一樣。

session A：查詢某條記錄session B : 修改該條記錄抵怎，并提交事務(wù)session A : 再次查詢?cè)摋l記錄反惕，發(fā)現(xiàn)前后查詢不一致

幻讀：前后多次讀取，數(shù)據(jù)總量不一樣悬赏。

session A：查詢表內(nèi)所有記錄session B : 新增一條記錄闽颇，并查詢表內(nèi)所有記錄session A : 再次查詢?cè)摋l記錄尖啡，發(fā)現(xiàn)前后查詢不一致

面試管：那什么情況下會(huì)出現(xiàn)這些問題呢？

MySQL標(biāo)準(zhǔn)中定義了四種隔離級(jí)別剩膘，并規(guī)定了每種隔離級(jí)別下上述幾個(gè)問題是否存在衅斩。

一般來說，隔離級(jí)別越低怠褐，系統(tǒng)開銷越低畏梆，可支持的并發(fā)越高，但隔離性也越差奈懒。隔離級(jí)別與讀問題的關(guān)系如下：

image

讀未提交：臟讀奠涌，不可重復(fù)讀，幻讀都有可能發(fā)生
讀已提交：不可重復(fù)讀筐赔，幻讀可能發(fā)生
可重復(fù)讀：幻讀可能發(fā)生
可串行化：都不可能發(fā)生

在實(shí)際應(yīng)用中，讀未提交在并發(fā)時(shí)會(huì)導(dǎo)致很多問題，而性能相對(duì)于其他隔離級(jí)別提高卻很有限物邑，因此使用較少科阎。

可串行化強(qiáng)制事務(wù)串行错英，并發(fā)效率很低，只有當(dāng)對(duì)數(shù)據(jù)一致性要求極高且可以接受沒有并發(fā)時(shí)使用惩琉，因此使用也較少伍玖。

因此在大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)中，默認(rèn)的隔離級(jí)別是讀已提交(如Oracle)或可重復(fù)讀祷蝌。

MySQL事務(wù)默認(rèn)的隔離級(jí)別是可重復(fù)讀，而且MySQL可以解決了幻讀的問題。

面試官：看來你對(duì)事務(wù)理解的還不錯(cuò)。那你知道MySQL的另一個(gè)重要特性索引嗎急黎？

2. MySQL索引

答：索引就是數(shù)據(jù)庫(kù)管理系統(tǒng)中一個(gè)排序的數(shù)據(jù)結(jié)構(gòu)汞贸，以協(xié)助快速查詢、更新數(shù)據(jù)庫(kù)表中數(shù)據(jù)。索引的實(shí)現(xiàn)通常使用B樹及其變種B+樹。

在數(shù)據(jù)之外，數(shù)據(jù)庫(kù)維護(hù)這些原來快速查找的索引也是要付出代價(jià)的芍殖。一是增加了數(shù)據(jù)庫(kù)的存儲(chǔ)计贰，二是在插入和修改數(shù)據(jù)時(shí)要花費(fèi)較多的時(shí)間（因?yàn)樗饕惨S之變動(dòng)）褐桌。

面試官：那索引是怎樣實(shí)現(xiàn)的呢？MyISAM和Innodb的實(shí)現(xiàn)方式一樣嗎骄崩？

答：不一樣的采盒。MyISAM和Innodb雖然都使用B+樹作為索引結(jié)構(gòu)除盏，但索引的實(shí)現(xiàn)方式還是不一樣的痴颊。

MyISAM的葉節(jié)點(diǎn)的data域存放的是數(shù)據(jù)記錄的地址甩栈，而Innodb數(shù)據(jù)文件本身就是索引文件袭灯。

MyISAM中索引檢索的算法為首先按照B+Tree搜索算法搜索索引，如果指定的Key存在豹绪，則取出其data域的值，然后以data域的值為地址瞒津，讀取相應(yīng)數(shù)據(jù)記錄蝉衣。

image

而在InnoDB中，表數(shù)據(jù)文件本身就是按B+Tree組織的一個(gè)索引結(jié)構(gòu)巷蚪，這棵樹的葉節(jié)點(diǎn)data域保存了完整的數(shù)據(jù)記錄病毡。這個(gè)索引的key是數(shù)據(jù)表的主鍵，因此InnoDB表數(shù)據(jù)文件本身就是主索引屁柏。

image

因?yàn)镮nnoDB的數(shù)據(jù)文件本身要按主鍵聚集啦膜，所以InnoDB要求表必須有主鍵（MyISAM可以沒有）有送。

如果沒有顯式指定，則MySQL系統(tǒng)會(huì)自動(dòng)選擇一個(gè)可以唯一標(biāo)識(shí)數(shù)據(jù)記錄的列作為主鍵僧家，如果不存在這種列雀摘，則MySQL自動(dòng)為InnoDB表生成一個(gè)隱含字段作為主鍵。

B+樹是一種B樹的變種八拱，為有序數(shù)組鏈表+平衡多叉樹阵赠。基本和B樹類似肌稻，只有葉子節(jié)點(diǎn)存放數(shù)據(jù)清蚀，而且葉子節(jié)點(diǎn)之間通過指針相連。

面試官：那為什么索引用B+樹呢爹谭，B+樹有什么優(yōu)點(diǎn)呢轧铁？

1、B+樹的磁盤讀寫代價(jià)更低: B+樹的內(nèi)部節(jié)點(diǎn)并沒有指向關(guān)鍵字具體信息的指針旦棉，因此其內(nèi)部節(jié)點(diǎn)相對(duì)B樹更小齿风，如果把所有同一內(nèi)部節(jié)點(diǎn)的關(guān)鍵字存放在同一盤塊中，那么盤塊所能容納的關(guān)鍵字?jǐn)?shù)量也越多绑洛，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多救斑，相對(duì)IO讀寫次數(shù)就降低了。

2真屯、由于B+樹的數(shù)據(jù)都存儲(chǔ)在葉子結(jié)點(diǎn)中脸候，分支結(jié)點(diǎn)均為索引，方便掃庫(kù)绑蔫，只需要掃一遍葉子結(jié)點(diǎn)即可运沦，但是B樹因?yàn)槠浞种ЫY(jié)點(diǎn)同樣存儲(chǔ)著數(shù)據(jù)，我們要找到具體的數(shù)據(jù)配深，需要進(jìn)行一次中序遍歷按序來掃携添，所以B+樹更加適合在區(qū)間查詢的情況，所以通常B+樹用于數(shù)據(jù)庫(kù)索引篓叶。

image

面試官：那什么是聚簇索引呢烈掠？

聚簇索引是一種數(shù)據(jù)存儲(chǔ)方式，它實(shí)際上是在同一個(gè)結(jié)構(gòu)中保存了B+樹索引和數(shù)據(jù)行缸托，InnoDB表是按照聚簇索引組織的左敌。
InnoDB通過主鍵聚簇?cái)?shù)據(jù)。他使用主鍵值的大小來進(jìn)行記錄和頁(yè)的排序俐镐。葉子節(jié)點(diǎn)存儲(chǔ)的是完整的用戶記錄矫限。

注：聚簇索引不需要我們顯示的創(chuàng)建，他是由InnoDB存儲(chǔ)引擎自動(dòng)為我們創(chuàng)建的。如果沒有主鍵叼风，其也會(huì)默認(rèn)創(chuàng)建一個(gè)取董。

但聚簇索引只有在搜索條件為主鍵是才發(fā)揮作用，如果為其他的字段就不行咬扇，這個(gè)時(shí)候就需要普通索引了甲葬。

二級(jí)索引的葉子節(jié)點(diǎn)不再是完整的數(shù)據(jù)記錄，而是字段和主鍵值懈贺。當(dāng)需要這條記錄的其他字段時(shí)仍然需要根據(jù)這個(gè)主鍵id去查詢经窖，這個(gè)步驟叫做回表。

聚簇索引表最大限度地提高了I/O密集型應(yīng)用的性能梭灿，但它也有以下幾個(gè)限制：

1. 插入速度嚴(yán)重依賴于插入順序画侣，按照主鍵的順序插入是最快的方式，否則將會(huì)出現(xiàn)頁(yè)分裂堡妒，嚴(yán)重影響性能配乱。因此，對(duì)于InnoDB表皮迟，我們一般都會(huì)定義一個(gè)自增的ID列為主鍵搬泥。

2. 更新主鍵的代價(jià)很高，因?yàn)閷?huì)導(dǎo)致被更新的行移動(dòng)伏尼。因此忿檩，對(duì)于InnoDB表，我們一般定義主鍵為不可更新的爆阶。

3. 二級(jí)索引訪問需要兩次索引查找燥透，第一次找到主鍵值，第二次根據(jù)主鍵值找到行數(shù)據(jù)辨图。

面試官：索引有哪些類型班套？索引越多越好嗎？

除了上面說的主鍵索引和普通索引故河，還有唯一索引吱韭，聯(lián)合索引和全文索引。

唯一索引：該列具有唯一性的同時(shí)又是索引忧勿，不允許重復(fù)杉女。
全文索引：主要用于文本的查詢，它的出現(xiàn)是為了解決WHERE name LIKE “%word%"這類針對(duì)文本的模糊查詢效率較低的問題鸳吸。
聯(lián)合索引：對(duì)多列值進(jìn)行一個(gè)索引，其效率大于索引合并速勇。需遵循前綴原則晌砾。

建索引是有開銷的所以也不是越多越好，只要在需要的字段上建立索引烦磁。

第一养匈，創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間哼勇，這種時(shí)間隨著數(shù)據(jù)量的增加而增加。

第二呕乎，索引需要占物理空間积担，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間猬仁，如果要建立聚簇索引帝璧，那么需要的空間就會(huì)更大。

第三湿刽，當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加的烁、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)诈闺，這樣就降低了數(shù)據(jù)的維護(hù)速度渴庆。

索引的使用需要注意以下幾點(diǎn)：

1.最左前綴原則。一個(gè)聯(lián)合索引（a,b,c）,如果有一個(gè)查詢條件有a雅镊，有b襟雷，那么他則走索引，如果有一個(gè)查詢條件沒有a仁烹，那么他則不走索引耸弄。

2.使用唯一索引。具有多個(gè)重復(fù)值的列晃危，其索引效果最差叙赚。

3.不要過度索引。每個(gè)額外的索引都要占用額外的磁盤空間僚饭，并降低寫操作的性能震叮。在修改表的內(nèi)容時(shí)，索引必須進(jìn)行更新鳍鸵，有時(shí)可能需要重構(gòu)苇瓣，因此，索引越多偿乖，所花的時(shí)間越長(zhǎng)击罪。

4、索引列不能參與計(jì)算贪薪，保持列“干凈”媳禁，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡(jiǎn)單画切，b+樹中存的都是數(shù)據(jù)表中的字段值竣稽，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大毫别。所以語(yǔ)句應(yīng)該寫成create_time = unix_timestamp(’2014-05-29’);

5.一定要設(shè)置一個(gè)主鍵娃弓。前面聚簇索引說到如果不指定主鍵，InnoDB會(huì)自動(dòng)為其指定主鍵岛宦，這個(gè)我們是看不見的台丛。反正都要生成一個(gè)主鍵的，還不如我們?cè)O(shè)置砾肺，以后在某些搜索條件時(shí)還能用到主鍵的聚簇索引挽霉。

6.主鍵推薦用自增id，而不是uuid债沮。上面的聚簇索引說到每頁(yè)數(shù)據(jù)都是排序的炼吴，并且頁(yè)之間也是排序的，如果是uuid疫衩，那么其肯定是隨機(jī)的硅蹦，其可能從中間插入，導(dǎo)致頁(yè)的分裂闷煤，產(chǎn)生很多表碎片童芹。如果是自增的，那么其有從小到大自增的鲤拿，有順序假褪，那么在插入的時(shí)候就添加到當(dāng)前索引的后續(xù)位置。當(dāng)一頁(yè)寫滿近顷，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)生音。

索引禁忌：

• 不在低區(qū)分度的列上建?索引，例如“性別”

• 盡量避免%前導(dǎo)的查詢窒升，如like “%ab”

• 盡量避免負(fù)向查詢缀遍，如not in/like

• 避免全表掃描和頻繁的回表操作

面試官：看來你對(duì)索引掌握的很不錯(cuò)啊，那你平常遇到慢查詢是怎么優(yōu)化的呢饱须？

3. SQL優(yōu)化

SQL語(yǔ)句從客戶端經(jīng)由網(wǎng)絡(luò)協(xié)議到查詢緩存域醇，如果沒有命中緩存，再經(jīng)過解析工作蓉媳，得到準(zhǔn)確的SQL然后再來到優(yōu)化器譬挚。

首先，我們知道每一條SQL都有不同的執(zhí)行方法酪呻，要不通過索引减宣，要不通過全表掃描的方式。

影響SQL速度的主要在I/O成本和CPU成本的消耗上玩荠。

數(shù)據(jù)存儲(chǔ)在硬盤上蚪腋，我們想要進(jìn)行某個(gè)操作需要將其加載到內(nèi)存中丰歌，這個(gè)過程的時(shí)間被稱為I/O成本姨蟋。在內(nèi)存對(duì)結(jié)果集進(jìn)行排序的時(shí)間被稱為CPU成本屉凯。

所以進(jìn)行sql優(yōu)化首先進(jìn)行索引優(yōu)化，讓我們的sql語(yǔ)句盡量走索引而不是走全表掃描的方法眼溶。

在平常遇到慢查詢時(shí)首先去分析慢查詢?nèi)罩居蒲猓页雎樵兊膕ql。然后針對(duì)這些sql進(jìn)行分析堂飞。常見慢查詢主要有以下幾種情況：

1. 索引沒起作用灌旧。字段沒建立索引，或者是索引沒有起作用绰筛。使用了like關(guān)鍵字或使用了多列索引的查詢語(yǔ)句枢泰。

2. 數(shù)據(jù)庫(kù)結(jié)構(gòu)不合理。合理的數(shù)據(jù)庫(kù)結(jié)構(gòu)不僅可以使數(shù)據(jù)庫(kù)占用更小的磁盤铝噩，也可以讓sql執(zhí)行速度更快衡蚂。一可以將字段很多的表拆解成多個(gè)表。二增加中間表骏庸。

3. 分解關(guān)聯(lián)查詢毛甲。將大查詢分成多個(gè)小查詢。

4. 優(yōu)化limit分頁(yè)具被。當(dāng)偏移量非常大時(shí)會(huì)導(dǎo)致前面查詢到的無(wú)用數(shù)據(jù)都要舍棄掉玻募，如果表非常大，且篩選字段沒有合適的索引一姿，那么這樣的代價(jià)是非常高的七咧。如我們下一次的查詢能從前一次查詢結(jié)束后標(biāo)記的位置開始查找，那將節(jié)省很多開銷叮叹。

4.常見問題

問題一：嗯現(xiàn)在我們來看看具體問題艾栋，那你看這條語(yǔ)句會(huì)用到索引嗎？

以下語(yǔ)句是否會(huì)應(yīng)用索引：SELECT FROM users WHERE YEAR(adddate) < 2007;

答：不會(huì)衬横，因?yàn)橹灰猩婕暗竭\(yùn)算裹粤，MySQL就不會(huì)使用索引。

問題二：那如果列值為NULL時(shí)蜂林，查詢是否會(huì)用到索引遥诉？

在MySQL里NULL值的列也是走索引的。當(dāng)然噪叙，如果計(jì)劃對(duì)列進(jìn)行索引矮锈，就要盡量避免把它設(shè)置為可空，MySQL難以優(yōu)化引用了可空列的查詢,它會(huì)使索引睁蕾、索引統(tǒng)計(jì)和值更加復(fù)雜苞笨。

問題三：索引一定會(huì)提高速度嗎债朵？

通常，通過索引查詢數(shù)據(jù)比全表掃描要快瀑凝。但是我們也必須注意到它的代價(jià)序芦。

索引需要空間來存儲(chǔ),也需要定期維護(hù), 每當(dāng)有記錄在表中增減或索引列被修改時(shí),索引本身也會(huì)被修改. 這意味著每條記錄的INSERT,DELETE,UPDATE將為此多付出4,5 次的磁盤I/O. 因?yàn)樗饕枰~外的存儲(chǔ)空間和處理,那些不必要的索引反而會(huì)使查詢反應(yīng)時(shí)間變慢。使用索引查詢不一定能提高查詢性能粤咪。

問題四：如何查詢第n高的工資谚中？

SELECT DISTINCT(salary) from employee ORDER BY salary DESC LIMIT n-1,1

問題五：****一個(gè)6億的表a，一個(gè)3億的表b寥枝，通過外間tid關(guān)聯(lián)宪塔，你如何最快的查詢出滿足條件的第50000到第50200中的這200條數(shù)據(jù)記錄。

1囊拜、如果A表TID是自增長(zhǎng),并且是連續(xù)的,B表的ID為索引

select * from a,b where a.tid = b.id and a.tid>500000 limit 200;

2某筐、如果A表的TID不是連續(xù)的,那么就需要使用覆蓋索引。TID要么是主鍵,要么是輔助索引,B表ID也需要有索引冠跷。

select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;

好啦南誊，今天的追命連環(huán)問就到這里了，下次繼續(xù)蔽莱，如對(duì)文章有疑惑或補(bǔ)充的地方歡迎留言交流(●'?'●)弟疆。原創(chuàng)不易，如果對(duì)你有幫助的話歡迎點(diǎn)贊盗冷！

相關(guān)推薦閱讀

你知道一次完整的HTTP請(qǐng)求過程嗎

Redis常見面試題連環(huán)問怠苔，你能回答到第幾問？****（上）

大廠高頻面試題：****高并發(fā)下接口冪等性的解決方案仪糖？

?