所謂慢SQL，就是執(zhí)行特別慢的SQL語(yǔ)句。什么樣的SQL語(yǔ)句才是慢SQL?多慢才算是慢SQL?對(duì)于這類問題，并沒有一個(gè)非常明確的標(biāo)準(zhǔn)，或者說是界限。但這并不代表區(qū)分正常的SQL和慢SQL很困難，在實(shí)際的大多數(shù)系統(tǒng)中，慢SQL消耗掉的數(shù)據(jù)庫(kù)資源，往往是正常SQL的幾倍、幾十倍，甚至幾百倍，所以慢SQL還是非常容易區(qū)分的。

問題是，我們不能等到系統(tǒng)上線，慢SQL消耗完數(shù)據(jù)庫(kù)的資源之后，再找出慢SQL來改進(jìn)，那樣將會(huì)造成很多不良影響。那么，怎樣才能在開發(fā)階段就盡量避免寫出慢SQL呢?

01定量認(rèn)識(shí)MySQL

這說明，慢SQL對(duì)數(shù)據(jù)庫(kù)的影響，是一個(gè)從量變到質(zhì)變的過程，對(duì)“量”的把握，對(duì)于開發(fā)人員來說是很重要的。一個(gè)合格的程序員，需要對(duì)數(shù)據(jù)庫(kù)的能力有一個(gè)定量的認(rèn)識(shí)。

影響MySQL處理能力的因素有很多，比如，服務(wù)器的配置、數(shù)據(jù)庫(kù)中數(shù)據(jù)量的大小、MySQL的一些參數(shù)配置、數(shù)據(jù)庫(kù)的繁忙程度，等等。但是，通常情況下，這些因素對(duì)于MySQL的性能和處理能力的影響，大概在一個(gè)數(shù)量級(jí)的范圍內(nèi)，也就是上下幾倍的性能差距。所以，我們不需要知道精確的性能數(shù)據(jù)，只要掌握一個(gè)大致的量級(jí)，就足夠應(yīng)對(duì)實(shí)際的開發(fā)工作了。

目前，一臺(tái)普通的MySQL數(shù)據(jù)庫(kù)服務(wù)器，處理能力的極限大致是，每秒一萬(wàn)條左右的簡(jiǎn)單SQL。這里的“簡(jiǎn)單SQL”，指的是類似于主鍵查詢這種不需要遍歷很多條記錄的SQL語(yǔ)句。根據(jù)配置的高低，服務(wù)器的處理能力也會(huì)有所不同，可能低配的服務(wù)器只能達(dá)到每秒幾千條，高配的服務(wù)器則可以達(dá)到每秒幾萬(wàn)條，所以這里給出的每秒一萬(wàn)條是中位數(shù)的經(jīng)驗(yàn)值?？紤]到正常的系統(tǒng)不可能只有簡(jiǎn)單SQL，所以實(shí)際的處理能力還要打很大折扣。

我個(gè)人的經(jīng)驗(yàn)是，一臺(tái)MySQL數(shù)據(jù)庫(kù)服務(wù)器，平均每秒執(zhí)行的SQL數(shù)量在幾百左右，一般就已經(jīng)是非常繁忙了。即使看起來CPU利用率和磁盤繁忙程度并不高，我們也需要考慮為數(shù)據(jù)庫(kù)“減負(fù)”了。

另外一個(gè)重要的定量指標(biāo)是，多慢的SQL才算是慢SQL?這里的“慢”，衡量的單位本來是執(zhí)行時(shí)長(zhǎng)，但是對(duì)于時(shí)長(zhǎng)這個(gè)指標(biāo)，我們?cè)诰帉慡QL的時(shí)候并不好衡量。因此可以用執(zhí)行SQL查詢時(shí)，需要遍歷的數(shù)據(jù)行數(shù)來替代時(shí)間作為衡量標(biāo)準(zhǔn)，因?yàn)椴樵兊膱?zhí)行時(shí)長(zhǎng)與遍歷的數(shù)據(jù)行數(shù)基本上是正相關(guān)的。

我們?cè)诰帉懸粭l查詢語(yǔ)句的時(shí)候，可以依據(jù)所要查詢數(shù)據(jù)表的數(shù)據(jù)總量估算一下這條查詢大致需要遍歷多少行數(shù)據(jù)。如果遍歷的行數(shù)在百萬(wàn)以內(nèi)，只要不是每秒都要執(zhí)行幾十上百次的查詢，就可以認(rèn)為該查詢是安全的。遍歷數(shù)據(jù)行數(shù)達(dá)到幾百萬(wàn)量級(jí)的，查詢最快也要花費(fèi)幾秒的時(shí)間，這時(shí)我們就要仔細(xì)考慮有沒有優(yōu)化的辦法。遍歷行數(shù)達(dá)到千萬(wàn)量級(jí)或以上的，這種SQL就不應(yīng)該出現(xiàn)在系統(tǒng)中了。當(dāng)然，我們這里討論的都是在線交易系統(tǒng)，離線分析類系統(tǒng)另當(dāng)別論。

遍歷行數(shù)達(dá)到千萬(wàn)量級(jí)的SQL，是MySQL查詢的一個(gè)坎兒。在MySQL中，單個(gè)表的數(shù)據(jù)量，也要盡量控制在一千萬(wàn)條以下，最多不要超過兩三千萬(wàn)這個(gè)量級(jí)。原因很簡(jiǎn)單，對(duì)一個(gè)千萬(wàn)量級(jí)的表執(zhí)行查詢，加上幾個(gè)WHERE條件過濾一下，符合條件的數(shù)據(jù)最多可能是幾十萬(wàn)或百萬(wàn)量級(jí)的，還是可以接受的。但如果再與其他的表做一個(gè)聯(lián)合查詢，遍歷的數(shù)據(jù)量很可能就會(huì)超過千萬(wàn)量級(jí)了。所以，每個(gè)表的數(shù)據(jù)量最好控制在千萬(wàn)量級(jí)以內(nèi)。

如果數(shù)據(jù)庫(kù)中的數(shù)據(jù)量本身就很多，而且查詢業(yè)務(wù)邏輯確實(shí)需要遍歷大量數(shù)據(jù)，應(yīng)該怎么辦呢?

02使用索引避免全表掃描

使用索引，可以有效減少執(zhí)行查詢時(shí)遍歷數(shù)據(jù)的行數(shù)，從而提高查詢的性能。

數(shù)據(jù)庫(kù)索引的原理比較簡(jiǎn)單，一個(gè)例子就能說明白。比如，有一個(gè)無序的數(shù)組，數(shù)組中的每個(gè)元素都是一個(gè)用戶對(duì)象。如果我們要把所有姓李的用戶都找出來，那么比較笨的辦法是，用一個(gè)循環(huán)把數(shù)組遍歷一遍。

是否還有更好的辦法呢?答案是肯定的。比如，我們可以用一個(gè)Map(在某些編程語(yǔ)言中是Dictionary)來為數(shù)組做一個(gè)索引，Key用于保存姓氏，值是所有這個(gè)姓氏的用戶對(duì)象在數(shù)組中序號(hào)的集合，如圖1所示。這樣在查找的時(shí)候，就不用遍歷數(shù)組了，只需要先在Map中查找，然后再根據(jù)序號(hào)直接去數(shù)組中獲取用戶數(shù)據(jù)即可，這樣查找速度就快多了。

圖1　基于Map構(gòu)建的內(nèi)存索引

下面我們把這個(gè)例子對(duì)應(yīng)到數(shù)據(jù)庫(kù)中，存放用戶數(shù)據(jù)的數(shù)組就是表，我們構(gòu)建的Map就是索引。實(shí)際上，數(shù)據(jù)庫(kù)索引的數(shù)據(jù)結(jié)構(gòu)與編程語(yǔ)言中的Map或Dictionary的結(jié)構(gòu)差不多，基本上都是各種B樹和哈希表。

絕大多數(shù)情況下，我們編寫的查詢語(yǔ)句，都應(yīng)該使用索引，以避免遍歷整張表，也就是通常所說的，避免全表掃描。在開發(fā)新功能時(shí)，每當(dāng)需要為數(shù)據(jù)庫(kù)增加一個(gè)新的查詢時(shí)，我們都要事先評(píng)估一下，是否可以由索引支撐新的查詢語(yǔ)句，如果有必要，則需要新建索引，以支持新增的查詢。

但是，增加索引需要付出的代價(jià)是，會(huì)降低數(shù)據(jù)插入、刪除和更新的性能。這一點(diǎn)也很好理解，增加了索引之后，當(dāng)數(shù)據(jù)發(fā)生變化的時(shí)候，不僅要變更數(shù)據(jù)表里的數(shù)據(jù)，還要變更各個(gè)索引。所以，對(duì)于更新頻繁并且對(duì)更新性能要求較高的表，可以盡量少建索引。而對(duì)于查詢較多、更新較少的表，可以根據(jù)查詢的業(yè)務(wù)邏輯，適當(dāng)多建一些索引。

那么，如何寫SQL才能更好地利用索引，使查詢效率更高呢?這是一門技藝，需要有豐富的經(jīng)驗(yàn)，不是學(xué)習(xí)完本文的內(nèi)容就能練成的(推薦閱讀《電商存儲(chǔ)系統(tǒng)實(shí)戰(zhàn)：架構(gòu)設(shè)計(jì)與海量數(shù)據(jù)處理》)。但是，對(duì)于SQL的查詢性能，我們還是有方法評(píng)估其是否為一個(gè)潛在的“慢SQL”的。

對(duì)于邏輯不是很復(fù)雜的單表查詢，我們可能還可以分析出查詢會(huì)使用哪個(gè)索引。但如果是比較復(fù)雜的多表聯(lián)合查詢，單看SQL語(yǔ)句本身，我們將很難分析出查詢到底會(huì)使用哪些索引，會(huì)遍歷多少行數(shù)據(jù)。MySQL和大部分?jǐn)?shù)據(jù)庫(kù)都提供了一個(gè)可用于分析查詢的功能，即執(zhí)行計(jì)劃。

03分析SQL執(zhí)行計(jì)劃

在MySQL中使用執(zhí)行計(jì)劃非常簡(jiǎn)單，只要在SQL語(yǔ)句前面加上EXPLAIN關(guān)鍵字，然后執(zhí)行這個(gè)查詢語(yǔ)句就可以了。

下面就來舉例說明，比如，有這樣一個(gè)用戶表，包含用戶ID、姓名、部門編號(hào)和狀態(tài)這幾個(gè)字段，如圖2所示。

圖2　用戶表示例

我們希望查詢某個(gè)二級(jí)部門下的所有人，查詢條件是，部門代號(hào)以00028開頭的所有人。下面這兩個(gè)SQL語(yǔ)句的查詢結(jié)果是一樣的，都滿足要求。那么，哪個(gè)查詢語(yǔ)句的性能更好呢?

1SELECT * FROM user WHERE left(department_code, 5) = '00028';
2
3SELECT * FROM user WHERE department_code LIKE '00028%';

我們分別查看一下這兩個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃，如圖3所示。

圖3　兩個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃

下面就來分析一下這兩個(gè)SQL語(yǔ)句的執(zhí)行計(jì)劃。首先來看rows這一列，rows列的含義是，MySQL預(yù)估執(zhí)行這個(gè)SQL可能會(huì)遍歷的數(shù)據(jù)行數(shù)。第一個(gè)SQL遍歷了4534行，即整個(gè)User表的數(shù)據(jù)條數(shù);第二個(gè)SQL只有8行，這8行其實(shí)就是符合條件的8條記錄。顯然，第二個(gè)SQL的查詢性能要遠(yuǎn)高于第一個(gè)SQL。

為什么第一個(gè)SQL需要全表掃描，而第二個(gè)SQL只需要遍歷很少的行數(shù)呢?注意看type這一列，type列表示這個(gè)查詢的訪問類型。ALL代表全表掃描，這是性能最差的情況。range代表使用了索引，表示只在索引中進(jìn)行范圍查找，這是因?yàn)镾QL語(yǔ)句的WHERE條件中有一個(gè)LIKE的查詢限制。如果直接使用了索引，則type列顯示的是index，并且可以在key列中看到實(shí)際上使用的是哪個(gè)索引。

通過對(duì)比這兩個(gè)SQL的執(zhí)行計(jì)劃，我們可以看到，第二個(gè)SQL雖然使用了公認(rèn)為低效的LIKE查詢條件，但是由于用到了索引的范圍查找，因此遍歷數(shù)據(jù)的行數(shù)遠(yuǎn)遠(yuǎn)少于第一個(gè)SQL，查詢性能更好。

04小結(jié)

在開發(fā)階段，衡量一個(gè)SQL語(yǔ)句查詢性能的手段是，預(yù)估執(zhí)行SQL時(shí)需要遍歷的數(shù)據(jù)行數(shù)。如果遍歷行數(shù)在百萬(wàn)量級(jí)以內(nèi)，則可以認(rèn)為是安全的SQL;百萬(wàn)到千萬(wàn)這個(gè)量級(jí)，則需要仔細(xì)評(píng)估和優(yōu)化;千萬(wàn)量級(jí)以上則是非常危險(xiǎn)的。為了降低寫出慢SQL的可能性，每個(gè)數(shù)據(jù)表的行數(shù)最好控制在千萬(wàn)量級(jí)以內(nèi)。

索引可以顯著減少查詢遍歷數(shù)據(jù)的數(shù)量，所以提升SQL查詢性能最有效的方式是，讓查詢盡可能多地使用索引。但是，索引也是一把雙刃劍，其在提升查詢性能的同時(shí)，也會(huì)降低數(shù)據(jù)更新的性能。

對(duì)于復(fù)雜的查詢，最好使用SQL執(zhí)行計(jì)劃，事先對(duì)查詢做一個(gè)分析。從SQL執(zhí)行計(jì)劃的結(jié)果中，我們可以看到查詢預(yù)估的遍歷行數(shù)，以及其會(huì)使用哪些索引。執(zhí)行計(jì)劃也可以很好地幫助大家對(duì)查詢語(yǔ)句進(jìn)行優(yōu)化。

關(guān)于作者：李玥，美團(tuán)基礎(chǔ)技術(shù)部高級(jí)技術(shù)專家，極客時(shí)間《后端存儲(chǔ)實(shí)戰(zhàn)課》《消息隊(duì)列高手課》等專欄作者。曾在當(dāng)當(dāng)網(wǎng)、京東零售等公司任職。從事互聯(lián)網(wǎng)電商行業(yè)基礎(chǔ)架構(gòu)領(lǐng)域的架構(gòu)設(shè)計(jì)和研發(fā)工作多年，曾多次參與雙十一和618電商大促。專注于分布式存儲(chǔ)、云原生架構(gòu)下的服務(wù)治理、分布式消息和實(shí)時(shí)計(jì)算等技術(shù)領(lǐng)域，致力于推進(jìn)基礎(chǔ)架構(gòu)技術(shù)的創(chuàng)新與開源。

本文摘編自《電商存儲(chǔ)系統(tǒng)實(shí)戰(zhàn)：架構(gòu)設(shè)計(jì)與海量數(shù)據(jù)處理》，經(jīng)出版方授權(quán)發(fā)布。(ISBN：9787111697411)轉(zhuǎn)載請(qǐng)保留文章出處。