MySQL之schema設計最佳化

標籤：

良好的邏輯設計和實體設計是高效能的基石，應該根據系統要執行的查詢語句來設計 schema。這往往需要權衡各種因素。

例如：反範式的設計可以加快某些類型的查詢，但同時可能使另一些類型的查詢變慢。比如添加計數表和匯總表是一種很好的最佳化查詢的方式，

但是這些表的維護成本會很高。MySQL專屬的特性和實現細節對效能影響也很大。

 

選擇最佳化的資料類型的簡單原則：

　　1.更小的通常更好

　　　　一般情況下，應該盡量使用可以正確儲存資料的最小資料類型。

　　2.簡單就好

　　　　單一資料型別的操作通常需要更少的cpu周期。

　　3.盡量避免NULL

　　　　如果查詢包含可為NULL的列，對mysql來說更難最佳化，因為可為NULL的列使得索引，索引統計，值比較都更複雜。

　　　　可為NULL的列會使用更多的儲存空間，在MySQL裡也需要特殊處理。

 

schema設計的第一步，那就是建表，建表的第一步呢就是設計欄位，然而瞭解mysql的資料類型又對設計欄位有莫大的協助。

那我們先來瞭解mysql的基礎資料型別 (Elementary Data Type)吧。

 

1.整數類型

　　TINYINT, SMALLINT,MEDIUMINT,INT,BIGINT

　　8，16，24，32，64，位儲存空間。

　　MySQL可為認證類型制定寬度，例如INT（11），但對大多數應用這是沒意義的；它不會限制值合法範圍。對於儲存和計算來說

　　INT(1) 和 INT(20)是相同的

2.實數類型

　　FLOAT,DOUBLE,DECIMAL(精確計算用的)

3.字串類型

　　VARCHARE

　　　　最常見的字串類型。它比定長類型更節省空間的，因為它僅適用必要的空間。有一種情況例外，如果MySQL表使用ROW_FORMAT=FIXED建立的話。

　　　　VARCHAR需要適用1或2個額外位元組記錄字串的長度。如果列的最大長度小於等於255，則使用一個位元組記錄長度。

　　CHAR

 　　　　是定長的。MySQL總是根據定義的字串分配足夠的空間。當儲存CHAR值時，MySQL會刪除所有的末尾空格。

　　　　CHAR值會根據需要採用空格填充以方便比較。

　　　　CHAR適合儲存很短的字串。對於經常變更的資料，CHAR也比VARCHAR更好，因為定長CHAR類型不容易產生片段。

　　BLOB和TEXT

　　　　BLOB和TEXT都是為儲存很大資料而設計的字串資料型別，分別採用二進位和字元方式儲存。

　　使用枚舉（ENUM）代替字串類型

4.日期和時間類型

　　DATETIME

　　　　這個類型能儲存大範圍的值。從1001年到9999年，精度為妙。

　　TIMESTAMP

 　　　　儲存空間小，並且會根據時區變化，有特殊的自動更新能力。精度為妙。

　　如果要儲存比秒更小粒度的時間時，可以用bigint類型儲存微秒層級的時間戳記。

5.位元據類型

　　BIT

 

標識符的選擇：

　　整數類型

　　　　整數通常是識別欄位的最好選擇。因為他們很快，並且可以使用auto_increment

　　enum 和 set 類型

　　

　　字串類型

　　　　如果可能，盡量避免使用字串類型作為標識符，因為它們很消耗空間，並且通常比數字類型慢。

　　　　尤其在MyISAM表裡使用字串作為識別欄位時要特別小心。MyISAM預設對字串使用壓縮索引，這會使查詢很慢。

　　　　對於完全‘隨機‘的字串也需要多加註意，例如md5(),sha1(),uuid()產生的字串。

　　　　這些函數產生的新值會任意分布在很大的空間內，這會導致insert以及一些select語句變得很慢：

　　　　　　1.因為插入值會隨機寫到索引的不同位置，所以使得insert語句更慢。這會導致頁分裂，磁碟隨機訪問，以及對於聚簇儲存引擎產生聚簇索引片段。

　　　　　　2.select語句會變得更慢，因為邏輯上相鄰的行為分布在磁碟和記憶體的不同地方。

　　　　　　3.隨機值會導致緩衝對所有類型的查詢語句效果都很差。

 

schema設計中的陷阱

　　1.太多的列

　　2.太多的關聯

　　3.全能的枚舉

　　

範式和反範式

　　三大範式：　　

　　　　1.必須有主鍵，列不可分　　

　　　　2.當一個表是複合主鍵時，非主鍵的欄位不依賴於部分主鍵(即必須依賴於全部的主鍵欄位)

　　　　3.關係模式R（U，F）中的所有非主屬性對任何候選關鍵字都不存在傳遞依賴

　　反範式，顧名思義，不遵守三大範式。

　　首先我們來看看範式的優缺點：

　　優點：

　　　　範式化的更新操作通常比反範式快。

　　　　當資料較好地範式化時，就只有很少或者沒有重複資料，所以只需要修改更少的資料

　　　　範式化的表通常很小，可以更好的放在記憶體裡，所以執行操作會更快。

　　　　很少有多餘的資料意味著檢索列表資料更少需要DISTINCT或者GROUP BY語句。

　　缺點：

　　　　過度依賴關聯。這不但代價昂貴，也可能使一些索引策略無效。

 

　　反範式優缺點：

　　　　因為所有的資料在一張表中，可以很好的避免關聯。

　　　　如果不需要關聯，則對大部分查詢最差的情況，即使沒有使用索引，是全表掃描，當資料比記憶體大時這可能比關聯要快得多，

　　　　因為這樣避免了隨機I/O。

　　　　單獨的表能更好的使用索引策略。

　混用範式和反範式才是schema的主流。

 

緩衝表和匯總表

　　方便查詢，不易維護。

 

物化視圖

　　

計數器表

MySQL之schema設計最佳化