MySQL 基礎資料類型最佳化（如何選擇資料類型）

標籤：sam   小數   進位   mint   影響   函數   tab   空間   資料   

前言：     最近在看高效能 MySQL，記錄寫學習筆記：          高效能 MySQL 學習筆記（二） Schema與資料類型最佳化     筆記核心內容：MySQL 如何選擇正確的資料類型，各資料類型的差異；      // 不要小看 MySQL 資料類型對效能的重要性，         當你的系統體量到達一定程度時，就知道這裡的效能差異了。  一、選擇最佳化的資料類型      // 更小的通常越好     盡量選擇使用可以正確儲存資料的最小資料類型。更小的資料類型通常更快，     因為它們佔用更少的磁碟、記憶體和 CPU 緩衝，並且處理時需要的 CPU 週期也更少；      // 簡單最好     整型比字元型要好，兩個例子，應該使用 MySQL 內建的類型來儲存日期而不是字串；     使用整形儲存 IP 位址。因為字元的校對、定序要複雜。      // 盡量避免 NULL     通常情況下最好指定列為 NOT NULL，除非真的需要儲存 NULL 值；     如果查詢中包含可為 NULL 的列，對 MySQL 來說更難最佳化，     因為可為 NULL 的列是的索引、索引統計和值比較，都更為複雜。     允許為 NULL 的列，儲存空間上佔用更多，同時 MySQL 還需要對它特殊處理。 整數類型：      可以使用的幾種整數類型：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT     分別使用8，16，24，32，64位儲存空間。      整數類型有可選的 UNSIGNED 屬性，表示不允許負值，這大致可以使正數的上限提高一倍。     整數計算一般使用 64 位元的 BIGINT 整數，即使 32 位環境也是如此（一些彙總函式是例外，他們使用DECIMAL或DOUBLE進行計算）。      MySQL 可以為整數類型指定寬度，例如 INT(11)，對大多數應用這是沒有意義的；     它並不會限制值的合法範圍。     對於儲存和計算來說，INT(1) 和 INT(20) 是相同的。 實數類型：      FLOAT 和 DOUBLE 類型支援使用標準的浮點運算進行近似計算。     DECIMAL 類型用於儲存精確的小數，MySQL 伺服器自身實現了 DECIMAL 的高精度計算，     相對而言，CPU直接支援原生浮點計算，所以浮點計算明顯更快。      浮點和 DECIMAL 類型都可以指定精度，對於 DECIMAL 可以指定小數點前後所允許的最大位元，     這會影響列的空間消耗。浮點類型在儲存同樣範圍的值時，     通常比 DECIMAL 使用更少的空間，所以應該盡量只在對小數進行精確計算時才使用。      DECIMAL(18, 9) 小數點兩點各儲存 9 個數字，一共使用 9 個位元組；     DECIMAL 可以儲存的整數比 BIGINT 還要大；      浮點型以及 DECIMAL 在存取時，都需要額外的空間和計算的開銷，     所以應該盡量只對小數進行精確計算時才使用 DECIAML；     如果資料量比較大的時候，可以考慮使用 BIGINT 代替 DECIAML。      // 這就是為什麼的資料庫（猜的因為介面都是分為單位）使用 BIGINT 來做處理，         目的就是為了節省 MySQL 計算帶來的額外損耗； 字串類型：      VARCHAR 儲存可變長字串。 當列的更新很少，使用了像 UTF-8 這樣複雜的字元集時，使用 VARCHAR 儲存。     CHAR 類型是定長的，當儲存 MD5 值，定長的值，或經常變更的資料時，用 CHAR 儲存，因為這樣不容易產生片段。       VARCHAR 類型儲存可變長字串，他比定長類型更節省空間的，VARCHAR 節省了儲存空間，所以對效能也有協助，但由於是變長的，     在 UPDATE 時可能使行變得比原來長，這就導致需要做額外的工作。           儲存引擎儲存 CHAR 或者 VARCHAR 值的方式在記憶體中和硬碟上可能不一樣。     如果一個行佔用的空間增長，並在業內沒有更多足夠的空間可以儲存時，     MyISAM 儲存引擎會將拆分成不同的片段儲存；而 InnerDB 則需要分裂頁放進頁內才行； BLOB 和 TEXT 類型      MySQL 把每個 BLOB 和 TEXT 值當作一個獨立的對象處理。     兩者都是為了儲存很大資料而設計的字串類型，分別採用二進位和字元方式儲存。 ENUM 枚舉類型：          有時候可以使用枚舉代替常用的字串類型，枚舉把一些不重複的字串儲存成一個預定義的集合。     枚舉在儲存時是（數字-字串）的形式。     將可最佳化的資料類型欄位改為 ENUM 枚舉類型後，關聯查詢效能可提升將近一倍。（書中列子暫不細說）          SELECT SQL_NO_CACHE COUNT(*) FROM tables     JOIN tables2 USING(day, account...); 日期和時間類型：          MySQL 使用許多類型儲存日期和時間值，例如 Year 和 Date，MySQL 能儲存的最小時間粒紋為秒。      MySQL 提供兩種相似的日期類型：DATETIME 和 TIMESTAMP，     在某些場合一個比另一個工作的更好。     DATETIME 這個類型儲存的最大值從1001到9999年，精度為秒。     TIMESTAMP 使用 4 個位元組保持日期，預設 NOT NULL。      除了特殊行為之外，通常也應該盡量使用 TIMESTAMP 類型。 位類型：      BIT 最大長度 64 個位。MySQL 把 BIT 當作字串類型，而不是數字類型。     // 很多開發人員喜歡使用 BIT 作為 BOOL 類型來儲存某些資料，true/false 這種，但是最好避免使用 BIT 類型。       如果想在一個 bit 的儲存空間中儲存一個 true/false 的話，建議使用一個可以為空白的 CHAR(0) 類型。      同時也可以考慮使用 SET 類型，SET 類型 MySQL 內部是以一系列打包位的集合來表示的，     MySQL 有像 FIND_IN_SET 和 FIELD 這樣的函數，方便在查詢中使用。     他的主要缺點是改變列的代價太高，也無法在SET上通過索引尋找。

MySQL 基礎資料類型最佳化（如何選擇資料類型）