你好，我是李輝。
這是專欄的第一篇文章，在專欄的前幾個小節(jié)，我會先和你先聊一聊 MySQL 的數(shù)據(jù)類型。了解 MySQL 的數(shù)據(jù)類型是開發(fā)人員在使用 MySQL 數(shù)據(jù)庫的時候，必備的基礎(chǔ)技能之一。也正因為此，這部分知識也是面試官面試的時候?qū)覍姨峒暗母哳l問題，所以盡量不要在這個地方栽跟頭。這一小節(jié)我們就先來說說在數(shù)據(jù)建模設(shè)計時使用非常頻繁的字符串類型 – VARCHAR。
為了在閱讀時不引起歧義，本專欄中我們先做一個簡單的約定：
- MySQL 數(shù)據(jù)庫版本 5.7
- 使用 InnoDB 存儲引擎
- 默認(rèn)隔離級別是 Repeatable Read
- 默認(rèn)使用 UTF8 編碼
本專欄中的所有文章都使用上述約定，有不同的地方我們再單獨做說明。
1. VARCHAR (50) 中的 50 到底是能存 50 個字還是 50 個字節(jié)？
我們先做個簡單的實驗：
mysql> create database imooc_mysql_interview;
Query OK, 1 row affected (0.00 sec)
mysql> use imooc_mysql_interview
Database changed
mysql> create table varchar_test(col_1 varchar(8));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into varchar_test values('ABCDEFGH'),('數(shù)一數(shù)是不是八個');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from varchar_test;
+--------------------------+
| col_1 |
+--------------------------+
| ABCDEFGH |
| 數(shù)一數(shù)是不是八個 |
+--------------------------+
2 rows in set (0.00 sec)
從上述實驗的結(jié)果可知，顯然是能存 8 個字符而不是 8 個字節(jié)，也就是說 VARCHAR 的括號中的數(shù)字代表的是字符。如果存的是字節(jié)，由于中文、英文和 emoji 在 utf-8 中的字節(jié)數(shù)都不一樣，勢必會給編程造成一定的困擾。
1.1 CHAR (50) 和 VARCHAR (50) 有什么區(qū)別？
剛工作不久的同學(xué)可能會有這個疑問，為什么大家都喜歡用 VARCHAR，CHAR 卻很少見，存的長度不是都一樣嗎？
首先要說明的一點，CHAR 和 VARCHAR 在存儲方式上存在著差異：CHAR 是定長字符，MySQL 數(shù)據(jù)庫會根據(jù)建表時定義的長度給它分配相應(yīng)的存儲空間。而 VARCHAR 是可變長度字符的數(shù)據(jù)類型，在存儲時只使用必要的空間。
舉個例子，假如一張表上有兩列，分別是 CHAR (20) 和 VARCHAR (20)，我們插入一個字符串 “abcd”，在數(shù)據(jù)庫中存儲時，CHAR 會使用全部的 20 個字符的長度，不足的部分用空格填充，而 VARCHAR 僅僅就只使用 4 個字符的長度。
其次，由于 CHAR 數(shù)據(jù)類型的這個特性，在將數(shù)據(jù)寫入表中時，如果字符串尾部存在空格，會被自動刪除，而 VARCHAR 數(shù)據(jù)類型會保留這個空格。在一些特殊場景中要注意這個問題。所以推薦你使用 CHAR 數(shù)據(jù)類型存儲一些固定長度的字符串，比如身份證號、手機號、性別等。
最后，CHAR 和 VARCHAR 的存儲長度不同。CHAR 數(shù)據(jù)類型可定義的最大長度是 255 個字符，而 VARCHAR 根據(jù)所使用的字符集不同，最大可以使用 65535 個字節(jié)。注意我剛說的 VARCHAR 的最大長度不是字符數(shù)而是字節(jié)數(shù)，那么新的問題來了，我們接著往下看。
2. VARCHAR 能使用的最大長度是多少？
由于 VARCHAR 能存儲的最大長度會因為你在表定義中使用的字符集不同而發(fā)生變化，下面我們就以業(yè)內(nèi)使用較多的 UTF8 這個字符集作為前提條件來做個分析。
我們再看一個例子：
mysql> create table varchar_test2(col_1 varchar(65535))charset=utf8 engine=innodb;
ERROR 1074 (42000): Column length too big for column 'col_1' (max = 21845); use BLOB or TEXT instead
mysql> create table varchar_test2(col_1 varchar(21845))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test2(col_1 varchar(21844))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
由于 UTF8 字符集中一個漢字占用 3 個字節(jié)，因此我們能創(chuàng)建的最大長度理論上應(yīng)該是 21845（65535/3=21845）。但是為什么 varchar (21845) 仍然報錯，而使用 varchar (21844) 卻創(chuàng)建成功？
這是因為觸及了 MySQL 數(shù)據(jù)庫定義的 VARCHAR 的最大行長度限制。
雖然 MySQL 官方定義了最大行長度是 65535 個字節(jié)，但是因為還有別的開銷，我們能使用的最大行長度只有 65532。
剛剛的實驗中，我們把 VARCHAR 的字段長度改成 21844 后騰出來 3 個字節(jié) (65535-21844*3=3)，因此可以創(chuàng)建成功。
因此在使用了 UTF-8 的字符集時，VARCHAR 的最大長度為 21844。
另外提醒你注意一下，做表設(shè)計時不要肆意的放飛自我，在單表上設(shè)計出一堆較大的 VARCHAR 字段，在失去了擴展性時以后可能會哭。因為 MySQL 的最大行長度限制不只是 1 個 VARCHAR 列，而是所有列的長度總和。
我們再做個實驗觀察一下：
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21844), primary key(id))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21843), primary key(id))charset=utf8 engine=innodb;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
mysql> create table varchar_test3(id int auto_increment, col_2 varchar(21842), primary key(id))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> create table varchar_test4(id int auto_increment, col_2 varchar(21842), col_3 smallint, primary key(id))charset=utf8 engine=innodb;
Query OK, 0 rows affected (0.02 sec)
你可以自行計算一下，在上面的這個實驗中，所有列的字節(jié)數(shù)加起來也是不能超過 65532 的，超出時會報錯。
你可能還有一個疑惑，在 UTF8 中英文字符不是只占用 1 個字節(jié)嗎，那 varchar (21844) 使用了 65532 個字節(jié)，如果我只存英文字符的話是不是就能存 65532 個？
然而并非如此，VARCHAR (M) 中的 M 仍然表示的是 M 個字符，而不是 M 個字節(jié)。
只不過它在存儲的時候仍然是按實際字節(jié)數(shù)來存的。所以在 UTF8 的字符編碼下，我們能使用的最大長度就只有 21844 個 VARCHAR 字符。
如果要存儲更多的字符該怎么辦呢？使用 TEXT、BLOB 這樣的大對象列類型。因為這些大對象可以把數(shù)據(jù)存放到溢出頁面上，也就是 DBA 們常說的行溢出。
3. VARCHAR 數(shù)據(jù)類型優(yōu)化
下面我們再聊一聊 VARCHAR 的性能優(yōu)化相關(guān)的一些事情。
3.1 只分配所需要用的 VARCHAR 空間
雖然使用 VARCHAR (50) 和 VARCHAR (1000) 存儲‘a(chǎn)bcd’的存儲空間開銷是一樣的，但是當(dāng)你在讀取數(shù)據(jù)時，把這些數(shù)據(jù)讀取到內(nèi)存的過程中，MySQL 數(shù)據(jù)庫需要分配相應(yīng)大小的內(nèi)存空間來存放數(shù)據(jù)。
所以更大的 VARCHAR 列在讀取時要使用更大的內(nèi)存空間，即使它實際上只存儲了一丁點數(shù)據(jù)。
并且在操作這個表的過程中，如果遇到一些聚合（GROUP BY）或排序（ORDER BY）的操作，需要調(diào)用內(nèi)存臨時表或磁盤臨時表時，性能會更加糟糕。
因此，在保留一定冗余的前提下，只給 VARCHAR 分配恰到好處的空間使用。
3.2 VARCHAR 的字段過長也會導(dǎo)致行溢出
剛剛你不是說了 TEXT 和 BLOB 會溢出嗎，VARCHAR 也會溢出？
是的。你在給 MySQL 的數(shù)據(jù)表加索引時，可能遇到過要在大的 VARCHAR 字段上創(chuàng)建索引卻發(fā)現(xiàn)只能創(chuàng)建前綴索引的問題。那這個其實是和行溢出有關(guān)。
mysql> create index idx_col_2 on varchar_test4(col_2);
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create index idx_col_2 on varchar_test4(col_2(3072));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
mysql> create index idx_col_2 on varchar_test4(col_2(1024));
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> drop index idx_col_2 on varchar_test4;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx_col_2 on varchar_test4(col_2(1025));
ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes
上面實驗可以看到，最大的索引長度不能超過 3072 字節(jié)（在 UTF-8 的字符集中對應(yīng)的是 1024 個字符）。
由于行溢出是另外一個話題，我們今天就不過多贅述，我們只說說大字段和行溢出造成的性能問題。
- 大字段會占用較大的內(nèi)存，使得 MySQL 內(nèi)存利用率較差
- 行溢出的數(shù)據(jù)在讀取時需要多一個 IO，造成 IO 效率下降
- 行溢出會使用 uncompress BLOB page，造成 InnoDB 的表空間越來越大
- InnoDB 中的大字段在做更新和刪除操作時，只能進行悲觀操作，這會造成并發(fā)性能下降。
另外，因為 InnoDB 的數(shù)據(jù)頁默認(rèn)是 16K，每個頁中至少存放 2 行數(shù)據(jù)，因此建議 VARCHAR 字段的總長度不要超過 8K。
4. 小結(jié)
今天我給你介紹了 MySQL 的 VARCHAR 數(shù)據(jù)類型，希望你對 VARCHAR 數(shù)據(jù)類型能有更多的了解。
由于篇幅的限制，諸如行溢出這些知識點就不再做深入的探討。
不過對于開發(fā)人員來說，了解到這里也就基本足夠了。如果你對沒有展開的那些知識點感興趣，可以自行搜索研究一番。
最后留一個問題吧，使用 UTF8 字符集時我們最大可以存儲 21844 個字符，那么如果是 UTF8MB4 呢？
感謝你的閱讀，歡迎你給我留言，也歡迎你分享給更多的朋友一起閱讀。