MySQL支持的數據類型很多,那麼選擇合適的數據類型對於獲得高性能就至關重要。那麼就先瞭解各種類型的優缺點! 一、類型介紹 1、整型類型 整型類型有: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 。他們分別占8,16,24,32,64位儲存空間。可存儲的整數範圍為-2 ...
MySQL支持的數據類型很多,那麼選擇合適的數據類型對於獲得高性能就至關重要。那麼就先瞭解各種類型的優缺點!
一、類型介紹
1、整型類型
整型類型有: TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT 。他們分別占8,16,24,32,64位儲存空間。可存儲的整數範圍為-2^(N-1)到2^(N-1)-1,其中N是存儲空間的位數。
還可以將整數類型設為 UNSIGNED ,這樣幾乎可以是其範圍增大一倍。例如TINYINT範圍是-128 - 127,而TINYINT UNSIGNED的範圍是0-255。不過這兩種只是在範圍上有缺別,在存儲空間和性能上都是一樣的。
2、實數類型
對於實數類型,MySQL即支持精確類型(DECIMAL),也支持不精確類型(FLOAT,DOUBLE)。
DECIMAL類型允許最多存儲65位數字,因此它可以存儲比BIGINT還大的數字。而且在MySQL5.0或更高版本中,MySQL伺服器自身實現的DECIMAL的高精度計算。但相比較浮點類型,因為CPU直接支持原聲浮點計算,所以浮點類型計算會更快。
通常來說,浮點類型在存儲相同的範圍時,比DECIMAL使用更少的空間。FLOAT占用4個位元組存儲,DOUBLE占用8個位元組存儲,但相比FLOAT有更高的精度和更大的範圍。浮點類型存儲時在精度上會有各種各樣的問題,例如當你只把一列設為FLOAT,而沒有指定精度時,在存儲1234567.33會變成1234570。
DECIMAL所占的位元組比較特殊。它是在小數點前後分別使用每4個位元組存儲9位數字。具體看mysql手冊說法:
所以我們使用最多的DECIMAL(10,2)所占的位元組數為1+4+1+1=7個位元組(小數點占一個位元組)。
因為需要額外的計算開銷和存儲空間,所以應該儘量只在對小數進行精確計算時才使用DECIMAL--例如存儲財務數據。當你的數據量比較大的時候,為了避免浮點存儲計算不精確和DECIMAL精確計算代價高的問題,可以使用BIGINT代替DECIMAL,只需將原來需要存儲的小數乘以相應的倍數即可(BIGINT的範圍滿足你的需求)。
3、字元串類型
VARCHAR類型用於存儲可變長的字元串,所以它需要1或2個額外的位元組記錄字元串的長度:如果列的長度小於或等於255個位元組,則只使用1個位元組表示,否則使用2個位元組表示。例如varchar(10)就需要11個位元組,varchar(1000)則需要1002個位元組。
VARCHAR節省了存儲空間,所以對性能有所幫助。但由於行是變長的,在UPDATE時可能是原來的行更長,這就會導致需要做一些額外的工作。如果一個行占用的空間曾長,並且在頁內沒有更多的空間可以存儲,這是INNODB就會分裂當前頁來使行可以放進頁內。
下麵這些情況使用VARCHAR是合適的:
- 字元串列的最大長度比平均長度大很多
- 列的更新很少
- 使用了UTF-8這樣的字元集,每個字元都是用不同的位元組存儲
CHAR類型是定長的:MySQL總是根據定義字元串的長度分配足夠空間。因為CHAR會根據需要採用空格填充到字元串末尾,而且當你檢索時,CHAR會刪除末尾的空格。所以會有一個很有趣的事情發生,當你存儲一個"Johnson "到char(10)時,檢索出來的結果卻是"Johnson",因為MySQL並不知道這空格是你存的還是系統自動填充的。
CHAR很適合存儲很短的字元串或所有值都接近同一個長度。例如密碼的MD5值。
BLOB和TEXT都是為了存儲很大的數據類型而設計的字元串數據類型,分別採用二進位和字元方式存儲。而且當它們存儲的數據過大時,INNOSB會使用專門的‘外部’空間來存儲數據,此時每個值的行內僅存儲一個1 ~ 4個位元組的指針,然後在外部區域存儲真實的指。當需要對BLOB和TEXT排序時,它只對每個列的最前 max_sort_length 進行排序。這個值是可以配置的。
4、 枚舉類型
有時候可以使用枚舉類型代替常用的字元串類型。MySQL在內部會將每個值在列表中的位置保存為整數,並且在表的.frm文件中保存“數字-字元串”的映射關係。比如性別列,就可以用enum(男,女,未知),這裡有些人可能用TINYINT代替枚舉,實際我感覺這並不能帶來性能的優化,只不過你把“數字-字元串”的映射關係搬到你的業務邏輯中處理,如果你的註釋寫的不清晰,反而會給新人帶來困惑。
對於弱類型語言來說,枚舉並不是狠友好。舉個慄子:select id,name from users where id = 1; 和 select id,name from users where id = ‘1’; 得到的結果是一樣的。因為ENUM內部存儲是用的整型,所以在檢索ENUM類型時也可以用整數,例如 select id,name from users where sex = 1; 和 select id,name from users where sex = '男'; 可以得到相同的結果。但
select id,name from users where sex = ‘1’;
就檢索不到任何值。但如果你設計和使用的好,依然可以用。
5、日期和時間類型
日期和時間類型共有:DATE、TIME、YEAR、DATETIME和TIMESTAMP。其中DATE、TIME、YEAR分別占4,3,1位元組,並且存儲的時間格式為YYYY-MM-DD,HH:MM:SS,YYYY。這三種日期和時間類型相對用的比較少。這裡主要介紹DATETIME和TIMESTAMP的區別。
DATETIME存儲的範圍大,從1001到9999年,精度為秒,存儲格式為YYYY-MM-DD HH:MM:SS,占8個位元組的存儲空間。
TIMESTAMP存的範圍要小很多,從1970年到2038年(快超出範圍了),精度為秒,存儲格式也為YYYY-MM-DD HH:MM:SS,但只占4個位元組的存儲空間。TIMESTAMP預設為NOT NULL,並且當插入時沒有指定該列值時,會預設把MySQL當前時間插入進去。除了特殊行為,否則應該儘量使用TIMESTAMP,因為它比DATETIME占更少的存儲空間。
這裡要特殊說明的是,有時候有的人會將Unix時間戳存儲為整數(我們公司就是這麼乾滴),但這不會帶來任何收益。反而用整數保存時間戳的格式通常不方便處理,所以不推薦這樣做。
二、選擇優化的數據類型
- 更小的更長更好
一般情況下,應該儘量使用可以正確存儲數據的最小數據類型。例如只需要存0~200的值,顯然TINYINT UNSIGNED就足夠了。更小的數據類型更快,因為它們占用更少的磁碟、記憶體和CPU,並且處理時需要的CPU周期也更少。
- 簡單就好
簡單的數據類型的操作通常需要更少的CPU周期。例如應該用整型存儲IP而不是字元串。
- 儘量避免NULL
可為NULL的列會使用更多的存儲空間,在MySQL里也需要特殊的處理。但可為NULL的列被索引時,每個索引記錄需要一個額外的位元組。