前 言

通過(guò)前幾期文章的積累，現(xiàn)在我們的理論知識(shí)已經(jīng)極為扎實(shí)了，這個(gè)時(shí)候就可以動(dòng)手開(kāi)始sql優(yōu)化了，sql優(yōu)化是非常重要，因?yàn)榧词乖俸玫腗ySQL設(shè)計(jì)架構(gòu)，也扛不住一個(gè)頻繁查詢的垃圾sql語(yǔ)句。

關(guān)于sql的優(yōu)化，我們也是有一定的原則和先后順序的，大體的步驟的我們用一張流程圖來(lái)看一下：

總體呢，大概可以分為以下幾個(gè)步驟：

（1）首先，我們得要看下sql語(yǔ)句中是否有join語(yǔ)句，比如內(nèi)連接查詢inner join，外連接查詢 left join right join等；因?yàn)閖oin語(yǔ)句一般都涉及到跨表查詢了，所以首先我們得要為join語(yǔ)句中，負(fù)責(zé)連接兩張表的字段創(chuàng)建索引，這樣的話可以利用索引加快兩張表關(guān)聯(lián)的速度。

（2）接下來(lái)，我們會(huì)再看一下sql語(yǔ)句中的where語(yǔ)句，我們可以根據(jù)當(dāng)前表中的數(shù)據(jù)量，以及where語(yǔ)句的過(guò)濾條件，預(yù)估下查詢結(jié)果的數(shù)據(jù)量是否會(huì)很大，如果數(shù)據(jù)量很大的話，查詢的速度肯定就會(huì)很慢，所以，為了提高sql語(yǔ)句的執(zhí)行效率，我們得要為where語(yǔ)句中過(guò)濾字段單獨(dú)創(chuàng)建索引。

（3）當(dāng)我們把join語(yǔ)句以及where語(yǔ)句中的字段優(yōu)化完之后，就可以來(lái)看一下其他的一些細(xì)節(jié)部分，比如sql語(yǔ)句中如果使用了聚合函數(shù)，或者對(duì)查詢的結(jié)果進(jìn)行了排序，那么，一般我們都建議為聚合函數(shù)中的字段，以及排序的字段都創(chuàng)建索引，讓這些操作利用索引速度更快點(diǎn)。

sql優(yōu)化中不管是對(duì)where語(yǔ)句、聚合函數(shù)、還是排序操作的優(yōu)化，優(yōu)化起來(lái)相對(duì)而言會(huì)簡(jiǎn)單點(diǎn)，為對(duì)應(yīng)的字段創(chuàng)建合適的索引就可以了，但是，join語(yǔ)句這塊的優(yōu)化涉及到一些比較重要的原理，我們還是有必要來(lái)看下的。

簡(jiǎn)單來(lái)說(shuō)，在mysql中使用join語(yǔ)句關(guān)聯(lián)2張表的話，比如執(zhí)行這條sql：

select * from order_info t1 left join order_item_detail t2 on t1.order_no = t2.order_no

這個(gè)時(shí)候，join關(guān)聯(lián)查詢的過(guò)程是什么樣子的呢？其實(shí)，這個(gè)就取決于當(dāng)前join語(yǔ)句用到的算法了，join語(yǔ)句一共有3種算法，最基礎(chǔ)的是Simple nested loop算法，接下來(lái)，我們一起來(lái)看下。

Simple nested loop算法

Simple nested loop算法，說(shuō)白了就是一個(gè)雙重for循環(huán)遍歷的算法，Simple nested loop算法匹配的過(guò)程是這樣的：

從左邊的驅(qū)動(dòng)表order_info中，每取出一條記錄都要遍歷一遍被驅(qū)動(dòng)表order_item_detail，說(shuō)白了就是一個(gè)雙重for循環(huán)。

如果驅(qū)動(dòng)表和被驅(qū)動(dòng)表中都有100條數(shù)據(jù)的話，那么此時(shí)就需要匹配 100 * 100 = 10000次，可見(jiàn)效率是非常低的，所以，MySQL并沒(méi)有選擇使用 Simple nested loop 算法，而是使用了優(yōu)化后的Block nested loop 算法。

Block nested loop 算法

Block nested loop 算法對(duì) Simple nested loop 算法進(jìn)行了優(yōu)化，它引入了 join buffer，join buffer 主要用于優(yōu)化不帶索引條件的 join 查詢，它會(huì)緩存連接過(guò)程中用到的字段，這樣可以有效減少匹配次數(shù)，就像這樣：

可以看到，Block nested loop的優(yōu)化思路，是減少被驅(qū)動(dòng)表的匹配次數(shù)，它主要是通過(guò)一次性緩存驅(qū)動(dòng)表的多條數(shù)據(jù)，以此來(lái)減少被驅(qū)動(dòng)表的匹配次數(shù)，從而可以達(dá)到提升性能的目的。

需要注意的是，MySQL提供了一個(gè)參數(shù)join buffer_size，它是用來(lái)控制 join buffer 大小的，而MySQL默認(rèn)的join_buffer_size 是 256K，所以如果驅(qū)動(dòng)表的數(shù)據(jù)太多的話，默認(rèn)的join buffer可能一次性放不下全部的數(shù)據(jù)。

這個(gè)時(shí)候，join buffer就會(huì)采用分段緩存的機(jī)制來(lái)緩存驅(qū)動(dòng)表的數(shù)據(jù)，但是這種分段緩存方式的性能，是比一次性緩存全部數(shù)據(jù)要差一些的。

所以，我們可以通過(guò)join_buffer_size參數(shù)，適當(dāng)調(diào)大join buffer的大小，使join buffer可以一次性放下驅(qū)動(dòng)表的所有數(shù)據(jù)，這樣可以提升join的性能。

Index nested loop算法

最后還有一種Index nested loop算法：

它的優(yōu)化思路主要是減少被驅(qū)動(dòng)表數(shù)據(jù)的匹配次數(shù)， 就是驅(qū)動(dòng)表直接與被驅(qū)動(dòng)表的索引進(jìn)行匹配，這樣就不用和被驅(qū)動(dòng)表的每條記錄比較了。

原來(lái)的匹配次數(shù)為：驅(qū)動(dòng)表行數(shù) * 被驅(qū)動(dòng)表行數(shù)，而現(xiàn)在變成了：驅(qū)動(dòng)表行數(shù) * 被驅(qū)動(dòng)表索引的高度，這樣就極大的減少了被驅(qū)動(dòng)表的匹配次數(shù)，極大的提升了join的性能。

如果join關(guān)聯(lián)查詢能使用到索引的話，MySQL就會(huì)使用Indexnestedloop算法，如果無(wú)法使用Indexnestedloop算法，MYSQL默認(rèn)會(huì)使用Blocknestedloop算法。

到底能不能使用join？

好了，我們剛才了解了Simple nested loop 、 Block nested loop、Index nested loop 這三種算法，那么現(xiàn)在可以回答開(kāi)頭的問(wèn)題了：到底能不能使用join？

其實(shí)，如果能用上被驅(qū)動(dòng)表上的索引，說(shuō)白了就是可以用上 Index nested loop 算法的話，是可以使用 join 的。

而如果使用的是 Block nested loop 算法的話，由于掃描行數(shù)和比較次數(shù)會(huì)比較多，所以會(huì)占用大量的系統(tǒng)資源，所以這種情況能不用join就不用join。

我們平常使用explain優(yōu)化sql的時(shí)候，如果 explain 結(jié)果中的 Extra 字段，如果包含 ' Using join buffer (Block Nested Loop) ' 的話，這個(gè)時(shí)候就代表使用了 Block nested loop 算法了。

如果能使用上被驅(qū)動(dòng)表上的索引的話，join還是可以使用的，這個(gè)時(shí)候基本不會(huì)影響性能，那么我們這里為什么要優(yōu)化掉join呢？

主要由于2個(gè)原因，首先后邊我們有分庫(kù)分表的計(jì)劃，所以為了有更好的擴(kuò)展性，我們會(huì)優(yōu)化掉join，其次MySQL是專門用來(lái)做數(shù)據(jù)存儲(chǔ)的，所以，還是盡量不要把業(yè)務(wù)相關(guān)的邏輯放到MySQL層面來(lái)做。

所以基于這2個(gè)原因，我們會(huì)將單體應(yīng)用版本的join給優(yōu)化掉。

join關(guān)聯(lián)查詢優(yōu)化實(shí)戰(zhàn)

被驅(qū)動(dòng)表order_no列未加索引

（1）join關(guān)聯(lián)查詢sql語(yǔ)句

可以看到，sql語(yǔ)句中，left join語(yǔ)句中，訂單明細(xì)表是通過(guò)order_no字段和訂單表關(guān)聯(lián)的，此時(shí)驅(qū)動(dòng)表order_info的order_no是加了索引的，而被驅(qū)動(dòng)表order_item_detail的order_no字段沒(méi)有添加索引

（2）看一下查詢時(shí)間

此時(shí)order_info中的數(shù)據(jù)量為2500萬(wàn)條，而訂單明細(xì)表 order_item_detail 的數(shù)據(jù)量是1億條。

可以看到被驅(qū)動(dòng)表order_item_detail沒(méi)使用到索引時(shí)，查詢效率是非常低下的。

優(yōu)化：被驅(qū)動(dòng)表order_no列添加索引

（1）為被驅(qū)動(dòng)表添加索引

現(xiàn)在我們?yōu)楸或?qū)動(dòng)表order_item_detail的order_no添加索引，添加索引sql如下：

create index inx_item_order_no
    on order_item_detail (order_no);

（2）再次查看join關(guān)聯(lián)查詢的時(shí)間

此時(shí)我們發(fā)現(xiàn)被驅(qū)動(dòng)表order_item_detail的關(guān)聯(lián)字段order_no用上索引后，查詢效率提升的非常明顯。

進(jìn)一步優(yōu)化：去掉join

此時(shí)我們?yōu)榱烁玫臄U(kuò)展性，需要將join關(guān)聯(lián)查詢給優(yōu)化掉

（1）看下join優(yōu)化后的代碼：

拆分join,改成單表查詢，內(nèi)存中再組裝數(shù)據(jù)。

（2）看一下優(yōu)化后的時(shí)間

可以看到，將join關(guān)聯(lián)查詢優(yōu)化掉之后，我們除了可以獲取到更大的擴(kuò)展性外，可以發(fā)現(xiàn)對(duì)查詢性能的提升也是非常大的。

被動(dòng)向主動(dòng)的轉(zhuǎn)變，監(jiān)控系統(tǒng)誕生

在sql優(yōu)化這個(gè)例子中，這個(gè)問(wèn)題是由DBA同學(xué)發(fā)現(xiàn)的，然后DBA同學(xué)將問(wèn)題反饋給了我們，實(shí)際在工作中呢，也可能是產(chǎn)品同學(xué)發(fā)現(xiàn)訂單信息查詢頁(yè)面有點(diǎn)慢，然后將問(wèn)題反饋給我們。

不管是誰(shuí)發(fā)現(xiàn)的，對(duì)于我們訂單系統(tǒng)的開(kāi)發(fā)人員來(lái)說(shuō)都是非常被動(dòng)的，因?yàn)槲覀儾荒芗皶r(shí)主動(dòng)的發(fā)現(xiàn)問(wèn)題，比如某一個(gè)接口變慢了，我們不能及時(shí)知道，只能等別人反饋給我們，這樣被動(dòng)的發(fā)現(xiàn)問(wèn)題，會(huì)在一定程度上擴(kuò)大問(wèn)題的影響。

為了解決這個(gè)問(wèn)題，我們建立了一套完善的監(jiān)控系統(tǒng)，這個(gè)監(jiān)控系統(tǒng)呢，可以添加很多監(jiān)控面板，比如我們可以添加訂單的監(jiān)控面板，訂單監(jiān)控面板中的核心指標(biāo)包含：訂單核心接口的請(qǐng)求次數(shù)、失敗次數(shù)、TP50、TP99等等。

然后，為了及時(shí)發(fā)現(xiàn)問(wèn)題，這個(gè)監(jiān)控系統(tǒng)還集成了報(bào)警的功能，說(shuō)白了就是針對(duì)某一個(gè)監(jiān)控指標(biāo)，我們會(huì)設(shè)置一個(gè)報(bào)警規(guī)則，比如每天的某一個(gè)時(shí)間段，在多少分鐘內(nèi)，失敗請(qǐng)求超過(guò)多少，那么就會(huì)報(bào)警給對(duì)應(yīng)的開(kāi)發(fā)人員，報(bào)警方式呢，會(huì)分為2種，分別是報(bào)警電話和消息推送（推送給公司內(nèi)部的辦公聊天軟件）

報(bào)警的時(shí)候?yàn)榱吮苊忾_(kāi)發(fā)人員的單點(diǎn)故障，報(bào)警接收人一般會(huì)添加多個(gè)，如果第一個(gè)人不接報(bào)警電話的話，那么就順延給第二個(gè)人打電話，這樣就可以最大程度的及時(shí)發(fā)現(xiàn)問(wèn)題了，就可以真正的由被動(dòng)轉(zhuǎn)為主動(dòng)了。