前言

介紹了MySQL的常用數(shù)據(jù)類型的基本特性，以及數(shù)據(jù)類型的選擇優(yōu)化。

MySQL數(shù)據(jù)類型是定義列中可以存儲什么數(shù)據(jù)以及該數(shù)據(jù)實際怎樣存儲的基本規(guī)則，正確的選擇數(shù)據(jù)庫字段的字段類型對于數(shù)據(jù)庫性能有很大的影響。

1 整數(shù)類型

整數(shù)類型有五種：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別使用1、2、3、4、8字節(jié)（8、16、24、32、64位）的存儲空間。

可以存儲的值的范圍從-2^(N-1)到2^(N-1)-1，其中N是存儲空間的位數(shù)，比如TINYINT，它的存儲范圍就是-128-127。

所有的整數(shù)類型都默認是有符號數(shù)，即可正可負。所有的整數(shù)類型都可以設(shè)置unsigned屬性，這表示將該整數(shù)字段無符號化，即不允許負值，這種操作大致可以使正數(shù)的上限提高一倍，例如TINYINT，unsigned可以存儲的范圍是0-255。

有符號數(shù)和無符號數(shù)，使用相同的存儲空間，具有相同的性能，因此可以根據(jù)實際情況選擇。但請注意，MySQL數(shù)據(jù)庫中unsigned數(shù)的操作結(jié)果都是unsigned的，因此如果執(zhí)行了計算，并且結(jié)果是負數(shù)，那么最終結(jié)果將難以預(yù)測，慎用unsigned。 

和整數(shù)的類型無關(guān)的是，整數(shù)的計算一般使用有64位的BIGINT整數(shù)，即使在32位的環(huán)境中即是如此。

MySQL可以為整數(shù)指定寬度，例如int(1)，但這對大多數(shù)應(yīng)用都是無意義的：它不會限制值的合法范圍，只是規(guī)定了MySQL的一些交互工具如MySQL命令客戶端）顯示字符的個數(shù)，而不是指定的存儲大小。int(1)和int(10)對存儲和計算來說是相同的，但這個規(guī)則只適用于整數(shù)類型而不適用于varchar和char類型。

2 實數(shù)類型

實數(shù)就是帶有小數(shù)部分的數(shù)字，然而它們不止是為了存儲小數(shù)部分，也可以使用DECIMAL存儲比BIGINT還大的整數(shù)。實數(shù)類型有三種：FLOAT、DOUBLE、DECIMAL。

實數(shù)都可以指定精度，例如DECIMAL(18,9)，這表示值存儲的有效位數(shù)為18，并且小數(shù)點后可以存儲的位數(shù)為9，因此整數(shù)部分的位數(shù)同樣也是9。MySQL 在存儲值時執(zhí)行舍入，因此如果將 89.0009 插入FLOAT(6,3) 列，則近似結(jié)果為 89.001。

MySQL既支持精確類型，也支持不精確類型。FLOAT和DOUBLE類型支持使用標(biāo)準(zhǔn)的浮點運算進行近似計算，稱為浮點數(shù)，F(xiàn)LOAT固定占4個字節(jié)，DOUBLE固定占8個字節(jié)，精度更高。和整數(shù)類型一樣，浮點只能選擇存儲類型，在計算時，MYSQL5使用DOUBLE作為內(nèi)部浮點計算的類型。

DECIMAL類型則用于存儲精確的小數(shù)，在MYSQL5.0以及更高的版本中，DECIMAL類型支持精確計算。DECIMAL 的最大允許65個數(shù)字。DECIMAL列使用二進制格式進行存儲，MYSQL5.0和更高版本中，會將數(shù)字打包保存到一個二進制字符串中：每個值的小數(shù)點占據(jù)一個字節(jié)，每個值的整數(shù)和小數(shù)部分的存儲要求分別確定，每9個十進制的數(shù)字被打包為4個字節(jié)，剩余的需要另外打包：

例如，DECIMAL (18, 9)小數(shù)點兩邊都有9位數(shù)，因此兩邊都需要4個字節(jié)，另外小數(shù)點本身占一個字節(jié)，一共需要九個字節(jié)。

CPU不支持DECIMAL的精確計算，精確計算是MySQL服務(wù)器自身實現(xiàn)的，因此需要額外的的空間和計算開銷。CPU直接支持浮點運算，所以浮點運算更快，所以盡量只在需要對小數(shù)進行精確計算時使用DECIMAL類型（如與錢相關(guān)的數(shù)據(jù)）。

在數(shù)據(jù)量比較大的時候，可考慮使用BIGINT代替DECIMAL，此時只需根據(jù)小數(shù)位數(shù)乘以相應(yīng)的倍數(shù)即可。比如，如果存儲的財務(wù)數(shù)據(jù)精確到萬分之一時，可以把所有的金額乘以一百萬，然后將結(jié)果存入BIGINT里，這樣就可以避免浮點存儲計算不準(zhǔn)確和DECIMAL精度計算代價高的問題。

3 字符串類型

MySQL支持多種字符串類型，主要的有：VARCHAR、CHAR、BLOB、TEXT、ENUM等。

3.1 VARCHAR和CHAR類型

VARCHAR和CHAR是兩種最主要的數(shù)據(jù)類型，但是不同的存儲引擎會將這兩種數(shù)據(jù)類型以不同的樣式存入磁盤和內(nèi)存中，下面是InnoDB或者MyISAM存儲引擎中VARCHAR和CHAR的介紹。

VARCHAR類型：

• VARCHAR類型用于存儲可變長度的字符串，是最常見的字符串?dāng)?shù)據(jù)類型，它比定長類型的CHAR更節(jié)省空間，因為它僅使用必要的空間。實際長度由最長的行的大小和使用的字符集確定。
• VARCHAR需要一個或者兩個額外的字節(jié)記錄字符串長度，VARCHAR列的最大長度小于或等于255個字節(jié)時需要一個字節(jié)來記錄，大于255時需要兩個字節(jié)來記錄。例如VARCHAR(10)的列需要11個字節(jié)的存儲空間，VARCHAR(1000)的列則需要1002個字節(jié)。（字符串長度不是指的字節(jié)數(shù)，而是字符數(shù)，因此多字節(jié)字符會需要更多的空間存儲，比如中文）。
• 雖然VARCHAR節(jié)省了存儲空間，但是在UPDATE列時如果使得行變得比原來更長，這就可能導(dǎo)致需要額外的工作，如果一個行占用的空間增長，并且在頁內(nèi)沒有更多空間可以存儲時，InnoDB引擎就會做頁分裂這個額外的操作來使行可以放進頁內(nèi)，而MyISAM則會將行拆成不同的片段存儲。
• 如果字符串列的最大長度可能比平均長度大很多，并且列的更新很少，那么建議使用VARCHAR類型。
• 在MYSQL 5.0及更高版本，MYSQL在使用VARCHAR存儲和檢索時會保留字符串末尾的空格，但在4.1或更老的版本，MYSQL會trim剔除末尾空格。
• 如果使用InnoDB引擎，那么過長的VARCHAR會被存儲為BLOB。

CHAR類型：

• CHAR類型是定長的：MySQL總是根據(jù)定義的字符串長度分配足夠的空間。
• CHAR值會根據(jù)需要采用空格進行填充以方便比較（插入長度小于定義長度時）。
• CHAR適合存儲很短的字符串，或者所有值都接近同一個長度。例如，CHAR非常適合存儲密碼的MD5值，因為這是一個定長的值。
• 對于經(jīng)常變更的數(shù)據(jù)，CHAR也比VARCHAR更好，因為定長的CHAR類型不容易產(chǎn)生碎片。
• 對于非常短的列，CHAR比VARCHAR在存儲空間上也更有效率，如存儲只有“Y”或"N"的值，如果采用單字節(jié)字符集，使用CHAR(1)占用一個字節(jié)，而VARCHAR (1)占用兩個字節(jié)（一個字節(jié)用于存儲長度）；
• 當(dāng)存儲和檢索CHAR值時，MySQL會刪除所有末尾空格。

如下案例：

CREATE TABLE test(a VARCHAR(4), b CHAR(4));
INSERT INTO test VALUES ('ab ', 'ab ');
SELECT CONCAT(a, '+'), CONCAT(b, '+') FROM test;

結(jié)果如下： 

另外，數(shù)據(jù)如何存儲取決于存儲引擎，比如Memeory引擎只支持定長的行，即使有變長字段，也會根據(jù)最大長度分配空間。但是對于填充和截取空格的行為在不同的存儲引擎都是一樣的，因為這是在MySQL服務(wù)器進行處理的。

另外，雖然VARCHAR(5)和VARCHAR(200)存儲"hello"的磁盤空間開銷是一樣的，然是使用更短的列仍然有很大的優(yōu)勢。因為更長的列會消耗更多的內(nèi)存，因為mysql通常會分配固定大小的內(nèi)存塊來保存內(nèi)部值，尤其是使用內(nèi)存臨時表來進行排序或操作時特別糟糕，在利用磁盤臨時表進行排序時也同樣糟糕。因此最好的策略是只分配真正需要的空間。

3.1.1 最大長度

MySQL4.1版本之前，varchar(n)，指的是n個字節(jié)，如果存放UTF8類型字符時，只能存n/3個字符（每個字符占3個字節(jié)）。

MySQL4.1版本開始，varchar(n)，指的是n個字符，無論存放的是數(shù)字、字母還是漢字，都可以存放3個，但是字節(jié)數(shù)不能超過最大限制。

首先，一張表中所有字段的長度總和不超過65535字節(jié)。

CHAR類型最大長度為255個字符（字符數(shù)量，而非字節(jié)數(shù)量）。

而VARCHAR類型的最大長度則是可變的，需要計算，VARCHAR的最大長度還與字符集有關(guān)。如果一張表有VARCHAR和CHAR類型的字段分別一個，采用utf8mb4編碼（一個字符最多占4個字節(jié)），那么所有字段一共最多65535/4=16383.75=16383個字符長度，如果其中CHAR類型的字段長度為5，那么VARCHAR類型的最大長度則是16378。

• 字符類型若為latin1，每個字符最多占1個字節(jié)，最大長度不能超過65532。
• 字符類型若為gbk，每個字符最多占2個字節(jié)，最大長度不能超過32766。
• 字符類型若為utf8，每個字符最多占3個字節(jié)，最大長度不能超過21844。
• 字符類型若為utf8mb4，每個字符最多占4個字節(jié)，最大長度不能超過16383。

3.2 Binary和VarBinary類型

Binary和VarBinary用于存儲二進制字符串，存儲的是字節(jié)碼而非字符。

MySQL在填充Binary時用的是\0(零字節(jié))而不是空格，在檢索時也不會去掉填充值（這是特別需要注意的）。

MySQL在比較Binary字符串時，每次按一個字節(jié)，并且是根據(jù)該字節(jié)的數(shù)值進行比較；因此二進制比較比字符串的比較快。

3.3 BLOB和TEXT類型

BLOB和TEXT都是為存儲很大的數(shù)據(jù)而設(shè)計的，分別采用二進制和字符方式存儲，都是變長字符串類型。

實際上它們屬于不同的數(shù)據(jù)類型家族：字符類型是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，對應(yīng)的二進制類型是 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB和SMALLBLOB是同義詞，TEXT和SMALLTEXT是同義詞。

與其他類型不同，每個BLOB和TEXT值會被當(dāng)成獨立的對象對象。當(dāng)數(shù)據(jù)很大時，InnoDB會專門使用“外部”存儲區(qū)域存儲，此時每個值在行內(nèi)需要1~4個字節(jié)存儲一個指針，然后在外部存儲區(qū)域存儲實際的值。

BLOB和TEXT家族的僅有不同是BLOB類型存儲的是二進制數(shù)據(jù)，沒有排序規(guī)則或者字符集，而TEXT類型有字符集和排序規(guī)則。

MySQL只會對BLOB和TEXT列中最前max_sort_length字節(jié)進行排序，而不是整個字符串。如果只排序前面一小部分字符，則可以減小max_sort_length的值，或者使用order by substring(列名，length)。

MYSQL不能將BLOB和TEXT列全部長度字符串進行索引，也不能用這些索引消除排序。

3.3 ENUM類型

有時可以使用ENUM代替常用的字符串類型。枚舉列可以把一些不重復(fù)的字符串存儲成一個預(yù)定義的集合。

ENUM的存儲非常緊湊，會根據(jù)列表值的數(shù)量壓縮到一個或兩個字節(jié)中。

ENUM在內(nèi)部會將每個值在列表中的位置保存為整數(shù)，并且在表的.frm文件中保存“數(shù)字-字符串”映射關(guān)系的“查找表”。

如下案例：

-- 創(chuàng)建表
CREATE TABLE enum_test(
 e enum('fish','apple','dog') NOT NULL
)
-- 插入枚舉數(shù)據(jù)
INSERT INTO enum_test (e) VALUES('fish'),('dog'),('apple');

測試實際存儲的是數(shù)字：

select e+0 from enum_test;  

結(jié)果如下： 

 枚舉字段支持同時采用字符串和數(shù)值進行判斷和計算，如上面的sql，比如和數(shù)值類型進行比較。采用數(shù)值時，使用的枚舉字段的排序值，排序值從1開始！

需要注意的是，枚舉字段是按照內(nèi)部存儲的整數(shù)而不是定義的字符串進行排序的！ 

一種繞過這種限制的方式是按照需要的順序來定義枚舉列的值，另外也可以使用FIELD()函數(shù)顯式的指定排序順序，但這會導(dǎo)致MySQL無法利用索引消除排序。 

枚舉的字符串列表是固定的，添加或者刪除枚舉字符串必須使用ALTER TABLE這樣的DDL語句，因此對于未來可能會改變的字符串，使用枚舉不是一個好主意。

由于MySQL把每一個枚舉值保存為整數(shù)，在檢索時必須進行額外的查找才能轉(zhuǎn)換為字符串，所以會有一定的開銷。

4 日期和時間類型

MySQL有很多日期和時間類型，比如DATE、DATETIME、TIMESTAMP、TIME、YEAR。可以存儲的最小時間粒度是秒。

DATETIME類型能保存大范圍的值，從1001年~9999年，精度為秒，占8個字節(jié)的長度。它將時間格式封裝為YYYYMMDDHHMMSS的整數(shù)中，與時區(qū)無關(guān)。默認情況下，MySQL以一種可排序的格式顯示DATETIME值，比如“2019-11-12 13:56:45”。

TIMESTAMP類型的存儲的是1970年1月1日午夜以來的秒數(shù)（格林威治標(biāo)準(zhǔn)時間），只使用了4個字節(jié)的存儲空間，因此它的范圍比DATETIME小得多：1970年~2038年。

TIMESTAMP列會默認設(shè)置為Not null屬性，所以在插入數(shù)據(jù)時，若不給值就會默認為當(dāng)前時間戳。

目前，MySQL沒有提供合適的數(shù)據(jù)類型以存儲比秒更小粒度的日期和時間格式，但可以使用其他方法，比如使用BIGINT存儲微秒級別的時間戳，比如用DOUBLE存儲秒之后的小數(shù)部分。

5 位數(shù)據(jù)類型

MySQL的所有位類型，不管底層存儲和處理方式如何，從技術(shù)上來說都是字符串類型。常見位數(shù)據(jù)類型是BIT和SET。

在MySQL5.0之前，BIT是TINYINT的同義詞，但5.0之后有了新的含義。

BIT(1)表示一個包含單個位的字段，BIT(2)則表示兩個位的字段，最大長度為64位。

不同的存儲引擎對BIT 有不同的存儲方式。MyISAM會打包存儲所有BIT列，比如17個單獨的BIT列只需要17位存儲，只需要3個字節(jié)的空間。而Memory和InnoDB等存儲引擎，則是為每個BIT列使用一個足夠存儲的最小整數(shù)類型來存放，所以不能節(jié)省存儲空間。

注意，MySQL將BIT當(dāng)作字符串類型，而不是數(shù)字類型。如果檢索BIT(1)的值時，結(jié)果是一個包含二進制0或1的字符串，而不是ASCII碼的“0”或“1”。然而，在數(shù)字上下文中檢索時，結(jié)果將是字符串轉(zhuǎn)換成的數(shù)字。比如存儲一個值為b‘00111001’（等于十進制57）到BIT(8)的列并直接檢索它，得到的內(nèi)容是“00111001，而如果檢索的內(nèi)容是“列+0”，那么得到的值是57。

如下案例：

CREATE TABLE `bits` (
  `bitss` bit(8) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `bits`(`bitss`) VALUES (b'00111001');
INSERT INTO `bits`(`bitss`) VALUES (b'111');

使用如下查詢：

select bitss,bitss+0 from bits  

結(jié)果如下： 

位類型的常見用法是：如果需要位一條數(shù)據(jù)保存許多true/false值，則可合并這些列到一個SET數(shù)據(jù)類型的列。

6 選擇優(yōu)化的數(shù)據(jù)類型

MySQL支持的數(shù)據(jù)類型非常多，選擇正確的數(shù)據(jù)類型對于獲得高性能至關(guān)重要，不管存儲哪種數(shù)據(jù)類型，下面幾個簡單的原則都有助于做出更好的選擇！

• 更小的通常更好。一般情況下，應(yīng)該盡量使用可以正確存儲數(shù)據(jù)的最小數(shù)據(jù)類型，因為它們占更少的磁盤，內(nèi)存和CPU緩存，處理時需要的CPU周期也更少。
• 簡單就好。簡單數(shù)據(jù)類型的操作通常需要更少的CPU周期，例如，整形比字符操作代價更低，因為字符集和校對規(guī)則（排序規(guī)則）是字符比較比整形比較更復(fù)雜。比如，使用MySQL內(nèi)建類型（date、time、datetime）而不是字符串來存儲日期和時間，另外一個是用整型來存儲IP地址。
• 盡量避免NULL。通常在定義表字段時，如果沒有指定列為NOT NULL，默認都是允許為NULL的。
  • 如果查詢中包含可為NULL的列，對MySQL來說更難優(yōu)化，因為可為NULL的列使得索引、索引統(tǒng)計和值的比較都更復(fù)雜?？蔀镹ULL的列會占用更多的存儲空間，當(dāng)可為NULL的列被索引時，每個索引記錄需要一個額外的字節(jié)，在MyISAM里甚至還可能導(dǎo)致固定大小的索引（例如只有一個整數(shù)列的索引）變成可變大小的索引。
  • 通常情況下最好指定列為NOT NULL，但一般把可為NULL的列改為NOT NULL帶來的性能提升比較小，所以在調(diào)優(yōu)時，沒有必要首先對此下手。
  • 但是對于NULL有一個例外是，InnoDB僅會使用單獨的位(bit)存儲NULL值（即是否為NULL的標(biāo)記位），因此NULL值所以對于稀疏數(shù)據(jù)（大部分數(shù)據(jù)為NULL，只有少部分非NULL的數(shù)據(jù)）有很好的空間效率，但這不使適于MyISAM。

6.1 特殊的數(shù)據(jù)類型

IPv4地址經(jīng)常被人用varchar(15)來存儲，但是它實際上是一個32位無符號的整數(shù)，不是字符串，小數(shù)點將地址分四段的表示方式只是為了讓人容易閱讀，所以應(yīng)該用無符號整型來存儲IP地址（如果不使用無符號INT，則長度不夠），MYSQL用INET_ATON() 和INET_NTOA()用于這兩種表示轉(zhuǎn)換。

• 將IP地址轉(zhuǎn)換為整型可以使用 select inet_aton(ip) from xxx;
• 將整型轉(zhuǎn)換為IP地址可以使用 select inet_ntoa(intip) from xxx;

到此這篇關(guān)于深入理解MySQL數(shù)據(jù)類型的選擇優(yōu)化的文章就介紹到這了,更多相關(guān)MySQL數(shù)據(jù)選擇優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論