MySQL資料庫性能優化之表結構優化

這是 MySQL資料庫性能優化專題 系列的第二篇文章：MySQL 資料庫性能優化之表結構優化

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

　　資料類型選擇

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

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

　　1.數字類型：非萬不得已不要使用DOUBLE，不僅僅隻是存儲長度的問題，同時還會存在精确性的問題。同樣，固定精度的小數，也不建議使用DECIMAL，建議乘以固定倍數轉換成整數存儲，可以大大節省存儲空間，且不會帶來任何附加維護成本。對于整數的存儲，在資料量較大的情況下，建議區分開 TINYINT / INT / BIGINT 的選擇，因為三者所占用的存儲空間也有很大的差别，能确定不會使用負數的字段，建議添加unsigned定義。當然，如果資料量較小的資料庫，也可以不用嚴格區分三個整數類型。

　　2.字元類型：非萬不得已不要使用 TEXT 資料類型，其處理方式決定了他的性能要低于char或者是varchar類型的處理。定長字段，建議使用 CHAR 類型，不定長字段盡量使用 VARCHAR，且僅僅設定适當的最大長度，而不是非常随意的給一個很大的最大長度限定，因為不同的長度範圍，MySQL也會有不一樣的存儲處理。

　　3.時間類型：盡量使用TIMESTAMP類型，因為其存儲空間隻需要 DATETIME 類型的一半。對于隻需要精确到某一天的資料類型，建議使用DATE類型，因為他的存儲空間隻需要3個位元組，比TIMESTAMP還少。不建議通過INT類型類存儲一個unix timestamp 的值，因為這太不直覺，會給維護帶來不必要的麻煩，同時還不會帶來任何好處。

　　4.ENUM & SET：對于狀态字段，可以嘗試使用 ENUM 來存放，因為可以極大的降低存儲空間，而且即使需要增加新的類型，隻要增加于末尾，修改結構也不需要重建表資料。如果是存放可預先定義的屬性資料呢?可以嘗試使用SET類型，即使存在多種屬性，同樣可以遊刃有餘，同時還可以節省不小的存儲空間。

　　5.LOB類型：強烈反對在資料庫中存放 LOB 類型資料，雖然資料庫提供了這樣的功能，但這不是他所擅長的，我們更應該讓合适的工具做他擅長的事情，才能将其發揮到極緻。在資料庫中存儲 LOB 資料就像讓一個多年前在學校學過一點Java的營銷專業人員來寫 Java 代碼一樣。

　　字元編碼

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

　　1.純拉丁字元能表示的内容，沒必要選擇 latin1 之外的其他字元編碼，因為這會節省大量的存儲空間。

　　2.如果我們可以确定不需要存放多種語言，就沒必要非得使用UTF8或者其他UNICODE字元類型，這回造成大量的存儲空間浪費。

　　3.MySQL的資料類型可以精确到字段，是以當我們需要大型資料庫中存放多位元組資料的時候，可以通過對不同表不同字段使用不同的資料類型來較大程度減小資料存儲量，進而降低 IO 操作次數并提高緩存命中率。

　　适當拆分

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

　　當我們的表中存在類似于 TEXT 或者是很大的 VARCHAR類型的大字段的時候，如果我們大部分通路這張表的時候都不需要這個字段，我們就該義無反顧的将其拆分到另外的獨立表中，以減少常用資料所占用的存儲空間。這樣做的一個明顯好處就是每個資料塊中可以存儲的資料條數可以大大增加，既減少實體 IO 次數，也能大大提高記憶體中的緩存命中率。

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

　　适度備援

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

　　确實，這樣做是會增大每條記錄的大小，降低每條記錄中可存放資料的條數，但是在有些場景下我們仍然還是不得不這樣做：

　　1.被頻繁引用且隻能通過 Join 2張(或者更多)大表的方式才能得到的獨立小字段。

　　2.這樣的場景由于每次Join僅僅隻是為了取得某個小字段的值，Join到的記錄又大，會造成大量不必要的 IO，完全可以通過空間換取時間的方式來優化。不過，備援的同時需要確定資料的一緻性不會遭到破壞，確定更新的同時備援字段也被更新。

　　盡量使用 NOT NULL