前言

??上一期??，我們講解了sql優(yōu)化的一般流程，不管是優(yōu)化join語(yǔ)句、where語(yǔ)句、聚合函數(shù)還是排序操作，核心在于利用索引來(lái)優(yōu)化sql語(yǔ)句，但是，大家以為我們?yōu)樽侄蝿?chuàng)建了索引之后，索引就一定會(huì)生效嗎？

當(dāng)然不是的，因?yàn)樗饕赡軙?huì)失效。

那索引為什么會(huì)失效呢？失效之后會(huì)導(dǎo)致什么樣的后果呢？這一節(jié)，我們利用當(dāng)下的問(wèn)題，也就是千萬(wàn)級(jí)的訂單表查詢(xún)居然需要耗費(fèi)3s的時(shí)間，通過(guò)這個(gè)問(wèn)題我們來(lái)一探究竟。

目前為止，我們已經(jīng)初步確定問(wèn)題原因，說(shuō)白了就是sql沒(méi)有正常使用到索引，因?yàn)閱伪砬f(wàn)級(jí)的數(shù)據(jù)，B+樹(shù)基本也就是三到四層，那么如果正常使用到索引的話，幾十毫秒sql就執(zhí)行完畢了。

所以這條sql，肯定是沒(méi)有使用到索引，說(shuō)白了就是索引失效了，此時(shí)就會(huì)發(fā)生大量的磁盤(pán)IO，最終就會(huì)導(dǎo)致sql查詢(xún)時(shí)間達(dá)到了3s。

索引失效會(huì)導(dǎo)致什么后果？

首先我們先來(lái)看下，索引失效的話，會(huì)導(dǎo)致什么后果呢？我們用之前文章出現(xiàn)過(guò)的圖來(lái)舉例。

我們可以看到，一個(gè)數(shù)據(jù)表中的數(shù)據(jù)，是通過(guò)多個(gè)數(shù)據(jù)頁(yè)的方式存儲(chǔ)起來(lái)的，并且數(shù)據(jù)頁(yè)之間是通過(guò)雙向鏈表的方式連接起來(lái)的 。

就以訂單表舉例，如果訂單表中的數(shù)據(jù)達(dá)到上千萬(wàn)數(shù)據(jù)的級(jí)別了，這個(gè)時(shí)候，存放訂單數(shù)據(jù)的數(shù)據(jù)頁(yè)數(shù)量，就不是100個(gè)數(shù)據(jù)頁(yè)這么少了，可能會(huì)有幾萬(wàn)甚至幾十萬(wàn)個(gè)數(shù)據(jù)頁(yè)。

如果我們不用上索引的話，就意味著我們要面臨加載幾萬(wàn)甚至幾十萬(wàn)個(gè)數(shù)據(jù)頁(yè)的風(fēng)險(xiǎn)，這個(gè)過(guò)程同時(shí)會(huì)導(dǎo)致大量的磁盤(pán)IO，是非常耗費(fèi)性能，影響我們查詢(xún)的效率的。

所以，我們也可以知道，為什么隨著表中的數(shù)據(jù)量越來(lái)越大，就會(huì)導(dǎo)致查詢(xún)的速度會(huì)越來(lái)越慢了。

而索引在這個(gè)時(shí)候，就變得越來(lái)越重要了，關(guān)鍵在于，我們?cè)鯓觾?yōu)化我們的sql語(yǔ)句，讓sql語(yǔ)句查詢(xún)數(shù)據(jù)的時(shí)候，盡量利用索引來(lái)查詢(xún)數(shù)據(jù)。

sql優(yōu)化案例實(shí)戰(zhàn)

體驗(yàn)下無(wú)索引的查詢(xún)效率

在進(jìn)行sql優(yōu)化之前，我們先來(lái)體驗(yàn)下沒(méi)有索引時(shí)，我們sql的一個(gè)查詢(xún)效率。

（1）無(wú)索引的查詢(xún)效率

當(dāng)前表的數(shù)據(jù)量為2500W，查詢(xún)時(shí)間已經(jīng)消耗了65秒。

（2）無(wú)索引的執(zhí)行計(jì)劃

通過(guò)explain分析該查詢(xún)sql的執(zhí)行計(jì)劃，我們可以看到這條sql進(jìn)行了全表掃描。

(3) sql執(zhí)行慢會(huì)發(fā)生什么連鎖反應(yīng)？

模擬并發(fā)請(qǐng)求時(shí)，會(huì)發(fā)現(xiàn)由于sql查詢(xún)時(shí)間過(guò)長(zhǎng)，導(dǎo)致數(shù)據(jù)庫(kù)連接數(shù)快速被消耗完，最終導(dǎo)致后面的sql再執(zhí)行的時(shí)候就被拒絕連接了。

體驗(yàn)下有索引的查詢(xún)效率

（1）創(chuàng)建索引

我們可以給order_no字段加上索引，如下圖：

（2）有索引的查詢(xún)效率

為order_no字段添加上索引后再試試查詢(xún)，看看效率有多大的提高。

我們可以發(fā)現(xiàn)同樣的sql在無(wú)索引和有索引的情況，查詢(xún)效率差距是非常大的。所以在遇到大表查詢(xún)慢的場(chǎng)景不妨先查看一下查詢(xún)字段是否有添加上合適的索引。

（3）有索引的執(zhí)行計(jì)劃

通過(guò)執(zhí)行計(jì)劃可以看到加索引后，只掃描了一行就找到了需要查詢(xún)的數(shù)據(jù)。

sql優(yōu)化案例：隱式轉(zhuǎn)換導(dǎo)致索引失效

（1）正常使用到索引的情況

在項(xiàng)目中訂單號(hào)的類(lèi)型為String,當(dāng)我們SQL語(yǔ)句編寫(xiě)正確的情況，查詢(xún)效率是很快的。

(2) 隱式轉(zhuǎn)換導(dǎo)致索引失效

當(dāng)我們錯(cuò)誤的使用數(shù)值類(lèi)型的訂單號(hào)去進(jìn)行查詢(xún)時(shí)，我們看一下查詢(xún)時(shí)間。

通過(guò)圖中的執(zhí)行情況，執(zhí)行效率確是天差地別的，查詢(xún)時(shí)間竟然需要整整24秒！

（3）通過(guò)explain查看索引失效的執(zhí)行計(jì)劃

通過(guò)explain分析這條SQL的執(zhí)行計(jì)劃，我們會(huì)發(fā)現(xiàn)雖然order_no字段上我們?cè)O(shè)置了索引，但這條查詢(xún)依然進(jìn)行了全表掃描，說(shuō)白了就是根本沒(méi)有用到索引，因此查詢(xún)效率才會(huì)大減。

結(jié)束語(yǔ)

最后，簡(jiǎn)單做一個(gè)說(shuō)明，那就是實(shí)際的sql優(yōu)化是比較復(fù)雜的，可能還會(huì)涉及到鎖、內(nèi)存和網(wǎng)絡(luò)，我們這里只是列舉了sql優(yōu)化中需要注意的2個(gè)點(diǎn)而已，而這2個(gè)點(diǎn)只是sql優(yōu)化的一小部分。之所以提出來(lái)這2個(gè)點(diǎn)，主要是為了達(dá)到拋磚引玉的效果，就是遇到問(wèn)題時(shí)，大家首先要聚焦在sql優(yōu)化這里，而不是說(shuō)先考慮一些高大上的解決方案。