MySQL單表查詢優(yōu)化技巧

我最近碰到了很多性能很糟糕的MySQL單表查詢。原因很簡(jiǎn)單：索引創(chuàng)建得不正確，導(dǎo)致執(zhí)行計(jì)劃的性能低下。下面是一些能幫助你優(yōu)化單表查詢性能的要點(diǎn)。

免責(zé)聲明：我會(huì)給出一些要點(diǎn)，但并不打算包含所有的可能情況。我100%相信你能夠找到我的要點(diǎn)不適應(yīng)的案例，但是我也相信大部分情況下，我寫(xiě)的這些要點(diǎn)會(huì)幫助到你。為了簡(jiǎn)單起見(jiàn)，我也不會(huì)討論一些MySQL 5.6+版本的一些新特性，如Index Condition Pushdown。注意這些新特性會(huì)對(duì)響應(yīng)時(shí)間有極大的影響（縮短或延長(zhǎng)均有可能）。

索引能做什么？

索引主要做3件事：過(guò)濾(filter)，排序或分組(sort/group)，覆蓋(cover)。前兩個(gè)沒(méi)什么好說(shuō)的，但并不是每個(gè)人都知道什么叫“覆蓋索引”。事實(shí)上這是個(gè)很簡(jiǎn)單的東西。

一個(gè)基本查詢的工作流如下：

1. 使用索引以查找匹配的記錄，并得到數(shù)據(jù)的指針。

2. 使用相關(guān)數(shù)據(jù)的指針。

3. 返回查詢到的記錄。

當(dāng)可以使用覆蓋索引時(shí)，索引將會(huì)覆蓋查詢中的所有字段，因此第二步將會(huì)被跳過(guò)，于是查詢流程就變成了下面這樣：

1. 使用索引以查找匹配的記錄

2. 返回查詢到的記錄。

大部分情況下，索引都比較小，可以加載在內(nèi)存中，而數(shù)據(jù)很大，無(wú)法全部存放在內(nèi)存里：當(dāng)使用覆蓋索引時(shí)，可以避免很多的磁盤(pán)操作，因此對(duì)性能也會(huì)有極大的改善。

下面讓我們來(lái)看一些常見(jiàn)的查詢案例。

單個(gè)等于查詢（Single equality）

這是最基本的情景：

SELECT * FROM t WHERE c = 100

毫無(wú)疑問(wèn)這種情況下，要給c字段創(chuàng)建索引。要注意的是，如果查詢條件不夠精確（if the criteria is not selective enough，這句話我不理解），優(yōu)化器很可能會(huì)選擇全表查詢，因?yàn)檫@樣有可能性能更好。

這種單個(gè)等于查詢也包括只查詢部分字段，而不是所有字段，如：

SELECT c1, c2 FROM t WHERE c = 100

這里應(yīng)該創(chuàng)建一個(gè)（c，c1，c2）的索引，因?yàn)檫@樣是覆蓋索引。注意不是創(chuàng)建（c1，c2，c）！這同樣也是覆蓋索引，但是對(duì)過(guò)濾沒(méi)什么幫助（記住MySQL索引的最左原則）。

多個(gè)等于查詢（Multiple equalities）

SELECT * FROM t WHERE c = 100 and d = 'xyz'

這種情況也很容易優(yōu)化：創(chuàng)建索引（c，d）或（d，c）。

最常見(jiàn)的錯(cuò)誤是建立兩個(gè)索引：一個(gè)是c，一個(gè)是d。盡管MySQL根據(jù)index_merge算法能同時(shí)使用這兩個(gè)索引，但這樣依然是糟糕的選擇。

等于與不等于并存的查詢（Equality and inequality）

SELECT * FROM t WHERE c > 100 and d = 'xyz'

這種情況我們必須要小心，因?yàn)橹灰幸涣惺褂昧瞬坏扔谟?jì)算，那么它將阻止其他列使用索引。

因此我們需要?jiǎng)?chuàng)建一個(gè)（d，c）的索引，這時(shí)候c和d兩個(gè)條件都會(huì)走索引，這也是我們想要的結(jié)果。

而如果我們創(chuàng)建的是（c，d）索引，則只有c列的索引會(huì)被利用，這樣效率會(huì)比較低。

因此，索引中字段的順序?qū)τ谶@種等于/不等于并存的查詢有極大的影響。

多個(gè)不等于查詢（Multiple inequalities）

SELECT * FROM t WHERE c > 100 and b < 10 and d = 'xyz'

這里有兩個(gè)不等于，前面已經(jīng)說(shuō)了不等于會(huì)終止索引查詢，因此我們不可能做到b、c、d都被索引覆蓋（注釋1）。因此我們必須要做出決定，到底是創(chuàng)建索引（d，b）還是索引（d，c）？

在不知道表里具體數(shù)據(jù)的情況下，創(chuàng)建上面任何一種都無(wú)所謂，最關(guān)鍵的是，一定要把等于條件（在這里是d）所在列，放在索引的最左側(cè)。

注釋1：事實(shí)上還是有一種“曲線救國(guó)”的方法，能同時(shí)滿足所有條件，即按照字段b分區(qū)(partition on b)，然后創(chuàng)建索引（d，c），或按照字段c分區(qū)(partition onc)，然后創(chuàng)建索引（d，b）。這個(gè)的細(xì)節(jié)已經(jīng)超出了本文的討論范圍，不過(guò)這也是這種情況下的一種解決方法。

多個(gè)等于與排序（Equalities and sort）

SELECT * FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

就像第一節(jié)中寫(xiě)的那樣，索引可以過(guò)濾、排序，因此這個(gè)查詢很容易優(yōu)化。不過(guò)和不等于類似，我們對(duì)于索引中字段的順序必須足夠小心：要求是先過(guò)濾后排序。

根據(jù)上面“先過(guò)濾后排序”的要求可知，（c，d，b）或（d，c，b）是不錯(cuò)的選擇；而（b，c，d）或（b，d，c）則比較糟糕，因?yàn)樗麄冎慌判颍贿^(guò)濾。

如果是下面這種情況：

SELECT c1, c2 FROM t WHERE c = 100 and d = 'xyz' ORDER BY b

我們可以創(chuàng)建一個(gè)集過(guò)濾、排序、覆蓋于一體的索引：（c，d，b，c1，c2）。

不等于與排序（Inequality and sort）

常見(jiàn)的情況有2種。下面是情況一（不等于、等于、排序都有）：

SELECT * FROM t WHERE c > 100 and d = 'xyz' ORDER BY b

這種情況有兩種思路：（d，b）或（d，c）。至于哪種效率更高，這取決于你的數(shù)據(jù)，需要具體情況具體分析。

情況二如下（只有不等于和排序）：

SELECT * FROM t WHERE c > 100 ORDER BY b

這種情況沒(méi)有等于條件，因此b和c只能選一種，具體選哪一種同樣和你的數(shù)據(jù)有關(guān)。通常情況下，選過(guò)濾的會(huì)多一些（即c字段）。

總結(jié)

本文并沒(méi)有包含所有的情況，但同樣指出了一些你必須要小心的地方。今后，我會(huì)列舉一個(gè)看起來(lái)十分復(fù)雜的例子，不過(guò)只要你把這篇文章看懂了，它其實(shí)很簡(jiǎn)單。

出處：blog.csdn.net/zhu19774279/article/details/46473981

關(guān)注GitHub今日熱榜，專注挖掘好用的開(kāi)發(fā)工具，致力于分享優(yōu)質(zhì)高效的工具、資源、插件等，助力開(kāi)發(fā)者成長(zhǎng)！

點(diǎn)個(gè)在看 你最好看