數(shù)據(jù)庫(kù)大量應(yīng)用程序開(kāi)發(fā)項(xiàng)目中，大多數(shù)情況下，數(shù)據(jù)庫(kù)的操作性能成為整個(gè)應(yīng)用的性能瓶頸。數(shù)據(jù)庫(kù)的性能是程序員需要去關(guān)注的事情，當(dāng)設(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)以及操作數(shù)據(jù)庫(kù)(尤其是查詢(xún)數(shù)據(jù)時(shí))，都需要注意數(shù)據(jù)操作的性能。本文我們以MySQL數(shù)據(jù)庫(kù)為例進(jìn)行討論。

一、數(shù)據(jù)庫(kù)優(yōu)化目標(biāo)

1. 減少 IO 次數(shù)

IO永遠(yuǎn)是數(shù)據(jù)庫(kù)最容易瓶頸的地方，這是由數(shù)據(jù)庫(kù)的職責(zé)所決定的，大部分?jǐn)?shù)據(jù)庫(kù)操作中超過(guò)90%的時(shí)間都是 IO 操作所占用的，減少 IO 次數(shù)是 SQL 優(yōu)化中需要第一優(yōu)先考慮，當(dāng)然，也是收效最明顯的優(yōu)化手段。

2. 降低 CPU 計(jì)算

除了 IO 瓶頸之外，SQL優(yōu)化中需要考慮的就是 CPU 運(yùn)算量的優(yōu)化了。order by,group by,distinct … 都是消耗 CPU 的大戶(hù)(這些操作基本上都是 CPU 處理內(nèi)存中的數(shù)據(jù)比較運(yùn)算)。當(dāng)我們的 IO 優(yōu)化做到一定階段之后，降低 CPU計(jì)算也就成為了我們 SQL 優(yōu)化的重要目標(biāo)。

MySql查詢(xún)過(guò)程

二、數(shù)據(jù)庫(kù)優(yōu)化方法

1. SQL語(yǔ)句優(yōu)化

明確了優(yōu)化目標(biāo)之后，我們需要確定達(dá)到我們目標(biāo)的方法。對(duì)于SQL語(yǔ)句來(lái)說(shuō)，達(dá)到上述2個(gè)優(yōu)化目標(biāo)的方法其實(shí)只有一個(gè)，那就是改變SQL的執(zhí)行計(jì)劃，讓他盡量“少走彎路”，盡量通過(guò)各種“捷徑”來(lái)找到我們需要的數(shù)據(jù)，以達(dá)到“減少I(mǎi)O次數(shù)”和“降低CPU計(jì)算”的目標(biāo)。

(1) 盡量少 join。MySQL 的優(yōu)勢(shì)在于簡(jiǎn)單，但這在某些方面其實(shí)也是其劣勢(shì)。MySQL優(yōu)化器效率高，但是由于其統(tǒng)計(jì)信息的量有限，優(yōu)化器工作過(guò)程出現(xiàn)偏差的可能性也就更多。對(duì)于復(fù)雜的多表 Join，一方面由于其優(yōu)化器受限，再者在Join這方面所下的功夫還不夠，所以性能表現(xiàn)離Oracle等關(guān)系型數(shù)據(jù)庫(kù)前輩還是有一定距離。但如果是簡(jiǎn)單的單表查詢(xún)，這一差距就會(huì)極小甚至在有些場(chǎng)景下要優(yōu)于這些數(shù)據(jù)庫(kù)前輩。

(2) 盡量少排序

(3) 排序操作會(huì)消耗較多的 CPU 資源，所以減少排序可以在緩存命中率高等 IO 能力足夠的場(chǎng)景下會(huì)較大影響 SQL的響應(yīng)時(shí)間。

(4) 盡量避免 select *，并盡量用join代替子查詢(xún)

(5) 盡量少使用“or”關(guān)鍵字

當(dāng) where 子句中存在多個(gè)條件以“或”并存的時(shí)候，MySQL 的優(yōu)化器并沒(méi)有很好的解決其執(zhí)行計(jì)劃優(yōu)化問(wèn)題，再加上 MySQL 特有的 SQL 與 Storage 分層架構(gòu)方式，造成了其性能比較低下，很多時(shí)候使用 union all 或者是union(必要的時(shí)候)的方式來(lái)代替“or”會(huì)得到更好的效果。

(6) 盡量用 union all 代替 union

union 和 union all 的差異主要是前者需要將兩個(gè)(或者多個(gè))結(jié)果集合并后再進(jìn)行唯一性過(guò)濾操作，這就會(huì)涉及到排序，增加大量的 CPU 運(yùn)算，加大資源消耗及延遲。所以當(dāng)我們可以確認(rèn)不可能出現(xiàn)重復(fù)結(jié)果集或者不在乎重復(fù)結(jié)果集的時(shí)候，盡量使用 union all 而不是 union。

(7) 避免類(lèi)型轉(zhuǎn)換

(8) 能用DISTINCT的就不用GROUP BY

(9) 盡量不要用SELECT INTO語(yǔ)句 ?

(10) 從全局出發(fā)優(yōu)化，而不是片面調(diào)整

SQL 優(yōu)化不能是單獨(dú)針對(duì)某一個(gè)進(jìn)行，而應(yīng)充分考慮系統(tǒng)中所有的 SQL，尤其是在通過(guò)調(diào)整索引優(yōu)化 SQL的執(zhí)行計(jì)劃的時(shí)候，千萬(wàn)不能顧此失彼，因小失大。

2. 表結(jié)構(gòu)優(yōu)化

MySQL數(shù)據(jù)庫(kù)是基于行(Row)存儲(chǔ)的數(shù)據(jù)庫(kù)，而數(shù)據(jù)庫(kù)操作 IO 的時(shí)候是以 page(block)的方式，也就是說(shuō)，如果我們每條記錄所占用的空間量減小，就會(huì)使每個(gè)page中可存放的數(shù)據(jù)行數(shù)增大，那么每次 IO 可訪(fǎng)問(wèn)的行數(shù)也就增多了。反過(guò)來(lái)說(shuō)，處理相同行數(shù)的數(shù)據(jù)，需要訪(fǎng)問(wèn)的 page 就會(huì)減少，也就是 IO 操作次數(shù)降低，直接提升性能。

(1) 數(shù)據(jù)類(lèi)型選擇

原則是：數(shù)據(jù)行的長(zhǎng)度不要超過(guò)8020字節(jié)，如果超過(guò)這個(gè)長(zhǎng)度的話(huà)在物理頁(yè)中這條數(shù)據(jù)會(huì)占用兩行從而造成存儲(chǔ)碎片，降低查詢(xún)效率;字段的長(zhǎng)度在最大限度的滿(mǎn)足可能的需要的前提下，應(yīng)該盡可能的設(shè)得短一些，這樣可以提高查詢(xún)的效率，而且在建立索引的時(shí)候也可以減少資源的消耗。

• 數(shù)字類(lèi)型：非萬(wàn)不得已不要使用DOUBLE，不僅僅只是存儲(chǔ)長(zhǎng)度的問(wèn)題，同時(shí)還會(huì)存在精確性的問(wèn)題。同樣，固定精度的小數(shù)，也不建議使用DECIMAL，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲(chǔ)，可以大大節(jié)省存儲(chǔ)空間，且不會(huì)帶來(lái)任何附加維護(hù)成本。
• 字符類(lèi)型：定長(zhǎng)字段，建議使用 CHAR 類(lèi)型(char查詢(xún)快，但是耗存儲(chǔ)空間，可用于用戶(hù)名、密碼等長(zhǎng)度變化不大的字段)，不定長(zhǎng)字段盡量使用VARCHAR(varchar查詢(xún)相對(duì)慢一些但是節(jié)省存儲(chǔ)空間，可用于評(píng)論等長(zhǎng)度變化大的字段)，且僅僅設(shè)定適當(dāng)?shù)淖畲箝L(zhǎng)度，而不是非常隨意的給一個(gè)很大的最大長(zhǎng)度限定，因?yàn)椴煌拈L(zhǎng)度范圍，MySQL也會(huì)有不一樣的存儲(chǔ)處理。
• 時(shí)間類(lèi)型：盡量使用TIMESTAMP類(lèi)型，因?yàn)槠浯鎯?chǔ)空間只需要DATETIME 類(lèi)型的一半。對(duì)于只需要精確到某一天的數(shù)據(jù)類(lèi)型，建議使用DATE類(lèi)型，因?yàn)樗拇鎯?chǔ)空間只需要3個(gè)字節(jié)，比TIMESTAMP還少。不建議通過(guò)INT類(lèi)型類(lèi)存儲(chǔ)一個(gè)unix timestamp 的值，因?yàn)檫@太不直觀，會(huì)給維護(hù)帶來(lái)不必要的麻煩，同時(shí)還不會(huì)帶來(lái)任何好處。
• ENUM &SET：對(duì)于狀態(tài)字段，可以嘗試使用 ENUM 來(lái)存放，因?yàn)榭梢詷O大的降低存儲(chǔ)空間，而且即使需要增加新的類(lèi)型，只要增加于末尾，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)。

(2) 字符編碼

字符集直接決定了數(shù)據(jù)在MySQL中的存儲(chǔ)編碼方式，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會(huì)有較大的差異，所以通過(guò)使用合適的字符集，可以幫助我們盡可能減少數(shù)據(jù)量，進(jìn)而減少I(mǎi)O操作次數(shù)。

(3) 盡量使用 NOT NULL

NULL 類(lèi)型比較特殊，SQL 難優(yōu)化。雖然 MySQL NULL類(lèi)型和 Oracle 的NULL有差異，會(huì)進(jìn)入索引中，但如果是一個(gè)組合索引，那么這個(gè)NULL 類(lèi)型的字段會(huì)極大影響整個(gè)索引的效率。雖然 NULL空間上可能確實(shí)有一定節(jié)省，倒是帶來(lái)了很多其他的優(yōu)化問(wèn)題，不但沒(méi)有將IO量省下來(lái)，反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL，也是一個(gè)很好的表結(jié)構(gòu)設(shè)計(jì)優(yōu)化習(xí)慣。

3. 數(shù)據(jù)庫(kù)架構(gòu)優(yōu)化

分布式和集群化：

• 負(fù)載均衡。負(fù)載均衡集群是由一組相互獨(dú)立的計(jì)算機(jī)系統(tǒng)構(gòu)成，通過(guò)常規(guī)網(wǎng)絡(luò)或?qū)Ｓ镁W(wǎng)絡(luò)進(jìn)行連接，由路由器銜接在一起，各節(jié)點(diǎn)相互協(xié)作、共同負(fù)載、均衡壓力，對(duì)客戶(hù)端來(lái)說(shuō)，整個(gè)群集可以視為一臺(tái)具有超高性能的獨(dú)立服務(wù)器。MySQL一般部署的是高可用性負(fù)載均衡集群，具備讀寫(xiě)分離，一般只對(duì)讀進(jìn)行負(fù)載均衡。
• 讀寫(xiě)分離。讀寫(xiě)分離簡(jiǎn)單的說(shuō)是把對(duì)數(shù)據(jù)庫(kù)讀和寫(xiě)的操作分開(kāi)對(duì)應(yīng)不同的數(shù)據(jù)庫(kù)服務(wù)器，這樣能有效地減輕數(shù)據(jù)庫(kù)壓力，也能減輕io壓力。主數(shù)據(jù)庫(kù)提供寫(xiě)操作，從數(shù)據(jù)庫(kù)提供讀操作，其實(shí)在很多系統(tǒng)中，主要是讀的操作。當(dāng)主數(shù)據(jù)庫(kù)進(jìn)行寫(xiě)操作時(shí)，數(shù)據(jù)要同步到從的數(shù)據(jù)庫(kù)，這樣才能有效保證數(shù)據(jù)庫(kù)完整性。
• 數(shù)據(jù)切分。通過(guò)某種特定的條件，將存放在同一個(gè)數(shù)據(jù)庫(kù)中的數(shù)據(jù)分散存放到多個(gè)數(shù)據(jù)庫(kù)上，實(shí)現(xiàn)分布存儲(chǔ)，通過(guò)路由規(guī)則路由訪(fǎng)問(wèn)特定的數(shù)據(jù)庫(kù)，這樣一來(lái)每次訪(fǎng)問(wèn)面對(duì)的就不是單臺(tái)服務(wù)器了，而是N臺(tái)服務(wù)器，這樣就可以降低單臺(tái)機(jī)器的負(fù)載壓力。

4. 其他優(yōu)化

(1) 適當(dāng)使用視圖加速查詢(xún)。

把表的一個(gè)子集進(jìn)行排序并創(chuàng)建視圖，有時(shí)能加速查詢(xún)(特別是要被多次執(zhí)行的查詢(xún))。它有助于避免多重排序操作，而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作。視圖中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤(pán)I/O，所以查詢(xún)工作量可以得到大幅減少。

(2) 算法優(yōu)化。

盡量避免使用游標(biāo)，因?yàn)橛螛?biāo)的效率較差，如果游標(biāo)操作的數(shù)據(jù)超過(guò)1萬(wàn)行，那么就應(yīng)該考慮改寫(xiě)。使用基于游標(biāo)的方法或臨時(shí)表方法之前，應(yīng)先尋找基于集的解決方案來(lái)解決問(wèn)題，基于集的方法通常更有效。與臨時(shí)表一樣，游標(biāo)并不是不可使用。對(duì)小型數(shù)據(jù)集使用 FAST_FORWARD 游標(biāo)通常要優(yōu)于其他逐行處理方法，尤其是在必須引用幾個(gè)表才能獲得所需的數(shù)據(jù)時(shí)。

(3)封裝存儲(chǔ)過(guò)程。

經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，運(yùn)行效率高，可以降低客戶(hù)機(jī)和服務(wù)器之間的通信量，有利于集中控制，易于維護(hù)。