MySQL詳解（22）------------表結構最佳化，mysql------------

MySQL詳解（22）------------表結構最佳化，mysql------------

很多人都將 資料庫設計範式 作為資料庫表結構設計“聖經”，認為只要按照這個範式需求設計，就能讓設計出來的表結構足夠最佳化，既能保證效能優異同時還能滿足擴充性要求。殊不知，在N年前被奉為“聖經”的資料庫設計3範式早就已經不完全適用了。此課時整理了一些比較常見的資料庫表結構設計方面的最佳化技巧，希望對大家有用。

由於MySQL資料庫是基於行（Row）儲存的資料庫，而資料庫操作 IO 的時候是以 page（block）的方式，也就是說，如果我們每條記錄所佔用的空間量減小，就會使每個page中可存放的資料行數增大，那麼每次 IO 可訪問的行數也就增多了。反過來說，處理相同行數的資料，需要訪問的 page 就會減少，也就是 IO 操作次數降低，直接提升效能。此外，由於我們的記憶體是有限的，增加每個page中存放的資料行數，就等於增加每個記憶體塊的快取資料量，同時還會提升記憶體換中資料命中的幾率，也就是快取命中率。

資料類型選擇

資料庫操作中最為耗時的操作就是 IO 處理，大部分資料庫操作 90% 以上的時間都花在了 IO 讀寫上面。所以儘可能減少 IO 讀寫量，可以在很大程度上提高資料庫操作的效能。

我們無法改變資料庫中需要儲存的資料，但是我們可以在這些資料的儲存方式方面花一些心思。下面的這些關於欄位類型的最佳化建議主要適用於記錄條數較多，資料量較大的情境，因為精細化的資料類型設定可能帶來維護成本的提高，過度最佳化也可能會帶來其他的問題：

字元編碼

字元集直接決定了資料在MySQL中的儲存編碼方式，由於同樣的內容使用不同字元集表示所佔用的空間大小會有較大的差異，所以通過使用合適的字元集，可以協助我們儘可能減少資料量，進而減少IO操作次數。 

適當拆分

有些時候，我們可能會希望將一個完整的對象對應於一張資料庫表，這對於應用程式開發來說是很有好的，但是有些時候可能會在效能上帶來較大的問題。

當我們的表中存在類似於 TEXT 或者是很大的 VARCHAR類型的大欄位的時候，如果我們大部分訪問這張表的時候都不需要這個欄位，我們就該義無反顧的將其拆分到另外的獨立表中，以減少常用資料所佔用的儲存空間。這樣做的一個明顯好處就是每個資料區塊中可以儲存的資料條數可以大大增加，既減少物理 IO 次數，也能大大提高記憶體中的快取命中率。

上面幾點的最佳化都是為了減少每條記錄的儲存空間大小，讓每個資料庫中能夠儲存更多的記錄條數，以達到減少 IO 操作次數，提高快取命中率。下面這個最佳化建議可能很多開發人員都會覺得不太理解，因為這是典型的反範式設計，而且也和上面的幾點最佳化建議的目標相違背。

適度冗餘

為什麼我們要冗餘？這不是增加了每條資料的大小，減少了每個資料區塊可存放記錄條數嗎？

確實，這樣做是會增大每條記錄的大小，降低每條記錄中可存放資料的條數，但是在有些情境下我們仍然還是不得不這樣做：被頻繁引用且只能通過 Join 2張（或者更多）大表的方式才能得到的獨立小欄位這樣的情境由於每次Join僅僅只是為了取得某個小欄位的值，Join到的記錄又大，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來最佳化。不過，冗餘的同時需要確保資料的一致性不會遭到破壞，確保更新的同時冗餘欄位也被更新

盡量使用 NOT NULL

NULL 類型比較特殊，SQL 難最佳化。雖然 MySQL NULL類型和 Oracle 的NULL 有差異，會進入索引中，但如果是一個複合式索引，那麼這個NULL 類型的欄位會極大影響整個索引的效率。此外，NULL 在索引中的處理也是特殊的，也會佔用額外的存放空間。
很多人覺得 NULL 會節省一些空間，所以盡量讓NULL來達到節省IO的目的，但是大部分時候這會適得其反，雖然空間上可能確實有一定節省，倒是帶來了很多其他的最佳化問題，不但沒有將IO量省下來，反而加大了SQL的IO量。所以盡量確保 DEFAULT 值不是 NULL，也是一個很好的表結構設計最佳化習慣。

著作權聲明：歡迎轉載，希望在你轉載的同時，添加原文地址，謝謝配合