深入理解MySQL資料型別的選擇優化

2022-08-10 14:04:16

前言

介紹了MySQL的常用資料型別的基本特性，以及資料型別的選擇優化。

MySQL資料型別是定義列中可以儲存什麼資料以及該資料實際怎樣儲存的基本規則，正確的選擇資料庫欄位的欄位型別對於資料庫效能有很大的影響。

1 整數型別

整數型別有五種：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別使用1、2、3、4、8位元組（8、16、24、32、64位元）的儲存空間。

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

所有的整數型別都預設是有符號數，即可正可負。所有的整數型別都可以設定unsigned屬性，這表示將該整數位段無符號化，即不允許負值，這種操作大致可以使正數的上限提高一倍，例如TINYINT，unsigned可以儲存的範圍是0-255。

有符號數和無符號數，使用相同的儲存空間，具有相同的效能，因此可以根據實際情況選擇。但請注意，MySQL資料庫中unsigned數的操作結果都是unsigned的，因此如果執行了計算，並且結果是負數，那麼最終結果將難以預測，慎用unsigned。

和整數的型別無關的是，整數的計算一般使用有64位元的BIGINT整數，即使在32位元的環境中即是如此。

MySQL可以為整數指定寬度，例如int(1)，但這對大多數應用都是無意義的：它不會限制值的合法範圍，只是規定了MySQL的一些互動工具如MySQL命令使用者端）顯示字元的個數，而不是指定的儲存大小。int(1)和int(10)對儲存和計算來說是相同的，但這個規則只適用於整數型別而不適用於varchar和char型別。

2 實數型別

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

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

MySQL既支援精確型別，也支援不精確型別。FLOAT和DOUBLE型別支援使用標準的浮點運算進行近似計算，稱為浮點數，FLOAT固定佔4個位元組，DOUBLE固定佔8個位元組，精度更高。和整數型別一樣，浮點只能選擇儲存型別，在計算時，MYSQL5使用DOUBLE作為內部浮點計算的型別。

DECIMAL型別則用於儲存精確的小數，在MYSQL5.0以及更高的版本中，DECIMAL型別支援精確計算。DECIMAL 的最大允許65個數位。DECIMAL列使用二進位制格式進行儲存，MYSQL5.0和更高版本中，會將數位打包儲存到一個二進位制字串中：每個值的小數點佔據一個位元組，每個值的整數和小數部分的儲存要求分別確定，每9個十進位制的數位被打包為4個位元組，剩餘的需要另外打包：

剩下的數位	所需位元組數
0	0
1-2	1
3-4	2
5-6	3
7-9	4

例如，DECIMAL (18, 9)小數點兩邊都有9位數，因此兩邊都需要4個位元組，另外小數點本身佔一個位元組，一共需要九個位元組。

CPU不支援DECIMAL的精確計算，精確計算是MySQL伺服器自身實現的，因此需要額外的的空間和計算開銷。CPU直接支援浮點運算，所以浮點運算更快，所以儘量只在需要對小數進行精確計算時使用DECIMAL型別（如與錢相關的資料）。

在資料量比較大的時候，可考慮使用BIGINT代替DECIMAL，此時只需根據小數位數乘以相應的倍數即可。比如，如果儲存的財務資料精確到萬分之一時，可以把所有的金額乘以一百萬，然後將結果存入BIGINT裡，這樣就可以避免浮點儲存計算不準確和DECIMAL精度計算代價高的問題。

3 字串型別

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

3.1 VARCHAR和CHAR型別

VARCHAR和CHAR是兩種最主要的資料型別，但是不同的儲存引擎會將這兩種資料型別以不同的樣式存入磁碟和記憶體中，下面是InnoDB或者MyISAM儲存引擎中VARCHAR和CHAR的介紹。

VARCHAR型別：

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

CHAR型別：

CHAR型別是定長的：MySQL總是根據定義的字串長度分配足夠的空間。
CHAR值會根據需要採用空格進行填充以方便比較（插入長度小於定義長度時）。
CHAR適合儲存很短的字串，或者所有值都接近同一個長度。例如，CHAR非常適合儲存密碼的MD5值，因為這是一個定長的值。
對於經常變更的資料，CHAR也比VARCHAR更好，因為定長的CHAR型別不容易產生碎片。
對於非常短的列，CHAR比VARCHAR在儲存空間上也更有效率，如儲存只有“Y”或"N"的值，如果採用單位元組字元集，使用CHAR(1)佔用一個位元組，而VARCHAR (1)佔用兩個位元組（一個位元組用於儲存長度）；
當儲存和檢索CHAR值時，MySQL會刪除所有末尾空格。

如下案例：

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

結果如下：

另外，資料如何儲存取決於儲存引擎，比如Memeory引擎只支援定長的行，即使有變長欄位，也會根據最大長度分配空間。但是對於填充和擷取空格的行為在不同的儲存引擎都是一樣的，因為這是在MySQL伺服器進行處理的。

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

3.1.1 最大長度

MySQL4.1版本之前，varchar(n)，指的是n個位元組，如果存放UTF8型別字元時，只能存n/3個字元（每個字元佔3個位元組）。

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

首先，一張表中所有欄位的長度總和不超過65535位元組。

CHAR型別最大長度為255個字元（字元數量，而非位元組數量）。

而VARCHAR型別的最大長度則是可變的，需要計算，VARCHAR的最大長度還與字元集有關。如果一張表有VARCHAR和CHAR型別的欄位分別一個，採用utf8mb4編碼（一個字元最多佔4個位元組），那麼所有欄位一共最多65535/4=16383.75=16383個字元長度，如果其中CHAR型別的欄位長度為5，那麼VARCHAR型別的最大長度則是16378。

字元型別若為latin1，每個字元最多佔1個位元組，最大長度不能超過65532。
字元型別若為gbk，每個字元最多佔2個位元組，最大長度不能超過32766。
字元型別若為utf8，每個字元最多佔3個位元組，最大長度不能超過21844。
字元型別若為utf8mb4，每個字元最多佔4個位元組，最大長度不能超過16383。

3.2 Binary和VarBinary型別

Binary和VarBinary用於儲存二進位制字串，儲存的是位元組碼而非字元。

MySQL在填充Binary時用的是(零位元組)而不是空格，在檢索時也不會去掉填充值（這是特別需要注意的）。

MySQL在比較Binary字串時，每次按一個位元組，並且是根據該位元組的數值進行比較；因此二進位制比較比字串的比較快。

3.3 BLOB和TEXT型別

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

實際上它們屬於不同的資料型別家族：字元型別是TINYTEXT、SMALLTEXT、TEXT、MEDIUMTEXT、LONGTEXT，對應的二進位制型別是 TINYBLOB、SMALLBLOB、BLOB、MEDIUMBLOB、LONGBLOB。BLOB和SMALLBLOB是同義詞，TEXT和SMALLTEXT是同義詞。

與其他型別不同，每個BLOB和TEXT值會被當成獨立的物件物件。當資料很大時，InnoDB會專門使用“外部”儲存區域儲存，此時每個值在行內需要1~4個位元組儲存一個指標，然後在外部儲存區域儲存實際的值。

BLOB和TEXT家族的僅有不同是BLOB型別儲存的是二進位制資料，沒有排序規則或者字元集，而TEXT型別有字元集和排序規則。

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

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

3.3 ENUM型別

有時可以使用ENUM代替常用的字串型別。列舉列可以把一些不重複的字串儲存成一個預定義的集合。

ENUM的儲存非常緊湊，會根據列表值的數量壓縮到一個或兩個位元組中。

ENUM在內部會將每個值在列表中的位置儲存為整數，並且在表的.frm檔案中儲存“數位-字串”對映關係的“查詢表”。

如下案例：

-- 建立表
CREATE TABLE enum_test(
 e enum('fish','apple','dog') NOT NULL
)
-- 插入列舉資料
INSERT INTO enum_test (e) VALUES('fish'),('dog'),('apple');

測試實際儲存的是數位：

select e+0 from enum_test;

結果如下：

列舉欄位支援同時採用字串和數值進行判斷和計算，如上面的sql，比如和數值型別進行比較。採用數值時，使用的列舉欄位的排序值，排序值從1開始！

需要注意的是，列舉欄位是按照內部儲存的整數而不是定義的字串進行排序的！

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

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

由於MySQL把每一個列舉值儲存為整數，在檢索時必須進行額外的查詢才能轉換為字串，所以會有一定的開銷。

4 日期和時間型別

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

DATETIME型別能儲存大範圍的值，從1001年~9999年，精度為秒，佔8個位元組的長度。它將時間格式封裝為YYYYMMDDHHMMSS的整數中，與時區無關。預設情況下，MySQL以一種可排序的格式顯示DATETIME值，比如“2019-11-12 13:56:45”。

TIMESTAMP型別的儲存的是1970年1月1日午夜以來的秒數（格林威治標準時間），只使用了4個位元組的儲存空間，因此它的範圍比DATETIME小得多：1970年~2038年。

TIMESTAMP列會預設設定為Not null屬性，所以在插入資料時，若不給值就會預設為當前時間戳。

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

5 位資料型別

MySQL的所有位型別，不管底層儲存和處理方式如何，從技術上來說都是字串型別。常見位資料型別是BIT和SET。

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

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

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

注意，MySQL將BIT當作字串型別，而不是數位型別。如果檢索BIT(1)的值時，結果是一個包含二進位制0或1的字串，而不是ASCII碼的“0”或“1”。然而，在數位上下文中檢索時，結果將是字串轉換成的數位。比如儲存一個值為b‘00111001’（等於十進位制57）到BIT(8)的列並直接檢索它，得到的內容是“00111001，而如果檢索的內容是“列+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

結果如下：

位型別的常見用法是：如果需要位一條資料儲存許多true/false值，則可合併這些列到一個SET資料型別的列。

6 選擇優化的資料型別

MySQL支援的資料型別非常多，選擇正確的資料型別對於獲得高效能至關重要，不管儲存哪種資料型別，下面幾個簡單的原則都有助於做出更好的選擇！

更小的通常更好。一般情況下，應該儘量使用可以正確儲存資料的最小資料型別，因為它們佔更少的磁碟，記憶體和CPU快取，處理時需要的CPU週期也更少。
簡單就好。簡單資料型別的操作通常需要更少的CPU週期，例如，整形比字元操作代價更低，因為字元集和校對規則（排序規則）是字元比較比整形比較更復雜。比如，使用MySQL內建型別（date、time、datetime）而不是字串來儲存日期和時間，另外一個是用整型來儲存IP地址。
儘量避免NULL。通常在定義表欄位時，如果沒有指定列為NOT NULL，預設都是允許為NULL的。
- 如果查詢中包含可為NULL的列，對MySQL來說更難優化，因為可為NULL的列使得索引、索引統計和值的比較都更復雜。可為NULL的列會佔用更多的儲存空間，當可為NULL的列被索引時，每個索引記錄需要一個額外的位元組，在MyISAM裡甚至還可能導致固定大小的索引（例如只有一個整數列的索引）變成可變大小的索引。
- 通常情況下最好指定列為NOT NULL，但一般把可為NULL的列改為NOT NULL帶來的效能提升比較小，所以在調優時，沒有必要首先對此下手。
- 但是對於NULL有一個例外是，InnoDB僅會使用單獨的位(bit)儲存NULL值（即是否為NULL的標記位），因此NULL值所以對於稀疏資料（大部分資料為NULL，只有少部分非NULL的資料）有很好的空間效率，但這不使適於MyISAM。

6.1 特殊的資料型別

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