快捷導(dǎo)航

MySQL優(yōu)化之表結(jié)構(gòu)優(yōu)化的5大建議（數(shù)據(jù)類型選擇講的很好）

更新時(shí)間：2014年03月25日 10:33:01 作者：

很多人都將數(shù)據(jù)庫設(shè)計(jì)范式作為數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)“圣經(jīng)”，認(rèn)為只要按照這個(gè)范式需求設(shè)計(jì)，就能讓設(shè)計(jì)出來的表結(jié)構(gòu)足夠優(yōu)化，既能保證性能優(yōu)異同時(shí)還能滿足擴(kuò)展性要求

殊不知，在N年前被奉為“圣經(jīng)”的數(shù)據(jù)庫設(shè)計(jì)3范式早就已經(jīng)不完全適用了。這里我整理了一些比較常見的數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)方面的優(yōu)化技巧，希望對(duì)大家有用。

由于MySQL數(shù)據(jù)庫是基于行(Row)存儲(chǔ)的數(shù)據(jù)庫，而數(shù)據(jù)庫操作 IO 的時(shí)候是以 page(block)的方式，也就是說，如果我們每條記錄所占用的空間量減小，就會(huì)使每個(gè)page中可存放的數(shù)據(jù)行數(shù)增大，那么每次 IO 可訪問的行數(shù)也就增多了。反過來說，處理相同行數(shù)的數(shù)據(jù)，需要訪問的 page 就會(huì)減少，也就是 IO 操作次數(shù)降低，直接提升性能。此外，由于我們的內(nèi)存是有限的，增加每個(gè)page中存放的數(shù)據(jù)行數(shù)，就等于增加每個(gè)內(nèi)存塊的緩存數(shù)據(jù)量，同時(shí)還會(huì)提升內(nèi)存換中數(shù)據(jù)命中的幾率，也就是緩存命中率。

一、數(shù)據(jù)類型選擇
數(shù)據(jù)庫操作中最為耗時(shí)的操作就是 IO 處理，大部分?jǐn)?shù)據(jù)庫操作 90% 以上的時(shí)間都花在了 IO 讀寫上面。所以盡可能減少 IO 讀寫量，可以在很大程度上提高數(shù)據(jù)庫操作的性能。

我們無法改變數(shù)據(jù)庫中需要存儲(chǔ)的數(shù)據(jù)，但是我們可以在這些數(shù)據(jù)的存儲(chǔ)方式方面花一些心思。下面的這些關(guān)于字段類型的優(yōu)化建議主要適用于記錄條數(shù)較多，數(shù)據(jù)量較大的場(chǎng)景，因?yàn)榫?xì)化的數(shù)據(jù)類型設(shè)置可能帶來維護(hù)成本的提高，過度優(yōu)化也可能會(huì)帶來其他的問題：

1.數(shù)字類型：非萬不得已不要使用DOUBLE，不僅僅只是存儲(chǔ)長度的問題，同時(shí)還會(huì)存在精確性的問題。同樣，固定精度的小數(shù)，也不建議使用DECIMAL，建議乘以固定倍數(shù)轉(zhuǎn)換成整數(shù)存儲(chǔ)，可以大大節(jié)省存儲(chǔ)空間，且不會(huì)帶來任何附加維護(hù)成本。對(duì)于整數(shù)的存儲(chǔ)，在數(shù)據(jù)量較大的情況下，建議區(qū)分開 TINYINT / INT / BIGINT 的選擇，因?yàn)槿咚加玫拇鎯?chǔ)空間也有很大的差別，能確定不會(huì)使用負(fù)數(shù)的字段，建議添加unsigned定義。當(dāng)然，如果數(shù)據(jù)量較小的數(shù)據(jù)庫，也可以不用嚴(yán)格區(qū)分三個(gè)整數(shù)類型。
2.字符類型：非萬不得已不要使用 TEXT 數(shù)據(jù)類型，其處理方式?jīng)Q定了他的性能要低于char或者是varchar類型的處理。定長字段，建議使用 CHAR 類型，不定長字段盡量使用 VARCHAR，且僅僅設(shè)定適當(dāng)?shù)淖畲箝L度，而不是非常隨意的給一個(gè)很大的最大長度限定，因?yàn)椴煌拈L度范圍，MySQL也會(huì)有不一樣的存儲(chǔ)處理。
3.時(shí)間類型：盡量使用TIMESTAMP類型，因?yàn)槠浯鎯?chǔ)空間只需要 DATETIME 類型的一半。對(duì)于只需要精確到某一天的數(shù)據(jù)類型，建議使用DATE類型，因?yàn)樗拇鎯?chǔ)空間只需要3個(gè)字節(jié)，比TIMESTAMP還少。不建議通過INT類型類存儲(chǔ)一個(gè)unix timestamp 的值，因?yàn)檫@太不直觀，會(huì)給維護(hù)帶來不必要的麻煩，同時(shí)還不會(huì)帶來任何好處。
4.ENUM & SET：對(duì)于狀態(tài)字段，可以嘗試使用 ENUM 來存放，因?yàn)榭梢詷O大的降低存儲(chǔ)空間，而且即使需要增加新的類型，只要增加于末尾，修改結(jié)構(gòu)也不需要重建表數(shù)據(jù)。如果是存放可預(yù)先定義的屬性數(shù)據(jù)呢？可以嘗試使用SET類型，即使存在多種屬性，同樣可以游刃有余，同時(shí)還可以節(jié)省不小的存儲(chǔ)空間。
5.LOB類型：強(qiáng)烈反對(duì)在數(shù)據(jù)庫中存放 LOB 類型數(shù)據(jù)，雖然數(shù)據(jù)庫提供了這樣的功能，但這不是他所擅長的，我們更應(yīng)該讓合適的工具做他擅長的事情，才能將其發(fā)揮到極致。在數(shù)據(jù)庫中存儲(chǔ) LOB 數(shù)據(jù)就像讓一個(gè)多年前在學(xué)校學(xué)過一點(diǎn)Java的營銷專業(yè)人員來寫 Java 代碼一樣。
二、字符編碼
字符集直接決定了數(shù)據(jù)在MySQL中的存儲(chǔ)編碼方式，由于同樣的內(nèi)容使用不同字符集表示所占用的空間大小會(huì)有較大的差異，所以通過使用合適的字符集，可以幫助我們盡可能減少數(shù)據(jù)量，進(jìn)而減少IO操作次數(shù)。

1.純拉丁字符能表示的內(nèi)容，沒必要選擇 latin1 之外的其他字符編碼，因?yàn)檫@會(huì)節(jié)省大量的存儲(chǔ)空間
2.如果我們可以確定不需要存放多種語言，就沒必要非得使用UTF8或者其他UNICODE字符類型，這回造成大量的存儲(chǔ)空間浪費(fèi)
3.MySQL的數(shù)據(jù)類型可以精確到字段，所以當(dāng)我們需要大型數(shù)據(jù)庫中存放多字節(jié)數(shù)據(jù)的時(shí)候，可以通過對(duì)不同表不同字段使用不同的數(shù)據(jù)類型來較大程度減小數(shù)據(jù)存儲(chǔ)量，進(jìn)而降低 IO 操作次數(shù)并提高緩存命中率

三、適當(dāng)拆分
有些時(shí)候，我們可能會(huì)希望將一個(gè)完整的對(duì)象對(duì)應(yīng)于一張數(shù)據(jù)庫表，這對(duì)于應(yīng)用程序開發(fā)來說是很有好的，但是有些時(shí)候可能會(huì)在性能上帶來較大的問題。

當(dāng)我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時(shí)候，如果我們大部分訪問這張表的時(shí)候都不需要這個(gè)字段，我們就該義無反顧的將其拆分到另外的獨(dú)立表中，以減少常用數(shù)據(jù)所占用的存儲(chǔ)空間。這樣做的一個(gè)明顯好處就是每個(gè)數(shù)據(jù)塊中可以存儲(chǔ)的數(shù)據(jù)條數(shù)可以大大增加，既減少物理 IO 次數(shù)，也能大大提高內(nèi)存中的緩存命中率。

上面幾點(diǎn)的優(yōu)化都是為了減少每條記錄的存儲(chǔ)空間大小，讓每個(gè)數(shù)據(jù)庫中能夠存儲(chǔ)更多的記錄條數(shù)，以達(dá)到減少 IO 操作次數(shù)，提高緩存命中率。下面這個(gè)優(yōu)化建議可能很多開發(fā)人員都會(huì)覺得不太理解，因?yàn)檫@是典型的反范式設(shè)計(jì)，而且也和上面的幾點(diǎn)優(yōu)化建議的目標(biāo)相違背。

四、適度冗余

為什么我們要冗余？這不是增加了每條數(shù)據(jù)的大小，減少了每個(gè)數(shù)據(jù)塊可存放記錄條數(shù)嗎？
確實(shí)，這樣做是會(huì)增大每條記錄的大小，降低每條記錄中可存放數(shù)據(jù)的條數(shù)，但是在有些場(chǎng)景下我們?nèi)匀贿€是不得不這樣做：

被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨(dú)立小字段
這樣的場(chǎng)景由于每次Join僅僅只是為了取得某個(gè)小字段的值，Join到的記錄又大，會(huì)造成大量不必要的 IO，完全可以通過空間換取時(shí)間的方式來優(yōu)化。不過，冗余的同時(shí)需要確保數(shù)據(jù)的一致性不會(huì)遭到破壞，確保更新的同時(shí)冗余字段也被更新

五、盡量使用 NOT NULL

NULL 類型比較特殊，SQL 難優(yōu)化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異，會(huì)進(jìn)入索引中，但如果是一個(gè)組合索引，那么這個(gè)NULL 類型的字段會(huì)極大影響整個(gè)索引的效率。此外，NULL 在索引中的處理也是特殊的，也會(huì)占用額外的存放空間。
很多人覺得 NULL 會(huì)節(jié)省一些空間，所以盡量讓NULL來達(dá)到節(jié)省IO的目的，但是大部分時(shí)候這會(huì)適得其反，雖然空間上可能確實(shí)有一定節(jié)省，倒是帶來了很多其他的優(yōu)化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL，也是一個(gè)很好的表結(jié)構(gòu)設(shè)計(jì)優(yōu)化習(xí)慣。

您可能感興趣的文章: